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
0
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
#1
0
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