Help with this query

PankajBanga

Pankaj Banga
Local time
Tomorrow, 02:10
Joined
Jan 17, 2005
Messages
12
My data looks like this in table tblDividend

CompanyKey DividendType PeriodEndDate DPS ExDivDate
1582 Q2 30/12/2001 0.03 25/01/2002
1582 Q4 30/06/2002 0.03 23/07/2002
1582 Q3 30/06/2002 0.03 22/04/2002
1582 Q2 30/12/2002 0.03 22/01/2003
1582 Q1 30/12/2002 0.03 23/10/2002
1582 Q4 30/06/2003 0.0375 28/07/2003
1582 Q3 30/06/2003 0.0375 24/04/2003
1582 Q2 30/09/2003 0.0187 27/01/2004
1582 Q1 30/12/2003 0.0187 24/10/2003
1582 Q4 30/03/2004 0.0187 26/07/2004
1582 Q3 30/06/2004 0.0187 23/04/2004
1582 Interim 30/09/2004 0.01875 26/10/2004
1582 Interim 30/12/2004 0.01875 25/01/2005

I need to write a query that gets SUM of DPS by PeriodEndDate. SUM should be based on half yearly basis for each year. For instance, for year 2004 it should sum all the DPS that falls into first 6 months (Jan – Jun) and next 6 months (Jul – Dec). Also, I want to show MAX(PeriodEndDate) and MAX(ExDivDate) for each half, as follows

CompanyKey MaxPeriodEndDate DPS MaxExDivDate
1582 30/12/2001 0.03 25/01/2002
1582 30/06/2002 0.06 23/07/2002
1582 30/12/2002 0.06 22/01/2003
1582 30/06/2003 0.075 28/07/2003
1582 30/12/2003 0.0374 27/01/2004
1582 30/06/2004 0.0374 26/07/2004
1582 30/12/2004 0.0375 25/01/2005

I got it working for a particular year and particular interval by doing something like this

SELECT CompanyKey, SUM(DPS), MAX(PeriodEndDate), MAX(MaxExDivDate)
FROM tblDividend
WHERE CompanyKey =1582
AND DatePart('yyyy',PeriodEndDate) = 2004 AND DatePart('m',PeriodEndDate) >= 7
GROUP BY CompanyKey;

But just can’t get on hands on for all the records!!!
 
I sugget you calculate the "half" that the PeriodEndDate belongs to, then group, Sum and Max accordingly.

e.g.

SQL:-
Code:
SELECT tblDividend.CompanyKey, DatePart("yyyy",[PeriodEndDate]) & Format(IIf(DatePart("m",[PeriodEndDate])<=6,1,2),"00") AS Half, Sum(tblDividend.DPS) AS SumOfDPS, Max(tblDividend.PeriodEndDate) AS MaxOfPeriodEndDate, Max(tblDividend.ExDivDate) AS MaxOfExDivDate
FROM tblDividend
GROUP BY tblDividend.CompanyKey, DatePart("yyyy",[PeriodEndDate]) & Format(IIf(DatePart("m",[PeriodEndDate])<=6,1,2),"00")
ORDER BY tblDividend.CompanyKey, DatePart("yyyy",[PeriodEndDate]) & Format(IIf(DatePart("m",[PeriodEndDate])<=6,1,2),"00");

HTH

Regards

John.
 
someone suggested this

SELECT tblDividend.CompanyKey, Sum(tblDividend.DPS) AS SumOfDPS,
Max(tblDividend.PeriodEndDate) AS MaxOfPeriodEndDate,
Max(tblDividend.ExDividendDate) AS MaxOfExDivDate
FROM tblDividend
WHERE CompanyKey =1582
GROUP BY tblDividend.CompanyKey, Year([PeriodEndDate]), Month([PeriodEndDate])<7

isn't this much efficient???
 

Users who are viewing this thread

Back
Top Bottom