Cross tab- How to order the pivot colum with out hard coding ( its date field)

[stalk]

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

[ranman256]

do not use code,
instead, make a 'picked' table. Place the dates desired to pull in the table.
For Q1, Join this tPicked table to the data table to get the data
Q2 is the Crosstab query of Q1.

[CJ_London]

you are ordering a short date which is text

1/10/2018
1/4/2018
10/26/2017
etc

in text 1/1 comes before 1/4 and 1/4 comes before 10/, the rest of the characters are immaterial

you need to use a format which is correct for text e.g.

20171026
20180104
20180110

or not tested but try just ordering by the date - as a separate column in your query

Dates are stored as numbers, what you see as a date is just a format. Today is 43123 which is formatted as 23/01/2018 for the UK and 01/23/2018 in the US. When communicating date values as text to SQL you have to use the US format of mm/dd/yyyy otherwise 1/4/2018 (UK 1st April) will be interpreted US style as 4th Jan.