Creating a View to SUM YTD a column from a different table and group by an additional column

Transact-SQL
1

Hi again, I'm sorry if this is the wrong forum. I am creating a view that will pull information from dbo.Workforce_Hours.

dbo.Workforce_Hours has columns called:

Hours_Date (ie. 12/01/2020, 11/01/2020)
Hours_Year (ie. 2020, 2021, depending on Hours_Date)
Operating_Area (Group A, Group B)
Hours_Type (Employee, Contractor)

I'm trying to create a view called dbo.Workforce_Hours_YTD that will have the following columns: Hours_Date (one row per month)
Group_A_Employee_Hours_YTD
Group_A_Contractor_Hours_YTD
Group_B_Employee_Hours_YTD
Group_B_Contractor_Hours_YTD

Plainly, I would like to get a rolling year to date total in each of these new columns. Here is the code I've developed so far, but I think it's basically giving me 20 rows of 12/01/2020 and they all have the same number of hours.

CREATE VIEW dbo.Workforce_Hours_YTD
WITH SCHEMABINDING
AS
SELECT Hours_Year,
Hours_Date,
SUM(CASE WHEN Operating_Area = 'Group A' AND Hours_Type = 'Employee' THEN Hours END) OVER (PARTITION BY Hours_Year ORDER BY Hours_Date) AS Group_A_Employee_Total_YTD,
SUM(CASE WHEN Operating_Area = 'Group A' AND Hours_Type = 'Employee' THEN Hours END) OVER (PARTITION BY Hours_Year ORDER BY Hours_Date) AS Group_A_Contractor_Total_YTD,
SUM(CASE WHEN Operating_Area = 'Group B' AND Hours_Type = 'Employee' THEN Hours END) OVER (PARTITION BY Hours_Year ORDER BY Hours_Date) AS Group_B_Employee_Total_YTD,
SUM(CASE WHEN Operating_Area = 'Group B' AND Hours_Type = 'Employee' THEN Hours END) OVER (PARTITION BY Hours_Year ORDER BY Hours_Date) AS Group_B_Contractor_Total_YTD

FROM dbo.Workforce_Hours

GO

CREATE UNIQUE CLUSTERED INDEX idx_vw_YearlyRunningTotals_Year_Date
ON dbo.Workforce_Hours_YTD (Hours_Date, Hours_Year)

Would someone be able to help me correct this code?

2

Add the ROWS clause to every one of the OVERs:


SUM(CASE WHEN Operating_Area = 'Group A' AND Hours_Type = 'Employee' THEN Hours END)
OVER (PARTITION BY Hours_Year ORDER BY Hours_Date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW)  --<<--
AS Group_A_Employee_Total_YTD,
3

Is there a reason why you wouldn't just use a normal GROUP BY with SUM(CASE) without all the OVER stuff?

4

Sorry it's taken this long to get back to you. I'm trying to implement this solution but something strange has happened in my SSMS environment? It's off topic but perhaps you can help me understand..

In my query, the table I'm pointing to in my FROM statement has squiggle underline as if it doesn't exist, but it certainly does. I was working with code like this all week, and now it can't seem to find it.

The table is underlined in green in the left pane.

image
image1684×378 49.9 KB

5

I attempted to do what you proposed anyway, with a new custom view and this is the response:

image
image1420×494 32.8 KB

6

Looks like you're using the gui for constructing queries/views. The gui does not support all SQL constructs. Try running the query natively in SSMS and see if you still get the same error.

7

I hope you'll forgive my ignorance, this is all new to me. I'm not quite sure what you mean. I went to the views section in the left pane and chose Create New, and now I'm manually entering the SQL code provided. I'm not sure how else I can natively enter the code, as you say?

image
image1893×936 66.6 KB