Query Total Asset
 

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...

Re: Query Total Asset
 

You are not summing in the previous month.

Re: Query Total Asset
 

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