Aggregation data monthly join tables 2

[peac3]

Hi all,

I am trying to aggregate the transaction data monthly within a year of 2017 from the customer location table then convert from row to column.
Please do not use Pivot function as I am not using MSSQL but using database without Pivot function but it does accept SQL.

Here is the input tables:

Customer_table
Cust_ID 	Name		Address            effective_date      expiry_date
1		John Smith	New York           2016-02-01          2017-03-10
1		John Smith	London             2017-03-11          9999-12-31
2		Jacky Fung	Sydney		   2017-01-01          9999-12-31

Transaction_Table
Cust_ID 	Tran_Date       Tran_Amount
1		2017-03-05	100	
1		2017-03-07	150	
1		2017-03-15	200
1		2017-04-01	300
1		2017-05-10	400
1		2017-05-12	500
2		2017-03-05	100	
2		2017-03-15	200
2		2017-04-01	300
2		2017-05-10	400
2		2017-05-12	500

I would like to aggregate the data become the output like below:

Cust_Id Effective_date Expiry_date  Adress        Mth01_Tot Mth02_Tot Mth03_Tot Mth04_Tot Mth05_Tot Mth06_Tot Mth07_Tot Mth08_Tot Mth09_Tot Mth10_Tot Mth11_Tot Mth12_Tot
1       2017-01-01      2017-03-10  New York       null      null      250      null      null      null      null      null      null      null      null      null      
1       2017-03-11      9999-12-31  London         null      null      200      300       900       null      null      null      null      null      null      null      
2       2017-01-01      9999-12-31  Sydney         null      null      300      300       900       null      null      null      null      null      null      null

Appreciate you guys help on this.

 

[gmmastros]

This is a good example of pivoting without the pivot function. This is how we did things way back in 2005...

declare @Customer_table
Table	(
          Cust_ID        int,
          Name           varchar(50),
          Address        varchar(50),
          effective_date DateTime,
          expiry_date    DateTime
	)

insert Into @Customer_table Values(1,'John Smith','New York', '2016-02-01','2017-03-10')
insert Into @Customer_table Values(1,'John Smith','London',   '2017-03-11','9999-12-31')
insert Into @Customer_table Values(2,'Jacky Fung','Sydney',	'2017-01-01','9999-12-31')

Declare @Transaction_Table
Table   (
          Cust_ID     int,
          Tran_Date   DateTime,
          Tran_Amount int
        )

Insert Into @Transaction_Table Values(1,'2017-03-05',100)	
Insert Into @Transaction_Table Values(1,'2017-03-07',150)	
Insert Into @Transaction_Table Values(1,'2017-03-15',200)
Insert Into @Transaction_Table Values(1,'2017-04-01',300)
Insert Into @Transaction_Table Values(1,'2017-05-10',400)
Insert Into @Transaction_Table Values(1,'2017-05-12',500)
Insert Into @Transaction_Table Values(2,'2017-03-05',100)	
Insert Into @Transaction_Table Values(2,'2017-03-15',200)
Insert Into @Transaction_Table Values(2,'2017-04-01',300)
Insert Into @Transaction_Table Values(2,'2017-05-10',400)
Insert Into @Transaction_Table Values(2,'2017-05-12',500)

Select  *
From    @Customer_Table As Customer_Table
        Left Join (
           Select Cust_ID,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 1 
                           Then Tran_Amount
                           Else 0
                           End) As Mth01_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 2
                           Then Tran_Amount
                           Else 0
                           End) As Mth02_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 3 
                           Then Tran_Amount
                           Else 0
                           End) As Mth03_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 4 
                           Then Tran_Amount
                           Else 0
                           End) As Mth04_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 5 
                           Then Tran_Amount
                           Else 0
                           End) As Mth05_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 6 
                           Then Tran_Amount
                           Else 0
                           End) As Mth06_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 7 
                           Then Tran_Amount
                           Else 0
                           End) As Mth07_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 8 
                           Then Tran_Amount
                           Else 0
                           End) As Mth08_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 9 
                           Then Tran_Amount
                           Else 0
                           End) As Mth09_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 10
                           Then Tran_Amount
                           Else 0
                           End) As Mth10_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 11
                           Then Tran_Amount
                           Else 0
                           End) As Mth11_Tot,
                  Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 12
			   Then Tran_Amount
			   Else 0
			   End) As Mth12_Tot
           From	@Transaction_Table
           Group By Cust_ID
           ) As Totals
             On Customer_Table.Cust_ID = Totals.Cust_ID

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[peac3]

Thanks George,

But the result is double counting when the customer moved locations

Cust_Id Effective_date Expiry_date  Adress        Mth01_Tot Mth02_Tot Mth03_Tot Mth04_Tot Mth05_Tot Mth06_Tot Mth07_Tot Mth08_Tot Mth09_Tot Mth10_Tot Mth11_Tot Mth12_Tot
1       2017-01-01      2017-03-10  New York       null      null      450      300       900      null      null      null      null      null      null      null      
1       2017-03-11      9999-12-31  London         null      null      450      300       900       null      null      null      null      null      null      null      
2       2017-01-01      9999-12-31  Sydney         null      null      300      300       900       null      null      null      null      null      null      null

we would like the result as per above

 

[Andrzejek]

Just a guess here...:

Sum(Case When Year(Tran_Date) = 2017 And Month(Tran_Date) = 1 [blue]
     And Tran_Date Between Customer_table.effective_date And Customer_table.expiry_date[/blue]
     Then Tran_Amount
     Else 0
     End) As Mth01_Tot,

---- Andy

There is a great need for a sarcasm font.