Selecting State from table based on a Begin and End Date
-
Greetings,
I have a Report that prompts for a begin and End Date and I need to select a state based on the values entered. I've The table I am comparing the begin and end dates to looks like this:
ID InitialState NewState Date Rules
123 Active Suspended 8/1/2022 1. If enddate < min(datechanged) for id then want earliest (initialstate) (Active)
123 Suspended Active 8/19/2022 2. If enddate = min(datechanged) want earliest newstate (Suspended)
123 Active Review 8/27/2022 3. If startdate >= max(datechanged) want latest newstate (Completed)
123 Review Completed 9/16/2022 4. If any part of the startdate and enddate falls within the Datechanged values, want the newstate less than or equal to the end date
The date parameter is entered without a time component and I have a function that strips the time when doing the date comparison. This works quite well except I am coming across some entries where the status has changed multiple times in a day and that screws up the rule when the date range falls within the list of dates. In this case I want the last value from the table with the same matching day but I can't figure out how to factor this in. Any suggestions would be appreciated. An example of the problem is shown below:
ID InitialState NewState Date
456 Review Suspended 2022-08-04 11:23:00
456 Suspended Active 2022-08-04 14:27:00
456 Active Completed 2022-08-04 16:03:00
Select
Case
When Exists (Select * from state where clientid = 123 and striptime(@enddate) < (Select top 1 striptime(date) from state where clientid = 123 order by stateid))
Then (Select top 1 initialstate from state where clientid = 123 order by stateid)
When Exists (Select * from state where clientid = 123 and striptime(@enddate) = (Select top 1 striptime(date) from state where clientid = 123 order by stateid))
Then (Select top 1 NewState from state where clientid = 123 order by stateid)
When Exists (Select * from state where clientid = 123 and striptime(@startdate) >= (Select top 1 striptime(date) from state where clientid = 123 order by stateid desc))
Then (Select top 1 NewState from state where clientid = 123 order by stateid desc)
When Exists (Select * from state where clientid = 123 and striptime(@enddate) > (Select top 1 striptime(date) from state where clientid = 123 order by stateid desc))
Then (Select top 1 NewState from state where clientid = 123 order by stateid desc)
When Exists (Select * from state where clientid = 123 and striptime(@enddate) < (Select top 1 striptime(date) from state where clientid = 123 order by stateid desc)
and striptime(@enddate) >(Select top 1 striptime(date) from state where clientid = 123 order by stateid) ) --enddate falls within range of values in datechange table
Then (Select top 1 NewState from state where clientid = 123 and striptime(@enddate) > striptime(Date) order by stateid desc)
Else 'Scream'
End as State -
Sounds something for the last_value function https://www.sqltutorial.org/sql-window-functions/sql-last_value/
You may get a quicker response if you paste the TSQL-scripts to reproduce the testcase
so we can run it in a "test" database without having to guess / type over the tables and queries
-
We have DATE data types, souse them and not the old Sybase datetime from UNIX. Does the status have to be contained in the temporal interval, or can it overlap?
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/state-transition-constraints/
We have DATE data types, souse them and not the old Sybase datetime from UNIX. Does the status have to be contained in the temporal interval, or can it overlap?
The old DATETIME datatype is marvelous in it's capabilities compared to the great mistakes they made with DATE, DATETIME2, and TIME. They tried to coverup for some of those horrible mistakes by creating DATEDIFF_BIG but failed, yet again, by not creating DATEADD_BIG.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I disagree. DATE, TIME and DATATIME2 match to the ANSI/ISO Standards. I do not have to play with PDP/11 clock ticks to fake a date, the temporal math is up to NIST specs, etc. Whar do you dislike?
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
I disagree. DATE, TIME and DATATIME2 match to the ANSI/ISO Standards. I do not have to play with PDP/11 clock ticks to fake a date, the temporal math is up to NIST specs, etc. Whar do you dislike?
No... DATE, DATETIME2, and TIME don't even come close to meeting ISO standards.
- The ISO standards dictate that you should be able to determine Duration by subtracting the start date and time from the end date and time. DATETIME does that. The "new" datatypes cannot do that. They throw an error instead.
- The ISO standards dictate that you should be able to determine the end date by adding a duration to the the start date and time. DATETIME does that. The "new" datatypes cannot do that. They throw an error instead.
- The ISO standards dictate that you should be able to determine the start date by subtracting a duration to the the start date and time. DATETIME does that. The "new" datatypes cannot do that. They throw an error instead.
Don't take my word for it, though. Take the word of the "code". Here's the code that proves that DATETIME meets all 3 of the ISO standards above...
--===== Variables for demonstation purposes
DECLARE @StartDT DATETIME = '2022-09-20 18:58:09'
,@EndDT DATETIME = '2022-09-21 06:07:25'
,@Duration DATETIME = 0
;
--===== DATETIME meets ISO requirement cited in #1 above.
-- (Subtract @StartDT from @EndDT to produce duration.)
-- CONVERT is being used ONLY to make the output
-- satisfactory for human consumption.
SELECT @Duration = @EndDT-@StartDt
;
SELECT Duration = CONVERT(CHAR(8),@Duration,108)
;
--===== DATETIME meets ISO requirement cited in #2 above.
-- (Add Duration to Start Date and Time to produce End Date and Time.)
SELECT End_Date_Time = @StartDT + @Duration
,Original_End_Date_Time = @EndDT
;
--===== DATETIME meets ISO requirement cited in #3 above.
-- (Subtract Duration from End Date and Time to produce Start Date and Time.)
SELECT Start_Date_Time = @EndDT - @Duration
,Original_Start_Date_Time = @StartDT
;And, here are the results... just as expected. And, guess what, Joe? I didn't have to play with even one "PDP/11 clock tick". 😉
Changing only the datatype of the variables from DATETIME to DATETIME2 (and the comments), let's see what happens...
--===== Variables for demonstation purposes
-- DATETIME2 IMMEDIATELY FAILS.
DECLARE @StartDT DATETIME2 = '2022-09-20 18:58:09'
,@EndDT DATETIME2 = '2022-09-21 06:07:25'
,@Duration DATETIME2 = 0
;
--===== DATETIME2 DOES NOT MEET THE REQUIREMENT cited in #1 above.
-- IT FAILS BEFORE IT GETS STARTED.
SELECT @Duration = @EndDT-@StartDt
;
SELECT Duration = CONVERT(CHAR(8),@Duration,108)
;
--===== DATETIME2 DOES NOT MEET THE REQUIREMENT cited in #2 above.
-- IT FAILS BEFORE IT GETS STARTED.
SELECT End_Date_Time = @StartDT + @Duration
,Original_End_Date_Time = @EndDT
;
--===== DATETIME2 DOES NOT MEET THE REQUIREMENT cited in #3 above.
-- IT FAILS BEFORE IT GETS STARTED.
SELECT Start_Date_Time = @EndDT - @Duration
,Original_Start_Date_Time = @StartDT
;And here are the results from that... FAILURES ACROSS THE BOARD!
If you try the DATE or TIME datatypes, you'll get similar failures across the board.
Like I said, the "new" datatypes are NOT, by any stretch of the imagination, ISO compliant.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
To get the last datetime for a given set of datetimes, you'd typically use ROW_NUMBER(), like this:
select ...
From (
select *, row_numnber() over(partition by cast(date as date), clientid order by date desc) as row_num
from state
) as state2
where state2.row_num = 1SQL 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".
-
Without the time portion, or an auto incrementing identity field, how is it possible to know which is the most recent ?
Start capturing the time portion ??
-
IDENTITY is not a field in SQL; it is a table property. A field is part of a column that has some meaning, like year, month & day in a DATE column. But onto your question, look up "lawful time"; by law, bank deposits credit before withdrawls in a given day in an account. Shop tickets and other sequences have lawful times. It's tricky!
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Simak was wrong; time is not the simplest thing. There are points in time ("high noon at the OK Coral") and maybe space,then there are durarions ("that is a 15 minute trip" and invervals (" Chrismas Day 2022" with a start and maybe an end point (see the half open interval model in ISO Standards). We do not have the INTERVAL data type, so you get those ereors when you try you use INTEGERs to fake them.
With old DATETIME you were always chopping those 3.33 ms clock ticks to .000 or pushing them up .998.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
Simak was wrong; time is not the simplest thing. There are points in time ("high noon at the OK Coral") and maybe space,then there are durarions ("that is a 15 minute trip" and invervals (" Chrismas Day 2022" with a start and maybe an end point (see the half open interval model in ISO Standards). We do not have the INTERVAL data type, so you get those ereors when you try you use INTEGERs to fake them.
With old DATETIME you were always chopping those 3.33 ms clock ticks to .000 or pushing them up .998.
Dates and times are internally represented as integers for the temporal datatypes in SQL server. Any INTERVAL datatype in other databases is also based on integers and, of course, all of those are based on bits that are assigned values of 1 or 0's with other bits representing if we actually know those bits or need to treat them as if we don't (NULL).
The old DATETIME having a resolution of rounded 3.33 milliseconds is a whole lot better than many systems that were based on whole seconds or even whole minutes. The newer DATETIME2 and TIME datatypes can have resolutions all over the place and are quite useful for people needing the finer resolutions.
They also did us all a major favor with the OFFSET functionality and a few related functions that I consider of major good importance.
You'll get no argument on any of that from me.
None of that has anything to do with what I'm talking about. What I'm talking about is that they flubbed the dub when they made the new datatypes and they did so in a major way, as I've previously described.
Once they realized the mistake, they attempted to soften the blow by creating DATEDIFF_BIG. A valiant attempt that works nicely except for the rather obvious and unfortunate fact that they forgot to create a DATEADD_BIG in the process.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
Once they realized the mistake, they attempted to soften the blow by creating DATEDIFF_BIG. A valiant attempt that works nicely except for the rather obvious and unfortunate fact that they forgot to create a DATEADD_BIG in the process.
This actually came up as an issue a while back and it went unmentioned. In this thread in this post a reason the precision was limited to microseconds instead of nanoseconds is because no DATEADD_BIG. The interval could be calculated using DATEDIFF_BIG but not applied. There's probably a work around but what would've been the simple way was not available
Extracting a Median Date out of a Group of Records using a Query with NTILE
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Changing 
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply