Crosstabe-Date Sorting

[Pearl1]

Hi All,

I've created a crosstab query using ODBC. What I've notice is the dates on the Column is not in seqeuntial order. Is there anything I can do to dispay the dates in order.

Please see the SQL data I copied below. Appreciate anyones assistance.


TRANSFORM Sum([COIL QUERY].QTY) AS SumOfQTY
SELECT PKV.[Item number], [ITEM MASTER STD COST QUERY].ITEMGROUPID, [ITEM MASTER STD COST QUERY].ITEMNAME, [COIL ACTUAL DEMAND QUERY].[ACTUAL DEMAND], Format(Date(),"ww\,mmm\,yy") AS [Current Wk], Format(#6/30/2014#,"ww\,mmm\,yy") AS [Future Date], [ITEM MASTER TEST (ITEM COVERAGE) ex FA].MININVENTONHAND, [ITEM MASTER TEST (ITEM COVERAGE) ex FA].REQGROUPID AS [RA Coverage], [ITEM MASTER TEST (ITEM COVERAGE) ZR].LEADTIMEPURCHASE, [ITEM MASTER TEST (ITEM COVERAGE) ZR].REQGROUPID AS [ZR Coverage], [ITEM MASTER STD COST QUERY].PRICE, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].MW, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].RA, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ZR, Sum([COIL QUERY].QTY) AS [Total Demand], Avg([COIL QUERY].QTY) AS AvgOfQTY, DateDiff("ww",[Current Wk],[Future Date]) AS [WK END], ([Total Demand]/[WK END])*-1 AS [Avg WK], Sum(Year([COIL QUERY].[REQDATE])*12+DatePart("m",[COIL QUERY].[REQDATE])-1) AS Expr1
FROM (((PKV LEFT JOIN (([COIL QUERY] LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE) ex FA] ON [COIL QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE) ex FA].ITEMID) LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE) ZR] ON [COIL QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE) ZR].ITEMID) ON PKV.[Item number] = [COIL QUERY].ITEMID) LEFT JOIN [ITEM MASTER STD COST QUERY] ON PKV.[Item number] = [ITEM MASTER STD COST QUERY].ITEMID) LEFT JOIN [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab] ON PKV.[Item number] = [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ITEMID) LEFT JOIN [COIL ACTUAL DEMAND QUERY] ON PKV.[Item number] = [COIL ACTUAL DEMAND QUERY].ITEMID
GROUP BY PKV.[Item number], [ITEM MASTER STD COST QUERY].ITEMGROUPID, [ITEM MASTER STD COST QUERY].ITEMNAME, [COIL ACTUAL DEMAND QUERY].[ACTUAL DEMAND], Format(Date(),"ww\,mmm\,yy"), Format(#6/30/2014#,"ww\,mmm\,yy"), [ITEM MASTER TEST (ITEM COVERAGE) ex FA].MININVENTONHAND, [ITEM MASTER TEST (ITEM COVERAGE) ex FA].REQGROUPID, [ITEM MASTER TEST (ITEM COVERAGE) ZR].LEADTIMEPURCHASE, [ITEM MASTER TEST (ITEM COVERAGE) ZR].REQGROUPID, [ITEM MASTER STD COST QUERY].PRICE, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].MW, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].RA, [Inventory all Warehouse- fa,fn,fq,fs,fw_Crosstab].ZR
PIVOT Format([REQDATE]," ww"", ""yyyy");

 

[CJ_London]

A couple of thoughts - doesn't look like you are sorting the data but more importantly format returns a text value so even if sorted it will be sorted based on text

So week 1 is " 1, 2014"
week 2 is " 2, 2014" (so far so good!)
but week 10 is " 10, 2014" - which in text sort comes before " 2, 2014"

To get the correct order you need to set your format to return a text value which will sort correctly and put in a preceding zero for weeks 1-9.

so you need a more complex format requirement

format(format(reqDate,"ww"),"00") & ", " & format(reqDate,"yyyy")

Alternatively - not tried, but you could just try sorting on ReqDate

 

[Pearl1]

Thank you CJ...

It worked to a certain degree. It didn't sort by week. From Week 10 to Week 52 is was grouped together followed by (new column created with blank information-don't where this came from) than all weeks 01 to 09.

I was able to move the columns to give me the right order.

Definately better than what I had. Thank you so much.

 

[CJ_London]

new column created with blank information-don't where this came from

Implies you have some incomplete data. Try filtering your source data based on the column heading and check the records

followed by ... all weeks 01 to 09.

Not sure why this should be - 01 should come first. If you want to post your revised sql I can take a look

 

[Pearl1]

Hi CJ,

I've attached an excel file...data dumped from access as to what results I'm getting.

Also below is the SQL data

TRANSFORM Sum([COIL QUERY].QTY) AS SumOfQTY
SELECT [COIL QUERY].ITEMID, Sum([COIL QUERY].QTY) AS [Total Demand], Avg([COIL QUERY].QTY) AS AvgOfQTY
FROM [COIL QUERY]
GROUP BY [COIL QUERY].ITEMID
ORDER BY Format(Format([REQDATE],"ww"),"00") & ", " & Format(Format([REQDATE],"yyyy"))
PIVOT Format(Format([REQDATE],"ww"),"00") & ", " & Format(Format([REQDATE],"yyyy"));

Regards

 

[CJ_London]

Are you asking a question? I'm not sure of the point you are making...

It looks like it is sorting in the right order. To paraphrase my point in my first post

more importantly format returns a text value so even if sorted it will be sorted based on text

so 10, 2015 comes before 29, 2014

if you want it so 2014 comes before 2015, then you need to have

2014, 29
2015, 10