Query issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query issue

Author

Topic

goodsolution
Starting Member

38 Posts

Posted - 2009-06-17 : 00:14:00

Hi all,
I have a scenario like,
i have a table called TableA

id Year Month
1 2009 Jan
2 2009 Feb
3 2009 March

and one more table called TableB

id Sales Amount
1 100 250
2 200 350
3 300 450

Now I want the o/p as

Jan = sum(sales) and Sum (amount)
Feb = Jan + sum(sales) and Sum (amount)
March = Jan + Feb + sum(sales) and Sum (amount)

how to achieve this?

-Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-17 : 00:29:27

do you have a date column in TableA rather than Year and Month in separate field ? Also is the month in varchar or integer ?

KH
[spoiler]Time is always against us[/spoiler]

goodsolution
Starting Member

38 Posts

Posted - 2009-06-17 : 01:32:34

No I dont have any date column in my tableA, and month is in Varchar(50)

-Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 02:07:31

Is it safe to assume that ID column in TableA represents the "order" of months and years?

E 12°55'05.63"
N 56°04'39.26"

goodsolution
Starting Member

38 Posts

Posted - 2009-06-17 : 08:51:51

Yes

-Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 09:28:08

[code]DECLARE @TableA TABLE
(
ID TINYINT,
[Year] SMALLINT,
[Month] VARCHAR(5)
)

INSERT @TableA
SELECT 1, 2009, 'Jan' UNION ALL
SELECT 2, 2009, 'Feb' UNION ALL
SELECT 3, 2009, 'March'

DECLARE @TableB TABLE
(
ID TINYINT,
Sales MONEY,
Amount MONEY
)

INSERT @TableB
SELECT 1, 100, 250 UNION ALL
SELECT 2, 200, 350 UNION ALL
SELECT 3, 300, 450

SELECT q.ID,
q.[Year],
q.[Month],
d.thisSales,
d.thisAmount,
d.accSales,
d.accAmount
FROM (
SELECT t1.ID,
MIN(t1.Sales) AS thisSales,
MIN(t1.Amount) AS thisAmount,
SUM(t2.Sales) AS accSales,
SUM(t2.Amount) AS accAmount
FROM @TableB AS t1
INNER JOIN @TableB AS t2 ON t2.ID <= t1.ID
GROUP BY t1.ID
) AS d
INNER JOIN @TableA AS q ON q.ID = d.ID
ORDER BY q.ID[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources