Former Member

# Sales query using multiple variables.

Hi,

I have a problem I'm trying to solve with a query to report sales in the last 7 days.

The query itself has to sales by day, by product and also by country.

This means that I require a separate row for each day, each product sold on each day, and each country every product was sold in on each day.

For example:

2/2/2012 - Product1 - Australia - Sales

2/2/2012 - Product1 - United States - Sales

2/2/2012 - Product 2 - Australia - Sales

3/2/2012 - Product 1 - Australia - Sales

3/2/2012 - Product 2 - Australia - Sales

3/2/2012 - Product 2 - United States - Sales

Hopefully this makes sense!

I've got to the point where I can see all sales for the week in sum but I need to be able to break it down to a more detailed level as described above.

The question I would really love answering is how I would achieve this query. Do I need many different queries and then use UNION to combine? Is there a simple solution?

Hopefully you can help me out! Please let me know if you need me to explain in any more detail.

Thanks.

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Feb 04, 2012 at 11:19 PM

Hi,

Welcome you post on the forum.

Try:

SELECT T1.DocDate, T1.Itemcode, T2.Country, Sum(T1.LineTotal) 'Sales'

FROM OINV T0

INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry

LEFT JOIN OCRD T2 ON T2.CardCode = T0.CardCode

WHERE DateDiff(DD,T1.Docdate,Getdate())<=7

GROUP BY T1.DocDate, T1.Itemcode,T2.Country

Thanks,

Gordon

10|10000 characters needed characters exceeded
• Former Member Former Member

To add formula fields to the group by query is a challenging task. Sometimes is not possible. Your question has been answered in this thread. Please post a new one and mark this one as Solved Problem.

• Former Member
Posted on Feb 04, 2012 at 03:26 PM

Does anyone have any suggestions? It would be greatly appreciated.

Please let me know if you need me to explain the problem in any more detail.

Thanks.