MS SQL Server MSEQ - YTD Last Year again

Author

YTD Last Year again

Benedikt Fridbjornsson

2005-09-02, 11:23 am

Hi there, I'd like to ask you for help with following:

I am trying to select YTD for last year in my sales table. Our accounting
year is from 1st of July to 30 June.

select *
from Salestable
Where Postingdate > "1st of July 2004" and
Postingdate < "today last year"

best regards,
Benedikt F.
Computer Department
Iceland Seafood Int.

Hugo Kornelis

2005-09-02, 8:24 pm

On Fri, 2 Sep 2005 14:59:01 -0000, Benedikt Fridbjornsson wrote:

>Hi there, I'd like to ask you for help with following:
>
>I am trying to select YTD for last year in my sales table. Our accounting
>year is from 1st of July to 30 June.
>
>
>
>select *
>from Salestable
>Where Postingdate > "1st of July 2004" and
>Postingdate < "today last year"

Hi Benedikt,

The easiest way to get this done is to use a calendar table.

http://www.aspfaq.com/show.asp?id=2519

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Benedikt F

2005-09-02, 8:24 pm

Hi Hugo

I have created the Calendar table with the FY column. How can I use your
Calendar table to select what I need from my salestable.

select *
from Salestable
Where Postingdate > "1st of July 2004" and
Postingdate < "today last year"

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:p0dhh15uk4v5l94
t10gkr7063d0ae39kop@
4ax.com...
> On Fri, 2 Sep 2005 14:59:01 -0000, Benedikt Fridbjornsson wrote:
>
>
> Hi Benedikt,
>
> The easiest way to get this done is to use a calendar table.
>
> http://www.aspfaq.com/show.asp?id=2519
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis

2005-09-05, 8:25 pm

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8727

On Fri, 2 Sep 2005 23:54:11 -0000, Benedikt F wrote:

>Hi Hugo
>
>I have created the Calendar table with the FY column. How can I use your
>Calendar table to select what I need from my salestable.
>
>select *
>from Salestable
>Where Postingdate > "1st of July 2004" and
>Postingdate < "today last year"

Hi Benedikt,

Here is one possible way:

SELECT s.Column1, s.Column2, ...
FROM Salestable AS s
INNER JOIN Calendar AS c
ON c.dt = s.Postingdate
WHERE c.FY = (SELECT FY
FROM Calendar
WHERE dt = DATEADD(year, -1,
DATEADD(day,
DATEDIFF(day,
'20040101',

CURRENT_TIMESTAMP),
'20040101')))
AND s.Postingdate < DATEADD(year, -1,
DATEADD(day,
DATEDIFF(day,
'20040101',
CURRENT_TIMESTAMP),
'20040101'))
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links