June 21, 2023 at 6:51 am
Hi all,
I have this report in SSRS that I need to set up but I'm going around in circles with the date, time selection in the report. Below is the script I've been given and it works fine when you input the required date, time in the script. They want this report in a schedule at different times of the day and it must contains data in certain time periods;
SELECT
$$$CHEQUENO
,AMOUNT
,$$$ROUTINGSEQNO
,$$$ACCOUNTNO
,$$RETURNREASON
,$$$$ROUTINGNO
,$$POSTINGDATE
,$$$REFERENCENO
,$$$REFERENCENO
FROM $$$INWARDDEBITPOSTING
WHERE ($$$REFERENCENO is null or $$$REFERENCENO = '')
and CAST(ENTRYDATETIME AS DATE) >= '2023-05-30'
and CAST(ENTRYDATETIME AS DATE) <= '2023-05-30'
ORDER BY $$POSTINGDATE asc
Report schedule;
10am - Extract to contain data from 5pm the previous day to 10am current day
12pm - Extract to contain data from 10am to 12pm
2pm - Extract to contain data from 12pm to 2pm
4pm - Extract to contain data from 2pm to 4pm
5pm - Extract to contain data from 4pm to 5pm
I don't have a problem getting it to give back data for 1 or 2 or 3, etc. days ago or for today, the challenge is when to get it to satisfy when to give data for a few hours ago from previous day and so on.
Any help will be appreciated - THank you
June 21, 2023 at 8:01 am
You would want to do some datemath depending on the current hour, and substitute the dates into the query.
So when current hour is 10, you want to do something like this pseudo code
ENTRY >= GETDATE() -17hours AND ENTRY < GETDATE()
When it's 12pm you want to do
ENTRY >= GETDATE() -2hours AND ENTRY < GETDATE()
etc, etc.
Take a look at DATEADD, DATEPART, and maybe DATEDIFF depending on your requirements, and if you get stuck post back with what you have tried.
June 21, 2023 at 7:14 pm
@AntGreen - using GETDATE() - N will subtract days not hours. So this needs a bit more:
WHERE ENTRY >= DATEADD(hour, CASE datepart(hour, GETDATE())
WHEN 10 THEN -7 --5pm previous day
WHEN 12 THEN 10
WHEN 14 THEN 12
WHEN 16 THEN 14
WHEN 17 THEN 16
ELSE 17 --anything after 5pm use 5pm
END, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) -- Add/Subtract from midnight
AND ENTRY < DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0) -- Truncate to current hour
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2023 at 7:40 am
Yeah that was what I was getting at to use dateadd to minus the 17 from the hour, it was rough pseudo code, to steer the OP in the right direction
July 19, 2023 at 5:54 am
Thank you @AntGreen and Jeffrey.
OK, I just want to ask, how is the best way to set this up? I tried doing it as an SQL job and then SSRS report (after I couldn't get it to work) but it's still not working. When I run the script itself in SQL server, it returns stuff but the job and query in report builder in SSRS doesn't give back anything. Let me know if you need to look at the script I put together. Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply