# question

## 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
-------------------------------------------------------------------------

David_________________90b\$______________1000b\$
....

---------------------------------
```

Could you help me?

1 comment

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

·
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 ·

·
``````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

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

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