question

Thang Nguyen avatar image
Thang Nguyen asked

SQL - Get sales figure in 2 period

I have two tables: one contains employees information and another is transactions (sales) information including sales man (employeee ID)

Table 1: employees,code name ...

Table 2: sales, employee_code, product, Date, Price, Amount

I would like to calculate how much each employees can generate revenue (total amount) for each of 2 periods (1 Jan to 30 Jun and 1 Jul to 31 Dec) or maybe any period of time - like this:

                    
Name _________Period1_1_30_6______Period 1_7_31_12                    
-------------------------------------------------------------------------                    
Adam__________________50b$______________70b$                    
                    
David_________________90b$______________1000b$                    
....                    
                    
---------------------------------                    

Could you help me?

querypivothomework
1 comment
10 |1200

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

Kristen avatar image Kristen ♦ commented ·
As your example revenue is in billions either you are working in the banking industry (in which case I expect you would know how to do this :) ) or its homework. For homework you need to show us what you have done and what you need help with, no sense us just doing the work for you as you won't learn anything. If it isn't homework you can re-edit your question and remove the "Homework" tag I have added :)
0 Likes 0 ·
Squirrel 1 avatar image
Squirrel 1 answered
select t1.code_name,            
       Period1_1_1_30_6 = sum(case when Date >= '20090101' and Date <= '20090630' then Amount else 0 end),            
       Period2_1_7_31_12 = sum(case when Date >= '20090701' and Date <= '20091231' then Amount else 0 end)            
from   table1 t1            
       inner join table2 t2 on t1.employees = t2.employee_code            
group by t1.code_name            

if you are using SQL 2005 / 2008, you can also use the PIVOT operator to do this

10 |1200

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

Madhivanan avatar image
Madhivanan answered
10 |1200

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

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.