April 11, 2006 at 11:57 pm
I have the following fields in table A:
GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
GL_ID | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
======================================================================
1000 | Inventory |8,000 | 5,000 | -3,000 | 2,000 |10,000
The above report has an Op Bal column which is the sum of all amount in Period 01 in
Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
all positive amount in Period 02 & Period_Cr Column would contain all negative amount
in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
column is the summation of Op Bal + Period Bal.
Guys, hope someone out there can help me with the sql command for the above report?
April 12, 2006 at 6:36 am
OK, this one is a little trickier.
DECLARE @p tinyint -- Stores the period we are looking for
DECLARE @y smallint -- Stores the year we are looking for
SET @p = 2
SET @y = 2005
SELECT A.GL_ID, A.GL_Name_VC,
ISNULL(PrevPd.Op_Bal,0) AS Op_Bal,
ISNULL(CurPd.Period_Dr) AS Period_Dr,
ISNULL(CurPd.Period_Cr) AS Period_Cr,
ISNULL(CurPD.Period_Bal) AS Period_Bal,
SUM(A.Amount) AS Closing_Bal
FROM A
LEFT JOIN (SELECT A.GL_ID, A.GL_Name_VC,
SUM(A.Amount) AS Op_Bal
FROM A
WHERE A.Period_TI < @p AND A.Year_SI 0 THEN A.Amount ELSE 0 END) AS Period_Dr,
SUM(CASE WHEN A.Amount < 0 THEN A.Amount ELSE 0 END) AS Period_Cr,
SUM(A.Amount) AS Period_Bal
FROM A
WHERE A.Period_TI = @p AND A.Year_SI = @y
GROUP BY A.GL_ID, A.GL_Name_VC) CurPd
ON CurPd.GL_ID = A.GL_ID AND CurPd.GL_Name_VC = A.GL_Name_VC
GROUP BY A.GL_ID, A.GL_Name_VC
I didn't test this, but I think it does what you're looking for. I used LEFT JOIN to cover cases in which a given GL might have no previous activity, or no current activity.
April 12, 2006 at 7:39 am
To show your results (treat year as one period) then
SELECT a.GL_ID, a.GL_Name_VC,
SUM(CASE WHEN a.Period_TI = 1 THEN Amount ELSE 0 END) AS [Op Bal],
SUM(CASE WHEN a.Period_TI > 1 AND Amount >= 0 THEN Amount ELSE 0 END) AS [Period_Dr],
SUM(CASE WHEN a.Period_TI > 1 AND Amount < 0 THEN Amount ELSE 0 END) AS [Period_Cr],
SUM(CASE WHEN a.Period_TI > 1 THEN Amount ELSE 0 END) AS [Period Bal],
SUM(a.Amount) AS [Closin Bal]
FROM [A] a
WHERE a.Year_SI = 2005
GROUP BY a.GL_ID, a.GL_Name_VC
ORDER BY a.GL_ID, a.GL_Name_VC
If you want each period to be shown then
SELECT a.GL_ID, a.GL_Name_VC, a.Period_TI,
SUM(CASE WHEN b.Period_TI < a.Period_TI THEN b.Amount ELSE 0 END) AS [Op Bal],
SUM(CASE WHEN b.Period_TI = a.Period_TI AND b.Amount >= 0 THEN b.Amount ELSE 0 END) AS [Period_Dr],
SUM(CASE WHEN b.Period_TI = a.Period_TI AND b.Amount < 0 THEN b.Amount ELSE 0 END) AS [Period_Cr],
SUM(CASE WHEN b.Period_TI = a.Period_TI THEN b.Amount ELSE 0 END) AS [Period Bal],
SUM(b.Amount) AS [Closin Bal]
FROM (SELECT DISTINCT x.GL_ID, x.GL_Name_VC, x.Year_SI, x.Period_TI FROM [A] x) a
INNER JOIN A b
ON b.GL_ID = a.GL_ID AND b.Year_SI = a.Year_SI AND b.Period_TI <= a.Period_TI
WHERE a.Year_SI = 2005
AND a.Period_TI > 1
GROUP BY a.GL_ID, a.GL_Name_VC, a.Period_TI
ORDER BY a.GL_ID, a.GL_Name_VC, a.Period_TI
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply