0

Asset Table

+-------+---------+-----------+
| Date | Asset | Value |
+-------+---------+-----------+
|1/1/07 |Building | $1000 |
|1/2/01 |Computer | $ 500 |
|2/1/07 | Fax | $ 100 |
+-------+--------+------------+

Result Table I would Like;
+-------+---------------+
| Month | Total Asset |
+-------+---------------+
| 1 | $1500 |
| 2 | $1600 |
+-------+---------------+

However using the following query like this:
"SELECT Sum(Value) AS Total_Aset, Month(Date) AS Month
FROM Asset
WHERE Asset.date between Begindate And Enddate
GROUP BY Month(date);"

I have incorrect query like
+-------+---------------+
| Month | Total Asset |
+-------+---------------+
| 1 | $1500 |
| 2 | $100 |
+-------+---------------+

Can Anyaone help me on this problem?
Thanx...

3
Contributors
2
Replies
3
Views
10 Years
Discussion Span
Last Post by cfAllie
0

You are not summing in the previous month.

As Midimagic said, you need to sum the previous month(s) too. One way to do this is using a join

SELECT    curr.AssetYear, curr.AssetMonth,
    SUM(curr.Total + ISNULL(prev.Total, 0)) AS Asset_Total
FROM    (
       SELECT  Year(Date) AS AssetYear, 
           Month(Date) AS AssetMonth, 
           Sum(Value) AS Total
          FROM    Asset
       WHERE   date between @beginDate And @endDate
       GROUP BY Year(Date), Month(Date)
    ) 
    AS curr LEFT JOIN
    (
       SELECT  Year(Date) AS AssetYear, 
           Month(Date) AS AssetMonth, 
           Sum(Value) AS Total
          FROM    Asset
       WHERE   date between @beginDate And @endDate
       GROUP BY Year(Date), Month(Date)
    ) 
    AS prev ON curr.AssetYear = prev.AssetYear 
        AND curr.AssetMonth > prev.AssetMonth
GROUP BY curr.AssetYear, curr.AssetMonth
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.