# question

## Weekly Total Comparison of data from 2 Concurrent Time Periods

Hello, I am trying to get the average allocated quantity per day for 6 items over two separate time periods. I need the average allocated quantities per day from 8/1/2013 to 1/31/2014 and the average allocated quantities per day from 2/1/2014 to current. This information will be turned into a report that will show a graph. The quantity will be the vertical axis and days 1 thru 31 will be the horizontal axis (see attached sketch). The red line represents the average quantity per day for the first query and the blue line represents the average quantity per day for the second query. The shaded green area is what I am trying to determine (the delta between the two). I have worked on this off and on over the last two days and I think that I was closer yesterday. The query that I have now is included below. I would greatly appreciate any help. Thank you select a.Name, pj.ItemNbr, pj.Itemdesc, pj.InvoiceDate, pj.AllocatedQty as AVGPerDay1 --DATEADD( wk, DATEDIFF(wk,6,pj.InvoiceDate),6) as Week from purchasedetailjay pj Join ABCAccount__c abc on pj.accountNbr = abc.AccountNumber__c and abc.Isdeleted = 0 Join Account a on abc.account__c = a.Id and a.Isdeleted = 0 Where pj.ItemNbr in (268425, 413411, 623777, 624643, 625590, 626804) and pj.invoicedate between '8/1/2013' and '1/31/2014' and a.Type = 'Member' UNION select a.Name, pj.ItemNbr, pj.Itemdesc, pj.InvoiceDate, pj.AllocatedQty as AVGPerDay2 --DATEADD( wk, DATEDIFF(wk,6,pj.InvoiceDate),6) as Week from purchasedetailjay pj Join ABCAccount__c abc on pj.accountNbr = abc.AccountNumber__c and abc.Isdeleted = 0 Join Account a on abc.account__c = a.Id and a.Isdeleted = 0 Where pj.ItemNbr in (268425, 413411, 623777, 624643, 625590, 626804) and pj.invoicedate >= '2/1/2014' and a.Type = 'Member' --Group by DATEADD(wk,DATEDIFF(wk,6,pj.InvoiceDate),6), a.Name, pj.ItemNbr, pj.Itemdesc, pj.InvoiceDate, pj.AllocatedQty --Order by DATEADD(wk,DATEDIFF(wk,6,pj.InvoiceDate),6) ![alt text][1] [1]: /storage/temp/1372-sql+query+graph.jpg
sql query graph.jpg (103.7 KiB)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for the nice low-tek graph. But I agree with @Squirrel - it's hard to advice without knowing some more about the schema, the data and the expected output. 'Cause I doubt T-SQL will produce a graph like the above with any query :)
2 Likes 2 ·
0 Likes 0 ·