By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,668 Members | 1,503 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,668 IT Pros & Developers. It's quick & easy.

Same date data for 12 months how?

P: n/a
Below is the sql for data with same date need
to extract + or - 5 days data of same date also.

How to also get data of + and - days related to same date.
SELECT IM.Area,
IM.Location,
IT.itemid,
IT.date,
IT.Qty,
IT.Rate,
IT.Amount
FROM ITran as IT
INNER JOIN
IMast as IM
ON IT.itemid = IM.itemid
WHERE IT.itemid="A101"
and (IT.date='01DEC2003'd
or IT.date='01DEC2003'd-30
or IT.date='01DEC2003'd-61
or IT.date='01DEC2003'd-91
or IT.date='01DEC2003'd-122
or IT.date='01DEC2003'd-153
or IT.date='01DEC2003'd-183
or IT.date='01DEC2003'd-214
or IT.date='01DEC2003'd-244
or IT.date='01DEC2003'd-275
or IT.date='01DEC2003'd-303
or IT.date='01DEC2003'd-334
or IT.date='01DEC2003'd-365)
order by IM.Area, IT.itemid, IT.date desc;
quit;

Captain
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
What data type is IT.Date? "IT.date='01DEC2003'd-30" is not a standard
date/time field type. Is the value actually a date and you are attempting to
subtract 30 from the date of 01 DEC 2003? It appears that what you want is
the value on the first day of the month (+/- 5 days) for the previous 12
months. Is this correct?

--
Wayne Morgan
MS Access MVP
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Below is the sql for data with same date need
to extract + or - 5 days data of same date also.

How to also get data of + and - days related to same date.
SELECT IM.Area,
IM.Location,
IT.itemid,
IT.date,
IT.Qty,
IT.Rate,
IT.Amount
FROM ITran as IT
INNER JOIN
IMast as IM
ON IT.itemid = IM.itemid
WHERE IT.itemid="A101"
and (IT.date='01DEC2003'd
or IT.date='01DEC2003'd-30
or IT.date='01DEC2003'd-61
or IT.date='01DEC2003'd-91
or IT.date='01DEC2003'd-122
or IT.date='01DEC2003'd-153
or IT.date='01DEC2003'd-183
or IT.date='01DEC2003'd-214
or IT.date='01DEC2003'd-244
or IT.date='01DEC2003'd-275
or IT.date='01DEC2003'd-303
or IT.date='01DEC2003'd-334
or IT.date='01DEC2003'd-365)
order by IM.Area, IT.itemid, IT.date desc;
quit;

Captain

Nov 13 '05 #2

P: n/a
Wayne,

it.date is a date field. Typo error
Yes, looking for extraction of same date
for previous consecutive month.

There after, have to do a subtraction i.e.

Date qty Diff (i.e. 5 - 4 = +1 also looking for this value to display)
5/1/2004 5 +1
4/1/2004 4
Captain

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<Q2***************@newssvr22.news.prodigy.com >...
What data type is IT.Date? "IT.date='01DEC2003'd-30" is not a standard
date/time field type. Is the value actually a date and you are attempting to
subtract 30 from the date of 01 DEC 2003? It appears that what you want is
the value on the first day of the month (+/- 5 days) for the previous 12
months. Is this correct?

--
Wayne Morgan
MS Access MVP
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Below is the sql for data with same date need
to extract + or - 5 days data of same date also.

How to also get data of + and - days related to same date.
SELECT IM.Area,
IM.Location,
IT.itemid,
IT.date,
IT.Qty,
IT.Rate,
IT.Amount
FROM ITran as IT
INNER JOIN
IMast as IM
ON IT.itemid = IM.itemid
WHERE IT.itemid="A101"
and (IT.date='01DEC2003'd
or IT.date='01DEC2003'd-30
or IT.date='01DEC2003'd-61
or IT.date='01DEC2003'd-91
or IT.date='01DEC2003'd-122
or IT.date='01DEC2003'd-153
or IT.date='01DEC2003'd-183
or IT.date='01DEC2003'd-214
or IT.date='01DEC2003'd-244
or IT.date='01DEC2003'd-275
or IT.date='01DEC2003'd-303
or IT.date='01DEC2003'd-334
or IT.date='01DEC2003'd-365)
order by IM.Area, IT.itemid, IT.date desc;
quit;

Captain

Nov 13 '05 #3

P: n/a
To get the same date for the previous 12 months, you need to put criteria on
two fields.

First Field:
Field - DateNeeded:(([IT].[Date]>=DateSerial(Year([IT].[Date]),
Month([IT].[Date]), 6-5)) And ([IT].[Date]<=DateSerial(Year([IT].[Date]),
Month([IT].[Date]), 6+5)))
Criteria - True

This will return True or False if IT.Date is 6 +/- 5 days. You will have to
verify that this is a valid day for all months (i.e. <=28) or you won't get
the results you expect. This will error out if any of the records contain a
Null value in IT.Date.

Second Field:
Field - [IT].[Date]
Criteria - >= DateAdd("m", -12, [IT].[Date])

This will give you 7/21/2003 or later if today is 7/21/2004. If you want to
include all of July 2003 then the Second Field should be:

Field - MonthsBack: Format([IT].[Date], "yyyymm")
Criteria - >= Format(DateAdd("m", -12, [IT].[Date]), "yyyymm")

This will use just the year and month values. So, today would show up a
200407 and 12 months ago would show up as 200307.

Just a quick comment, you shouldn't use a reserved word, such as Date, as
the name of a field, variable, or other items. This will prevent Access from
confusing it with the Date function in VBA.

--
Wayne Morgan
Microsoft Access MVP
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Wayne,

it.date is a date field. Typo error
Yes, looking for extraction of same date
for previous consecutive month.

There after, have to do a subtraction i.e.

Date qty Diff (i.e. 5 - 4 = +1 also looking for this value to
display)
5/1/2004 5 +1
4/1/2004 4
Captain

"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:<Q2***************@newssvr22.news.prodigy.com >...
What data type is IT.Date? "IT.date='01DEC2003'd-30" is not a standard
date/time field type. Is the value actually a date and you are attempting
to
subtract 30 from the date of 01 DEC 2003? It appears that what you want
is
the value on the first day of the month (+/- 5 days) for the previous 12
months. Is this correct?

--
Wayne Morgan
MS Access MVP
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
> Below is the sql for data with same date need
> to extract + or - 5 days data of same date also.
>
> How to also get data of + and - days related to same date.
>
>
> SELECT IM.Area,
> IM.Location,
> IT.itemid,
> IT.date,
> IT.Qty,
> IT.Rate,
> IT.Amount
> FROM ITran as IT
> INNER JOIN
> IMast as IM
> ON IT.itemid = IM.itemid
> WHERE IT.itemid="A101"
> and (IT.date='01DEC2003'd
> or IT.date='01DEC2003'd-30
> or IT.date='01DEC2003'd-61
> or IT.date='01DEC2003'd-91
> or IT.date='01DEC2003'd-122
> or IT.date='01DEC2003'd-153
> or IT.date='01DEC2003'd-183
> or IT.date='01DEC2003'd-214
> or IT.date='01DEC2003'd-244
> or IT.date='01DEC2003'd-275
> or IT.date='01DEC2003'd-303
> or IT.date='01DEC2003'd-334
> or IT.date='01DEC2003'd-365)
> order by IM.Area, IT.itemid, IT.date desc;
> quit;
>
> Captain

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.