help me - 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

General SQL Server Forums

New to SQL Server Programming

help me

Author

Topic

alapatisai1984
Starting Member

5 Posts

Posted - 2007-10-10 : 01:20:08

hi i worte a query like this by using this

select
temp1.code ,items.itname,type,

stock = case type when 'p' then sum(qty)
else sum(-qty)end

from (SELECT 'p' type,dpurchase.code, dpurchase.qty, purchase.[date]
FROM purchase INNER JOIN
dpurchase ON purchase.refid = dpurchase.drefid
union
SELECT 's' type,dsales.code, dsales.qty, sales.[date]
FROM sales INNER JOIN
dsales ON sales.sid = dsales.dtsid

) as Temp1
inner Join Items On Items.code = Temp1.code

where date <='2007-10-08'

group by temp1.code,type,items.itname

o/p

100 tv p 5
100 tv s -4
101 radio p 5
101 radio s -5
102 cdplayer p 2
102 cdplayer s -2
103 dvdplayer s -2
104 mobile p 2
104 mobile s -3
now i want the out put like

stock
100 tv p 5 s -4 1
101 radio p 5 s -5 0
102 cdplayer p 2 s -2 0
103 dvdplayer s -2 -2
104 mobile p 2 s -3 -1
plz give the query for that

sai krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 02:04:42

Something like this

SELECT		ColW,
		SUM(CASE WHEN Type = 'p' THEN Value ELSE 0 END) AS Col1,
		SUM(CASE WHEN Type = 'd' THEN -Value ELSE 0 END) AS Col2
FROM		Table1
GROUP BY	ColW

E 12°55'05.25"
N 56°04'39.16"

alapatisai1984
Starting Member

5 Posts

Posted - 2007-10-10 : 02:47:52

thank you but that will give duplicate values

sai krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 03:05:41

How can you say that!? Do you even understand what is going on?

1) Did you test?
2) Do you have fact before you post "IT DO NO WORK!"?

Try this for fact

-- Prepare sample data
DECLARE	@Sample TABLE (Col1 INT, Col2 VARCHAR(10), Col3 CHAR(1), Col4 INT)

INSERT	@Sample
SELECT	100, 'tv', 'p', 5 UNION ALL
SELECT	100, 'tv', 's', -4 UNION ALL
SELECT	101, 'radio', 'p', 5 UNION ALL
SELECT	101, 'radio', 's', -5 UNION ALL
SELECT	102, 'cdplayer', 'p', 2 UNION ALL
SELECT	102, 'cdplayer', 's', -2 UNION ALL
SELECT	103, 'dvdplayer', 's', -2 UNION ALL
SELECT	104, 'mobile', 'p', 2 UNION ALL
SELECT	104, 'mobile', 's', -3

-- Show the expected output
SELECT		Col1,
		Col2,
		p,
		s,
		p + s AS r
FROM		(
			SELECT		Col1,
					Col2,
					SUM(CASE WHEN Col3 = 'p' THEN Col4 ELSE 0 END) AS p,
					SUM(CASE WHEN Col3 = 's' THEN Col4 ELSE 0 END) AS s
			FROM		@Sample
			GROUP BY	Col1,
					Col2
		) AS q
ORDER BY	Col1