Skip to Content
0
Former Member
Jan 10, 2012 at 03:54 PM

Wish to include Posting Periods rather than hard coded Dates

32 Views

Hi Guys,

I have this query that I would like to have it look for the financial year based on the posting periods rather than hard code in the year - i.e 2011. The reason being that the financial year is from 1st October to 30th September, so my query below will not give me accurate information when going from December to January as the Year is Hard Coded in currently.

Does anyone have any ideas how I can achieve this, I must admit to be totaly stuck.

Kind regards

Sean

SELECT P.CardCode,P.CardName,

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

[1] as 'January',

[2] as 'February',

[3] as 'March',

[4] as 'April',

[5] as 'May',

[6] as 'June',

[7] as 'July',

[8] as 'August',

[9] as 'September',

[10] as 'October',

[11] as 'November',

[12] as 'December'

FROM (SELECT T0.CARDCODE, T0.CARDNAME, (T1.Debit - T1.Credit) AS BAL,

MONTH(T1.Duedate) as month

FROM dbo.OCRD T0

LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.Duedate) = 2011 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