April 17, 2009 at 2:44 am
I am developing a suite of reports for an HR dept. Done most of them now but a few of the reports need to just report differences which have occurred from one month to the next e.g. cost code changes. The SQL is written in SQL Manager Studio and derived from a large number of tables and executed as sp's in BIDS. The resulting reporting data is stored in #tmp tables. I am a little unsure how to proceed but I think what I need is a var to hold the date for the current month which is then executed and then another var to hold the month part of the report date but for one month ago and the sql to be executed again and then only the differences between the two reported (using EXCEPT) Any advice on how to proceed greatly appreciated.
Current Month End Last Month End
Differences only for report
April 19, 2009 at 11:29 pm
EXCEPT is one way , but its only available in sql 2005 . You need to list the query of current month (ex: using parameter where clause ) then followed by EXCEPT and query for previous month.
select * from tab1 where month = @cmonth
EXCEPT
select * from tab1 where month = @pmonth
or you can use getdate() function instead of params
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply