August 9, 2023 at 4:31 pm
I have an employee table with job Start and End dates. I also have a shift table of all available shifts for the company. Employee activity is contained in the #value table. I'm trying to obtain all shifts each employee crossed based on #EmpTable.Enc_Start and #EmpTable.Enc_end....and which shift an employee had 'activity' (#value.ProcDate). I've tried to create the query but it's not taking the 1st shift into account. Also, if the employee has two activities in one shift, I just need the last date (example: Employee 125 has two 'ProcDates' in one shift, I just need the 2021-01-02 13:02). Thanks so much in advance.
IF OBJECT_ID('TempDB..#Emptable','U') IS NOT NULL
DROP TABLE #Emptable
create table #EmpTable
(PatID Int
,Enc_start DateTime
,Enc_end DateTime
)
IF OBJECT_ID('TempDB..#Shifttable','U') IS NOT NULL
DROP TABLE #Shifttable
create table #Shifttable
(Shift_Date Date
,Shift_Type varchar(10)
,time_start DateTime
,time_end DateTime
)
IF OBJECT_ID('TempDB..#value','U') IS NOT NULL
DROP TABLE #value
create table #value
(PatID varchar(10)
,ProcID Int
,ProcDate DateTime
)
Insert into #Shifttable
values
('2021-01-01', 'Day', '2021-01-01 07:01:00.000', '2021-01-01 19:00:00.000')
,('2021-01-01', 'Night', '2021-01-01 19:01:00.000', '2021-01-02 07:00:00.000')
,('2021-01-02', 'Day', '2021-01-02 07:01:00.000', '2021-01-02 19:00:00.000')
,('2021-01-02', 'Night', '2021-01-02 19:01:00.000', '2021-01-03 07:00:00.000')
Insert into #EmpTable
values
(123, '2021-01-01 08:15:00.000', '2021-01-04 15:23:00.000')
,(124, '2021-01-01 19:54:00.000', '2021-01-06 23:35:00.000')
,(125, '2021-01-01 07:15:00.000', '2021-01-05 15:13:00.000')
,(126, '2021-01-02 17:43:00.000', '2021-01-04 22:37:00.000')
Insert into #value
values
(123, 4, '2021-01-01 08:43:00.000')
,(124, 4, '2021-01-01 20:43:00.000')
,(125, 4, '2021-01-02 08:25:00.000')
,(125, 4, '2021-01-02 13:02:00.000')
,(126, 4, '2021-01-02 20:45:00.000')
Select
p.PatID
,p.Enc_start
,p.Enc_end
,d.time_start
,d.time_end
,v.ProcDate
from #EmpTable p
left join #Shifttable d on d.time_start >= p.Enc_start and d.time_end <= p.Enc_end
left join #value v on v.PatID=p.PatID and v.ProcDate >= d.time_start and v.ProcDate <= d.time_end
order by p.PatID,d.time_start
August 9, 2023 at 10:38 pm
This was removed by the editor as SPAM
August 9, 2023 at 11:09 pm
Using the sample data that you've so nicely provided, can you post what the required output is?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2023 at 2:54 pm
You've made a very common mistake when working with intervals. You're comparing starts with starts and ends with ends. Instead, you should be comparing whether each interval starts before the other interval ends.
Also, since you only want one record from the #Value table, I would use an OUTER APPLY with a TOP(1) rather than a LEFT OUTER JOIN.
The following code produces your expected results.
SELECT
p.PatID
,p.Enc_start
,p.Enc_end
,d.time_start
,d.time_end
,v.ProcDate
from #EmpTable p
left join #Shifttable d on d.time_start <= p.Enc_end and p.Enc_start <= d.time_end
OUTER APPLY
(
SELECT TOP(1) *
FROM #value v
WHERE v.PatID=p.PatID and v.ProcDate >= d.time_start and v.ProcDate <= d.time_end
ORDER BY v.ProcDate DESC
) AS v
order by p.PatID,d.time_start
Also, I recommend that you start using the more succinct and intuitive DROP TABLE IF EXISTS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 10, 2023 at 3:03 pm
Also, many people hesitate to download files from the Internet. You should create expected results in the same way that you created your sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 14, 2023 at 4:49 pm
this works like a charm. I appreciate the help and your friendly suggestions!
August 17, 2023 at 5:41 am
This was removed by the editor as SPAM
August 18, 2023 at 6:12 am
This was removed by the editor as SPAM
August 22, 2023 at 4:34 am
You've made the usual beginner's mistake of confusing a table with a 1950s file system. We have had the daytime2(n) datatype for several years now, so there's no need to use the original Sybase temporal data types. A table should be named for what it is and very often. Obviously, this table does not model people, but the timecards. Why are all your columns NULL-able? That is actually impossible in a properly designed SQL table.
Perhaps the worst mistake is that you don't seem to know that by definition, a table has to have a key. Otherwise this is just a deck of old punchcards modeled in SQL. One of the blessings of DDL is a jump constraints on it, so instead of fixing things after they get screwed up you can use a check() and save yourself thousands of lines of code. Identifiers are by definition a nominal scale; remember the first week of your data modeling course? Nominal scales are modeled as character strings, not integers. I also question that you need a full timestamp down to nanoseconds for this, but I went ahead and wrote it that way anyway.
CREATE TABLE Timesheets
(patient_id CHAR(10) NOT NULL,
enc_start_timestamp DATETIME2(7) NOT NULL,
enc_end _timestamp DATETIME2(7),
CHECK(enc_start_timestamp < enc_end _timestamp),
PRIMARY KEY (patient_id , enc_start_timestamp)
);
Putting the affix "-table" in a table name is an error of design called a "tibble". Phil Factor wrote a poem about about it in one of his old columns.
CREATE TABLE Shift_Calendar
(shift_date DATE NOT NULL,
shift_type CHAR(10) NOT NULL
CHECK(shift_type IN ('day', 'night'),
shift_start_timestamp DATETIME2(7) NOT NULL,
shift_end _timestamp DATETIME2(7),
CHECK(shift_start_timestamp < shift_end _timestamp),
PRIMARY KEY (shift_date, shift_type)
);
And I made a guess on the key. Pretty sure the datatypes are right. I'm not sure how you handle the procedures, since you're in coating them with integers instead of the international standards for medical procedures. They also should have an expected duration, and it's a lot easier to remove a mole than it is to replace a human heart.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 23, 2023 at 1:34 am
Nominal scales are modeled as character strings, not integers.
Instead of the normal long retort that proves you wrong that subject, I jump straight to the punchline this time... Bullshit. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2023 at 5:13 pm
I think I'll go to the other extreme. I am waiting for eye surgery right now, so there will be a little delay, but I will develop an article for an online or printed edition of an SQL forum. That'll let me go into the details about the nature of scales and measurements, why validation and verification are important for data integrity, and maybe a brief list of all the ANSI/ISO standards that do not use integers for nominal scales (unfortunately, that's all of them! They are always kept strings).
Please post DDL and follow ANSI/ISO standards when asking for help.
August 28, 2023 at 1:21 am
I think I'll go to the other extreme. I am waiting for eye surgery right now, so there will be a little delay, but I will develop an article for an online or printed edition of an SQL forum. That'll let me go into the details about the nature of scales and measurements, why validation and verification are important for data integrity, and maybe a brief list of all the ANSI/ISO standards that do not use integers for nominal scales (unfortunately, that's all of them! They are always kept strings).
I wish you good fortune with the eye surgery, Joe.
As for standards, I like proving them wrong. For example, Random GUIDs as a Clustered Key causes massive fragmentation, right? Actually, not. They're the epitome of how people believe and index should operate. It turns out that the former supposed "Best Practice" index maintenance is the issue and, within that, it's because REORGANIZE doesn't work the way people think it does and it's certainly not the table little resource kitten everyone makes it out to be. I have code that destroys all those myths and mistaken "Best Practices".
But, getting back to you... take care of your eyes first. Heh... and sorry... I can't let the "Dad Joke" go to waste.. maybe that'll help us see "eye-to-eye" on things. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply