×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Aggregation data monthly join tables
2

Aggregation data monthly join tables

Aggregation data monthly join tables

(OP)
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:

CODE

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:

CODE

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.

RE: Aggregation data monthly join tables

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

CODE

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

RE: Aggregation data monthly join tables

(OP)
Thanks George,

But the result is double counting when the customer moved locations

CODE

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

RE: Aggregation data monthly join tables

Just a guess here...:

CODE

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


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close