day of week question

[LloydDobler]

Is there any way I can turn the following code into the desired results? I currently only get 'A shift' showing up

DESIRED RESULTS:
shift MON TUES WED THU FRI
A 1.2 3.2 0 0 0
B 0 0 0 0 0
C 0 0 0 0 0

SELECT     shift, 
SUM(CASE DATENAME(dw, dbo.tbl_production.PRDdate) WHEN 'MONDAY' THEN dbo.tbl_production.TotalTime ELSE 0 END) AS MON, etc through all days of the week

FROM   dbo.tbl_production LEFT OUTER JOIN
       dbo.tbl_machinenumbers 
ON      dbo.tbl_machinenumbers.machinenumber =           dbo.tbl_production.MachineNumber

WHERE     (DATEPART(yy, dbo.tbl_production.PRDdate) = DATEPART(yy, GETDATE())) AND (dbo.tbl_production.Division = 1) AND (dbo.tbl_production.workcentre LIKE 'C%') AND (DATEPART(ww, dbo.tbl_production.PRDdate) = DATEPART(ww, GETDATE())) AND  dbo.tbl_machinenumbers.machgroup = 'ZONE 1'

GROUP BY shift

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[gmmastros]

Can you show some sample data?

On a side note, it appears as though you want to select data for the current week only. Since you are using functions on the left side of your where clause, indexes on the PRDate column are not being used. By rewriting the query, you should be able to get better performance.

For example...

SET NOCOUNT ON
Declare @Start DateTime
Declare @End DateTime

Set @Start = DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)
Set @End = @Start + 7

SELECT shift, 
       SUM(CASE DATENAME(dw, dbo.tbl_production.PRDdate)
           WHEN 'MONDAY' 
           THEN dbo.tbl_production.TotalTime 
           ELSE 0 END) AS MON, 
       etc through all days of the week

FROM   dbo.tbl_production 
       LEFT OUTER JOIN dbo.tbl_machinenumbers 
         ON dbo.tbl_machinenumbers.machinenumber = dbo.tbl_production.MachineNumber
         AND dbo.tbl_machinenumbers.machgroup = 'ZONE 1'
WHERE  dbo.tbl_production.PRDate >= @Start
       And dbo.tbl_production.PRDdate < @End
       And dbo.tbl_production.Division = 1
       AND dbo.tbl_production.workcentre LIKE 'C%'
Group By Shift

This will improve performance (assuming you have an index on PRDate). I'm hoping that the sample data you provide will help determine why the other shifts are not showing up in the output. Is it possible that there simply isn't any data for the other shifts?

Also notice that I moved the Where condition for the machine numbers table to the On clause. This may or may not help (depending on your data). Try it both ways to see if it makes a difference.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LloydDobler]

You're right in that there isn't any data to show up, but I would like to display a 0 when that occurs. Is that possible?

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[gmmastros]

I'm sure it's possible, but I'm also not sure if this query will work for you. Since there is a cross join involved, you would be better off running this against a test database on another server, or possibly when the usage for the database is very low. At a minimum, if this query take longer than your original, you should stop it from executing. I hope this helps.

SET NOCOUNT ON
Declare @Start DateTime
Declare @End DateTime

Set @Start = DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)
Set @End = @Start + 7

SELECT AllShifts.shift, 
       SUM(CASE DATENAME(dw, dbo.tbl_production.PRDdate)
           WHEN 'MONDAY' 
           THEN dbo.tbl_production.TotalTime 
           ELSE 0 END) AS MON, 
       etc through all days of the week

FROM   dbo.tbl_production 
       Cross Join (Select Distinct Shift From dbo.tbl_production) As AllShifts
       LEFT OUTER JOIN dbo.tbl_machinenumbers 
         ON dbo.tbl_machinenumbers.machinenumber = dbo.tbl_production.MachineNumber
         AND dbo.tbl_machinenumbers.machgroup = 'ZONE 1'
WHERE  dbo.tbl_production.PRDate >= @Start
       And dbo.tbl_production.PRDdate < @End
       And dbo.tbl_production.Division = 1
       AND dbo.tbl_production.workcentre LIKE 'C%'
Group By Shift

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LloydDobler]

that doesn't help with my original question at all! My query works if there's data for the shift/zone, so I don't know why you would say "I'm also not sure if this query will work for you"

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[gmmastros]

I think the query will work, but I didn't want to get your hopes up. Without having the tables to play with, it is sometimes difficult to give advice to people. Obviously, I don't want to mislead you. If it's not too much bother, perhaps you could try it and see if you get the correct results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LloydDobler]

yours didn't work. It just returned the same value for all all 3 shifts.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."