SQL - Get sales figure in 2 period

I have two tables: one contains employees information and another is transactions (sales) information including sales man (employ开发者_如何学Goeee 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$ ....

You can try something like this

DECLARE @Employees TABLE(
        EmpCode INT,
        EmpName VARCHAR(50)
)

INSERT INTO @Employees (EmpCode,EmpName) SELECT 1, 'Adam'
INSERT INTO @Employees (EmpCode,EmpName) SELECT 2, 'David'

DECLARE @sales TABLE(
        EmpCode INT,
        product VARCHAR(50),
        Date DATETIME, 
        Price FLOAT, 
        Amount FLOAT
)

INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 1, 'A', '01 Jan 2009', 5, 10
INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 1, 'A', '01 Mar 2009', 5, 10
INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 1, 'A', '01 May 2009', 5, 10
INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 1, 'A', '01 Jul 2009', 5, 10
INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 1, 'A', '01 Sep 2009', 5, 10

INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 2, 'A', '01 Jan 2009', 5, 10
INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 2, 'A', '01 May 2009', 5, 10
INSERT INTO @sales (EmpCode,product,Date,Price,Amount) SELECT 2, 'A', '01 Sep 2009', 5, 10

DECLARE @Period1Start DATETIME,
        @Period1End DATETIME,
        @Period2Start DATETIME,
        @Period2End DATETIME

SELECT  @Period1Start = '01 Jan 2009',
        @Period1End = '30 Jun 2009',
        @Period2Start = '01 Jul 2009',
        @Period2End = '31 Dec 2009'


SELECT  e.EmpName,
        Totals.Period1,
        Totals.Period2
FROM    @Employees e INNER JOIN
        (
            SELECT  EmpCode,
                    SUM(CASE WHEN Date BETWEEN @Period1Start AND @Period1End THEN Price * Amount ELSE 0 END) Period1,
                    SUM(CASE WHEN Date BETWEEN @Period2Start AND @Period2End THEN Price * Amount ELSE 0 END) Period2
            FROM    @sales
            GROUP BY EmpCode
        ) Totals ON e.EmpCode = Totals.EmpCode

Use subqueries:

select employee.name, (select sum(amount) from sales where date between <date> and <date>) as <month1>, (select sum(amount) from sales where date between <date> and <date>) as <month1>
from employee order by employee.name

A slight change to astander's solution:

SELECT
     E.name,
     SUM(
          CASE
               WHEN S.sale_date BETWEEN @period_1_start AND @period_1_end THEN S.[amount]
               ELSE 0
          END) AS period_1_sales,
     SUM(
          CASE
               WHEN S.sale_date BETWEEN @period_2_start AND @period_2_end THEN S.[amount]
               ELSE 0
          END) AS period_2_sales
FROM
     Employees E
LEFT OUTER JOIN Sales S ON
     S.employee_code = E.employee_code AND
     (
          S.sale_date BETWEEN @period_1_start AND @period_1_end OR
          S.sale_date BETWEEN @period_2_start AND @period_2_end
     )
GROUP BY
     E.name