在不使用存储过程的情况下，在sql中多次运行查询以获取不同的日期范围（从单独的表中获取）？

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

我只选择访问数据库。此查询获取整年的结果(1行)。

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.

dbo.tblbatch有一个日期列表。此查询需要在第1和第2个日期之间运行,然后在第2个和第3个日期之间运行,依此类推,直到它涵盖列表中的所有日期,并提供所有结果,就好像我每次都手动更改了from和to日期一样。

1 个解决方案

#1

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