How to order the date_received column with out hard coding. As I will not be knowing the future dates to hard code.
TRANSFORM Count(PR_S.Id_NO) AS tot
SELECT PR_S.Final_Result, Count(PR_S.Id_NO) AS Overall_counts
FROM PR_S
GROUP BY PR_S.Final_Result
ORDER BY PR_S.Final_Result, Format([DateReceived],"Short Date")
PIVOT Format([DateReceived],"Short Date") in(10/26/2017,11/7/2017, 12/14/2017, 12/28/2017,1/4/2018, 1/10/2018);
If the dates are not hard code, the order is not sorted- 1/10/2018, 1/4/2018, 10/26/2017,11/7/2017, 12/14/2017, 12/28/2017,1/4/2018, 1/10/2018);
I tried to create a separate table with distinct dates and when I joined with RIGHT join it gave me transform error as I'm not sure what to select in the second table for total(Group by or expression or var or where etc..)
Thank you so much for your help