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.
Author |
Topic |
vjs2445
Starting Member
16 Posts |
Posted - 2012-01-26 : 15:12:12
|
I am looking for help to build a query.For one particular date condition and by each department: If there are two records then combine those two records fields in one record asDept, Date, Value1, Value2, Value3, Value4where Value1 and Value2 are first recordand Value 3 and Value4 are from secod record. Note Value3 and Value4 are same column but 2nd row for same date condition.In some case there will be always one row instead of two then I want to display Null for Value3 and Value4 such asDept, Date, Value1, Value2, Null, NullHelp would be appreciated.Thanks and regards, |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-26 : 15:50:28
|
What determines the "first" and "second" rows? By definition, data stored in a table have no order, so if you do want to define first and second in a deterministic manner, there has to be some other column that will help you do that. Assuming there is no such column(s), you can assign a "first" and "second" and then get your results as follows:;WITH cte AS( SELECT Dept, [Date], Value1, Value2, ROW_NUMBER() OVER(PARTITION BY Dept,[Date] ORDER BY (SELECT NULL)) AS RN FROM YourTable)SELECT a.Dept, a.[Date], a.Value1, a.Value2, b.Value1 AS Value3, b.Value2 AS Value4FROM cte a LEFT JOIN cte b ON a.Dept = b.Dept AND a.[Date] = b.[Date] AND a.RN=1 AND b.RN=2; |
 |
|
vjs2445
Starting Member
16 Posts |
Posted - 2012-01-26 : 16:26:17
|
Thanks Sunitabeck.Here is my issue?Each Dept has multiple records range from 1 to 10 or could be more 10. But I am only interested in the records whose date is greater than today and If there are one record then value3 and value4 will null (cuzz no second record exists) if there are two records with same date then output will be Dept, Date, Value1, Value2, Value3, Value4Hope I am able to explain.Thanks for your help |
 |
|
X002548
Not Just a Number
15586 Posts |
|
vjs2445
Starting Member
16 Posts |
Posted - 2012-01-26 : 16:40:42
|
Following is the sample data as well the output I am looking for.I need the only the record greater than today only:Date Amount Type Description Amount_2 ID 3/15/2012 45 1 R 500 ID_1 3/15/2012 -35 2 P 500 ID_1 6/15/2012 NULL 1 P 500 ID_1 9/17/2012 450 2 R 500 ID_1 9/17/2012 NULL 2 P 500 ID_1 3/15/2012 45 1 R 500 ID_2 6/15/2012 NULL 1 P 500 ID_2 9/17/2012 450 2 R 500 ID_2 9/17/2012 NULL 2 P 500 ID_2 OutPut Date Amount Type Description Amount_2 ID Date Amount Type Description Amount_2 ID3/15/2012 45 1 R 500 ID_1 3/15/2012 -35 2 P 500 ID_13/15/2012 45 1 R 500 ID_2 Null Null Null Null Null Null |
 |
|
|
|
|
|
|