CODEWITHSUNDEEP
sql-serverdate
Sindhu
Sindhu

Reputation: 23

To run a query multiple times in sql for different date ranges (acquired from a separate table) without using a stored procedure?

I have only select access to the database. This query gets the result (1 row) for the entire year.

SELECT 
    COUNT(U.ssnnoncumhrs) as [Non Cums Hrs] 
FROM
    (SELECT TOP 100 PERCENT
         (CASE 
             WHEN (([Social Security Number (SSN)] = LEAD([Social Security Number (SSN)], 1, 0) OVER (ORDER BY [Social Security Number (SSN)], sequencedate DESC, [YTD Productive Hours] DESC, [YTD Uncapped gross FUTA Wages] DESC)) 
                  AND ([YTD Productive Hours] < LEAD([YTD Productive Hours], 1, 0) OVER (ORDER BY [Social Security Number (SSN)], sequencedate DESC, [YTD Productive Hours] DESC, [YTD Uncapped gross FUTA Wages] DESC)))
                THEN [Social Security Number (SSN)] 
          END) as ssnnoncumhrs
      FROM
          dbo.staging P, dbo.tblBatch T 
      WHERE
          P.BatchId = T.BatchId 
          AND P.FileId = T.FileId 
          AND sequencedate >= '1/1/2016'
          AND sequencedate <='12/31/2016' 
      ORDER BY
          [Social Security Number (SSN)], sequencedate DESC,
          [YTD Productive Hours] DESC, [YTD Uncapped gross FUTA Wages] DESC)  as U

Instead, I want this query to be executed for different date ranges.

dbo.tblbatch has a list of dates. This query needs to run between 1st and 2nd date, then 2nd and 3rd date and so on until it covers all the dates in the list and gives all the results as though I had manually changed the from and to dates each time.

Upvotes: 0

Views: 1142

Answers (1)

DataWrangler
DataWrangler

Reputation: 2165

Something like this would work

CREATE TABLE #Test
(RowID INT IDENTITY(1,1),
DateRange DATETIME
)

--Insert the date from date range table
INSERT INTO #Test
SELECT DISTINCT TOP 10 Inserted FROM Table1

--Variables decleration
DECLARE @MaxID INT
DECLARE @MinID INT
DECLARE @DATE DATETIME

SET @MinID = 1
SELECT @MaxID = MAX(RowID) FROM #Test



WHILE @MaxID >= @MinID
BEGIN
    SELECT @DATE = DateRange FROM #Test WHERE RowID = @MinID

    SELECT TOP 1 * FROM Table2 WHERE Inserted = @DATE

    SET @MinID = @MinID + 1
END

Upvotes: 1

Related Questions