Nested SQL for Criteria

[MiamiDave]

I have a large number of records that detail selling by item and location and week.

How can I query the table to get the total sales over the last 3mos, 6mos, and 12mos?

 

[AlPhilps]

Depends how your dates are held:-

SELECT Item, Location, Week, Sum(TotalWeek)
FROM TableName
WHERE DayOfSale >=(Now()-90)
GROUP BY Item, Location, Week;

.. where *90* equates to 3 months (roughly), in days.
Any help?

 

[cmmrfrds]

If you can live with standard date parts then the partition function along with the datepart function may work for you.

Example using quarter 'q' from Northwind database.

SELECT DISTINCTROW Partition(datepart('q',[dbo_Invoices].[orderdate]),0,52,1) AS Range, Count([dbo_Invoices].[orderdate]) AS [Count]
FROM dbo_Invoices
GROUP BY Partition(datepart('q',[dbo_Invoices].[orderdate]),0,52,1);

Same query could be run for 'w' 'ww' 'm' etc...

 

[MiamiDave]

Thanks.

That's part of the solution I've adopted. The issue is I want the answers for 3mos, 6mos, and 12mos on the same query from the same source table.

I used a solution similar to that posted three times, one for each total, by adding the source table 3 times (source_1, source_2, source_3) joining item and loc but not time. It works but it's not very fast. Any ideas for improving it?

Dave

 

[cmmrfrds]

If I understand what you are looking for the iif function may do it. Here is the basic idea - you will need to fill in the correct names and check syntax.

Select sum(iif(yourdate>Now()-90,amount,0)) as month3,
sum(iif(yourdate>Now()-180,amount,0)) as month6,
sum(iif(yourdate>Now()-360,amount,0)) as month12,
Item,
Location
From yourtable
Group by Item, Location
Where yourdate>Now()-160

 

[MichaelRed]

Hmmmmmmmmmmm,

Each "interval" will include the preceeding intervals' data as well as the "intended". Thus the 90 day thing is o.k., but the 180 days also includes the 90 days, and the 360 days includes all of the above. And the where clause includes the typo (160 days).

The 'calculated field' approach is probably the way to go, but I'm not sure the intent is (or should be) to accumulate the totals in the different intervals. Could be. I'm not sure.

I would at least change the date interval calc to include the requested date intervals (months) as opposed to the approximation of N Days, as in ~~~~:

Between Date and DateAdd("m", -3, Date)

(change 3 to 6 or 12 for the other intervals) MichaelRed
m.red@att.net

Searching for employment in all the wrong places