SQL Server: I need to manipulate data differently on weekends than weekdays

Question

I have a SQL Server table that has production running times for different pieces of equipment organized by date and shift.

For example:

Date, Shift, Running Hours  
7/11/14, 1, 4.2

My problem is that on weekdays, there are 3 eight hour shifts but on weekends there are 2 twelve hour shifts. All the data is being stored based on 3 shifts regardless of day of the week.

In plain English, on weekends I need to take the 2nd shift running times, divide it by two and add it to the 1st and 3rd shift times. Also, the 1st and 3rd shifts on the weekends need to be relabeled as 4th and 5th shifts because they are not of equal duration as the weekday shifts.

I cannot touch the data in the original table, I essentially need to have a live copy of the data in another table (or view?) and manipulate it there. I started by creating a view off of the mother table and then tried to do an update statement. I started with just updating the shift number but I just can't seem to get it off the ground.

Is using a view even the correct method? Will updating the view change the values in the original table? Or should I have another table that is populated by a stored procedure which has all the manipulations in it?

Thank you for any help you may be able to provide. And I apologize for any formatting problems. This is my first post.

SELECT TOP (100) PERCENT 
    ShiftNumber, TowerString, 
    convert(varchar,a.ShiftDateTime,101) AS Day, Running, 
FROM 
    DBName.dbo.ShiftDetails

UPDATE 
    v_ViewName
SET 
    ShiftNumber = 4
WHERE     
    DATENAME(dw, Day) = 'Saturday' AND ShiftNumber = 1

Will this data be reused? ie. if you change the shifts to 4 and 5 for Saturdays will you need to access this data again outside of this function? — abalos, Aug 08 '14 at 16:17
I don't fully understand your question. The original data is used elsewhere and cannot be touched. This new, manipulated data will be used in a SSRS report by a production engineer for operational efficiency metrics. — d.munky, Aug 08 '14 at 16:21
Will this manipulated data be calculated every time the report is ran? Or will it be stored somewhere for the report to access? — abalos, Aug 08 '14 at 17:02
Either would be acceptable. The manipulation, if possible, could be stored in the Dataset query in SSRS. I was thinking it would be easier to just store the manipulated dataset in a table for faster report response. But really, whatever works will be fine. Thank you for your time. — d.munky, Aug 08 '14 at 17:38
Here's the existing query in SSRS. It works great for the regular 3 shift weekdays. I have nothing in there yet for the manipulation of the weekend shifts — d.munky, Aug 08 '14 at 18:05
SELECT distinct convert(varchar,a.shiftdatetime,101) AS Day, SUM(Running) AS [Run Hours], SUM(Breakdown) AS Breakdown, SUM(Unmanned) AS Unmanned, ShiftNumber AS Shift, DateName(dw,a.shiftdatetime) AS DayOfWeek FROM DBName.dbo.ShiftDetails A WHERE (A.ShiftDateTime BETWEEN (@StartDate) AND DATEADD(day, 1, (@EndDate))) AND A.TowerString = 'TowerName' GROUP BY ShiftNumber, convert(varchar,a.shiftdatetime,101), DateName(dw,a.shiftdatetime) — d.munky, Aug 08 '14 at 18:07

Sean · Accepted Answer · 2014-08-08T17:43:55.553

If I understand correctly, you don't necessarily want to create a copy, but rather use it to perform the manipulation you require. To do the whole thing without performing data manipulation of the source data, you could union sets together. The Unions allow you to split the 2nd shift into 2 halves, whilst assigning them to shift 4 and 5. The group by will aggregate the "2" 4th and 5th records together to give you your total.

   ;WITH ShiftDetail AS (    
    Select ShiftDateTime    
    , Case when DateName(dw,ShiftDateTime) = 'Saturday' and shiftnumber = 1 then 4 when DateName(dw,ShiftDateTime) = 'Saturday' and shiftnumber = 3 then 5 else ShiftNumber end    
    ,Running    
    ,TowerString    
    FROM dbo.ShiftDetails    
    WHERE DateName(dw,ShiftDateTime) <> 'Saturday' OR  ShiftNumber IN (1,3)    
    UNION ALL    
    SELECT ShiftDateTime, 4 as ShiftNumber, Running/2.0 as Running, TowerString    
    FROM dbo.ShiftDetails    
    where DateName(dw,ShiftDateTime) = 'Saturday' and ShiftNumber =2    
    UNION ALL    
    SELECT ShiftDateTime, 5 as ShiftNumber, Running/2.0 as Running, TowerString    
    FROM dbo.ShiftDetails    
    where DateName(dw,ShiftDateTime) = 'Saturday' and ShiftNumber =2
)    
    SELECT ShiftDateTime, ShiftNumber, TowerString, Sum(Running) as Running    
    FROM ShiftDetail    
    GROUP BY ShiftDateTime, ShiftNumber,TowerString

score 0 · Answer 2 · answered Aug 08 '14 at 18:08

I don't know the specifics of the tables well enough to make the query, but the best options for you are to:

Use a Common Table Expression in place of a view to get a dataset and then perform the necessary queries for the report on that data. Please see [Microsoft's Technet](Use a Common Table Expression in place of a view to get a dataset and then perform ) for examples. This would be the preferred method in my eyes because it segregates the business logic from the data (slightly).

OR

Manipulate the information using case statements based on the day of the week of the entry. This is similar to the answer that Sean recommended.

Let me know more specifics and I am willing to help you craft the query.

What specifics would you like to know? I'm out of here soon for the weekend and will be able to supply them on Monday. Thanks for your help. — d.munky, Aug 08 '14 at 19:01
Basically just the table columns and what the data is supposed to look like. From there I could figure out the CTE and select statements that you'd need to use. Just let me know if you need more help. — abalos, Aug 08 '14 at 19:10

SQL Server: I need to manipulate data differently on weekends than weekdays

2 Answers2