Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trying to get MAX Values

Author  Topic 

aantiix
Starting Member

6 Posts

Posted - 2014-10-13 : 14:22:18
In my database I am adding up the occurrences of reports on days of the week. If I am not using the MAX value then I get something returned such as



EmployeeID DaysOfWeek ReportID
1001 1 201
1001 2 201


As I just need the highest value returned I'm attempting to use MAX. However, the problem is that all values are being returned in the DaysOfWeek column as 5 even though they may range anywhere from 1-7. The DaysOfWeek should be for a unique EmployeeID/ReportID combination. What am I doing wrong?


WITH sub AS(
SELECT Shifts1.EmployeeID, X.*, Schedule.ReportID
FROM
(
SELECT
CASE WHEN [M] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Tu] = '1' THEN 1 ELSE 0 END +
CASE WHEN [W] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Th] = '1' THEN 1 ELSE 0 END +
CASE WHEN [F] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Sa] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Su] = '1' THEN 1 ELSE 0 END AS DaysofWeek
FROM
Schedule
) X, Shifts1 INNER JOIN (Shifts2 INNER JOIN Schedule ON Shifts2.Shifts2ID = Schedule.Shifts2ID) ON Shifts1.Shifts1ID = Shifts2.Shifts1ID
GROUP BY Shifts1.EmployeeID, Schedule.ReportID, X.DaysofWeek
)
SELECT X.*
FROM sub X
JOIN (SELECT EmployeeID, ReportID, MAX(DaysofWeek) AS DaysofWeek
FROM sub
GROUP BY EmployeeID, ReportID) Y
ON X.DaysofWeek = Y.daysofweek
and X.EmployeeID = Y.EmployeeID
and X.ReportID = Y.ReportID
ORDER BY DaysofWeek ASC

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 14:53:07
In your CTE you are setting DaysOfWeek to 1 for every day of the week. Is that what you want?
Go to Top of Page

aantiix
Starting Member

6 Posts

Posted - 2014-10-13 : 15:00:07
Thats correct. I have a separate column for each day of the week (type bit). So what I'm doing in my case statement is assigning if the value of that column is 1 then add 1. So basically an employee may need to do a report 3 days a week (M, W, F) so in the database those three days will have values of 1 where as the other days will have values of 0. So I need to see that employee needs to do a total of 3 of that specific report per week.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 15:17:55
OK but as it stands your CTE will only set DaysofWeek to 1 or 0. Max(1 or 0) = 1 or 0. MAybe you want to SUM(DaysOfWeek)?
Go to Top of Page

aantiix
Starting Member

6 Posts

Posted - 2014-10-13 : 15:38:13
SUM(DaysofWeek) returns 0 records. I think maybe the issue is with my CASE statements after all though I'm not sure how to fix. So the way I was trying to do it is if EmployeeID = x and ReportID = x then do each of the case statements which would in turn assign 1 or 0 and add to existing value. So if all 7 days were true then it would assign 1 to each of the days and add as it goes though for a total value of 7 for DaysofWeek. If only three days were true it would assign only 1 to each of those and 0 to the days that weren't true for a total of 3 DaysofWeek.

However, I removed the MAX statement and looked at it more closely and it looks as though all cases are being assigned 5 values regardless of which values are true/false such as:


EmployeeID DaysOfWeek ReportID
1001 1 201
1001 2 201
1001 3 201
1001 4 201
1001 5 201


I tried to manually change some case statements such as the following but seems no matter what I do the values are always returned as 5 so something is not being validated somehow as I'm expecting.


FROM
(
SELECT
CASE WHEN [M] = '9' THEN 1 ELSE 0 END +
CASE WHEN [Tu] = '9' THEN 1 ELSE 0 END +
CASE WHEN [W] = '9' THEN 1 ELSE 0 END +
CASE WHEN [Th] = '9' THEN 1 ELSE 0 END +
CASE WHEN [F] = '9' THEN 1 ELSE 0 END +
CASE WHEN [Sa] = '9' THEN 1 ELSE 0 END +
CASE WHEN [Su] = '9' THEN 1 ELSE 0 END AS DaysofWeek
FROM


The above returns the same results as the first, which looks to be the problem since none of those statements would be true since its a bit type only 1 or 0 are possible values in those day columns.


If I try to SUM in the FROM statement it doesn't add up the values where EmployeeID/ReportID are the same but looks to add up all day values in the entire table. So the following returns a value of over 100,000 for all rows returned


FROM
(
SELECT
SUM((CONVERT(int,M))+(CONVERT(int,Tu))+(CONVERT(int,W))) AS DaysofWeek
FROM
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 16:00:52
Can post some of the underlying data (just a few rows)
Go to Top of Page

aantiix
Starting Member

6 Posts

Posted - 2014-10-13 : 16:19:02
If I simplify this by just working with the CASE statements you can see that the values are not as expected here. In this case I would expect the following results:


EmployeeID DaysofWeek ReportID
1009773 1 100303


However, instead I'm receiving the following:


EmployeeID DaysofWeek ReportID
1009773 3 100303
1009773 1 100303
1009773 4 100303
1009773 5 100303
1009773 2 100303


The query that I did to get these results is:


SELECT Shifts1.EmployeeID, X.*, Schedule.ReportID
FROM
(
SELECT
CASE WHEN [M] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Tu] = '1' THEN 1 ELSE 0 END +
CASE WHEN [W] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Th] = '1' THEN 1 ELSE 0 END +
CASE WHEN [F] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Sa] = '1' THEN 1 ELSE 0 END +
CASE WHEN [Su] = '1' THEN 1 ELSE 0 END AS DaysofWeek
FROM
Schedule
) X, Shifts1 INNER JOIN (Shift2 INNER JOIN Schedule ON Shift2.Shift2ID = Schedule.Shift2ID) ON Shifts1.Shifts1ID = Shift2.Shifts1ID
GROUP BY Shifts1.EmployeeID, Schedule.ReportID, X.DaysofWeek
HAVING ReportID = '100303'


The relevant data from the three tables used in the query is as follows:


dbo.Schedule
ReportID ShiftsID2 M Tu W Th F Sa Su
100303 10018 0 0 0 0 0 1 0

dbo.Shifts2
ShiftsID2 ShiftsID1
10018 1002

dbo.Shifts1
ShiftsID1 EmployeeID
1002 1009773
Go to Top of Page

aantiix
Starting Member

6 Posts

Posted - 2014-10-13 : 16:56:58
A little more on this that I think may be the issue...

The first EmployeeID/ReportID in the database has a value of 5 (M=1, Tu=1, W=1, Th=1, F=1, Sa=0, Su=0). I'm wondering if the CASE statements are being done only on that for all rows instead of being run each time for each row. Just a thought. If that is happening, I'm not sure how to resolve this.
Go to Top of Page

aantiix
Starting Member

6 Posts

Posted - 2014-10-13 : 17:16:52
Apparently I was seriously over complicating this. Go this working as needed. Below query for reference:


SELECT Shifts1.EmployeeID, SUM((CONVERT(int,Schedule.M))+(CONVERT(int,Schedule.Tu))+(CONVERT(int,Schedule.W))+(CONVERT(int,Schedule.Th))+(CONVERT(int,Schedule.F))+(CONVERT(int,Schedule.Sa))+(CONVERT(int,Schedule.Su))) AS DaysofWeek, Schedule.ReportID
FROM Schedule
JOIN Shift2
ON Schedule.Shift2ID = Shift2.Shift2ID
JOIN Shifts1
ON Shift2.Shifts1ID = Shifts1.Shifts1ID
GROUP BY Shifts1.EmployeeID, Schedule.ReportID
Go to Top of Page
   

- Advertisement -