question

jvaudio avatar image
jvaudio asked

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
sumunionweeks
sql query graph.jpg (103.7 KiB)
2 comments
10 |1200

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 ·
please post your table schema, sample data and expected result
0 Likes 0 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered
I think you can extract the data that you need with a union simple query with a column that signifies which dataset the row is from. The interpretation of this into a graph needs to be done by your presentation layer so that 'set1' are used to build the blue line while 'set2' are used to build the red line. SELECT column1 AS [col1] , column2 AS [col2] , 1 AS [SetName] FROM tables WHERE date BETWEEN '8-1-2013' AND '31-1-2013' UNION SELECT column1 , column2 , 2 AS [SetName] FROM tables WHERE date > '31-01-2013' You then need to set SSRS or Excel to give you two series - one based on SetName =1 and the other on SetName =2
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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