May 15, 2014 at 7:39 pm
Hi,
I need to fill data in my rows for missing years with 0 like in sample below, I know that I have to use ph (placeholder) table, but I still need to put rest of information for these missing years, so each CustID should have entries for ALL years from ph with zero or NUll valus, and keep that filter too: ProdID = 12
... and here I bit confused, can anybody provide me with hint?
Tx much
Mario
/*
SELECT * INTO #ph FROM (SELECT 2001 yy union -- select * from #ph drop table #ph
SELECT 2002 yy union
SELECT 2003 yy ) a
SELECT * INTO #det FROM (SELECT 2001 yy, 201 Amt, 12 ProdID, 111 CustID union -- select * from #det -- drop table #det
SELECT 2002 yy, 220 Amt, 12 ProdID, 111 CustID union
SELECT 2003 yy, 233 Amt, 999 ProdID, 111 CustID union
SELECT 2002 yy, 330 Amt, 12 ProdID, 222 CustID ) a
*/
SELECT ph.YY Ph_YY,
d.YY det_YY,
d.CustID,
d.Amt,
d.ProdID
FROM #det d
RIGHT JOIN #ph ph ON ph.yy = d.yy -- and for each CustID
WHERE ProdID = 12 --OR ProdID = NULL
--------------result wtd
Ph_YYdet_YYCustIDAmtProdID
2001200111120112
2002200211122012
20032003111012 -- added from ph
20012001222012 -- added from ph
2002200222233012
20032003222012 -- added from ph
May 15, 2014 at 9:22 pm
COALESCE maybe? You might need to use a windowing function to get the previous row (LAG?) Something like
COALESCE(MyColumn, LAG(...))
May 15, 2014 at 11:19 pm
The problem in your attempt is that sql server interprets queries like
[font="Courier New"]select * from x left outer join y where y = z[/font]
to
[font="Courier New"]select * from x inner join y where y = z [/font]
effectively excluding the empty records.
Here is a quick solution to your problem
😎
SELECT * INTO #ph FROM (SELECT 2001 yy union
SELECT 2002 yy union
SELECT 2003 yy ) a
SELECT * INTO #det FROM (SELECT 2001 yy, 201 Amt, 12 ProdID, 111 CustID union
SELECT 2002 yy, 220 Amt, 12 ProdID, 111 CustID union
SELECT 2003 yy, 233 Amt, 999 ProdID, 111 CustID union
SELECT 2002 yy, 330 Amt, 12 ProdID, 222 CustID ) a
;WITH ALL_CUST AS
(
SELECT
X.CustID
FROM #det X
GROUP BY X.CustID
)
,ALL_CUST_YEAR AS
(
SELECT
C.CustID AS CUST_ID
,Y.yy AS CUST_YEAR
FROM ALL_CUST C
CROSS JOIN #ph Y
)
SELECT
ACY.CUST_YEAR AS Ph_YY
,ISNULL(DP.yy,ACY.CUST_YEAR) AS det_YY
,ACY.CUST_ID AS CustID
,ISNULL(DP.Amt,0) AS Amt
,FIRST_VALUE(DP.ProdID) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY DP.ProdID DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS ProdID
FROM ALL_CUST_YEAR ACY
LEFT OUTER JOIN
(
SELECT
D.yy
,D.Amt
,D.ProdID
,D.CustID
FROM #det D
WHERE D.ProdID = 12
) AS DP
ON ACY.CUST_ID = DP.CustID
AND ACY.CUST_YEAR = DP.yy
ORDER BY ACY.CUST_ID, ACY.CUST_YEAR
drop table #ph
drop table #det
Results
Ph_YY det_YY CustID Amt ProdID
----------- ----------- ----------- ----------- -------
2001 2001 111 201 12
2002 2002 111 220 12
2003 2003 111 0 12
2001 2001 222 0 12
2002 2002 222 330 12
2003 2003 222 0 12
May 16, 2014 at 11:40 am
Thanks much !!!! Eirikur !!!
I got it generally:
Learning now how this works:
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
Best
Mario
May 16, 2014 at 12:14 pm
Here's another option. It works with the sample data and I would expect it to work with real data as well. The only difference is with det_YY column which you can change to use the same value as Ph_YY column.
WITH CTE AS(
SELECT ph.YY Ph_YY,
d.YY det_YY,
d.CustID,
CASE WHEN ph.YY - d.YY = 0 THEN d.Amt ELSE 0 END Amt,
d.ProdID,
ROW_NUMBER() OVER( PARTITION BY CustID, ph.YY ORDER BY ABS(ph.YY - d.YY)) rn
FROM #ph ph
CROSS
JOIN #det d --ON ph.yy = d.yy
WHERE d.ProdID = 12 --OR ProdID = NULL
)
SELECT Ph_YY,
det_YY,
CustID,
Amt,
ProdID
FROM CTE
WHERE rn = 1
ORDER BY CustID, Ph_YY
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply