November 12, 2012 at 10:22 pm
I have a parent table and 2 children tables that store date ranges. I'm trying to create a view to support a transaction report and I'm having trouble getting the rows flattened so I don't have to have so many rows returned.
CREATE TABLE Parent (
PntKeyint,
PntQtyint)
CREATE TABLE Child1 (
Cd1Keyint,
Cd1PntKeyint,
Cd1FromDatedate,
Cd1ToDatedate)
CREATE TABLE Child2 (
Cd2Keyint,
Cd2PntKeyint,
Cd2FromDatedate,
Cd2ToDatedate)
INSERT INTO Parent (PntKey, PntQty)
SELECT1, 100
UNION ALL SELECT 2, 200
UNION ALL SELECT 3, 300
INSERT INTO Child1 (Cd1Key, Cd1PntKey, Cd1FromDate, Cd1ToDate)
SELECT 1,1,'1-1-2012','2-1-2012'
UNION ALL SELECT 2,1,'4-1-2012','5-1-2012'
UNION ALL SELECT 3,2,'1-15-2012','5-15-2012'
UNION ALL SELECT 4,3,'5-1-2012','7-1-2012'
INSERT INTO Child2 (Cd2Key, Cd2PntKey, Cd2FromDate, Cd2ToDate)
SELECT 1,1,'12-1-2011','12-31-2011'
UNION ALL SELECT 2,2,'1-1-2012','2-1-2012'
UNION ALL SELECT 3,2,'2-15-2012','5-15-2012'
UNION ALL SELECT 4,3,'9-1-2012','10-1-2012'
SELECTPntKey,
Qty,
FromDate1,
ToDate1,
FromDate2,
ToDate2
FROM (
SELECTPntKeyPntKey,
PntQtyQty,
NULLFromDate1,
NULLToDate1,
NULLFromDate2,
NULLToDate2
FROMParent
UNION ALL
SELECTCd1PntKeyPntKey,
NULLQty,
Cd1FromDateFromDate1,
Cd1ToDateToDate1,
NULLFromDate2,
NULLToDate2
FROMChild1
UNION ALL
SELECTCd2PntKeyPntKey,
NULLQty,
NULLFromDate1,
NULLToDate1,
Cd2FromDateFromDate2,
Cd2ToDateToDate2
FROMChild2
) AS WORK
ORDER BY PntKey, Qty desc
The final SELECT statement returns results like this:
PntKeyQtyFromDate1ToDate1 FromDate2ToDate2
1100NULL NULL NULL NULL
1NULL2012-01-012012-02-01NULL NULL
1NULL2012-04-012012-05-01NULL NULL
1NULLNULL NULL 2011-12-012011-12-31
2200NULL NULL NULL NULL
2NULLNULL NULL 2012-01-012012-02-01
2NULLNULL NULL 2012-02-152012-05-15
2NULL2012-01-152012-05-15NULL NULL
3300NULL NULL NULL NULL
3NULL2012-05-012012-07-01NULL NULL
3NULLNULL NULL 2012-09-012012-10-01
But what I'd really like is something like this:
PntKeyQtyFromDate1ToDate1 FromDate2ToDate2
11001/1/2012 2/1/2012 12/1/201112/31/2011
1NULL4/1/2012 5/1/2012 NULL NULL
22001/15/20125/15/20121/1/20122/1/2012
2NULLNULL NULL 2/15/20125/15/2012
33005/1/2012 7/1/2012 9/1/2012 10/1/2012
Is it possible to get the data back in a flatter way?
Thanks.
November 13, 2012 at 7:34 am
This was removed by the editor as SPAM
November 13, 2012 at 7:47 am
This was removed by the editor as SPAM
November 13, 2012 at 9:59 am
Thanks for responding. I actually did try using joins as my first choice, but the results were incorrect:
PntKey Qty FromDate1 ToDate1 FromDate2 ToDate2
----------- ----------- ---------- ---------- ---------- ----------
1 100 2012-01-01 2012-02-01 2011-12-01 2011-12-31
1 100 2012-04-01 2012-05-01 2011-12-01 2011-12-31
2 200 2012-01-15 2012-05-15 2012-01-01 2012-02-01
2 200 2012-01-15 2012-05-15 2012-02-15 2012-05-15
3 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01
(5 row(s) affected)
For PntKey 1 the FromDate2 and ToDate2 are duplicated and for PntKey 2 the FromDate1 and ToDate1 are duplicated, but I only want them to appear once. Also, the Qty gets duplicated, which will overstate it. It seems like it shouldn't be this much of a challenge but I've been struggling to come up with something that works.
November 13, 2012 at 4:41 pm
I came up with this code:
selectPntKey,
RowNumber,
CASE
WHEN RowNumber = 1 THEN Qty
ELSE 0
ENDQty,
min(FromDate1),
max(ToDate1),
min(FromDate2),
max(ToDate2)
from
(
selectPntKey,
ROW_NUMBER() OVER(PARTITION BY PntKey ORDER BY PntKey) AS 'RowNumber',
Parent.PntQty Qty,
Child1.Cd1FromDate FromDate1,
Child1.Cd1ToDate ToDate1,
null FromDate2,
null ToDate2
FROM Parent
LEFT JOIN Child1 ON Child1.Cd1PntKey = Parent.PntKey
union
select PntKey,
ROW_NUMBER() OVER(PARTITION BY PntKey ORDER BY PntKey) AS 'RowNumber',
Parent.PntQty Qty,
null FromDate1,
null ToDate1,
Child2.Cd2FromDate FromDate2,
Child2.Cd2ToDate ToDate2
FROM Parent
LEFT JOIN Child2 ON Child2.Cd2PntKey = Parent.PntKey
) as work
group by PntKey,
RowNumber,
CASE
WHEN RowNumber = 1 THEN Qty
ELSE 0
END
And I get results like this:
PntKey RowNumber Qty FromDate1 ToDate1 FromDate2 ToDate2
----------- -------------------- ----------- ---------- ---------- ---------- ----------
1 1 100 2012-01-01 2012-02-01 2011-12-01 2011-12-31
1 2 0 2012-04-01 2012-05-01 NULL NULL
2 1 200 2012-01-15 2012-05-15 2012-01-01 2012-02-01
2 2 0 NULL NULL 2012-02-15 2012-05-15
3 1 300 2012-05-01 2012-07-01 2012-09-01 2012-10-01
Warning: Null value is eliminated by an aggregate or other SET operation.
(5 row(s) affected)
November 14, 2012 at 2:13 am
You can try this. It looks like the results you're after but why you'd want such bizarre results eludes me (enlightnment requested :-)).
SELECT PntKey=ISNULL(PntKey, Cd2PntKey)
,PntQty=CASE WHEN n1 = 1 THEN PntQty END
,FromDate1, ToDate1
,FromDate2=Cd2FromDate, ToDate2=Cd2ToDate
FROM (
SELECT p.PntKey
,p.PntQty
,FromDate1=Cd1FromDate
,ToDate1=Cd1ToDate
,n1=ROW_NUMBER() OVER (PARTITION BY PntKey ORDER BY Cd1Key)
FROM #Parent p
RIGHT JOIN #Child1 c1 ON p.PntKey = c1.Cd1PntKey) a
FULL JOIN (
SELECT Cd2Key, Cd2PntKey, Cd2FromDate, Cd2ToDate
,n2=ROW_NUMBER() OVER (PARTITION BY Cd2PntKey ORDER BY Cd2Key)
FROM #Child2) c2
ON a.PntKey = c2.Cd2PntKey AND a.n1 = c2.n2
ORDER BY PntKey
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply