Please support our MS SQL advertiser: Programming Forums
Views: 1426 | Replies: 2
 |
•
•
Join Date: May 2007
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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...
+-------+---------+-----------+
| 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...
•
•
Join Date: Jan 2007
Posts: 2,640
Reputation:
Rep Power: 7
Solved Threads: 118
You are not summing in the previous month.
Daylight-saving time uses more gasoline
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
•
•
•
•
Originally Posted by MidiMagic 
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 |
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
