Date parameter & comparison

[mgerard802]

I have a query where the user enters a begin date & end
date for criteria (1/1/03 - 3/31/03). Another column
sums data and return the sum for the date range entered.
How do I get another column summing the data for the same
date range (ie 1/1/02-3/31/02) for the prior year?
Basically a comparion fo current year vs prior year

 

[Golom]

Try something like

SELECT F1, Sum(F2) As ThisYearSum,

(Select Sum(F2) From tbl
Where DateField Between 
DateAdd("y",-1,[StartDate]) AND DateAdd("y",-1,[EndDate]) ) As LastYearSum

FROM tbl

Where DateField Between [StartDate] AND [EndDate]

 

[mgerard802]

Thanks, here is what I have:
SELECT sum(Timespend) as CY, (select sum(timespend) from tblmain where Period between DateAdd ("y",-1,[StartDate]) and DateAdd("y",-1,[EndDate])) as PY
FROM tblProducers INNER JOIN tblMain ON tblProducers.ProducerID = tblMain.ProducerID
where Period between [StartDate] and [EndDate] and SetID=1;

the 1st column is correct for the current year, but the second column for the prior year is coming up with the same result?

 

[JonFer]

You could also try this:

Select IIf([Period]<[StartDate],&quot;Prior&quot;,&quot;Current&quot; as PeriodYear,
       Sum(timespend) as timespend
From  tblProducers INNER JOIN tblMain ON tblProducers.ProducerID = tblMain.ProducerID
Where Period Between [StartDate] and [EndDate] Or
      DateAdd(&quot;y&quot;,1,Period) Between [StartDate] and
      [EndDate])

It puts the years on different rows but is more straightforward and will run faster on a large table. Are you making sure the range is always less than a year?

If you want to see all prior years do this for the Where:

Where Format(DateAdd("d",DateDiff(StartDate,DateSerial(Year(StartDate),1,1),Period),"mmdd") Betweeen 
    "0101" and Format(DateAdd("d",DateDiff(StartDate,DateSerial(Year(StartDate),1,1),EndDate),"mmdd")

I didn't test the above but the idea is to frameshift the Start and EndDate range to always start at January 1st and then move the Period into the same frame by subtracting the number of days between StartDate and January 1st. You'd have to frameshift to get the right year for the PeriodYear in the SELECT, too.