select query problem - 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)

select query problem

Author

Topic

lyealain
Starting Member

4 Posts

Posted - 2009-02-03 : 22:46:48

hi i got a question on my query.
imagine u want to get data from 2 tables.

Services - product details {Id, Name, Category}
Inventories- contains buy in/ sell out stock quantity
{ServiceId, LedgerId,Qty,DateCreatd}
LedgerId =4 is Buy in
LedgerId = 5 is sell out

i would like to get the product left unit with the product description in one query.

so i do like this.. but this seems to be a very long query..
Please advice whether i am doing a good way?

select *
from Services AS S
	left outer join
	(select BuyIn.serviceId,IsNull(BuyIn.Total,0)-IsNull(SoldOut.Total,0) AS Remain
	from
		(select serviceId,sum(qty) as Total
		from Inventories
		where ledgerId=4
		group by serviceId) as BuyIn
	left outer join
	(select serviceId,sum(qty) as Total
	from Inventories
	where ledgerId=5
	group by serviceId) as SoldOut
on BuyIn.serviceId=SoldOut.serviceId) AS Sold
on Sold.ServiceId=S.Id
where S.Status = 1

lyealain hong loon

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 01:07:57

Shouldn't it be like this?

Select desired columns
from Services AS S
	Inner join
	(Select ServiceId,(Buyin - Soldout) AS Remain
	from
		(Select serviceId,
         Sum(Case When ledgerId=4 then qty else 0 end) as Buyin,
         Sum(Case When ledgerId=5 then qty else 0 end) as Soldout,
         from Inventories
		 group by serviceId) Z)M 
On M.ServiceId=S.Id
Where S.Status = 1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 02:42:36

hi sodeep, how will u get the difference in subquery
once check this sample data output
declare @tab table( EMPID int NOT NULL,
EMPNAME nvarchar(32) ,
EMPSAL decimal(10, 5) ,
EMPDATE datetime )

INSERT INTO @tab
VALUES(1, 'B.Kaladhar', 33433.11000, '2008-09-08 18:18:27');
INSERT INTO @tab
VALUES(2, 'RAMU', 2232.11000, '2008-09-08 18:18:27');
INSERT INTO @tab
VALUES(5, 'RAJU', 2232.11000, '2008-09-08 18:18:27');
INSERT INTO @tab
VALUES(7, 'KUMAR', 2232.11000, '2008-09-08 18:18:27');
INSERT INTO @tab
VALUES(76, 'HARI', 2343.00000, '2008-09-08 18:18:27');

select * from @tab

Select empid,(Buyin - Soldout) AS Remain
from
(Select empid,
Sum(Case When EMPID=1 then EMPSAL else 0 end) as Buyin,
Sum(Case When EMPID=5 then EMPSAL else 0 end) as Soldout
from @tab
group by empid) Z

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 03:07:19

[code]
SELECT s.Name,
SUM(CASE WHEN i.LedgerId =4 THEN i.qty ELSE -1 * i.qty END) AS Remain
FROM Services s
INNER JOIN Inventories i
ON i.ServiceId=s.Id
AND i.LedgerId IN (4,5)
WHERE s.Status=1
GROUP BY s.Name
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources