how to compare values in t-sql 2005

Question

Post reply

how to compare values in t-sql 2005

alinusha_sibi

Old Hand

Points: 329
More actions
April 15, 2009 at 5:10 am

#95123

hello all,

can anyone help me with writing a query? i have to compare values and print the last value from the two consecutive ones.

04/18/08; 05/02/08; 05/09/08 ;05/16/08 -- weekending

5 ; 5 ; 2 ; 4 -- days

i need to print 05/02/08 and sum(days)=10

or

05/09/08; 05/16/08 ;05/23/08; 07/18/08; 08/22/08 ;08/29/08 ;10/10/08 ;10/17/08 ;11/28/08

1 ; 5 ; 5 ; 4 ; 5 ; 5 ; 5 ; 5 ; 2

result: 05/23/08 and sum(days)=10

08/29/08 and sum(days)=10

10/17/08 and sum(days)=20

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hello

Can you please provide a table creation script and some sample data in an easily-cunsumable format? This will show you roughly how to do it:

CREATE TABLE #sample (id INT, Ecode1 VARCHAR(5), Ecode2 VARCHAR(5), Ecode3 VARCHAR(5), Ecode4 VARCHAR(5), Ecode5 VARCHAR(5))



INSERT INTO #sample (id, Ecode1, Ecode2, Ecode3, Ecode4, Ecode5)

SELECT 1, NULL, NULL, NULL, 'E8800', 'E8800' UNION ALL

SELECT 2, NULL, NULL, NULL, 'E8800', 'E8490' UNION ALL

SELECT 3, NULL, NULL, NULL, 'E8889', 'E8499' UNION ALL

SELECT 4, NULL, NULL, NULL, 'E8490', 'E8499' UNION ALL

SELECT 5, NULL, NULL, NULL, NULL, NULL

SELECT * FROM #sample

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

alinusha_sibi Old Hand Points: 329 More actions · Answer 2

hi,

here is the 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]

how should i do to print WeeklyPay/2 between 2008-08-15 and 2008-09-26 for WeeklyPay

and 0 between 2008-11-25 and 2008-12-25 for WeeklyPay. And I also need to print StartPeriod & EndPeriod for each case

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thank you for providing the sample data. Your requirement is very difficult to understand, there is probably a language difference. Can you please provide a sample of what you expect your results to be? Also, any values which you would expect to use to obtain your results, such as start date and end date.

Many thanks

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

alinusha_sibi Old Hand Points: 329 More actions · Answer 4

Hello,

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 ?

:unsure:

Thanks,

Alina

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 5

I hope this helps. There is a faster way to do this with a single UPDATE to @Results but there are some issues with that approach I am not going to try to discuss here. These issues involve the fact that an UPDATE to a table may or may not update the rows in sequence. I urge you to keep checking this article[/url] for the revision Jeff Moden has promised. (The article covers running totals, and you are basically wanting to keep a running total of consecutive 5-day rows.)

The solution below first added a rowID column to @Results, and also added an ORDER BY to the insert so we could be sure of sequence in which the rows are loaded. We can now be sure that the previous row has a rowID equal to one less than the current rowID. It then uses a CTE to CROSS APPLY a query that tells you how many consecutive 5-day rows you have. (You can uncomment the select statement after the CTE to see the results it produces.) Finally, we simply update the five-day rows, joining them to the CTE and selecting half pay or no pay depending on the number of consecutive "five" rows are calculated by the CTE.

Please let me know if you have any questions, or if this is not quite the result you expected.

Someone else may chime in and give you a quicker result using the single UPDATE. I had that solution coded, but I'm not certain if I had the potential sequence error covered, so I will not post it now.



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,rowID INT IDENTITY(1,1)

)

INSERT INTO @Result

SELECT *

FROM #Salary

ORDER BY EmployeeId,WeekEndingDate

;with cteTrick as

(select *

 from @Result r1

 cross apply (select case when r1.days <> 5 then 0

				          else r1.rowID - MAX(r3.rowID)

				          end as fives

				from @Result r3 where r3.employeeId = r1.employeeID

				and r3.rowID   5) as dt

)

-- select * from cteTrick

UPDATE @Result

	SET [WeeklyPay]= CASE when fives between 2 and 3 then r1.WeeklyPay/2

			          when fives >= 4 then 0

			          else  r1.WeeklyPay

			          end

FROM @Result r1

JOIN cteTrick r2 on r2.rowID = r1.rowID

where r2.fives > 1

SELECT * FROM @Result

drop table #salary

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

I'm thinking that once you can identify things that have more than 1 consecutive 5, you could probably write code to do anything you wanted. Here's the code that takes care of that hardpart using a new technique. Please not the last 3 columns of the result...

[font="Courier New"]DROP TABLE #Salary

GO

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 UNION ALL

SELECT 2,'2008-07-04 00:00:00.000',4,2000 UNION ALL

SELECT 2,'2008-07-11 00:00:00.000',5,2000 UNION ALL

SELECT 2,'2008-07-11 00:00:00.000',4,2000 UNION ALL

SELECT 2,'2008-08-08 00:00:00.000',5,2000 UNION ALL

SELECT 2,'2008-08-15 00:00:00.000',5,2000 UNION ALL

SELECT 2,'2008-08-22 00:00:00.000',3,1800 UNION ALL

SELECT 2,'2008-09-19 00:00:00.000',5,1500 UNION ALL

SELECT 2, '2008-09-26 00:00:00.000',5,1000 UNION ALL

SELECT 2,'2008-10-03 00:00:00.000',5,1200 UNION ALL

SELECT 2,'2008-10-10 00:00:00.000',5,700 UNION ALL

SELECT 2,'2008-11-25 00:00:00.000',2,1200 UNION ALL

SELECT 2,'2008-12-25 00:00:00.000',2,1200

;WITH

cteGroup AS

(--==== Create groupings by calculating the difference between two different Row Number methods

SELECT *,

ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY WeekEndingDate,Days)

- ROW_NUMBER() OVER (PARTITION BY EmployeeID, Days ORDER BY WeekEndingDate,Days) AS SeqGroup

FROM #Salary

)

--===== Using those groupings, calculate a sequence in the group and the total items in each group (SequenceCount)

SELECT *,

ROW_NUMBER() OVER (PARTITION BY EmployeeID, SeqGroup ORDER BY WeekEndingDate,Days) AS Sequence,

COUNT(*) OVER (PARTITION BY EmployeeID, SeqGroup) AS SequenceCount

FROM cteGroup

ORDER BY EmployeeID, WeekEndingDate, Days

[/font]

========================================================================================================================================

And, here's the result...

[font="Courier New"]EmployeeId WeekEndingDate Days WeeklyPay SeqGroup Sequence SequenceCount

----------- ----------------------- ---- --------- -------- -------- -------------

1 2008-07-04 00:00:00.000 4 2000.00 0 1 2

1 2008-07-11 00:00:00.000 4 2000.00 0 2 2

1 2008-07-11 00:00:00.000 5 2000.00 2 1 3

1 2008-08-08 00:00:00.000 5 2000.00 2 2 3

1 2008-08-15 00:00:00.000 5 2000.00 2 3 3

1 2008-08-22 00:00:00.000 3 1800.00 5 1 1

1 2008-09-19 00:00:00.000 5 1500.00 3 1 4

1 2008-09-26 00:00:00.000 5 1000.00 3 2 4

1 2008-10-03 00:00:00.000 5 1200.00 3 3 4

1 2008-10-10 00:00:00.000 5 700.00 3 4 4

1 2008-11-25 00:00:00.000 2 1200.00 10 1 2

1 2008-12-25 00:00:00.000 2 1200.00 10 2 2

2 2008-07-04 00:00:00.000 4 2000.00 0 1 2

2 2008-07-11 00:00:00.000 4 2000.00 0 2 2

2 2008-07-11 00:00:00.000 5 2000.00 2 1 3

2 2008-08-08 00:00:00.000 5 2000.00 2 2 3

2 2008-08-15 00:00:00.000 5 2000.00 2 3 3

2 2008-08-22 00:00:00.000 3 1800.00 5 1 1

2 2008-09-19 00:00:00.000 5 1500.00 3 1 4

2 2008-09-26 00:00:00.000 5 1000.00 3 2 4

2 2008-10-03 00:00:00.000 5 1200.00 3 3 4

2 2008-10-10 00:00:00.000 5 700.00 3 4 4

2 2008-11-25 00:00:00.000 2 1200.00 10 1 2

2 2008-12-25 00:00:00.000 2 1200.00 10 2 2

(24 row(s) affected)

[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions