SQL Advanced Query Outputting to Excel

[AlexMwells]

Hi,

I'm using the following code to find the total sales of each product to each customer, for a user choosen month.

SELECT HistoryHeader.DelAddress01 as Shop, HistoryLines.Description, Sum(HistoryLines.DiscountAmount) AS TotalQty
FROM CustomerMaster INNER JOIN (HistoryHeader INNER JOIN HistoryLines ON HistoryHeader.DocumentNumber=HistoryLines.DocumentNumber) ON CustomerMaster.CustomerCode=HistoryHeader.CustomerCode
WHERE datepart("m",[HistoryLines.DDate])=[Enter month number Jan=1,feb=2,etc] And Year([HistoryLines.DDate])=Year(Date())
GROUP BY HistoryHeader.DelAddress01,HistoryLines.Description;

Now this works fine, however, what i really need is to be able to run this query 12 times each time changing the month so i can look at the past 12 months. And feed the results into excel and into a standard report. So i can end up with a table that will enable me to compare one month with another.


ie like the following table

Customer, product, JanTotals, FebTotals, .....DecTotals

I have no idea where to start this, or even it can be automated. Any help would be greatly appreciated.

Alex

[Rabbit]

Rather than filtering by month, why not group by it instead? Then you just need to export one query and it will be split up by month.

[AlexMwells]

Hi Rabbit,

Thank you very much for your response. Grouping by month was a very good idea. For anyone who stores their accounting data in Pastel Partner 2009 the following code should work.

SELECT datepart("m",[HistoryLines.DDate]) as Month,CustomerMaster.CustomerCode, HistoryHeader.DelAddress01, HistoryHeader.DelAddress02, HistoryLines.Description, Sum(HistoryLines.Qty) as TotalQty, Sum(HistoryLines.DiscountAmount) AS TotalSale
FROM CustomerMaster INNER JOIN (HistoryHeader INNER JOIN HistoryLines ON HistoryHeader.DocumentNumber=HistoryLines.DocumentNumber) ON CustomerMaster.CustomerCode=HistoryHeader.CustomerCode
WHERE Year([HistoryLines.DDate])=Year(Date())
GROUP BY datepart("m",[HistoryLines.DDate]), CustomerMaster.CustomerCode, HistoryHeader.DelAddress01,HistoryHeader.DelAddress02, HistoryLines.Description;

Thanks Again

Alex

[AlexMwells]

Thanks Daniel,

I am in the process of downloading it. It will take 1hr 8min. Thats living in Africa for you.

Alex