aantiix aantiix - 3 months ago 7
SQL Question

Return only unique values

I'm trying to get counts of how many jobs were done, regardless of personnel working on them. What I need to do is filter out any duplicates, the catch is that the entire row isn't duplicate so DISTINCT won't work here. I want to filter out if there is any duplication based on JobCode, JobType TaskTime and day of week. So my table looks like the following:

JobCode JobType TaskTime EmployeeID M Tu W Th F Sa Su
==================================================================
1800 1 06:49 101 1 1 1 1 1 0 0
1800 1 06:49 102 1 0 0 0 0 0 0
1800 1 07:04 101 1 1 1 1 1 0 0
1800 1 07:26 101 1 1 1 1 1 0 0
1800 1 07:49 101 1 1 1 1 1 0 0
1800 2 15:55 101 1 1 1 1 1 0 0
1800 1 16:20 101 1 1 1 1 1 0 0
1800 1 16:50 101 1 1 1 1 1 0 0
1800 2 16:55 101 1 1 1 1 1 0 0


My SQL Query is like this

SELECT t1.JobCode, t1.JobType,
t1.M, t1.Tu, t1.W, t1.Th, t1.F, t1.Sa, t1.Su,
SUM(t1.M + t1.Tu + t1.W + t1.Th + t1.F + t1.Sa + t1.Su) as Totals
FROM Table1 AS t1
JOIN Table1 AS t1_overlap ON
t1_overlap.EmployeeID = t1.EmployeeID AND
t1_overlap.JobType = t1.JobType AND
t1_overlap.TaskTime = t1.TaskTime
AND
(
(t1.M = 1 AND t1_overlap.M = t1.M) OR
(t1.Tu = 1 AND t1_overlap.Tu = t1.Tu) OR
(t1.W = 1 AND t1_overlap.W = t1.W) OR
(t1.Th = 1 AND t1_overlap.Th = t1.Th) OR
(t1.F = 1 AND t1_overlap.F = t1.F) OR
(t1.Sa = 1 AND t1_overlap.Sa = t1.Sa) OR
(t1.Su = 1 AND t1_overlap.Su = t1.Su)
)
GROUP BY t1.JobCode, t1.JobType, t1.M, t1.Tu, t1.W, t1.Th, t1.F, t1.Sa, t1.Su


The data returned is like this

JobCode JobType M Tu W Th F Sa Su Totals
==================================================
1800 1 1 0 0 0 0 0 0 1
1800 1 1 1 1 1 1 0 0 30
1800 2 1 1 1 1 1 1 1 10


What I want to see is only unique values, so I don't want that first line that shows the job was worked on by employee 102 on only M because I'm already seeing that employee 101 worked on that same job on that same day and time. So what I want to see instead is the following:

JobCode JobType M Tu W Th F Sa Su Totals
==================================================
1800 1 1 1 1 1 1 0 0 30
1800 2 1 1 1 1 1 0 0 10


Really I don't need to see the days of the week, I'm just showing them here so I can see whats being returned. All I actually need to see for output is the JobCode, JobType and Totals like the following:

JobCode JobType Totals
======================
1800 1 30
1800 2 10


Help is greatly appreciated.

Answer

I think a quick subquery where you grab the max of each day, grouping by your key, then sum the results, would do the trick:

SELECT
    jobcode,
    jobtype,
    sum(monday+tuesday+wednesday+thursday+friday+saturday+sunday) AS total
FROM
    (
        SELECT
            jobcode,
            jobtype,
            tasktime,
            max(m) as monday,
            max(tu) as tuesday,
            max(w) as wednesday,
            max(th) as thursday,
            max(f) as friday,
            max(sa) as saturday,
            max(su) as sunday
        FROM Table1 T1
        GROUP BY jobcode, jobtype, tasktime
    ) t2
GROUP BY jobcode, jobtype

There may be something more eloquent than that, but this should get the job done.

Comments