DateDiff Select Statement
- From: Toni <Toni@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 11 Nov 2008 11:09:01 -0800
I have the below select statement to basically pull all records that have
data for a full year. The below statement works.
SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (DateDiff("yyyy",VolumeDate,TermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#]=SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",VolumeDate,TermDate))) Is Null
Or ((DateDiff("yyyy",VolumeDate,TermDate)))>0))
ORDER BY Member.[Store#], SIVolume.VolumeDate;
We then realized that the company has an unusual fiscal year. Starts in Nov
and ends in Oct.
Example Data
Store Volume volumeDate TermDate (in a different table)
100 $500.00 10/31/05 10/15/06
100 $700.00 10/31/06
Because we want a full year worth of data the data for store 100 should
appear for 2005 only.
Another Example
Store Volume volumeDate TermDate (in a different table)
999 $300.00 10/31/05 11/13/06
999 $400.00 10/31/06
Data for store 999 should show both 2005 and 2006 data, because 11/13/06 is
part of FY07.
I tried adding 2 months to the termDate and compare that date to volumeDate.
Below is what I have tried to do but was unsuccessful, it now prompts me for
FYTermDate.
SELECT Member.[Store#], Member.StoreName, Member.TermDate,
SIVolume.VolumeDate, (Format(DateAdd("m",+2,TermDate))) AS FYTermDate,
(DateDiff("yyyy",VolumeDate,FYTermDate)) AS YearDiff
FROM Member
INNER JOIN SIVolume ON Member.[Store#] = SIVolume.StoreNumber
WHERE ((((DateDiff("yyyy",[VolumeDate],[FYTermDate]))) Is Null
Or ((DateDiff("yyyy",[VolumeDate],[FYTermDate])))>0))
ORDER BY Member.[Store#], SIVolume.VolumeDate;
Any help would be greatly appreciated
Tsharp
.
- Follow-Ups:
- Re: DateDiff Select Statement
- From: MGFoster
- Re: DateDiff Select Statement
- Prev by Date: Re: Query for a range of records
- Next by Date: Re: Time difference
- Previous by thread: Calculate business days
- Next by thread: Re: DateDiff Select Statement
- Index(es):
Relevant Pages
|
