I have a database where I draw a report over a range of dates (like a week)
First, I have to convert the hours from UTC to CST
Then for only the last day of the range, I want to exclude everything after a certain hour
I'm tying my brain in knots trying to balance the where, between, or , and constrictors to get what I need. Only started SQL this week, so forgive the n00b question
What have you tried so far?
It would help a lot if you could provide sample data and expected results. Sample data can be setup using a declare or create - and insert statements to populate the temp table.
Declare @testTable table ({column definitions here})
Insert Into @testTable ({column list here})
Values (value, value, value, ...)
, (value, value, value, ...)
...
This would allow us to provide a tested solution that you can then incorporate into your actual query.
With that said - converting from UTC to CST:
, dateColumn at time zone 'UTC' at time zone 'Central Standard Time'
Filtering by date column:
Declare @startDate datetime = dateadd(month, datediff(month, 0, getdate()), 0) --beginning of month
, @endDate datetime = dateadd(hour, 12, dateadd(day, 0, getdate()), 0)) --12pm today
Select ...
From ...
Where dateColumn >= @startDate
And dateColumn < @endDate
If your column is stored as UTC - the best way to do this is to convert your CST parameters to UTC date/time, as that will allow for any indexes on your date/time column to be utilized.
Set @startDate = @startDate at time zone 'Central Standard Time' at time zone 'UTC';
Set @endDate = @endDate at time zone 'Central Standard Time' at time zone 'UTC';
DECLARE @PickedDateStart DATETIME;
DECLARE @PickedDateEnd DATETIME; --uncomment this if you need to do a range
SET @PickedDateStart = '2020-12-27';
SET @PickedDateEnd = '2021-01-01'; -- uncomment this line if you need a range
BEGIN
select convert(date,scd.PickedDateTime) Date,DATEPART(HOUR, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), scd.PickedDateTime)) + 0 Hour,UnitNumber,upper(PickingOperatorID) Operator,count(distinct scd.OrderNumber) Orders,count(distinct od.RxNumber) Scripts from ScriptContainerData scd with(nolock)
join OrderMaster om with(nolock) on om.OrderNumber = scd.OrderNumber
join OrderDetails od with(nolock) on od.OrderNumber = scd.OrderNumber and od.OrderDetailNumber = scd.OrderDetailNumber
--where cast(scd.PickedDateTime as date) = @PickedDateStart --comment this out if you uncomment the next line
where cast(scd.PickedDateTime as date) between @PickedDateStart and @PickedDateEnd --uncomment this line for a range
and OrderType in (1,3) and UnitNumber in (52,53,54)
group by convert(date,scd.PickedDateTime),DATEPART(HOUR, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), scd.PickedDateTime)),UnitNumber,upper(PickingOperatorID)
order by UnitNumber,convert(Date, scd.PickedDateTime), DATEPART(HOUR, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), scd.PickedDateTime))
END;
So, on a range of days, the report kicks out information, but the last day of the report might not go all the way to 2300 hours. I need to truncate everything past close down hour on the last day of the range, leaving all other days of the range alone.
How do you define the end as being 2300 hours - will that change?
You would be much better off using >= and < for your date checks. Converting the column to a date and using between will likely prevent usage of any indexes on the column scd.PickedDateTime.
Where scd.PickedDateTime >= @PickedDateStart
And scd.PickedDateTime < dateadd(hour, 23, @PickedDateEnd)
This will include everything from '2020-12-27 00:00:00.000' through '2021-01-01 22:59:59.997'. When you assign the value '2021-01-01' to a datetime you get '2020-01-01 00:00:00.000'. Using less than you include everything up to but not including that end date.
I am not sure why you have separate columns for date and hour - but you could do something like this in a single column to get date and time:
Select dateadd(hour, datediff(hour, 0, scd.PickedDateTime), 0)
With that said - you are converting to UTC hour which can be incorrect depending on the time and the offset from UTC. If the value in PickedDateTime is '2021-01-01 05:00:00.000' and that is stored as UTC - then your hour in Central Standard Time would be 23 of the previous day.
A better way of looking at this - if the PickedDateTime was performed at '2020-12-31 22:50:01.997' it would be recorded in the table as UTC '2021-01-01 04:50:01.997'.
If your input is in Central Standard Time - and your column is stored as UTC, you could do something like this:
Declare @PickedDateStart datetime;
Declare @PickedDateEnd datetime; --uncomment this if you need to do a range
Set @PickedDateStart = '2020-12-27' at time zone 'Central Standard Time' at time zone 'UTC';
--==== 2300 on the day prior to the end date requested
Set @PickedDateEnd = dateadd(hour, 23, '2021-01-01') at time zone 'Central Standard Time' at time zone 'UTC';
Begin
Select [Date] = dateadd(hour, datediff(hour, 0, scd.PickedDateTime at time zone 'UTC' at time zone 'Central Standard Time'), 0)
, UnitNumber
, upper(PickingOperatorID) Operator
, count(Distinct scd.OrderNumber) Orders
, count(Distinct od.RxNumber) Scripts
From ScriptContainerData scd With(nolock)
Join OrderMaster om With(nolock) On om.OrderNumber = scd.OrderNumber
Join OrderDetails od With(nolock) On od.OrderNumber = scd.OrderNumber
And od.OrderDetailNumber = scd.OrderDetailNumber
--where cast(scd.PickedDateTime as date) = @PickedDateStart --comment this out if you uncomment the next line
Where scd.PickedDateTime >= @PickedDateStart
And @PickedDateEnd < @PickedDateEnd
And OrderType In (1, 3)
And UnitNumber In (52, 53, 54)
Group By
dateadd(hour, datediff(hour, 0, scd.PickedDateTime at time zone 'UTC' at time zone 'Central Standard Time'), 0)
, UnitNumber
, upper(PickingOperatorID)
Order By
UnitNumber
, dateadd(hour, datediff(hour, 0, scd.PickedDateTime at time zone 'UTC' at time zone 'Central Standard Time'), 0)
End;
Thanks.