Pivot question

[Ana]

Hi,
I've four tables with the following fields:

Ta:
Agent_ID
Claim_Date
Claim_Cost

Tb:
Agent_ID
Agent_Name

Tc:
Agent_ID
Contract_Date
Contract_Cost

Td:
Agent_ID
Agent_Exp_Date
Agent_Exp_Cost

I need to create a query with the following information:
1. Grouping the dates per month so a yearly report can be created.
2. For each month should be applied the following formula:
SUM(CASE WHEN DATEPART(M, Tc.Contract.date) = 1 THEN Tc.Contract_Cost -
(Tc.Contract_Cost *0.10 + Ta.Claim.Cost + Td.Agent_Exp.Cost) ELSE 0 END) AS
Jan. and so on.to obtain the 12 months. Then SUM (all months per Agent) AS
TotalAnnual.

WHERE DATEPART (YYYY, all dates) = 2007

I've created tree subqyueries linking them with Agent_ID with no avail and
was wondering if someone can help me out.

TIA
Ana

SQL2K -Access 2003

 

[Michel Walsh]

SELECT DATEPART(mm, ContractDate) AS Expr1, SUM(ContractCost) AS Expr2

FROM table1

WHERE DATEPART(yyyy, ContractDate) = 2007

GROUP BY DATEPART(mm, ContractDate) WITH ROLLUP





will produce one row per month, plus one row, with a null under Expr1, for
the annual total. Sure, you replace the simple ContractCost with your Case
expression, and the FROM clause with the proper inner joins (which seems to
be inner join between tc and ta through their Agent_Id field, and another
also with td, again with its Agent_Id field:





FROM ( ta INNER JOIN tc ON ta.Agent_id=tc.Agent_id ) INNER JOIN td ON
tc.Agent_ID=td.Agent_ID



Vanderghast, Access MVP