| Author |
 Topic  |
|
|
Blade Hunter
Starting Member
Australia
6 Posts |
 Posted - 03/21/2012 : 21:31:03
|
Hi Guys, thanks so much for the help yesterday, I feel I am finally starting to understand these pivot tables.
I need to group some data and that part I am lost on.
So I have a pivot returning:
Date Of Birth in column 1
Redemtion, download, acc created....... in the following columns (all from the pivot)
The Date of Birth column which is the none pivoted column needs to have brackets and respective summing.
What I mean by this is instead of returning every unique DOB, I need to bracket them into the following:
<18 years old
18-20
21-25
26-30
and so on until I get to the end which is 76+.
Here is my existing WORKING query minus this bracketing.
SELECT D_DOB,
[310] 'STD VCH REDEMP',
[510] 'STD DOWNLOAD',
[410] 'STD STR ACC CREATED',
[110] 'STD REG',
[120] 'FB REG',
[121] 'TWT REG',
[210] 'STD LOGIN',
[220] 'FB LOGIN',
[221] 'TWT LOGIN'
FROM
(
SELECT (UA.N_ACTIVITY_TYPE*10)+UA.N_SOURCE_TYPE N_ACTIVITY_TYPE,
U.D_DOB,
1 as TransCount
FROM BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG UA, BanditWarehouseV3.dbo.DSP_USER U
WHERE convert(DATE,dateadd(HH,-17,UA.D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
AND UA.N_USER_ID = U.N_USER_ID
) AS SourceTable
PIVOT(
count(TransCount)
FOR N_ACTIVITY_TYPE IN (
[310],
[510],
[410],
[110],
[120],
[121],
[210],
[220],
[221]
)
) AS PivotTable
order by D_DOB desc;
Once again thanks in advance for any help offered, I really appreciate it.
Cheers
Dan |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/21/2012 : 22:10:08
|
change the inner query (SourceTable) to group the DOB
SELECT N_ACTIVITY_TYPE, AGE_GROUP, TransCount = sum(TransCount)
FROM
(
SELECT (UA.N_ACTIVITY_TYPE*10)+UA.N_SOURCE_TYPE N_ACTIVITY_TYPE,
U.D_DOB,
AGE_GROUP = case when datediff(year, U.D_DOB, getdate() < 18 then '< 18'
when datediff(year, U.D_DOB, getdate() between 19 and 20 then '18-20'
. . .
end,
1 as TransCount
FROM BanditWarehouseV3.dbo.DSP_USER_ACTIVITY_LOG UA, BanditWarehouseV3.dbo.DSP_USER U
WHERE convert(DATE,dateadd(HH,-17,UA.D_CREATED),112) BETWEEN '2012-03-01' AND '2012-03-31'
AND UA.N_USER_ID = U.N_USER_ID
) d
GROUP BY N_ACTIVITY_TYPE, AGE_GROUP
KH
Time is always against us
|
Edited by - khtan on 03/21/2012 22:12:49 |
 |
|
|
Blade Hunter
Starting Member
Australia
6 Posts |
Posted - 03/21/2012 : 23:01:43
|
Worked perfectly, thanks once again khtan
Cheers
Dan |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 03/21/2012 : 23:06:48
|
welcome
KH
Time is always against us
|
|
|
| |
Topic |
|
|
|
Another SQL Pivot question
