on 03-04-2009 2:20 PM
Looking for advice on how to create query that compares two period (Date Range of sale totals)
Total sales for year 2007 and 2008 in two separate columns.
Depending on how you want to view your report there are a few ways to achieve it.
1. Using a main query and sub-select queries within it.
2. Stored procedures
There are quite a few examples on the forum as this has been answered in the past
Suda
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Suda,
I looked for some links for the message below and was unable to locate, please show me a link.
Regards,
M. Jenkins
Depending on how you want to view your report there are a few ways to achieve it.
1. Using a main query and sub-select queries within it.
2. Stored procedures
There are quite a few examples on the forum as this has been answered in the past
Suda
You may try this one:
SELECT T0.CardCode, T0.CardName,
(SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode
AND YEAR(DocDate) = '2007') - (SELECT SUM(DocTotal) FROM ORIN
WHERE CardCode = T0.CardCode
AND YEAR(DocDate) = '2007') AS [Sales 2007],
(SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode
AND YEAR(DocDate) = '2008') - (SELECT SUM(DocTotal) FROM ORIN
WHERE CardCode = T0.CardCode
AND YEAR(DocDate) = '2008') AS [Sales 2008]
FROM [dbo].[OCRD] T0 LEFT JOIN [dbo].[OINV] T1 ON T1.CardCode = T0.CardCode
WHERE T0.CardType = 'C'
GROUP BY T0.CardCode, T0.CardName
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is something you can get started with
SELECT T0.CardCode, T0.CardName,
(SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode
AND YEAR(DocDate) = '2008') AS [Sales 2008],
(SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode
AND YEAR(DocDate) = '2009') AS [Sales 2009]
FROM [dbo].[OCRD] T0 LEFT JOIN [dbo].[OINV] T1 ON T1.CardCode = T0.CardCode
WHERE T1.DocDate BETWEEN '[%0]' AND '[%1]'
GROUP BY T0.CardCode, T0.CardName
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Could not locate info
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.