Hi,
I am looking to create a case statement that gets different quantities. For example, I would like to see how many orders were created between 6pm yesterday till 6pm today, how much created between 3pm yesterday till 3pm today, and how much was dispatched between 3pm yesterday till 3pm today. I created this case statement, but It doesn't seem correct. The 6-6pm quantities are not correct, but the 3pm seem to be correct. I'm using 5 columns, create date, create time, dispatch date, dispatch time and qty. I have the following case statment:
SELECT
CREATE_DATE
, CREATE_TIME
, DISPATCH_DATE
, DISPATCH_TIME
, CASE
WHEN DATEDIFF(DAY,CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME),ADJ_REQ_SHIP) = 1
THEN
CASE
WHEN CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME) BETWEEN
dateadd(hh, 15, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
AND dateadd(ms, -3, dateadd(hh, 15, convert(datetime, convert(char(12), CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
THEN 'SMALLER HOUR WINDOW'
WHEN CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME) BETWEEN
dateadd(hh, 18, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
AND dateadd(ms, -3, dateadd(hh, 18, convert(datetime, convert(char(12), CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
THEN 'NORMAL HOUR WINDOW'
WHEN CAST(CONCAT(RELEASED_DATE, ' ', RELEASED_TIME) AS DATETIME) BETWEEN
dateadd(hh, 15, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(RELEASED_DATE, ' ', RELEASED_TIME) AS DATETIME)))))
AND dateadd(ms, -3, dateadd(hh, 15, convert(datetime, convert(char(12), CAST(CONCAT(RELEASED_DATE, ' ', RELEASED_TIME) AS DATETIME)))))
THEN 'AGREED UPON WINDOW'
END
END AS 'WINDOW'
August 31, 2021 at 7:00 pm
I think your problem is with your WHEN statements. Let us look at the first one:
WHEN CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME) BETWEEN
dateadd(hh, 15, dateadd(dd, -1, convert(datetime, convert(char(12),CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
AND dateadd(ms, -3, dateadd(hh, 15, convert(datetime, convert(char(12), CAST(CONCAT(CREATE_DATE, ' ', CREATE_TIME) AS DATETIME)))))
THEN 'SMALLER HOUR WINDOW'
So, lets break this down. Everything before the BETWEEN looks fine - you are CASTING and CONCATing strings. Not a big deal here. The problem comes AFTER your BETWEEN statement. Let's break the first one down and you may see what is wrong:
dateadd(hh,
15,
dateadd(
dd,
-1,
convert(
datetime,
convert(
char(12),
CAST(
CONCAT(
CREATE_DATE, ' ', CREATE_TIME
) AS DATETIME
)
)
)
)
)
What is wrong with the above is that the date field you are using for the comparison is the exact same date field you are using before the BETWEEN. Why is this problematic? Lets say that Create_Date and Create_Time work out to be "02/01/2021 00:00:00" (in dd/mm/yyyy hh:mm:ss format). You are comparing if that date is between itself minus 1 day plus 15 hours. So you are comparing it to 01/01/2021 15:00:00 which it is obviously greater than as you just did the dateadd on it. Then you are doing the same thing with the second half of the between. So basically you are doing if X-9H < X < X+15H-3ms. Not sure why you are subtracting 3 milliseconds, but that is your logic and the result will always be true.
What you probably want to do is use a dynamic date with a static time. So something like
DATEADD(hh,15,DATEADD(dd,-1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)))
Which will be yesterday at 3:00 PM. To get today at 3 PM you would use:
DATEADD(hh,15,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
CASTING GETDATE() to DATE removes the time portion, and then casting it to DATETIME puts the time in at midnight.
Personally, I would do the date comparison in a WHERE clause to get the values you are looking for rather than in a CASE statement as the CASE statement will stop processing once the first TRUE value is returned. So if an order was placed at 7:00 PM yesterday, it meets all of the requirements for all of your use cases but will only show up on the first CASE statement.
Alternately, you could use 3 bit fields and a CASE statement for each to track which order falls under which category.
If you need more help on this, we will need some sample data, actual output, and expected output.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 31, 2021 at 7:42 pm
Thank you so much for your response, I do see the issue here. My problem is that I cannot use getdate, as I am comparing historical data. I'm doing some root cause analysis so I need to split the days a certain way, to see what's been going on, which is why I splitting the different times.
Also, as you mentioned not to use a case statement, I did figure that because my first statement rings true, the second one doesn't work properly. Do you have any other idea on how to do this? 6pm and 3pm are our 2 different cutoff times, so I need to compare what's been going on in the 24 hours in between.
August 31, 2021 at 7:43 pm
For comparison purposes the date and time columns could be combined in a virtual table ('conv') into datetime columns. Then maybe 3 separate CASE statements so as not to overlap time conditions. Also at least one DATEADD() could be eliminated by using the built in +/-# offset parameter to the GETDATE() function. Because the comparison is DATETIME and not DATE you could use >= and < instead of BETWEEN. Maybe something like this
select CREATE_DATE, CREATE_TIME, DISPATCH_DATE, DISPATCH_TIME,
case when conv.create_dt >= yesterday_dt+three_pm_dt and
conv.create_dt < today_dt+three_pm_dt
then qty else 0 end as [SMALLER HOUR WINDOW],
case when conv.create_dt >= yesterday_dt+six_pm_dt and
conv.create_dt < today_dt+six_pm_dt
then qty else 0 end as [NORMAL HOUR WINDOW],
case when conv.released_dt >= yesterday_dt+three_pm_dt and
conv.released_dt < today_dt+three_pm_dt
then qty else 0 end as [AGREED UPON WINDOW]
from WTable w
cross apply (values (cast(w.CREATE_DATE as datetime)+
cast(w.CREATE_TIME as datetime),
cast(w.RELEASED_DATE as datetime)+
cast(w.RELEASED_TIME as datetime),
cast(cast(getdate() -1 as date) as datetime),
cast(cast(getdate() as date) as datetime),
cast('15:00:00.000' as datetime),
cast('18:00:00.000' as datetime)))
conv(create_dt, released_dt, yesterday_dt, today_dt,
three_pm_dt, six_pm_dt);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 31, 2021 at 8:38 pm
There are other methods, with less use of date functions. If uahmed90 provides CREATE TABLE statement and some reasonable test records, we could try another approach.
Zidar's Theorem: The best code is no code at all...
August 31, 2021 at 8:57 pm
Hi Zidar,
Here's the create table code and attached is a sample output. Hopefully this helps!
CREATE TABLE [dbo].[FactBP](
[CREATE_DATE] [date] NULL,
[CREATE_TIME] [varchar](8) NULL,
[ADJ_REQ_SHIP] [datetime] NULL,
[RELEASED_DATE] [date] NULL,
[RELEASED_TIME] [varchar](8) NULL,
[ORDER_QTY] [numeric](5, 0) NULL,
[RELEASED_QTY] [numeric](7, 0) NULL,
[SHIP_QTY] [numeric](7, 0) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
)
August 31, 2021 at 9:04 pm
Thank you for this response. It does work, but it's not what I need, because I need to compare with historical data, so I can't use getdata() in this instance. Any other ideas on doing this?
August 31, 2021 at 9:32 pm
I think maybe you can just change the CROSS APPLY in Steve's code?!:
...
cross apply (values (cast(w.CREATE_DATE as datetime)+
cast(w.CREATE_TIME as datetime),
cast(w.RELEASED_DATE as datetime)+
cast(w.RELEASED_TIME as datetime),
cast(w.CREATE_DATE as datetime) - 1, --<--
cast(w.CREATE_DATE as datetime), --<<--
cast('15:00:00.000' as datetime),
cast('18:00:00.000' as datetime)))
conv(create_dt, released_dt, yesterday_dt, today_dt,
three_pm_dt, six_pm_dt);
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 1, 2021 at 12:29 pm
Thank you for this response. It does work, but it's not what I need, because I need to compare with historical data, so I can't use getdata() in this instance. Any other ideas on doing this?
I'm curious about Zidar's approach. Scott has the right idea tho. Instead of 'yesterday_dt' the code calculates the previous day datetime 'prev_day_dt' and today based on the CREATE datetime. The '+/- #n' offset parameter only works (afaik) with the GETDATE() function and not literal values so this adds back the DATEADD(DAY, -1, ...) to a separate CROSS APPLY.
select w.CREATE_DATE, w.CREATE_TIME, w.DISPATCH_DATE, w.DISPATCH_TIME,
case when conv.create_dt >= calc.prev_day_dt+three_pm_dt and
conv.create_dt < today_dt+three_pm_dt
then 1 else 0 end as [SMALLER HOUR WINDOW],
case when conv.create_dt >= calc.prev_day_dt+calc.six_pm_dt and
conv.create_dt < calc.today_dt+calc.six_pm_dt
then 1 else 0 end as [NORMAL HOUR WINDOW],
case when conv.released_dt >= calc.prev_day_dt+calc.three_pm_dt and
conv.released_dt < calc.today_dt+calc.three_pm_dt
then 1 else 0 end as [AGREED UPON WINDOW],
qty
from WTable w
cross apply (values (cast(w.CREATE_DATE as datetime)+
cast(w.CREATE_TIME as datetime),
cast(w.RELEASED_DATE as datetime)+
cast(w.RELEASED_TIME as datetime)))
conv(create_dt, released_dt)
cross apply (values (cast(cast(dateadd(day, -1, conv.create_dt) as datetime)),
cast(cast(conv.create_dt as date) as datetime),
cast('15:00:00.000' as datetime),
cast('18:00:00.000' as datetime)))
calc(prev_day_dt, today_dt,
three_pm_dt, six_pm_dt);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 1, 2021 at 3:27 pm
The '+/- #n' offset parameter only works (afaik) with the GETDATE() function and not literal values
I believe the +/- n days works with any datetime value. I 'm also fairly sure SQL treats GETDATE() as a literal value anyway.
At any rate, this code works fine:
;WITH test_data AS (
SELECT CAST(GETDATE() AS date) AS CREATE_DATE
)
SELECT
CREATE_DATE, cast(CREATE_DATE as datetime) - 1
FROM test_data
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 1, 2021 at 4:24 pm
Thanks for the code Scott. I was messing around with the GETDATE function and +/-# parameter and how it worked wasn't totally clear to me. I looked and didn't find much about it in the Docs either. It's a shame the 'date' type doesn't have likewise offset
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Zidar's solution will always be in data modeling and table design 🙂
The first thing to do is to add two calculated fields to the table design - that does not affect what you have:
DrOP TABLE IF EXISTS #FactBP
;
CREATE TABLE #FactBP(
[CREATE_DATE] [date] NULL,
[CREATE_TIME] [datetime] NULL,--- use Time data type instead of varchar!
--[ADJ_REQ_SHIP] [datetime] NULL,
[RELEASED_DATE] [date] NULL,
[RELEASED_TIME] [datetime] NULL,--- use Time data type instead of varchar!
[ORDER_QTY] [numeric](5, 0) NULL,
[RELEASED_QTY] [numeric](7, 0) NULL,
[SHIP_QTY] [numeric](7, 0) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
---- added by Zidar:
Create_DateTime AS CAST(CREATE_DATE AS DateTime) + CAST(CREATE_TIME AS DAteTime),
Released_DateTime AS CAST([RELEASED_DATE] AS DateTime) + CAST([RELEASED_TIME] AS DAteTime)
)
;
It is easier to work with full datetime than with separate date and time portions. Let's add some sample data -not all 1000 rows you provides, just a few. Nothing wrong with providing 1000 rows sample, but including them all would make the post unnecessary cluttered. Here:
-- Add sample data:
INSERT INTO #FactBP (CREATE_DATE,CREATE_TIME,RELEASED_DATE,RELEASED_TIME,ORDER_QTY,RELEASED_QTY,SHIP_QTY)
VALUES
('2021-08-03','21:37:00','2021-08-04','17:56:49',1,1,1)
, ( '2021-08-04' ,'11:31:00','2021-08-04', '19:46:46',2, 2,2)
, ( '2021-08-03' ,'22:29:00','2021-08-04', '19:46:46',1, 1,1)
, ( '2021-08-04' ,'4:39:00','2021-08-04', '19:46:46',1, 1,1)
, ( '2021-08-03' ,'22:59:00','2021-08-04', '19:46:46',1, 1,1)
, ( '2021-08-04' ,'10:58:00','2021-08-04', '19:46:46',1, 1,1)
, ( '2021-08-03' ,'23:58:00','2021-08-04', '19:46:46',1, 1,1)
, ( '2021-08-08' ,'12:28:00','2021-08-09', '21:33:27',1, 1,1)
, ( '2021-08-08' ,'13:28:00','2021-08-09', '21:33:27',1, 1,0)
, ( '2021-08-07' ,'10:28:00','2021-08-08', '20:55:49',1, 1,1)
, ( '2021-08-07' ,'9:58:00','2021-08-08', '20:55:49',1, 1,1)
, ( '2021-08-09' ,'6:28:00','2021-08-11', '5:50:33' ,1, 1,1)
, ( '2021-08-08' ,'15:58:00','2021-08-11', '5:50:33' ,1, 1,1)
, ( '2021-08-08' ,'16:28:00','2021-08-11', '5:50:33' ,1, 1,1)
, ( '2021-08-28' ,'12:27:00','2021-08-30', '7:45:04' ,2, 2,2)
, ( '2021-08-28' ,'16:27:00','2021-08-30', '7:45:04' ,1, 1,1)
, ( '2021-08-29' ,'4:28:00','2021-08-30', '9:26:31' ,2, 2,2)
, ('2021-08-29' ,'4:28:00','2021-08-30', '9:26:31' ,1, 1,1)
;
Next thing we need is a variant of a Calendar table. In case you don't have one, here it goes:
-- In order to build table with dates, we need table of numbers:
DROP TABLE IF EXISTS #Numbers
CREATE TABLE #Numbers (Number int NOT NULL PRIMARY KEY CLUSTERED)
;
INSERT INTO #Numbers (Number)
VALUES ( 1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
, (12),(13), (14), (15),(16),(17),(18),(19), (20)
, (21), (22), (23), (24), (25),(26),(27),(28),(29),(30)
;
-- Add dates to numbers, any convenient start date can be used. To be in synch with your sample,
-- 2021 Aug 01 is assumedn as start date.
DROP TABLE IF EXISTS #Dates
;
SELECT Number, Dateadd (d,Number,'20210801') AS MyDate
INTO #Dates
FROM #Numbers
;
SELECT * FROM #Dates
;
From table #Dates, it is easy to create table or view of intervals. I prefer table, the code will be easier to follow. We will create interval 3 PM for preceding day and 3 PM for the next one:
-- Add intervals [previous day @ 3:pm, current day @ 3:PM]
-- 3 PM = 15.00 hours
DROP TABLE IF EXISTS #Itervals
;
; WITH MyDates AS
(
SELECT Number, Dateadd (hh,15,MyDate) AS MyDate-- time = 3 PM on given date
FROM #Dates
)
SELECT A.Number, StartTime = A.MyDate, EndTime = B.MyDAte
INTO #Itervals
FROM MyDates AS A
JOIN MyDates AS B ON A.Number = B.Number - 1
;
SELECT * FROM #Itervals
;
All we need to do now is to see which of our values Create_DateTime (calculated field, added to the original design) fits in given intervals. here is a full sample, sorted by Interval.StartDate, so you can count:
-- Display intervals for Created_DateTime:
SELECT I.StartTime, I.EndTime, F.Create_DateTime
, F.ORDER_QTY
FROM #FactBP AS F
, #Itervals AS I
WHERE F.Create_DateTime >= I.StartTime AND F.Create_DateTime <=I.EndTime
ORDER BY StartTime, Create_DateTime
Results:
StartTime EndTime Create_DateTime ORDER_QTY
---------------- ---------------- ---------------- ---------------------------------------
2021-08-03 15:00 2021-08-04 15:00 2021-08-03 21:37 1
2021-08-03 15:00 2021-08-04 15:00 2021-08-03 22:29 1
2021-08-03 15:00 2021-08-04 15:00 2021-08-03 22:59 1
2021-08-03 15:00 2021-08-04 15:00 2021-08-03 23:58 1
2021-08-03 15:00 2021-08-04 15:00 2021-08-04 04:39 1
2021-08-03 15:00 2021-08-04 15:00 2021-08-04 10:58 1
2021-08-03 15:00 2021-08-04 15:00 2021-08-04 11:31 2
2021-08-06 15:00 2021-08-07 15:00 2021-08-07 09:58 1
2021-08-06 15:00 2021-08-07 15:00 2021-08-07 10:28 1
2021-08-07 15:00 2021-08-08 15:00 2021-08-08 12:28 1
2021-08-07 15:00 2021-08-08 15:00 2021-08-08 13:28 1
2021-08-08 15:00 2021-08-09 15:00 2021-08-08 15:58 1
2021-08-08 15:00 2021-08-09 15:00 2021-08-08 16:28 1
2021-08-08 15:00 2021-08-09 15:00 2021-08-09 06:28 1
2021-08-27 15:00 2021-08-28 15:00 2021-08-28 12:27 2
2021-08-28 15:00 2021-08-29 15:00 2021-08-28 16:27 1
2021-08-28 15:00 2021-08-29 15:00 2021-08-29 04:28 2
2021-08-28 15:00 2021-08-29 15:00 2021-08-29 04:28 1
(18 rows affected)
Completion time: 2021-09-01T12:31:46.4412052-04:00
The final step:
SELECT I.StartTime, I.EndTime, cnt = count(F.Create_DateTime)
--, F.ORDER_QTY,F.RELEASED_QTY,F.SHIP_QTY
FROM #FactBP AS F
, #Itervals AS I
WHERE F.Create_DateTime >= I.StartTime AND F.Create_DateTime <=I.EndTime
GROUP BY I.StartTime, I.EndTime
;
results:
StartTime EndTime cnt
---------------- --------------- ---
2021-08-03 15:00 2021-08-04 15:0 7
2021-08-06 15:00 2021-08-07 15:0 2
2021-08-07 15:00 2021-08-08 15:0 2
2021-08-08 15:00 2021-08-09 15:0 3
2021-08-27 15:00 2021-08-28 15:0 1
2021-08-28 15:00 2021-08-29 15:0 3
(6 rows affected)
This works for interval 3 yesterday to 3 pm. For another intervals, 6 to 6, anything else, the process should be repeated, in general. In practice, perhaps the best thing is to have all expected intervals in table Intervals, with flags to tell ono kind of interval of another.
This may look complicated, but table of numbers is built once in a lifetime, so is Calendar table. Intervals can be set in advance. For repeated work, only thing to do is run some queries.
Good luck
Zidar's Theorem: The best code is no code at all...
September 8, 2021 at 3:33 pm
>> I am looking to create a CASE statement NOT NULLsic .. <<
There is no CASE statement in SQL; it is an expression
>> that gets different quantities. For example, I would like to see how many orders were created between 6pm yesterday till 6pm today, how much created between 3pm yesterday till 3pm today, and how much was dispatched between 3pm yesterday till 3pm today. <<
The ANSI/ISO standards for SQL use timestamps based on the ISO 8601 standards. You probably call this 24 hour time. I also see that you're used to using punch cards. Your code is written in all uppercase. Just like we did in the 1960s. You put a comma at the start of each line so you could rearrange the deck of punch cards. Just like we did in the 1960s. You treat temporal data as if it was character strings. That is pure 1960s COBOL!
It is also considered very rude for the past 30 years not to post DDL to start your question. However, what you did post is not a table at all! Did you know that a table has to have a key? Here's my attempt at correcting what you finally did post for us:
why did you use NULL on all the columns? That prevents ever having a proper key! Why did you use the numeric data type instead of integers? Are you writing COBOL or some Oracle dialect instead of SQL Server? Did you know that we have a DATETIME2(n) data type in SQL Server that combines a date and a time? There's no need to use COBOL style 1960s character strings. Why do you have an identity column? What attribute do you think it models ? In RDBMS. This is what is called a table property, it is nothing like an attribute and it is metadata.
CREATE TABLE Something_Orders
(order_nbr CHAR(16) NOT NULL PRIMARY KEY, –– a key is not an option!
creation_timestamp timeslot_name
adj_req_ship_timestamp DATETIME2(0) NOT NULL,
released_date DATE NOT NULL,
order_qty INTEGER NOT NULL,
released_qty INTEGER NOT NULL,
ship_qty INTEGER NOT NULL);
Because this is a simple posting, I have not put in the CHECK() constraints that a competent DDL would include. You might want to actually fill them in.
You are still trying to use SQL as a computational language and right procedural code in it. My advice would be to learn to think in terms of data in a database language. You have time frames that go over dates, so build a table with a century's worth of data in it:
CREATE TABLE Timeslots
(timeslot_name CHAR(5) NOT NULL PRIMARY KEY,
timeslot_start_timestamp DATETIME2(0) NOT NULL,
timeslot_end_timestamp DATETIME2(0) NOT NULL,
CHECK (timeslot_start_timestamp < timeslot_end_timestamp)
);
you now do groupings based on the timeslot name. No proprietary computations. Fast, simple joins. The ability to adjust your timeslots in the event of banking holidays. You also need to download a copy of the Rick Snodgrass PDF on temporal SQL ; it is free from the University of Arizona.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 10, 2021 at 11:57 am
This was removed by the editor as SPAM
October 12, 2021 at 3:34 am
you now do groupings based on the timeslot name. No proprietary computations. Fast, simple joins. The ability to adjust your timeslots in the event of banking holidays.
Cool! Got code?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply