SQL - Get sales figure in 2 period

Question

question

Thang Nguyen asked Dec 3, '09

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?

query pivot homework

1

10 |1200

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

Kristen ♦ commented · Dec 04, 2009 at 08:55 AM

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 ·

Answer 1 · 2009-12-03T21:15:32Z

Squirrel 1 answered Dec 3, '09

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

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

Answer 2 · 2009-12-04T04:50:17Z

Madhivanan answered Dec 4, '09

Refer this too http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

10 |1200

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

question