Search code examples
sqldateview

Create date table with activity in SQL

I currently have a date table linked to my activity table:

Date table example:

Date table

Also I have an table with activities per day, but there are days in which I don't have any activites: Activity Table

I want to create a view where I have all my dates, also including the dates on which I don't have any activities. For those dates I want to calculate the average of the weekday and add it there, once I have the activities from our WMS I want to replace the data. In this way I can create a average on where we will land for different activities in the current month.

Now I have created the view already but I can't get the missing dates inside this view: View Example

How can I ensure that I have the full range of dates for this year and then all the activities for the days. So if I don't have activities from my WMS I want to have a line with: Date - Activity

Currently I have joined my tables using the following codes:

FROM [LOG].[dbo].[DATE_TABLE] b 

JOIN CTE1 c
on b.[date] = c.[date]

JOIN CTE2 D
on c.[Activity]  = d.[Activity]
AND b.[Day_of_Week] = d.[Day_of_Week]

GROUP BY b.[date], b.[Day_Name], c.[Activity],b.[Day_of_Week], d.[Trans], d.[Items], d.[Scans]
Solution

  • Instead of a join on JOIN CTE2 D on c.[Activity] = d.[Activity] And CTE1

    You could do a left join so if you don’t have an activity from CTE2 or a date from CTE1 you still get the activity and date from date table