Sum orders by day groups 7/30/60/90 (1 Viewer)

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
Hi, I am trying to create a query that will give me the total sales of each item col 1 based on 7 days, col 2 30 days, col 3 60 days etc.
My simple table has fields, "Item" "SaleDate" "Qty".

I did it with the crosstab query worked great using Date: Partition(Now()-[SaleDate],1,90,30) but i am only getting 30/60/90 and cant get the 7 day on the same query.

My other option was to filter them based on form text boxes using start dates, for ex I would have 3 boxes, and the query should return 3 columns each based on the end dates of the boxes, but I cannot figure out how to put in a criteria in the field.

This is my statement
Code:
TRANSFORM Sum(SBSales.UnitsSold) AS SumOfUnitsSold
SELECT SBSales.SKU
FROM SBSales
GROUP BY SBSales.SKU
PIVOT Partition(Now()-[SaleDate],1,90,30);


?Any solutions
 
Last edited by a moderator:

plog

Banishment Pending
Local time
Today, 11:55
Joined
May 11, 2011
Messages
11,611
I'm not sure your query is producing the correct results for the time frames it is returning. Using that method I think a sale can only fall into 1 time frame and you need it in multiple time frames. If an item was sold yesterday it should show in every column correct?

I would do this with a field for each time frame:

Code:
SELECT SKU, SUM(Iif((Date() - SalesDate)<=7, UnitsSold, 0)) AS SoldIn7Days, .....Soldin30Days, ...SoldIn60Days, ...
FROM SBSales
GROUP BY SKU
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:55
Joined
May 7, 2009
Messages
19,169
Code:
SELECT SBSales.SKU, (SELECT SUM(A.UnitsSold)
FROM SBSales AS A WHERE A.SKU = SBSales.SKU AND DateDiff("d", A.[SalesDate], Date()) <= 7) As [7 days],
(SELECT SUM(A.UnitsSold)
FROM SBSales AS A WHERE A.SKU = SBSales.SKU AND (DateDiff("d", A.[SalesDate], Date()) > 7) AND
(DateDiff("d", A.[SalesDate], Date()) < 31)) As [30 days],
(SELECT SUM(A.UnitsSold)
FROM SBSales AS A WHERE A.SKU = SBSales.SKU AND (DateDiff("d", A.[SalesDate], Date()) > 30) AND
(DateDiff("d", A.[SalesDate], Date()) < 61)) As [60 days]
FROM SBSales
GROUP BY SBSales.SKU
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
I'm not sure your query is producing the correct results for the time frames it is returning. Using that method I think a sale can only fall into 1 time frame and you need it in multiple time frames. If an item was sold yesterday it should show in every column correct?

I would do this with a field for each time frame:

Code:
SELECT SKU, SUM(Iif((Date() - SalesDate)<=7, UnitsSold, 0)) AS SoldIn7Days, .....Soldin30Days, ...SoldIn60Days, ...
FROM SBSales
GROUP BY SKU
Hi, thank you so much this worked like a charm. But, I would also like to set it up where an item sold yesterday would only show in the first 2 columns,
Grouped like this; 1-7 1-30 31-60 61-90.
Do I rather need to use the datediff?
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
Code:
SELECT SBSales.SKU, (SELECT SUM(A.UnitsSold)
FROM SBSales AS A WHERE A.SKU = SBSales.SKU AND DateDiff("d", A.[SalesDate], Date()) <= 7) As [7 days],
(SELECT SUM(A.UnitsSold)
FROM SBSales AS A WHERE A.SKU = SBSales.SKU AND (DateDiff("d", A.[SalesDate], Date()) > 7) AND
(DateDiff("d", A.[SalesDate], Date()) < 31)) As [30 days],
(SELECT SUM(A.UnitsSold)
FROM SBSales AS A WHERE A.SKU = SBSales.SKU AND (DateDiff("d", A.[SalesDate], Date()) > 30) AND
(DateDiff("d", A.[SalesDate], Date()) < 61)) As [60 days]
FROM SBSales
GROUP BY SBSales.SKU
Thank you it looks like it should work but its not working for me, it takes forever to run.
 

plog

Banishment Pending
Local time
Today, 11:55
Joined
May 11, 2011
Messages
11,611
First, yes I would use DateDiff--I think its just best practice to use the function designed to calculate differences in dates.

Second, you can make your data in appear in however many coluns you want, just compose each IIF such that it captures the records you want.

..SUM(.IIf(DateDiff, Date1, Date2) >=0 AND DateDiff(Date1, Date2) <=30), SalesDate, 0)) AS Days0To30, ...
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
First, yes I would use DateDiff--I think its just best practice to use the function designed to calculate differences in dates.

Second, you can make your data in appear in however many coluns you want, just compose each IIF such that it captures the records you want.

..SUM(.IIf(DateDiff, Date1, Date2) >=0 AND DateDiff(Date1, Date2) <=30), SalesDate, 0)) AS Days0To30, ...
Hi this is giving me an error.

Table field to sum is UnitsSold
Table field of dates to use SalesDate
Would like to sum UnitsSold for orders that occurred between Date() and SalesDate if its <=30 and so on.
Do I need to use "between"?
 

plog

Banishment Pending
Local time
Today, 11:55
Joined
May 11, 2011
Messages
11,611
What is your actual code?
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
Thanks again. I got it now using 1to7: Sum(IIf(DateDiff("d",[SaleDate],Date())<=7,[UnitsSold],0))

Code is;
Sum(IIf(DateDiff("d",[SaleDate],Date())<=7,[UnitsSold],0)) AS 1to7, Sum(IIf(DateDiff("d",[SaleDate],Date())<=14,[UnitsSold],0)) AS 1to14, Sum(IIf(DateDiff("d",[SaleDate],Date())<=30,[UnitsSold],0)) AS 1to30, Sum(IIf(DateDiff("d",[SaleDate],Date())<=60,[UnitsSold],0)) AS 1to60, Sum(IIf(DateDiff("d",[SaleDate],Date())<=90,[UnitsSold],0)) AS 1to90
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
What is your actual code?
Oh I noticed now that it will only give 1 to 60 and I cannot get 30 to 60.
31to60: Sum(IIf(DateDiff("d",[SaleDate],Date()-30)<=30,[UnitsSold],0)) is resulting in 30 days before and after 30 days ago. hense returning 60 days. DateDiff goes both ways.

Can I use Sum(IIf(DateDiff("d",[SaleDate],Between Date()-31 and Date()-60)<=30,[UnitsSold],0)) ??
 

plog

Banishment Pending
Local time
Today, 11:55
Joined
May 11, 2011
Messages
11,611
Can I use Sum(IIf(DateDiff("d",[SaleDate],Between Date()-31 and Date()-60)<=30,[UnitsSold],0)) ??

If only there was a way to find out
 

plog

Banishment Pending
Local time
Today, 11:55
Joined
May 11, 2011
Messages
11,611
I would not add/subtract from Date(), but do the comparison on what DateDiff returns, as I demonstrated initially:

SUM(.IIf(DateDiff, Date1, Date2) >=0 AND DateDiff(Date1, Date2) <=30), SalesDate, 0)) AS Days0To30, ...
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
LOL
If only there was a way to find out
LOL. it dosnt work.
I ended up adding another column 31to60: [1to60]-[1to30] I'm sure there's a simpler way, but at least it works
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
I would not add/subtract from Date(), but do the comparison on what DateDiff returns, as I demonstrated initially:

SUM(.IIf(DateDiff, Date1, Date2) >=0 AND DateDiff(Date1, Date2) <=30), SalesDate, 0)) AS Days0To30, ...
I tried that, there an issue with the . before the IIF.
Also where do I put in the UnitsSold to sum?
 

plog

Banishment Pending
Local time
Today, 11:55
Joined
May 11, 2011
Messages
11,611
I was just spitballing code to give you an idea of the methodology. You have it working on your data like so:

Sum(IIf(DateDiff("d",[SaleDate],Date())<=60,[UnitsSold],0))

Change it by adding a second condition on the criteria argument:

Sum(IIf(DateDiff("d",[SaleDate],Date())>=30 AND DateDiff("d",[SaleDate],Date())<=60,[UnitsSold],0))
 

Wolf

Registered User.
Local time
Today, 12:55
Joined
Oct 24, 2012
Messages
30
I was just spitballing code to give you an idea of the methodology. You have it working on your data like so:

Sum(IIf(DateDiff("d",[SaleDate],Date())<=60,[UnitsSold],0))

Change it by adding a second condition on the criteria argument:

Sum(IIf(DateDiff("d",[SaleDate],Date())>=30 AND DateDiff("d",[SaleDate],Date())<=60,[UnitsSold],0))
Worked like a charm.
Thanks again
 

Users who are viewing this thread

Top Bottom