SQL Query Help

  • Guest, it's time once again for the massively important and exciting FoH Asshat Tournament!



    Go here and give us your nominations!
    Who's been the biggest Asshat in the last year? Give us your worst ones!
Kyougou

Kyougou

<Gold Donor>
1,639
5,300
I'm assuming that when you tried to filter by day you did something like:

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
and nom_day_key = day_you_want

and thats why you didnt see all the results, I think if you add the clause to the inner query it works the way you want it to, like so:


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
where nom_day_key = day_you_want
group by
employee_key
) b
on a.employee_key = b.employee_key
and a.mod_dt = b.max_mod_dt
 
Ravishing

Ravishing

Uninspiring Title
<Bronze Donator>
8,456
3,577
I do a lot of SQL at work but am self-learned so usually have to use my Google-fu.
When presented with a situation like yours, first instinct is you need to do some sort of JOIN.

Because I suck at writing SQL from memory, here's the first solution I found:
how do I query sql for a latest record date for each user - Stack Overflow

Using your example, I attempted to translate it for you. This might work, might not. I could have fucked something up somewhere. I didn't actually test it.

Might need another WHERE clause at the end, not exactly sure
biggrin.png


rrr_img_135594.jpg
 
Khane

Khane

Got something right about marriage
20,556
14,278
That inner join is the same thing as the sub select in a where clause. But many people find SQL looks cleaner using ANSI joins rather than WHERE clause pseudo joins
 
C

Composter

Golden Knight of the Realm
505
22
Yeah, I'd rather filter in the join, personally, but sometimes it just makes more sense if you have your subquery in a where clause. But I don't really have to do this any more, since I bumped up to management.
 
T

Thorbadin

Trakanon Raider
16
10
Not sure which database you are using but on a decent one you can use analytics (oracle). Basically with analytics you do a group by on a line without using a group by clause.Analytic functions by Example | Oracle FAQis a good post I have used often on it. It's alot faster then joining like the example above because you only need to scan the data once and do not need to do any joins.

So you add a column with something like this:
ROW_NUMBER( ) OVER (PARTITION BY a.employee_key ORDER BY a.mod_dt desc NULLS LAST) ROW_NUMBER_EMP

What this will give you is a row number ordered by the mod_dt desc for each partition in this case employee_key.

Example data:

emp_key, date, row_number
emp_A, 20161001, 3
emp_A, 20161002, 2
emp_A, 20161003, 1
emp_B, 20161003, 1
emp_C, 20161003, 2
emp_C, 20161005, 1

You put that in a subquery and you select * from that sub_query where row_number = 1.

Those fonctions are extremely powerfull and you can do stuff with them you can only dream of doing with sub queries and normal joins basically.
 
Amzin

Amzin

Lord Nagafen Raider
2,917
361
The beauty and terror that is the possibilities in SQL. It looks like you're using T-SQL but Thorbadin's solution should still work for what you're after. So it'd look roughly like this:

SELECT * FROM
(ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY mod_dt desc) AS 'Row_Num'
,nom_day_key as 'Date'
,employee_key as 'Emp_ID'
,Local_START_DT as 'Start Time'
,Local_END_DT as 'End Time'
,mod_dt as 'Updated On'

FROM fact_aspect_emp_schedule_detail
WHERE mod_dt IS NOT NULL ) L1

WHERE ROW_NUM = 1

I don't believe T-SQL supports NULLS LAST but I've never tried it so can't say. There's a few other ways to do this as well using joins and where clauses and whatnot but if your server supports OVER then this is by far the simplest way.
 
T

TKde0

6
0
from fact_aspect_emp_schedule_detail detail
where --(your other filters)
-- maybe filter on nom_day_key = getdate() if you want to. May need to cast getdate() as a string if it can't or you don't want it implicit
and detail.mod_dt = (
select max(detail_sub.mod_dt)
from fact_aspect_emp_schedule_detail detail_sub
where detail.employee_key = detail_sub.employee_key
and detail.nom_day_key = detail_sub.nom_day_key
--if there are other table keys add them here like above
)
 
K

k^M

Blackwing Lair Raider
2,741
2,008
Fawe_sl said:
I do a lot of SQL at work but am self-learned so usually have to use my Google-fu.
When presented with a situation like yours, first instinct is you need to do some sort of JOIN.

Because I suck at writing SQL from memory, here's the first solution I found:
how do I query sql for a latest record date for each user - Stack Overflow

Using your example, I attempted to translate it for you. This might work, might not. I could have fucked something up somewhere. I didn't actually test it.

Might need another WHERE clause at the end, not exactly sure
biggrin.png
Click to expand...
This worked perfectly and coincidentally managed to solve another issue where split shifts get assigned. Much appreciated for everyone who chipped in thoughts!

Will talk to her about sharing something else as it seems she got a kick out of it
biggrin.png
 
Ridas

Ridas

Pay to play forum
2,889
4,143
k^M_sl said:
This worked perfectly and coincidentally managed to solve another issue where split shifts get assigned. Much appreciated for everyone who chipped in thoughts!

Will talk to her about sharing something else as it seems she got a kick out of it
biggrin.png
Click to expand...
Post her penis :>
 
You must log in or register to reply here.