Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

First, Last row and other data from those rows Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 1:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 1:04 AM
Points: 10, Visits: 31
Hi,
I have the following data in tables

T1:
ID Dept Cat
1 WER 2
2 TTR 7


T2:
ID Date Type
1 2013-07-01 GA
2 2013-07-04 FS
2 2013-07-08 TR
2 2013-07-01 TT
1 2013-04-05 RT
1 2013-05-13 GG
2 2013-04-18 TT


I would like to query first table and then join to the second to have first and last transaction and some other data from the first and last transaction row. I would like to have the output similar to the following:

ID   Cat  FirstDate  FirstType  LastDate LastType
1 2 2013-04-05 RT 2013-07-01 GA
2 7 2013-04-18 TT 2013-07-08 TR


I was trying to use MIN MAX to query the First and Last Date (T2.Date) but then I can't to extract the related First and Last Type (T2.Type)
Post #1476391
Posted Tuesday, July 23, 2013 2:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:29 AM
Points: 1,678, Visits: 19,554
DECLARE @T1 TABLE(ID INT, Dept CHAR(3), Cat INT)
INSERT INTO @T1(ID,Dept,Cat)
VALUES
(1, 'WER', 2),
(2, 'TTR', 7);



DECLARE @T2 TABLE(ID INT, [Date] DATE, Type CHAR(2))
INSERT INTO @T2(ID, [Date], Type)
VALUES
(1, '2013-07-01', 'GA'),
(2, '2013-07-04', 'FS'),
(2, '2013-07-08', 'TR'),
(2, '2013-07-01', 'TT'),
(1, '2013-04-05', 'RT'),
(1, '2013-05-13', 'GG'),
(2, '2013-04-18', 'TT');


WITH CTE AS (
SELECT t1.ID,
t1.Cat,
t2.[Date],
t2.Type,
ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.[Date]) AS rn,
ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.[Date] DESC) AS rnRev
FROM @T1 t1
INNER JOIN @T2 t2 ON t2.ID = t1.ID)
SELECT ID,
Cat,
MIN([Date]) AS FirstDate,
MAX(CASE WHEN rn=1 THEN Type END) AS FirstType,
MAX([Date]) AS LastDate,
MAX(CASE WHEN rnRev=1 THEN Type END) AS LastType
FROM CTE
GROUP BY ID, Cat;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1476405
Posted Tuesday, July 23, 2013 7:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Just for a little variety, I think this is another way:

SELECT a.ID, Cat -- , [Date], [Type]
,[FirstDate]=MIN([Date])
,[FirstType]=MAX(CASE WHEN a=[Date] THEN [Type] END)
,[LastDate]=MAX([Date])
,[LastType]=MAX(CASE WHEN b=[Date] THEN [Type] END)
FROM (
SELECT ID, [Date], [Type]
,a=MIN([Date]) OVER (PARTITION BY ID)
,b=MAX([Date]) OVER (PARTITION BY ID)
FROM @T2) a
INNER JOIN @t1 b ON a.ID = b.ID
WHERE a=[Date] OR b=[Date]
GROUP BY a.ID, b.Cat;


Thanks to Mark for the DDL and sample data!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1476854
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse