selective rollups?

[wbodger]

Hello all,
If I have a table with iownerid and dtassigned for a number of users (say 1.5 million records) and I want to pull a report with their initial record (this is a customer table with purchase data), then I would like to pull their second purchase date with number of items and then third purchase date. This data is all on a SQL 2000 box, but I do have box running 2005 so I could do this with SSRS, but I have not really used SSRS before. Any help or hints for the query would be greatly appreciated.

wb

 

[bborissov]

Please provide some example data and waht you want as a result from it.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[wbodger]

Well, my thought is something like

PAFGLLE105000P0DMDEN	2140628	2009-01-07 15:41:15.543
PAFGLLE105000P0DMDEN	2140628	2009-01-07 16:16:38.717
PAFGPCMV03000P0DMDEN	2140628	2009-01-21 12:39:31.390
PAFGPCDE01000P0EVDEN	2140628	2009-01-22 14:08:29.467
PAFGPCSY05000P0DMDEN	2140628	2009-01-22 14:18:36.107
PAFGPCMV03000P0DMDFR	2140628	2009-01-27 11:13:43.513
PAFGPCMV03000P0DMDEN	2140628	2009-02-13 14:47:28.560
PAFGPCMV03000P0DMDEN	2140628	2009-02-13 15:00:18.123
PAFGPCMV03000P0DMDEN	2140628	2009-02-13 15:15:21.357
PAFGLPLK0E000P0EVDEN	2140628	2009-02-17 07:20:41.357
PAFGPCMV03000P0DMDEN	2140628	2009-03-11 15:45:34.030
PAFGPCMV03000P0DMDEN	2140628	2009-03-11 16:09:01.420
PAFGLPLK0E000P0RTPEN	2140628	2009-03-30 03:13:12.233
PAFGLPLK0E0BMS0EVDML	2140628	2009-03-30 03:13:12.250

would give me

user    first purch  num second_purch num third_purch num
2140628 2009-01-07   2   2009-01-21   1   2009-01-22  2

Ideally I would like it to iterate thru for as many purchases as each user made. I can then break ti down by year or month etc.

thanks,
wb

 

[wbodger]

Here is an update. I can select the data that I want, mostly... Turns out they just want the first three purchase dates for each customer. I have this code

select msn.iownerid, DATEADD(dd, DATEDIFF(dd,0,msn.dtassigned), 0) AS Date, count(DATEADD(dd, DATEDIFF(dd,0,msn.dtassigned), 0)) AS Num_Products
from tmasterserialnumbers msn join productmaster_Extended pme
	on msn.vchproductnumber = pme.chproductnumber
where pme.bistrial=0
and pme.bisvla=0
and msn.iownerid=2140628
group by msn.iownerid, DATEADD(dd, DATEDIFF(dd,0,msn.dtassigned), 0)
having min(DATEADD(dd, DATEDIFF(dd,0,msn.dtassigned), 0))>'2006-01-01'
order by iownerid

which gives me something like this

2140628	2007-07-30 00:00:00.000	1
2140628	2007-08-16 00:00:00.000	1
2140628	2007-09-10 00:00:00.000	1
2140628	2007-10-12 00:00:00.000	1
2140628	2009-01-07 00:00:00.000	2
2140628	2009-01-21 00:00:00.000	1
2140628	2009-01-22 00:00:00.000	1
2140628	2009-01-27 00:00:00.000	1
2140628	2009-02-13 00:00:00.000	3
2140628	2009-03-11 00:00:00.000	2

what I want to do is limit that to the first three entries for each user AND I want to return that all in one row per user.

Thoughts?

Thanks!
wb

 

[wbodger]

I can also do this in SQL Server 2005

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
    iownerid, init_date, num_purch
FROM
    Purchases
WHERE
    pdate <= 3
GROUP BY iOwnerId, init_date, num_purch

which gives me

32587	2010-07-13 00:00:00.000	3
33419	2004-11-17 00:00:00.000	1
33708	2004-09-22 00:00:00.000	2
33708	2004-11-01 00:00:00.000	2
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

so that gives me the info that i need, but I would really like to
have everything for the same user on the same line, so it would look more like

32587	2010-07-13 00:00:00.000	3
33419	2004-11-17 00:00:00.000	1
33708	2004-09-22 00:00:00.000	2 2004-11-01 00:00:00.000 2
38626	2004-04-05 00:00:00.000	1
41364	2008-05-20 00:00:00.000	2 2008-05-21 00:00:00.000 1

wb
Any suggestions would be greatly appreciated.

 

[NiceArms]

Hey,

If I have understood you correctly I think this is what you are looking for:

CREATE TABLE TMP (
	id VARCHAR(100),
	REF VARCHAR(100),
	DATE DATETIME)
CREATE TABLE TMP2 (
	id VARCHAR(100),
	REF VARCHAR(100),
	DATE DATETIME,
	CNT INT)

INSERT TMP VALUES('PAFGLLE105000P0DMDEN',	'2140628',	'2009-01-07 15:41:15.543')
INSERT TMP VALUES('PAFGLLE105000P0DMDEN',	'2140628',	'2009-01-07 16:16:38.717')
INSERT TMP VALUES('PAFGPCMV03000P0DMDEN',	'2140628',	'2009-01-21 12:39:31.390')
INSERT TMP VALUES('PAFGPCDE01000P0EVDEN',	'2140628',	'2009-01-22 14:08:29.467')
INSERT TMP VALUES('PAFGPCSY05000P0DMDEN',	'2140628',	'2009-01-22 14:18:36.107')
INSERT TMP VALUES('PAFGPCMV03000P0DMDFR',	'2140628',	'2009-01-27 11:13:43.513')
INSERT TMP VALUES('PAFGPCMV03000P0DMDEN',	'2140628',	'2009-02-13 14:47:28.560')
INSERT TMP VALUES('PAFGPCMV03000P0DMDEN',	'2140628',	'2009-02-13 15:00:18.123')
INSERT TMP VALUES('PAFGPCMV03000P0DMDEN',	'2140628',	'2009-02-13 15:15:21.357')
INSERT TMP VALUES('PAFGLPLK0E000P0EVDEN',	'2140628',	'2009-02-17 07:20:41.357')
INSERT TMP VALUES('PAFGPCMV03000P0DMDEN',	'2140628',	'2009-03-11 15:45:34.030')
INSERT TMP VALUES('PAFGPCMV03000P0DMDEN',	'2140628',	'2009-03-11 16:09:01.420')
INSERT TMP VALUES('PAFGLPLK0E000P0RTPEN',	'2140628',	'2009-03-30 03:13:12.233')
INSERT TMP VALUES('PAFGLPLK0E0BMS0EVDML',	'2140628',	'2009-03-30 03:13:12.250')

INSERT TMP2
	(id,REF,DATE)
SELECT DISTINCT ID, REF, CONVERT(DATETIME,CONVERT(VARCHAR(10),DATE,103),103)
FROM TMP

UPDATE TMP2 SET CNT = DATECNT
FROM (
	SELECT id, COUNT(DATE) AS 'DATECNT'
	FROM TMP
	GROUP BY id)A
WHERE A.ID = TMP2.ID

SELECT * FROM TMP2

DROP TABLE TMP2
DROP TABLE TMP

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[NiceArms]

Gah i missed you last post!!!!

http://msdn.microsoft.com/en-us/library/ms177410.aspx

- PIVOT looks to be what you need



I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!

 

[wbodger]

OK, so I have not worked with Pivot tables before and my first attempt

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 ) DataTable
PIVOT
(
SUM(DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0))
FOR DATEADD(dd, DATEDIFF(dd,0,dtassigned), 0)
) PivotTable

gives me an error at line 20

Incorrect syntax near '('.

and given that I am new to CTEs and Pivot tables, I was wondering if somebody could point out where I went astray... What I am trying to get is the user id, their first purchase date and how many items, their second purchase date and how many items and their third purchase date and how many items.

Thanks,
WB