SQL 2005 CTEs and Pivots 1

wbodger · Dec 29, 2011

OK, I think it is time for a new thread as my last one was a bit off. I am trying to create a report I have gone to SQL 2005 and this is my first attempt with CTEs and Pivot Tables. I have this

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        RANK() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'pdate'
    FROM
        tmasterserialnumbers
	GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch
FROM
    Purchases
WHERE
    pdate <= 3
GROUP BY iOwnerId, init_date, num_purch ) Purchases
PIVOT
(
SUM(iownerid)
FOR init_date IN (DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0))
) AS PivotTable

which is still telling me there is an error in line 21 (FOR init_date...) near'('. I know this is not correct and have been unable to find the correct solution to this problem. Without the Pivot, the query with the CTE gives me the first (up to) three separate purchase dates for each customer with the number of items purchased each time. My goal in adding the Pivot was to make each row in the recordset contain everything for each customer, so rather than having three lines, one for each purchase date (dtAssigned), I could have one line with all (up to) three purchase dates included. It looks to me like this should be a fine situation to use Pivot as NiceArms suggested, but it seems to be choking where I narrow the datetime field down to just the date.

Any help?

Thanks,
wb

markros · Dec 29, 2011

In the pivot you can not use any functions, they have to be plain values.

E.g. this is invalid syntax
FOR init_date IN (DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0))

This one

FOR INIT_date in ([1],[2])) pvt

is correct.

PluralSight Learning Library

wbodger · Dec 30, 2011

OK, that makes sense, but I think that gives me a problem. init_date is going to be different for every row. If I remove the Pivot and just run

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        RANK() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'pdate'
    FROM
        tmasterserialnumbers
	GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch
FROM
    Purchases
WHERE
    pdate <= 3
GROUP BY iOwnerId, init_date, num_purch ) Purchases

then I get (in part)

Code:

iownerid init_date             num_purch
38626	2004-04-05 00:00:00.000	1
41364	2008-05-20 00:00:00.000	2
41364	2008-05-21 00:00:00.000	1
41869	2008-05-14 00:00:00.000	2
42232	2005-06-22 00:00:00.000	1
42232	2005-09-12 00:00:00.000	1
42232	2005-10-17 00:00:00.000	1
121653	2007-02-15 00:00:00.000	1
121706	2006-01-15 00:00:00.000	1
121788	2004-09-10 00:00:00.000	3
121788	2008-01-15 00:00:00.000	1

but when I add the pivot

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        RANK() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'pdate'
    FROM
        tmasterserialnumbers
	GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch
FROM
    Purchases
WHERE
    pdate <= 3
GROUP BY iOwnerId, init_date, num_purch ) Purchases
PIVOT
(
SUM(iownerid)
FOR init_date IN ([1],[2],[3])
) AS PivotTable

I get an error

Code:

Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to datetime.
Msg 473, Level 16, State 1, Line 1
The incorrect value "1" is supplied in the PIVOT operator.

Now, init_date will be any date value and it will be different for each row. My intent is to roll those up into one row for each ID. My query pulls just the first three purchase dates and the number of items purchased on each of those dates, but I cannot see how to make that work. Pivot tables were suggested as a good solution, but I have never worked with them before, so I am not really good at the troubleshooting on those as of yet... What I would like it to look like is more like

Code:

iownerid	purch_date	num_purch	purch_date2	num_purch	purch_date3	num_purch
38626	4/5/2004	1	null	null	null	null
41364	5/20/2008	2	5/21/2008	1	null	null
41869	5/14/2008	2	null	null	null	null
42232	6/22/2005	1	9/12/2005	1	10/17/2005	1
121653	2/15/2007	1	null	null	null	null
121706	1/15/2006	1	null	null	null	null
121788	9/10/2004	3	1/15/2008	1	null	null

Any suggestions on how to best get there?

wb

markros · Dec 30, 2011

You need to use pDate here is this is your 1,2,3 field. The RANK() field is what you need to use.

PluralSight Learning Library

wbodger · Jan 2, 2012

If I say

Code:

FOR init_date in (pdate]

I get the error

Code:

Msg 8114, Level 16, State 1, Line 1
Error converting data type nvarchar to datetime.
Msg 473, Level 16, State 1, Line 1
The incorrect value "pdate" is supplied in the PIVOT operator.

and if I say

Code:

FOR pdate IN ([1],[2],[3])

then I get the error

Code:

Msg 207, Level 16, State 1, Line 21
Invalid column name 'pdate'.

So, I am not sure what you are saying, markros. what field becomes pDate and where do I use RANK() again?

Thanks again for your help,
WB

wbodger · Jan 2, 2012

I changed the query to

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        RANK() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'pdate'
    FROM
        tmasterserialnumbers
	GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch, pDate
FROM
    Purchases
WHERE
    pdate <= 3
GROUP BY iOwnerId, init_date, num_purch, pDate ) Purchases
PIVOT
(
SUM(iownerid)
FOR pDate IN ([1],[2],[3])
) AS PivotTable

and got the error

Code:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

markros · Jan 2, 2012

This will be a bit hard to track. I suggest the way I usually debug such problems:

try selecting the results after each CTE to figure out where we have the problem, e.g.

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        RANK() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'pdate'
    FROM
        tmasterserialnumbers
    GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)

select * from Purchases

If the above works fine, let's try next step

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        RANK() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'pdate'
    FROM
        tmasterserialnumbers
    GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch, pDate
FROM
    Purchases
WHERE
    pdate <= 3
GROUP BY iOwnerId, init_date, num_purch, pDate ) Purchases

Also, I think pDate name for the field may be a bit misleading. It's a rank field, so I may name it Rnk. Also, why you're using Rank function? Should not you be using row_number() or DENSE_RANK() instead of RANK() ?

PluralSight Learning Library

wbodger · Jan 2, 2012

Everything before the pivot is working great. As to why RANK() over row_number() or dense_rank(), I do not know, it works with row_number() and dense_rank() as well. dense_rank() seems to be the fastest. Now, how to get Pivot to work as I am hoping it will...

wbodger · Jan 2, 2012

without the pivot

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        dense_Rank() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'rnk'
    FROM
        tmasterserialnumbers
	GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch, rnk
FROM
    Purchases
WHERE
    rnk <= 3
GROUP BY iOwnerId, init_date, num_purch, rnk ) Purchases

gives me

Code:

iownerid init_date             num_purch
38626    2004-04-05 00:00:00.000    1
41364    2008-05-20 00:00:00.000    2
41364    2008-05-21 00:00:00.000    1
41869    2008-05-14 00:00:00.000    2
42232    2005-06-22 00:00:00.000    1
42232    2005-09-12 00:00:00.000    1
42232    2005-10-17 00:00:00.000    1
121653    2007-02-15 00:00:00.000    1
121706    2006-01-15 00:00:00.000    1
121788    2004-09-10 00:00:00.000    3
121788    2008-01-15 00:00:00.000    1

changing the code to

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        dense_Rank() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'rnk'
    FROM
        tmasterserialnumbers
	GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)
select * from (
SELECT
    iownerid, init_date, num_purch, rnk
FROM
    Purchases
WHERE
    rnk <= 3
GROUP BY iOwnerId, init_date, num_purch, rnk ) Purchases
PIVOT
(
SUM(rnk)
FOR rnk IN ([1],[2],[3])
) AS PivotTable

is closer, it gives me

Code:

1	2004-03-02 00:00:00.000	2	1	NULL	NULL
1	2010-01-13 00:00:00.000	1	NULL	2	NULL
1	2010-04-20 00:00:00.000	1	NULL	NULL	3
32587	2010-07-13 00:00:00.000	3	1	NULL	NULL
33419	2004-11-17 00:00:00.000	1	1	NULL	NULL
33708	2004-09-22 00:00:00.000	2	1	NULL	NULL
33708	2004-11-01 00:00:00.000	2	NULL	2	NULL
38626	2004-04-05 00:00:00.000	1	1	NULL	NULL
41364	2008-05-20 00:00:00.000	2	1	NULL	NULL
41364	2008-05-21 00:00:00.000	1	NULL	2	NULL
41869	2008-05-14 00:00:00.000	2	1	NULL	NULL
42232	2005-06-22 00:00:00.000	1	1	NULL	NULL
42232	2005-09-12 00:00:00.000	1	NULL	2	NULL
42232	2005-10-17 00:00:00.000	1	NULL	NULL	3

but I want everything for one user in one row, so it would look more like

Code:

iownerid	date_1	num_1	date_2	num_2	date_3	num_3
1	3/2/2004	2	1/13/2010	1	4/20/2010	1
32587	7/13/2010	3	NULL	NULL	NULL	NULL
33419	11/17/2004	1	NULL	NULL	NULL	NULL
33708	9/22/2004	2	11/1/2004	NULL	NULL	NULL
38626	4/5/2004	1	NULL	NULL	NULL	NULL
41364	5/20/2008	2	5/21/2008	1	NULL	NULL
41869	5/14/2008	2	NULL	NULL	NULL	NULL
42232	6/22/2005	1	9/12/2005	1	10/17/2005	1

markros · Jan 2, 2012

Unfortunately, PIVOT doesn't allow you to pivot by multiple columns. However, in your case the solution is quite easy:

Code:

WITH Purchases AS
(
    SELECT
        iownerid, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) as init_date, count(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)) as num_purch,
        dense_Rank() OVER (PARTITION BY iownerid ORDER BY DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0) ASC) AS 'rnk'
    FROM
        tmasterserialnumbers
    GROUP BY iOwnerID, DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
)

SELECT iOnwerID, 
max(case when Rnk = 1 then Init_Date end) as [Date_1],
max(case when Rnk = 1 then Num_Purch end) as [Num_1],
max(case when Rnk = 2 then Init_Date end) as [Date_2],
max(case when Rnk = 2 then Num_Purch end) as [Num_2],
max(case when Rnk = 3 then Init_Date end) as [Date_3],
max(case when Rnk = 3 then Num_Purch end) as [Num_3]
from Purchases GROUP BY iOwnerID

PluralSight Learning Library

wbodger · Jan 2, 2012

OK, so dense_rank() gives me the PARTITION BY field ordered by the ORDER BY field, is that right? Could this be done cleanly without CTEs? Your solution works great, thank you very much for your help.

wb

markros · Jan 2, 2012

You're using partition by iOwnerID, so if you have many records per OwnerID you will have Rn = 1 .. 4 in the order of dtAssigned (its date portion only). Since you're using DENSE_RANK() function, if you have same date, the records with the same date will get the same Rnk number.

PluralSight Learning Library

wbodger · Jan 2, 2012

OK, that makes sense. Thank you very much!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL 2005 CTEs and Pivots 1

wbodger

Programmer

markros

Programmer

wbodger

Programmer

markros

Programmer

wbodger

Programmer

wbodger

Programmer

markros

Programmer

wbodger

Programmer

wbodger

Programmer

markros

Programmer

wbodger

Programmer

markros

Programmer

wbodger

Programmer

Similar threads

Part and Inventory Search

Sponsor