Trying to get MAX Values - SQL Server Forums

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?

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.

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)?

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

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)

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

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources