comparing dates not working

[alh1002]

Big picture: I want to get a year to date field for every day

I have MTD for each month, so in Jan MTD and YTD are equal

but from then on YTD= YTD of previous month times MTD
So with that information


I tried
SELECT A.ladder_date, A.gMTDPER, (select B.gMTDPER from gMTDSummary B WHERE
DatePart("m",[A.LADDER_DATE])=(DatePart("m",[B.ladder_date])-1))
FROM dailySUMMARY AS A;

to see the previous MTD number. But the last column returned is blank

The query gMTDSummary only has monthly information. In reality I all the same info in the table dailySummary

 

[lameid]

For YTD wouldn't your want the sum of everything previous in the year?

Secondly, this becomes really easy to handle in a report with grouping and seting a running sum for a control.

If you use a report you don't even have to create a Month To Date summary table. If you need the MTD summary table, why not just make a YTD summary as well?

 

[alh1002]

The issue is this is percentages, so you can't sum. You must use products. We can't use real numbers because on a regular basis the base changes. Hence the real measure is percentages

 

[lameid]

Pehaps if you told us what is in dailySummary and what you are trying to accomplish, we might be able to help more. I don't know what your percents are percents for... I have no real idea of what result you are looking for.