cancel
Showing results for 
Search instead for 
Did you mean: 

Query advice for comparing data

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member583013
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

Answers (3)

Answers (3)

Former Member
0 Kudos

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

former_member583013
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Great!

Thanks a bunch!

Former Member
0 Kudos

Could not locate info