Re: Help w/pivot
- From: "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@xxxxxxxxxx>
- Date: Sat, 17 Mar 2007 18:44:48 +0100
Ana,
For SQL 2k code, do please look at
http://www.databasejournal.com/features/mssql/article.php/3516331.
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Ana" <nospam@xxxxxxxxx> wrote in message
news:u2kvuCXYHHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
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
--------------------------------------------------------------------------------
Estoy utilizando la versión gratuita de SPAMfighter para usuarios
privados.
Ha eliminado 2939 correos spam hasta la fecha.
Los abonados no tienen este mensaje en sus correos.
¡Pruebe SPAMfighter gratis ya!
.
- References:
- Help w/pivot
- From: Ana
- Help w/pivot
- Prev by Date: Re: how does ado.net SqlDataReader.GetString() know which encoding to read the
- Next by Date: Re: How to obtain significance of the nodes (p-value of the nodes)
- Previous by thread: Help w/pivot
- Next by thread: Text Mining Issue
- Index(es):
Relevant Pages
|
