Set report date for date other than today in SQL reporting

1
votes

I have a report that I have been asked to produce. The first column of data is total time entered from the first of the year to the end of the previous month. The next column of data is total time from beginning of "current" month to the end of "current" month.

For example. If this report was being run for March then the first column would be total time for Jan and Feb and the second column would be total time for March. If I were to run it in April then the first column would be total time for Jan/Feb/Mar and the second column total time for April etc.

I am using various expressions to get first date of the year, last date of previous month, first date of this month, last date of this month. All working fine and it runs like a dream if you run the report in the current month (i.e. March) but if you want to run the report in April for March's data it won't do it as it's reading the date on the computer and using that to calculate the prev month.

In Crystal reports you can set a report date. Is there something similar in SQL reporting? I'm assuming you DECLARE the report date in your initial query but I haven't yet found the right combination of functions.

  • This report is for an external bit of software that we run.
  • The only parameters I can use are @FromDate and @ToDate and these are set as text rather than date
  • I was planning on using the @ToDate to set the report date but would obviously have to convert it from text first

Any guidance very much appreciated

2
sql-serverdatereporting-services
When you say "total time" do you mean the column would just have like the number of days or something? i.e. For Feburary col1 would just be 31 and column 2 would be 28? - Element Zero
Total time would be hours. So, for example, 8 hours a day for 5 days for four weeks would be 160hrs - C_K

2 Answers

0
votes

Try this. You could actually do it without all the declare statements, I just did those to break it down to be easy to read.

declare @date_entered datetime = '2/7/2017'
declare @start_of_year datetime =  DATEADD(yy, DATEDIFF(yy, 0, @date_entered), 0)
declare @start_of_month datetime = DATEADD(month, DATEDIFF(month, 0, @date_entered), 0)
declare @end_of_month datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date_entered)+1,0))
declare @end_of_last_month datetime = DATEADD(day,-1,@start_of_month)
-- Now remove weekends from days found
declare @amount_of_days_previous tinyint = datediff(day,@start_of_year,@end_of_last_month) - (datediff(wk, @start_of_year, @end_of_last_month) * 2)
declare @amount_of_days_this tinyint = DATEDIFF(day,@start_of_month,@end_of_month) - (datediff(wk, @start_of_month, @end_of_month) * 2)

select  @amount_of_days_previous * 8 as WorkHoursinDaysPrevious, 
    @amount_of_days_this * 8 as WorkHoursinDaysThis
0
votes

I had a think over the weekend and realised that I was approaching the problem all wrong. I was trying to set the report date so that my query could read that date and then go back and find the relevant date (first date in the year, last date of previous month etc).

What I needed to do was use my date criteria (FromDate and ToDate) in my DATEADD selections.

CONVERT(datetime,@FromDate,103) AS FIRSTDAYOFYEAR, 
DATEADD(D, - 1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', CONVERT(datetime,@ToDate,103)), '19000101')) AS LASTDAYPREVMONTH, 
DATEADD(MONTH,DATEDIFF(MONTH, '19000101',CONVERT(datetime,@ToDate,103)), '19000101') AS FIRSTDAYMONTH, 
CONVERT(datetime,@ToDate,103) AS LASTDAYMONTH,

This is now much better because my "first date of year" can be anything the user wants, not just the first day of the year.