question

Wert avatar image
Wert asked

How can I query for result?

I have 3 tables, all tables contains date1, date2 and acctno, Some date1 and date2 in 3 tables are different and some are the same. Like the detail below. I want to get results date1, date2 and count(acctno). 

date1tbl1  date2tbl1  date1tbl2  date2tbl2  date1tbl3  date2tbl3  A  B  C     
                                            20090126    20081222        1   
                                            20090128    20090126        1   
                      20090126  20081222                             1
                      20090108  20090106                             1
20090105    20081229  20090105  20081229                          11 2
20090105    20081230  20090105  20081230                          47 5
20090107    20081230                                               2        
20090107    20090105                                               1            
20090126    20081229  20090126  20081229    20090126  20081229     7 2  2
20090126    20090123  20090126  20090123    20090126  20090123    12 7  9
20090127    20090126                        20090127  20090126    10    14

A is count(acctno) from tbl1
B is count(acctno) from tbl2
C is count(acctno) from tbl3

I want to get result look like 

date1     date2     A     B     C     
20090126 20081222               1   		
20090128 20090126               1   		
20090126 20081222         1
20090108 20090106         1
20090105 20081229   11    2
20090105 20081230   47    5
20090107 20081230   2
20090107 20090105   1			
20090126 20081229    7    2     2
20090126 20090123   12    7     9
20090127 20090126   10         14
t-sqlquery
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
hmmm... tried formatting the code better, but the preview doesn't match the final result!
1 Like 1 ·
Ian Roke avatar image Ian Roke commented ·
You don't specify which table 'date1' and 'date2' come from. You could use a JOIN between the tables but I see some dates missing from some of the tables. Is this deliberate? Would you want a line for each 'date1' and 'date2'?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - to @Kev Riley for trying. I looked at it and was glad I dont have the rep/ability to try to format it !! – Fatherjack 0 secs ago
0 Likes 0 ·

2 Answers

·
Kev Riley avatar image
Kev Riley answered

Your first sample of data is really an expanded view of aggregated data over the three tables, which I have done here as derived tables (newtbl1, newtbl2, newtbl3). The full join them across the date fields to return the further aggregated results.

select
    coalesce(newtbl1.date1, newtbl2.date1, newtbl3.date1) as date1,
    coalesce(newtbl1.date2, newtbl2.date2, newtbl3.date2) as date2,
    newtbl1.A,
    newtbl2.B,
    newtbl3.C

from
    (select date1, date2, count(acctno) as A from tbl1 group by date1, date2) as newtbl1
full join 
    (select date1, date2, count(acctno)as B from tbl2 group by date1, date2) as newtbl2
    on newtbl1.date1=newtbl2.date1 and newtbl1.date2=newtbl2.date2
full join 
    (select date1, date2, count(acctno) as C from tbl3 group by date1, date2) as newtbl3
    on newtbl2.date1=newtbl3.date1 and newtbl2.date2=newtbl3.date2
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image Ian Roke commented ·
+1 Nice answer let's just hope it's what the OP wants! :-)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
yes it is difficult to really know without some sample data, and I haven't got time to reverse engineer some tables from the data snapshot above...
0 Likes 0 ·
Wert avatar image
Wert answered

thanks a lot mate. ^-^

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image Ian Roke commented ·
You need to select the tick to highlight the response as the right answer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.