April 11, 2006 at 11:58 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 |Period_01|Period_02 |Total_YTD
================================================
1000 | Inventory | 8,000 | 2,000 |10,000
Percentage| 10% | 20% | 0
================================================
Total | 800 | 400 | 1,200
The Total row is calculated by multiplying the percentage row by the Inventory amount in
each Period.
Guys, hope someone out there can help me with the sql command for the above report?
April 12, 2006 at 3:59 am
Try This
Declare @tab Table(
GL_ID Int
, GL_Name_VC VarChar(15)
, Amount Int
, Period_TI TinyInt
, Year_SI SmallInt
)
Insert into @tab Values( 1000, 'Inventory', 8000 , 01 ,2005 )
Insert into @tab Values( 1000, 'Inventory', -3000 , 02 ,2005 )
Insert into @tab Values( 1000, 'Inventory', 5000 , 02 ,2005 )
-- Get Period and YTD Aggregations -------------------------------------
Select
GL_id
, GL_Name_VC
, Year_SI
, Period_01 = Sum( Case Period_ti When 1 Then Amount End ) * 0.1 --10%
, Period_02 = Sum( Case Period_ti When 2 Then Amount End ) * 0.20 --20%
, YTD =
Sum( Amount *
Case Period_ti
When 1 Then 0.1 --10%
When 2 Then 0.2 -- 20%
End
)
From @tab
Group by
GL_id
, GL_Name_VC
, Year_SI
--, Period_TI
With Rollup
Having Grouping( Year_SI ) = 0
April 12, 2006 at 4:01 am
Sorry I forgot to remove the 'With Rollup and Having clause'
There is no need for that.
April 13, 2006 at 6:43 pm
Hi AnzioBake,
Thanks for your kind reply, i've tested the script,
Below is the output:
| GL_ID | GL_Name_VC | Year_SI | Period_01 | Period_02 | Total_YTD |
| 1000 | Inventory | 2005 | 800 | 400 | 1200 |
But, can i extract something like this:
| GL_ID | GL_Name_VC | Year_SI | Period_01 | Period_02 | Total_YTD |
| 1000 | Inventory | 2005 | 8000 | 2000 | 10000 |
| Percentage | 10% | 20% | |||
| Total | 2005 | 800 | 400 | 1200 |
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply