Best way to write TSQL query

Question

I'm curious if there is a better way to write this query, either syntactically to make simpler/easier to follow, or for processing speed.

I've been writing a lot of similar queries recently and hoping to streamline this. I'm not a developer by trade, but I have to dive into sql

quintiles table

costcenter	quintile	quintilevalue
A	Max	50
A	Q1	8
A	Q2	12
A	Q3	14
A	Q4	18
B	Max	45
B	Q1	5
B	Q2	10
B	Q3	12
B	Q4	16

employees table

costcenter	employee	hiredate
A	W	2021-01-01
A	X	2021-02-08
B	Y	2020-12-16
B	Z	2021-01-15

workcomplete table

employee	workdate	widgetsassembled
W	2021-02-26	4
W	2021-03-05	5
X	2021-05-24	6
X	2021-05-31	3
Y	2021-04-07	2
Y	2021-04-14	8
Z	2021-02-07	4
Z	2021-02-14	1

My goal: for each record in the workcomplete table, find out what the tenure was when the employee did the work and what quintile it falls in.

employee	workdate	widgetsassembled	costcenter	tenure	smallestquintile	quintile
W	2021-02-26	4	A	8	8	Q1
W	2021-03-05	5	A	9	12	Q2
X	2021-05-24	6	A	15	18	Q4
X	2021-05-31	3	A	16	18	Q4
Y	2021-04-07	2	B	16	16	Q4
Y	2021-04-14	8	B	17	45	Max
Z	2021-02-07	4	B	4	5	Q1
Z	2021-02-14	1	B	5	5	Q1

This is what I did, it works fine:

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),

employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'

),

workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1


)
SELECT t.*
    ,q.quintile
FROM (
    SELECT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure 
        ,MIN(q.quintilevalue) as smallestquintile
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue
    GROUP BY wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate)
)t
LEFT JOIN quintiles q
    ON t.smallestquintile = q.quintilevalue and t.costcenter = q.costcenter

This also works.

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),

employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'

),

workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1


)
SELECT t.*
    ,q.quintile
FROM (
    SELECT DISTINCT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure 
        ,MIN(q.quintilevalue) OVER (PARTITION BY wc.employee, wc.workdate) as smallestquintile
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue

)t
LEFT JOIN quintiles q
    ON t.smallestquintile = q.quintilevalue and t.costcenter = q.costcenter

Is there a simpler way to do this, without nesting selects?

score 1 · Accepted Answer · answered Nov 07 '21 at 02:51

It sounds like you just need a top-1-per-group query.

The standard solution for that is to use ROW_NUMBER

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),

employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'

),

workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1


)
SELECT t.*
FROM (
    SELECT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure
        ,q.quintile
        ,ROW_NUMBER() OVER (PARTITION BY wc.employee, wc.workdate ORDER BY q.quintilevalue) as rn
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue
)t
WHERE rn = 1;

db<>fiddle

Thank you! I like that syntax a lot better. I also looked at solutions in the link you set. I ended up creating a custom function using your syntax and an OUTER APPLY in the queries I'm writing. It seems to be significantly faster, and it makes writing the query much easier. Plus I need to do that lookup in multiple places so I can just reuse the function. — Tristen Hannah, Nov 08 '21 at 20:32

Best way to write TSQL query

1 Answers1