Help with a query...compare consecutive values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a query...compare consecutive values

Author

Topic

alinamiha
Starting Member

2 Posts

Posted - 2009-04-15 : 17:30:28

Hello,
I'm new to sql 2005...and I need some help...
I have for example this table:

CREATE TABLE #Salary
( EmployeeId INT ,
WeekEndingDate DATETIME,
Days INT,
WeeklyPay MONEY
)
INSERT INTO [#Salary] ( [EmployeeId],[WeekEndingDate],[Days],[WeeklyPay])
SELECT 1,'2008-07-04 00:00:00.000',4,2000 UNION ALL
SELECT 1,'2008-07-11 00:00:00.000',5,2000 UNION ALL
SELECT 1,'2008-07-11 00:00:00.000',4,2000 UNION ALL
SELECT 1,'2008-08-08 00:00:00.000',5,2000 UNION ALL
SELECT 1,'2008-08-15 00:00:00.000',5,2000 UNION ALL
SELECT 1,'2008-08-22 00:00:00.000',3,1800 UNION ALL
SELECT 1,'2008-09-19 00:00:00.000',5,1500 UNION ALL
SELECT 1, '2008-09-26 00:00:00.000',5,1000 UNION ALL
SELECT 1,'2008-10-03 00:00:00.000',5,1200 UNION ALL
SELECT 1,'2008-10-10 00:00:00.000',5,700 UNION ALL
SELECT 1,'2008-11-25 00:00:00.000',2,1200 UNION ALL
SELECT 1,'2008-12-25 00:00:00.000',2,1200

SELECT * FROM [#Salary]
---------------------------------
Next I build an table with the result wanted:
DECLARE @Result AS TABLE
( employeeId INT, Weekendingdate DATETIME,Days INT,WeeklyPay MONEY
)
INSERT INTO @Result
SELECT *
FROM #Salary

UPDATE @Result SET [WeeklyPay]=
( CASE WHEN [Weekendingdate]>'8/15/2008' AND [Weekendingdate]<='10/10/2008' THEN [WeeklyPay]/2 -- there are 2 "5" values entered consecutive
WHEN [Weekendingdate]>'10/10/2008' THEN 0 -- i have 4 time "5" entered consecutive
ELSE weeklypay
END
)
FROM @Result

SELECT * FROM @Result

---- i may have more than one employee in #Salary table
---- for each employee i want to select in the end something like this:
SELECT
employeeId,
'8/22/2008' AS StartHalfPay , -- the begining of half pay
'10/10/2008' AS EndHalfPay, -- the end of half pay
'11/25/2008' AS StartNoPay, -- the begining of no pay
'12/25/2008' AS EndNoPay, -- the end of no pay
3100 AS TotalHalfPay
FROM @Result
GROUP BY [employeeId]
--------
how should I write to compare if i have two "5" consecutive entries ?

Thanks,
Alina

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 14:07:36

i think you need another column to store the intermediate result (weekpay1) and then get date ranges based on that.


UPDATE @Result SET [WeeklyPay1]=
( CASE WHEN [Weekendingdate]>'8/15/2008' AND [Weekendingdate]<='10/10/2008' THEN [WeeklyPay]/2 -- there are 2 "5" values entered consecutive
WHEN [Weekendingdate]>'10/10/2008' THEN 0 -- i have 4 time "5" entered consecutive
ELSE weeklypay
END
)
FROM @Result

SELECT
employeeId,
MIN(CASE WHEN WeeklyPay1=WeeklyPay/2 THEN [WeekEndingDate] ELSE NULL END) AS StartHalfPay , -- the begining of half pay
MAX(CASE WHEN WeeklyPay1=WeeklyPay/2 THEN [WeekEndingDate] ELSE NULL END) AS EndHalfPay, -- the end of half pay
MIN(CASE WHEN WeeklyPay=0 THEN [WeekEndingDate] ELSE NULL END) AS StartNoPay, -- the begining of no pay
MAX(CASE WHEN WeeklyPay1=0 THEN [WeekEndingDate] ELSE NULL END) AS EndNoPay, -- the end of no pay
SUM(CASE WHEN WeeklyPay1=WeeklyPay/2 THEN [WeeklyPay1] ELSE 0 END) AS TotalHalfPay
FROM @Result
GROUP BY [employeeId]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources