CODEWITHSUNDEEP
sqlgrouping
Joel
Joel

Reputation: 25

SQL Grouping Issues

I'm attempting to write a query that will return any customer that has multiple work orders with these work orders falling on different days of the week. Every work order for each customer should be falling on the same day of the week so I want to know where this is not the case so I can fix it.

The name of the table is Core.WorkOrder, and it contains a column called CustomerId that specifies which customer each work order belongs to. There is a column called TimeWindowStart that can be used to see which day each work order falls on (I'm using DATENAME(weekday, TimeWindowStart) to do so).

Any ideas how to write this query? I'm stuck here.

Thanks!

Upvotes: 0

Views: 53

Answers (3)

Quassnoi
Quassnoi

Reputation: 425341

SELECT  *
FROM    (
        SELECT  *,
                COUNT(dp) OVER (PARTITION BY CustomerID) AS cnt
        FROM    (
                SELECT  DISTINCT CustomerID, DATEPART(dw, TimeWindowStart) AS dp
                FROM    workOrder
                ) q
        ) q
WHERE   cnt >= 2

Upvotes: 1

rudi-moore
rudi-moore

Reputation: 2710

SELECT CustomerId,
       MIN(DATENAME(weekday, TimeWindowStart)), 
       MAX(DATENAME(weekday, TimeWindowStart))
FROM Core.WorkOrder
GROUP BY CustomerId
HAVING MIN(DATENAME(weekday, TimeWindowStart)) != MAX(DATENAME(weekday, TimeWindowStart))

Upvotes: 0

Thomas
Thomas

Reputation: 64635

Select ...
From WorkOrder As W
Where Exists    (
                Select 1
                From WorkOrder As W1
                    And W1.CustomerId = W.CustomerId    
                    And DatePart( dw, W1.TimeWindowStart ) <> DatePart( dw, W.TimeWindowStart )
                )

Upvotes: 1

Related Questions