How do I write this code? 1

[Chumley40]

I have two datasets.
The first one is a list of clientid's for a given time period. There are thousands.
The second dataset receives the selected clientid's and runs a query based on them. I was using a multiparameter and checking several at a time, but that is cumbersome. Instead, I want to use two parameters to choose my records:
RecordStart
RecordStop

I want to be able to enter which records to pull. For example RecordStart 10 to RecordStop 20 would pull row 10 to 20 from my list dataset. If I chose 100 to 220, it would pull rows 100 to 120.

How do I code for this in my main dataset? I am a at a loss. Right now my where statement parses the multiple clientid's in my where statement. I am just not sure how to do a where that does what I explained above. Anyone?

 

[GHolden]

Hi, one way to select a range within your dataset is to add row numbers using OVER to your SQL then filter by row number.

E.g.

WITH NUMBERED_ROWS
	AS 
		(
		SELECT 
			row_number() OVER (ORDER BY FAULT_NUMBER) AS ROW_NUM,
			FAULT_NUMBER,
			REPORTED_DATE_TIME			
		FROM  property.FAULT   
        )

You would need to replace the FAULT_NUMBER, REPORTED_DATE_TIME, property.FAULT to your own Table / Fields

You can the select records between two points using something like the following (pass 100 and 120 as variables)

SELECT  
	ROW_NUM,
	FAULT_NUMBER, 
	REPORTED_DATE_TIME
FROM NUMBERED_ROWS 
WHERE ROW_NUM >= 100 and ROW_NUM <= 120

Hope this helps.

There are two ways to write error-free programs; only the third one works.

 

[Chumley40]

Very nice--thank you!