December 7, 2011 at 9:14 am
Executing below query gives 10 records with NULL values.
DECLARE @Pet Table
(
PetID INT
, PetName NVARCHAR(10)
)
DECLARE @PetDetail Table
(
PetDetailID INT
, DType INT
, DDate DATETIME
)
INSERT INTO @Pet(PetID, PetName) VALUES
(1, 'AAA')
, (2, 'BBB')
INSERT INTO @PetDetail(PetDetailID, DType, DDate) VALUES
(1, 14, '2001-01-01 00:00:00.000')
, (1, 15, '2003-02-02 00:00:00.000')
, (2, 14, '1998-06-01 00:00:00.000')
, (2, 15, '1999-07-02 00:00:00.000')
, (2, 14, '2007-01-01 00:00:00.000')
, (2, 15, '2008-02-02 00:00:00.000')
, (2, 14, '2009-08-14 00:00:00.000')
, (2, 15, '2010-09-15 00:00:00.000')
, (2, 14, '2009-09-02 00:00:00.000')
, (2, 15, '2010-10-01 00:00:00.000')
SELECT PetID
, PetName
, Date1 = CASE WHEN DType=14 THEN DDate END
, Date2 = CASE WHEN DType=15 THEN DDate END
FROM @Pet p1
INNER JOIN @PetDetail p2 ON p1.PetID=p2.PetDetailID
Here, if DType=14 then I need DDate as Date1 and if DType=15, Still I need DDate but as Date2.
Final Result set should be:
I need only 5 records without NULL values as :
PetID PetName Date1 Date2
1 AAA 2001-01-01 00:00:00.000 2003-02-02 00:00:00.000
2 BBB 1998-06-01 00:00:00.000 1999-07-02 00:00:00.000
2 BBB 2007-01-01 00:00:00.000 2008-02-02 00:00:00.000
2 BBB 2009-08-14 00:00:00.000 2010-09-15 00:00:00.000
2 BBB 2009-09-02 00:00:00.000 2010-10-01 00:00:00.000
Hope this is clear!!
Thanks in advance!!
December 7, 2011 at 9:26 am
1 little thing. When you want to pivot data (multiple rows into a single one), you need to do something like this =>
SELECT PetID
, PetName
, Date1 = MAX(CASE WHEN DType=14 THEN DDate END)
, Date2 = MAX(CASE WHEN DType=15 THEN DDate END)
FROM @Pet p1
INNER JOIN @PetDetail p2 ON p1.PetID=p2.PetDetailID
GROUP BY PetID, PetName
You can use SUM instead of max when you need totals.
However in this case you need a different solution.
Just give me a couple minutes.
December 7, 2011 at 9:31 am
;
WITH CTE (PetID, PetName, DType, DDate, RN)
AS
(
SELECT PetID
, PetName
, DType
, DDate
, ROW_NUMBER() OVER (PARTITION BY PetID, DType ORDER BY DDATE) AS RN
FROM @Pet p1
INNER JOIN @PetDetail p2 ON p1.PetID=p2.PetDetailID
)
--SELECT * FROM CTE ORDER BY PetID, RN, DType
SELECT C1.PetID, C1.PetName, C1.DDate, C2.DDate FROM CTE C1 LEFT OUTER JOIN CTE C2 ON C1.PetID = C2.PetID AND C1.RN = C2.RN
WHERE C1.DType = 14 AND C2.DType = 15
December 7, 2011 at 10:17 am
Thank You so much!!
December 7, 2011 at 10:22 am
HTH 🙂
December 7, 2011 at 10:27 am
Here is a different approach using a CROSS APPLY.
SELECT PetID
, p.PetName
, pd1.DDate AS Date1
, pd2.Date2
FROM @Pet AS p
INNER JOIN @PetDetail AS pd1
ON p.PetID=pd1.PetDetailID
AND pd1.DType = 14
CROSS APPLY (
SELECT TOP (1) pd.DDate AS Date2
FROM @PetDetail AS pd
WHERE pd.PetDetailID = p.PetID
AND pd.DType = 15
AND pd.DDate >= pd1.DDate
) AS pd2
Here is a comparison of the IO stats for the two approaches
CTE
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3628A90C'. Scan count 2, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#371CCD45'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CROSS APPLY
Table '#371CCD45'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#3628A90C'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I believe that @Pet is #3628A90C and that @PetDetail is #371CCD45 in these stats.
Drew
PS: The convention for names is that somethingID uniquely identifies a record in the "something" table. Your PetDetailID does not uniquely identify a PetDetail record, but rather uniquely identifies a Pet record, so it should probably be renamed PetID.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2011 at 10:30 am
I knew I was forgetting something :w00t:.
Tx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply