I am a little stuck with a SQL Query of which I use to populate a pivot grid control in my application. I have attached an excel spreadsheet to illustrate what the source data looks like and what my desired query result should look like.
The issue I am facing is that I cannot use the group by statement on User, Date and Stage without only showing values where there was actually records within that specific week.
SELECT dbo.MyTable.User, dbo.MyTable.Stage, dbo.MyTable.Date, SUM(dbo.MyTable.Amount) AS Amount, COUNT(DISTINCT dbo.MyTable.Id) AS Total
GROUP BY dbo.MyTable.User, dbo.MyTable.Stage, dbo.MyTable.Date
Could someone please kindly assist me with this query of which your help is most appreciated?