Last Day Of Previous Month...with a twist

Hi,



I have a requirement to design a query that identifies items sold
between two dates. There is a 'SoldDate' datetime field used to
register what date the item was sold.

The query needs to identify all sales between the last day of the
previous month and going back one year.

What I would like to do is to design a query / stored procedure that
will dynamically create the criteria to allow the client to simply run
the query or stored proc.

I know how to establish the last day of the previous month part, I'm
just not sure of how best to design the remainder of the query.

Thank in advance

Re: Last Day Of Previous Month...with a twist

> The query needs to identify all sales between the last day of the
> previous month and going back one year.

Below is one method. I generally recommend using >= and < instead of
BETWEEN for datetime data types. This will better handle datetime values
that include time.

WHERE
SoldDate >= CAST(CONVERT(CHAR(6), DATEADD(year, -1, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)
AND
SoldDate < CAST(CONVERT(CHAR(6), DATEADD(day, 0, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)

--
Hope this helps.

Dan Guzman
SQL Server MVP

wrote in message
news:1178789743.579297.70040@n59g2000hsh.googlegroups.com...
> Hi,
>
>
>
> I have a requirement to design a query that identifies items sold
> between two dates. There is a 'SoldDate' datetime field used to
> register what date the item was sold.
>
> The query needs to identify all sales between the last day of the
> previous month and going back one year.
>
> What I would like to do is to design a query / stored procedure that
> will dynamically create the criteria to allow the client to simply run
> the query or stored proc.
>
> I know how to establish the last day of the previous month part, I'm
> just not sure of how best to design the remainder of the query.
>
> Thank in advance
>

Re: Last Day Of Previous Month...with a twist

Here is an alternative to Dan's method, just using only the datetime
functions:

WHERE
SoldDate >= DATEADD(year, -1, DATEADD(month, DATEDIFF(month, 0,
CURRENT_TIMESTAMP), 0))
AND
SoldDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Last Day Of Previous Month...with a twist

On 10 May, 13:03, "Dan Guzman"
wrote:
> > The query needs to identify all sales between the last day of the
> > previous month and going back one year.
>
> Below is one method. I generally recommend using >= and < instead of
> BETWEEN for datetime data types. This will better handle datetime values
> that include time.
>
> WHERE
> SoldDate >= CAST(CONVERT(CHAR(6), DATEADD(year, -1, DATEDIFF(day, 0,
> GETDATE())), 112) + '01' AS datetime)
> AND
> SoldDate < CAST(CONVERT(CHAR(6), DATEADD(day, 0, DATEDIFF(day, 0,
> GETDATE())), 112) + '01' AS datetime)
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> wrote in message
>
> news:1178789743.579297.70040@n59g2000hsh.googlegroups.com...
>
>
>
> > Hi,
>
> > I have a requirement to design a query that identifies items sold
> > between two dates. There is a 'SoldDate' datetime field used to
> > register what date the item was sold.
>
> > The query needs to identify all sales between the last day of the
> > previous month and going back one year.
>
> > What I would like to do is to design a query / stored procedure that
> > will dynamically create the criteria to allow the client to simply run
> > the query or stored proc.
>
> > I know how to establish the last day of the previous month part, I'm
> > just not sure of how best to design the remainder of the query.
>
> > Thank in advance- Hide quoted text -
>
> - Show quoted text -


Hi Dan & Plamen,

Thanks for the solutions. Both worked great.

B

Re: Last Day Of Previous Month...with a twist

"Plamen Ratchev" wrote in news:BjE0i.8597$Ut6.2872
@newsread1.news.pas.earthlink.net:

> Here is an alternative to Dan's method, just using only the datetime
> functions:
>
> WHERE
> SoldDate >= DATEADD(year, -1, DATEADD(month, DATEDIFF(month, 0,
> CURRENT_TIMESTAMP), 0))
> AND
> SoldDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com
>

Is it rash to assume that CURRENT_TIMESTAMP will not be called twice for
each row? Perhaps only twice for the whole query? If it is called more than
once (as implied by the query), there will be a problem if the query is run
at a time such that midnight on the last day of the month occurs between
two calls. Or have I missed something (which I admit is likely).

Re: Last Day Of Previous Month...with a twist

Although CURRENT_TIMESTAMP/GETDATE is listed as non-deterministic, it is
deterministic at the statement level (it is evaluated only once per
statement).

Try this query on any large table:

SELECT *, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM AnyLargeTable
WHERE CURRENT_TIMESTAMP = CURRENT_TIMESTAMP
AND CURRENT_TIMESTAMP = CURRENT_TIMESTAMP


Plamen Ratchev
http://www.SQLStudio.com

Re: Last Day Of Previous Month...with a twist

"Plamen Ratchev" wrote in news:STG0i.9686$j63.2095
@newsread2.news.pas.earthlink.net:

> Although CURRENT_TIMESTAMP/GETDATE is listed as non-deterministic, it is
> deterministic at the statement level (it is evaluated only once per
> statement).
>
> Try this query on any large table:
>
> SELECT *, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
> FROM AnyLargeTable
> WHERE CURRENT_TIMESTAMP = CURRENT_TIMESTAMP
> AND CURRENT_TIMESTAMP = CURRENT_TIMESTAMP
>
>
> Plamen Ratchev
> http://www.SQLStudio.com

I hoped that was the case - thanks for confirming it.

Chris

Re: Last Day Of Previous Month...with a twist

>> I know how to establish the last day of the previous month part, I'm just not sure of how best to design the remainder of the query. <<

Instead of using procedural coding, why not use a table of the
reporting periods for a decade or two? A simple BETWEEN predicate
will classify each sale quickly and give you extra control over non-
operating days, etc.

Re: Last Day Of Previous Month...with a twist

On 10 May, 23:22, --CELKO-- wrote:
> >> I know how to establish the last day of the previous month part, I'm just not sure of how best to design the remainder of the query. <<
>
> Instead of using procedural coding, why not use a table of the
> reporting periods for a decade or two? A simple BETWEEN predicate
> will classify each sale quickly and give you extra control over non-
> operating days, etc.

Hi Joe,

Thanks for the feedback. Your solution of "using a table of reporting
periods" was one of a number that I did think about at the time.
However, as I have learned over the years when it comes to using SQL,
there is , as we somtimes say here in the UK, "more than one way to
skin a cat..."

Now I'm not saying that I won't adopt your solution, just that its
good for me to have a number of "options" up my sleeve.

By the way I have read and own a number of your books. They have been
a big help :)

Thanks

Bryan

Re: Last Day Of Previous Month...with a twist

On 10 May 2007 15:22:08 -0700, --CELKO-- wrote:

>>> I know how to establish the last day of the previous month part, I'm just not sure of how best to design the remainder of the query. <<
>
>Instead of using procedural coding, why not use a table of the
>reporting periods for a decade or two? A simple BETWEEN predicate
>will classify each sale quickly and give you extra control over non-
>operating days, etc.

Hi Joe,

When has using a call to a standard function in a query started to be
"procedural coding"?

Using a table of periods is a great technique, especially when there are
exceptions. But in situations without exceptions, using builtin
functions is far faster than joining to a secondary table.

For the original question in this thread, the best and fastest technique
is:

WHERE solddate >= DATEADD(month,
DATEDIFF(month,'20000101',CURRENT_TIMESTAMP),
'19990101')
AND solddate < DATEADD(month,
DATEDIFF(month,'20000101',CURRENT_TIMESTAMP),
'20000101')

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis