sql server mseq: 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.

[quoted text, click to view]

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
--

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"


[quoted text, click to view]

[quoted text, click to view]

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
--