Help with access query 2

[meinhunna]

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!!!

 

[traingamer]

typed, not tested

SELECT CompanyKey, SUM(DPS), DatePart('yyyy',PeriodEndDate)& iif(DatePart('m',PeriodEndDate)<7, "06", "12") as PED, MAX(MaxExDivDate)
FROM tblDividend
WHERE CompanyKey =1582

GROUP BY CompanyKey, 3;

traingamer

 

[PHV]

Something like this ?
SELECT CompanyKey, SUM(DPS), MAX(PeriodEndDate), MAX(MaxExDivDate)
FROM tblDividend
GROUP BY CompanyKey, Year(PeriodEndDate), Int((Month(PeriodEndDate)-1)/6);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Try:
SELECT tblDividend.CompanyKey, Sum(tblDividend.DPS) AS SumOfDPS,
Max(tblDividend.PeriodEndDate) AS MaxOfPeriodEndDate,
Max(tblDividend.ExDivDate) AS MaxOfExDivDate
FROM tblDividend
GROUP BY tblDividend.CompanyKey, Year([PeriodEndDate]), Month([PeriodEndDate])<7;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[meinhunna]

Thanks everyone.

traingamer i cudn't make ur's work though.

PHV and dhookom cud u pls through some light on how does the logic works.

PHV why have to subtracted 1 from Month(PeriodEndDate) and then dividing by 6, where as i get same result set with Month([PeriodEndDate])<7

 

[PHV]

I group by semester number.
Duane groups by boolean value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meinhunna]

Sorry I am not a veteran with db query. In case of Month(PeriodEndDate) < 7, does the GROUP BY clause first groups all the DPS where month is < 7 for the year and then groups the remaining DPS?

Secondly, I couldn’t understand “group by semester number”.

 

[PHV]

Yes, because True=-1 and False=0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244