Pivot question
- From: "Ana" <nospam@xxxxxxxxx>
- Date: Mon, 12 Mar 2007 17:44:20 +0100
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
--------------------------------------------------------------------------------
Estoy utilizando la versión gratuita de SPAMfighter para usuarios privados.
Ha eliminado 2958 correos spam hasta la fecha.
Los abonados no tienen este mensaje en sus correos.
¡Pruebe SPAMfighter gratis ya!
.
- Follow-Ups:
- Re: Pivot question
- From: Michel Walsh
- Re: Pivot question
- Prev by Date: Re: query for tag renewal
- Next by Date: Re: query for tag renewal
- Previous by thread: Multiple Action Queries Currentdb.execute
- Next by thread: Re: Pivot question
- Index(es):
Relevant Pages
|
