cancel
Showing results for 
Search instead for 
Did you mean: 

Problem With Query

Former Member
0 Kudos

Hi Guys,

I am trying to write a query so that I can show the Customer Sales only on a month by month basis for the financial year against the SlpCode/SlpName associated with the account held in Item Master Data, however I seemed to have made an error or two and cannot get it to work, it is also pulling in supplier accounts which I do not want !

Could someone please give me a pointer or two as to where I have gone wrong :

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

(SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2012 AND TransType IN ('13','14')) '2012 Sales',
[1] as [Jan],
[2] as [Feb],
[3] as [Mar],
[4] as [Apr],
[5] as [May],
[6] as [Jun],
[7] as [Jul],
[8] as [Aug],
[9] as [Sep],
[10] as [Oct],
[11] as [Nov],
[12] as [Dec]

FROM (SELECT T0.CARDCODE, T0.SlpCode, T0.CARDNAME,  (T1.Debit - T1.Credit) AS [BAL],
MONTH(T1.RefDate) as [month]
FROM dbo.OCRD T0
Left join dbo.oslp T2 On T0.SlpCode = T2.SlpCode
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode  AND T0.CardType = 'C'  AND Year(T1.RefDate) = 2012 AND T1.TransType in ('13','14')) S
PIVOT  (SUM(S.[BAL]) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Many thanks in Advance

Sean

Accepted Solutions (0)

Answers (4)

Answers (4)

KennedyT21
Active Contributor
0 Kudos

Hi Sean Martin...

Try This

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

(SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2012 AND TransType IN ('13','14')) '2012 Sales',

[1] as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

FROM (SELECT T0.CARDCODE,T0.CARDNAME, T0.SlpCode, T2.SlpName ,  (T1.Debit - T1.Credit) AS [BAL],

MONTH(T1.RefDate) as [month]

FROM dbo.OCRD T0

Left join dbo.oslp T2 On T0.SlpCode = T2.SlpCode

LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode  AND T0.CardType = 'C'  AND Year(T1.RefDate) = 2012 AND T1.TransType in ('13','14')) S

PIVOT  (SUM(S.[BAL]) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Regards

Kennedy

Former Member
0 Kudos

Hi Guys,

Thanks for all your help, I resolved the issue with the below query, however, I just realised that these figures are inclusive of tax, is there any way I can get the figures without tax/vat ? Also is it possible to change the date selection to a parameter so that I can select the dates required so that I do not have to hard code them in as the financial year is from 1st September to 31st August ?

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

(SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2012 AND TransType IN ('13','14')) '2012 Sales',

[1] as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

FROM (SELECT T0.CARDCODE, T0.SlpCode, T0.CARDNAME, T2.SlpName, (T1.Debit - T1.Credit) AS [BAL],

MONTH(T1.RefDate) as [month]

FROM dbo.OCRD T0

Left join dbo.oslp T2 On T0.SlpCode = T2.SlpCode

LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode  Where T0.CardType = 'C'  AND Year(T1.RefDate) = 2012 AND T1.TransType in ('13','14')) S

PIVOT  (SUM(S.[BAL]) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Kind regards

Sean

Former Member
0 Kudos

Hi,

To get the figures without tax/vat, you need to use OINV ans ORIN table directly and UNION clause. You can select the dates as predefined parameter. However, this report heading may not be as dynamic.

If only consider OINV, you can get:

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

(SELECT SUM(DocTotal) - sum(VatSum) FROM OINV WHERE CardCode= P.CardCode AND Year(DocDate) = 2012) '2012 Sales',

[1] as [Jan],

[2] as [Feb],

[3] as [Mar],

[4] as [Apr],

[5] as [May],

[6] as [Jun],

[7] as [Jul],

[8] as [Aug],

[9] as [Sep],

[10] as [Oct],

[11] as [Nov],

[12] as [Dec]

FROM (SELECT T0.CARDCODE, T0.SlpCode, T0.CARDNAME, T2.SlpName, (T1.DocTotal - T1.VatSum) AS [BAL],

MONTH(T1.DocDate) as [month]

FROM dbo.OCRD T0

Left join dbo.oslp T2 On T0.SlpCode = T2.SlpCode

LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode  Where T0.CardType = 'C'  AND Year(T1.DocDate) = 2012) S

PIVOT  (SUM(S.[BAL]) FOR [month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Thanks,

Gordon


former_member186712
Active Contributor
0 Kudos

Hi Sean,

Your problem is in :

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

FROM (SELECT T0.CARDCODE, T0.SlpCode, T0.CARDNAME,

These columns needs to be the same so change the last line to :

FROM (SELECT T0.CARDCODE, T0.CARDNAME, T0.SlpCode, T2.SlpName

And it works.

Regards,

Augusto

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please try this:

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

(SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2012 AND TransType IN ('13','14')) '2012 Sales',
[1] as [Jan],
[2] as [Feb],
[3] as [Mar],
[4] as [Apr],
[5] as [May],
[6] as [Jun],
[7] as [Jul],
[8] as [Aug],
[9] as [Sep],
[10] as [Oct],
[11] as [Nov],
[12] as [Dec]

FROM (SELECT T0.CARDCODE, T0.SlpCode, T0.CARDNAME,  (T1.Debit - T1.Credit) AS [BAL],
MONTH(T1.RefDate) as [month]
FROM dbo.OCRD T0
Left join dbo.oslp T2 On T0.SlpCode = T2.SlpCode
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode  Where T0.CardType = 'C'  AND Year(T1.RefDate) = 2012 AND T1.TransType in ('13','14')) S
PIVOT  (SUM(S.[BAL]) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Thanks & Regards,

Nagarajan

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

The above qurery is working?

Regards,

Nagarajan