Last Day Of Previous Month...with a twist

[brymcguire@googlemail.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

[Dan Guzman]

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(CH AR(6), DATEADD(year, -1, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)
AND
SoldDate < CAST(CONVERT(CH AR(6), DATEADD(day, 0, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<brymcguire@goo glemail.comwrot e in message
news:1178789743 .579297.70040@n 59g2000hsh.goog legroups.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
>

[Plamen Ratchev]

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_TIMESTA MP), 0))
AND
SoldDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTA MP), 0)

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[brymcguire@googlemail.com]

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

On 10 May, 13:03, "Dan Guzman" <guzma...@nospa m-online.sbcgloba l.net>
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(CH AR(6), DATEADD(year, -1, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)
AND
SoldDate < CAST(CONVERT(CH AR(6), DATEADD(day, 0, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<brymcgu...@goo glemail.comwrot e in message
>
news:1178789743 .579297.70040@n 59g2000hsh.goog legroups.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

[Chris.Cheney]

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

"Plamen Ratchev" <Plamen@SQLStud io.comwrote 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_TIMESTA MP), 0))
AND
SoldDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTA MP), 0)
>
HTH,
>
Plamen Ratchev

http://www.SQLStudio.com

>

Is it rash to assume that CURRENT_TIMESTA MP 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).

[Plamen Ratchev]

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

Although CURRENT_TIMESTA MP/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_TIMESTA MP, CURRENT_TIMESTA MP
FROM AnyLargeTable
WHERE CURRENT_TIMESTA MP = CURRENT_TIMESTA MP
AND CURRENT_TIMESTA MP = CURRENT_TIMESTA MP


Plamen Ratchev

http://www.SQLStudio.com

[Chris.Cheney]

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

"Plamen Ratchev" <Plamen@SQLStud io.comwrote in news:STG0i.9686 $j63.2095
@newsread2.news .pas.earthlink. net:

Although CURRENT_TIMESTA MP/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_TIMESTA MP, CURRENT_TIMESTA MP
FROM AnyLargeTable
WHERE CURRENT_TIMESTA MP = CURRENT_TIMESTA MP
AND CURRENT_TIMESTA MP = CURRENT_TIMESTA MP
>
>
Plamen Ratchev
http://www.SQLStudio.com

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

Chris

[--CELKO--]

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.

[brymcguire@googlemail.com]

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

On 10 May, 23:22, --CELKO-- <jcelko...@eart hlink.netwrote:

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

[Hugo Kornelis]

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',CURR ENT_TIMESTAMP),
'19990101')
AND solddate < DATEADD(month,
DATEDIFF(month, '20000101',CURR ENT_TIMESTAMP),
'20000101')

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