SQL Query Help

You may need to do a subquery and return entire row based on max index or max date updated.

Will look later.

Palum is right, sounds like you need to use a max(mod_dt) in a subquery in your where clause.

Can't you order the results by Updated time and limit the result list to one? So like SELECT yadda yadda WHERE yadda yadda ORDER BY mod_dt DESC LIMIT 1 ?

EDIT: Oh that won't work if you want the latest schedule of everyone instead of the latest schedule of one person.

Moving this to screenshots because I'm on my phone. Please don't post titties even in spoilers outside of SS, it still loads on work computers.

PS nice titties

Agree with Palum and Khane. Below is one way, assuming you only want one record per employee across any number of nom_day_key (and assuming mod_dt is unique within an employee_key):

select
a.nom_day_key,
a.employee_key,
a.local_start_dt,
a.local_end_dt,
a.mod_dt
from fact_aspect_emp_schedule_detail a
join
(
select
employee_key,
max(mod_dt) as max_mod_dt
from fact_aspect_emp_schedule_detail
group by
employee_key
) b
on a.employee_key = b.employee_key
and a.mod_dt = b.max_mod_dt
--any where statement criteria goes here etc

If I'm right you are looking at something like this:



If you don't group by, you end up with duplicate rows (employee 1 has two schedules for 6/1)
If you group by, whatever you use as a WHERE clause is going to return some artifacts.

My SQL server syntax is a bit rusty but I think the asiest way to do this without going back and redoing the table structure and for ease of understanding/use (at a penalty to run time) would be something like this:

SELECT e.EmployeeName AS Name,e.EmployeeID AS ID,
(SELECT TOP 1 s.StartTime FROM ScheduleTable AS s WHERE e.EmployeeID=s.EmployeeID AND[email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--if(t[e].getAttribute('data-cfhash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-cfemail')){for(e='',r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2)e+='%'+('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */ORDER BY s.DateUpdated DESC) AS StartTime,
(SELECT TOP 1 s.EndTime FROM ScheduleTable AS s WHERE e.EmployeeID=s.EmployeeID AND[email protected]/* <![CDATA[ */!function(t,e,r,n,c,a,p){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--if(t[e].getAttribute('data-cfhash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-cfemail')){for(e='',r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2)e+='%'+('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */ORDER BY s.DateUpdated DESC) AS EndTime
FROM EmployeeTable AS e;

sliverstorm_sl said:

Agree with Palum and Khane. Below is one way, assuming you only want one record per employee across any number of nom_day_key (and assuming mod_dt is unique within an employee_key):

select
a.nom_day_key,
a.employee_key,
a.local_start_dt,
a.local_end_dt,
a.mod_dt
from fact_aspect_emp_schedule_detail a
join
(
select
employee_key,
max(mod_dt) as max_mod_dt
from fact_aspect_emp_schedule_detail
group by
employee_key
) b
on a.employee_key = b.employee_key
and a.mod_dt = b.max_mod_dt
--any where statement criteria goes here etc

Click to expand...

This would be the better performance option but it is more complex and like you mention we don't know table layout RE your key assumptions there.

EDIT: actually thinking about it that may not work because they could update different days on different days. In other words, your ad hoc join table is returning the specific update time from the max grouped by employee key. Since it's clearly not normalized properly you end up with a situation where you could have the same employee but

6/1 updated 5/7 and 5/27
6/2 updated 5/27 and 5/30

Thus it will fail to return data on the 6/1 date because it wasn't updated again on 5/30 like the 6/2 schedule where the max(date updated) date will be joined with. Since it finds no schedule data for 6/1 on 5/30 it will just be blank.

k^M_sl said:

This appears to work and matches up things the way it should be, but when ran for tomorrow its giving 294 rows and there are 414 schedules. I'm quite ecstatic that it's 1 line per employee and matching up to those 294 though, so this looks like its on track.

Edit: Palum is this what you're referring to where it will not pull some of the rows?

Click to expand...

That is a possible culprit (and likely I think).

Palum_sl said:

This would be the better performance option but it is more complex and like you mention we don't know table layout RE your key assumptions there.

EDIT: actually thinking about it that may not work because they could update different days on different days. In other words, your ad hoc join table is returning the specific update time from the max grouped by employee key. Since it's clearly not normalized properly you end up with a situation where you could have the same employee but

6/1 updated 5/7 and 5/27
6/2 updated 5/27 and 5/30

Thus it will fail to return data on the 6/1 date because it wasn't updated again on 5/30 like the 6/2 schedule where the max(date updated) date will be joined with. Since it finds no schedule data for 6/1 on 5/30 it will just be blank.

Click to expand...

I agree. If it needs to return one record per date, I think we would add the date key to the join to force picking the most recent mod_dt per date (again, assuming mod_dt is never null and now unique within an employee_key | nom_day_key combo):

select
a.nom_day_key,
a.employee_key,
a.local_start_dt,
a.local_end_dt,
a.mod_dt
from fact_aspect_emp_schedule_detail a
join
(
select
nom_day_key,
employee_key,
max(mod_dt) as max_mod_dt
from fact_aspect_emp_schedule_detail
group by
nom_day_key,
employee_key
) b
on a.employee_key = b.employee_key
and a.nom_day_key = b.nom_day_key
and a.mod_dt = b.max_mod_dt
--any where statement criteria goes here etc

I don't know SQL server, but your method definitely looks cleaner.

Tits have been given, a vote has to be cast.

A man has to vote.

Gutterflesh_sl said:

I'm a product manager, so I think I can help out here.

Yeah this doesn't look right.you'll need to fix it. The UI also needs to be more user friendly and align with our company branding.

We launch on Tuesday, so we'll need this all wrapped up and pass QA before tomorrow morning.

Click to expand...

jesus fucking christ this is my company in a nutshell... I literally just twitched reading this ...