| Author |
 Topic  |
|
|
gomoka
Starting Member
Tanzania
4 Posts |
 Posted - 06/04/2012 : 04:54:26
|
Pls I'm very new to this forum and I don't know if I am posting Questions in the right way. I am trying to generate a report of following format:
MemberAge(Col1) Against 18 Colums for # of dependent children by age (From DEP Table)
(Frm MEM Table) =====================> 0 1 2 3 4 5 6 …. 18
20=================================>>? ? ? ? ? ? ? ... ?
21==================================>>
22
23
24
…
75
In summary: How many members of age 20 has dependents 1 yrs old, 2yrs, 3yrs ... 18 yrs. Similarly for members with 21, 22 .. 75 yrs
The query is very complecated for me. I believe there is a way. Please Help, I'm stuck
|
|
|
mani_12345
Starting Member
India
35 Posts |
Posted - 06/04/2012 : 05:42:56
|
| will u provide some data it will be more clear |
 |
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/04/2012 : 06:31:28
|
Welcome to SQLTeam.
Please post DDL of tables and some sample data.
If you don't know what I mean then please visit the following link:
http://www.sqlservercentral.com/articles/Best Practices/61537/
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
gomoka
Starting Member
Tanzania
4 Posts |
Posted - 06/04/2012 : 08:18:11
|
Hi members
I am giving important colums only as requested
Members Table
DDL: Member_id(PK),sex,DoB
Sample data:
1000,M,19900515
1001,F,19880720
1002,F,19881122
1003,M,19900625
1004,M,19701030
1005,F,19700920
Dependents Table
DDL:Member_id(FK),SL_Num,DOB note Member_id+SL_num are composite keys
Sample Data:
1000,01,20100515
1000,02,20121115
1000,03,20131015
1001,01,20080720
1001,02,20100720
1002,01,20111122
1003,01,10100625
1003,02,10100625
1004,01,20091030
1004,02,20100230
1004,03,20110307
1004,04,20120430
1005,01,20110920 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47172 Posts |
Posted - 06/04/2012 : 10:20:08
|
SELECT Age,[1],[2],[3],[4],...[18]
FROM
(
SELECT m.Age,SL_Num,d.Age AS DependentAge
FROM (SELECT CASE WHEN MONTH(DOB) > MONTH(GETDATE())
OR (MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB) > DAY(GETDATE()))
THEN -1
ELSE 0
END + DATEDIFF(yy,DOB,GETDATE()) AS Age,
Member_id
FROM Member )m
INNER JOIN ( SELECT CASE WHEN MONTH(DOB) > MONTH(GETDATE())
OR (MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB) > DAY(GETDATE()))
THEN -1
ELSE 0
END + DATEDIFF(yy,DOB,GETDATE()) AS Age,
Member_id,
SL_Num
FROM Dependents) d
ON d.Member_id = m.Member_id
)m
PIVOT (COUNT(SL_Num) FOR DependentAge IN ([1],[2],[3],...[18]))p
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
gomoka
Starting Member
Tanzania
4 Posts |
Posted - 06/04/2012 : 11:27:01
|
Thanks for the quick response. But I get this error message
ORA-00936: missing expression
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47172 Posts |
Posted - 06/04/2012 : 12:44:13
|
quote:
Originally posted by gomoka
Thanks for the quick response. But I get this error message
ORA-00936: missing expression
You're in wrong forum
the error message suggests you're using Oracle
This is MS SQL Server forum
Please post it in some Oracle forum like www.orafaq.com to get syntax specific help
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/04/2012 : 23:50:34
|
quote:
Originally posted by visakh16
SELECT Age,[1],[2],[3],[4],...[18]
FROM
(
SELECT m.Age,SL_Num,d.Age AS DependentAge
FROM (SELECT CASE WHEN MONTH(DOB) > MONTH(GETDATE())
OR (MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB) > DAY(GETDATE()))
THEN -1
ELSE 0
END + DATEDIFF(yy,DOB,GETDATE()) AS Age,
Member_id
FROM Member )m
INNER JOIN ( SELECT CASE WHEN MONTH(DOB) > MONTH(GETDATE())
OR (MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB) > DAY(GETDATE()))
THEN -1
ELSE 0
END + DATEDIFF(yy,DOB,GETDATE()) AS Age,
Member_id,
SL_Num
FROM Dependents) d
ON d.Member_id = m.Member_id
)m
PIVOT (COUNT(SL_Num) FOR DependentAge IN ([1],[2],[3],...[18]))p
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Hi Visakh,
I still have a doubt about what the OP wants....He wants the Count of Dependants of every age group 1 to 18 for every Member Id....right??
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47172 Posts |
Posted - 06/05/2012 : 00:15:30
|
not for each MemberId but for each Member AGe Group. see posted output in first thread
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/05/2012 : 02:36:17
|
So, it can be done like this too....right??
Select a.Age,
Sum(Case When b.Age = 0 then 1 Else 0 End) As Count_0yrs,
Sum(Case When b.Age = 1 then 1 Else 0 End) As Count_1yrs,
Sum(Case When b.Age = 2 then 1 Else 0 End) As Count_2yrs,
Sum(Case When b.Age = 3 then 1 Else 0 End) As Count_3yrs,
Sum(Case When b.Age = 4 then 1 Else 0 End) As Count_4yrs,
Sum(Case When b.Age = 5 then 1 Else 0 End) As Count_5yrs,
Sum(Case When b.Age = 6 then 1 Else 0 End) As Count_6yrs,
Sum(Case When b.Age = 7 then 1 Else 0 End) As Count_7yrs,
Sum(Case When b.Age = 8 then 1 Else 0 End) As Count_8yrs,
Sum(Case When b.Age = 9 then 1 Else 0 End) As Count_9yrs,
Sum(Case When b.Age = 10 then 1 Else 0 End) As Count_10yrs,
Sum(Case When b.Age = 11 then 1 Else 0 End) As Count_11yrs,
Sum(Case When b.Age = 12 then 1 Else 0 End) As Count_12yrs,
Sum(Case When b.Age = 13 then 1 Else 0 End) As Count_13yrs,
Sum(Case When b.Age = 14 then 1 Else 0 End) As Count_14yrs,
Sum(Case When b.Age = 15 then 1 Else 0 End) As Count_15yrs,
Sum(Case When b.Age = 16 then 1 Else 0 End) As Count_16yrs,
Sum(Case When b.Age = 17 then 1 Else 0 End) As Count_17yrs,
Sum(Case When b.Age = 18 then 1 Else 0 End) As Count_18yrs
From
((Select *, (DATEDIFF(DD,DoB,GETDATE())/365) As Age From Members) As a
Left JOIN
(Select *, (DATEDIFF(DD,DoB,GETDATE())/365) As Age From Dependents) As b ON a.Member_id = b.Member_id)
Group By a.Age
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
gomoka
Starting Member
Tanzania
4 Posts |
Posted - 06/05/2012 : 09:41:26
|
Hi, Hi Visakh & vinu.vijayan
Thanks for your help. The code posted by vinu.vijayan worked fine except for some changes to suit the Oracle database. For instance I had to use the Function DECODE instead for CASE.
Thanks again.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47172 Posts |
Posted - 06/05/2012 : 09:59:55
|
quote:
Originally posted by vinu.vijayan
So, it can be done like this too....right??
Select a.Age,
Sum(Case When b.Age = 0 then 1 Else 0 End) As Count_0yrs,
Sum(Case When b.Age = 1 then 1 Else 0 End) As Count_1yrs,
Sum(Case When b.Age = 2 then 1 Else 0 End) As Count_2yrs,
Sum(Case When b.Age = 3 then 1 Else 0 End) As Count_3yrs,
Sum(Case When b.Age = 4 then 1 Else 0 End) As Count_4yrs,
Sum(Case When b.Age = 5 then 1 Else 0 End) As Count_5yrs,
Sum(Case When b.Age = 6 then 1 Else 0 End) As Count_6yrs,
Sum(Case When b.Age = 7 then 1 Else 0 End) As Count_7yrs,
Sum(Case When b.Age = 8 then 1 Else 0 End) As Count_8yrs,
Sum(Case When b.Age = 9 then 1 Else 0 End) As Count_9yrs,
Sum(Case When b.Age = 10 then 1 Else 0 End) As Count_10yrs,
Sum(Case When b.Age = 11 then 1 Else 0 End) As Count_11yrs,
Sum(Case When b.Age = 12 then 1 Else 0 End) As Count_12yrs,
Sum(Case When b.Age = 13 then 1 Else 0 End) As Count_13yrs,
Sum(Case When b.Age = 14 then 1 Else 0 End) As Count_14yrs,
Sum(Case When b.Age = 15 then 1 Else 0 End) As Count_15yrs,
Sum(Case When b.Age = 16 then 1 Else 0 End) As Count_16yrs,
Sum(Case When b.Age = 17 then 1 Else 0 End) As Count_17yrs,
Sum(Case When b.Age = 18 then 1 Else 0 End) As Count_18yrs
From
((Select *, (DATEDIFF(DD,DoB,GETDATE())/365) As Age From Members) As a
Left JOIN
(Select *, (DATEDIFF(DD,DoB,GETDATE())/365) As Age From Dependents) As b ON a.Member_id = b.Member_id)
Group By a.Age
N 28° 33' 11.93148"
E 77° 14' 33.66384"
yep you can. I just used PIVOT operator instead
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/06/2012 : 00:12:13
|
Hi Visakh,
Isn't something like PIVOT available in Oracle??....
@gomoka : You're Welcome.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47172 Posts |
|
| |
Topic |
|
|
|
How To Create Report From Multible Tables
