How To Create Report From Multible Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How To Create Report From Multible Tables

Author

Topic

gomoka
Starting Member

4 Posts

Posted - 2012-06-04 : 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

35 Posts

Posted - 2012-06-04 : 05:42:56

will u provide some data it will be more clear

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-04 : 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:

[url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

N 28° 33' 11.93148"
E 77° 14' 33.66384"

gomoka
Starting Member

4 Posts

Posted - 2012-06-04 : 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

52326 Posts

Posted - 2012-06-04 : 10:20:08

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gomoka
Starting Member

4 Posts

Posted - 2012-06-04 : 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

52326 Posts

Posted - 2012-06-04 : 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

227 Posts

Posted - 2012-06-04 : 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

52326 Posts

Posted - 2012-06-05 : 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

227 Posts

Posted - 2012-06-05 : 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

4 Posts

Posted - 2012-06-05 : 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

52326 Posts

Posted - 2012-06-05 : 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

227 Posts

Posted - 2012-06-06 : 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

52326 Posts

Posted - 2012-06-07 : 22:32:08

quote:
Originally posted by vinu.vijayan

Hi Visakh,

Isn't something like PIVOT available in Oracle??....

@gomoka : You're Welcome.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

I'm not an Oracle expert

but seems like it has one

http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources