CODEWITHSUNDEEP
sql-server-2008
Shreyas Achar
Shreyas Achar

Reputation: 1435

Subtraction in SQL Server 2008

I have two select queries like this:

SELECT COUNT(WARD_id) 
FROM IP_Admission 
WHERE (Status = 'V' OR Status = 'D') AND WARD_ID = 1

SELECT BED_STRENGTH 
FROM Ward_Master 
WHERE Ward_ID = 1

Output of the query is 

   2
   6

Now I need answer 4. Any help appreciated.

Upvotes: 1

Views: 3371

Answers (6)

Shreyas Achar
Shreyas Achar

Reputation: 1435

Thanks everyone i solved my query as

   SELECT M.Bed_Strength - COUNT(A.WARD_id)  as diff
  FROM IP_Admission A  
JOIN  Ward_Master M ON A.WARD_ID = M.WARD_ID 
 WHERE (A.Status='V' OR A.Status='D') and M.Ward_ID=1 group by M.Bed_Strength;

Upvotes: 0

yxre
yxre

Reputation: 3704

Since you are pulled these values from two different tables, the best way to do this will to be to join the table on ward id.

SELECT wm.BED_STRENGTH - COUNT(ip.WARD_id) 
FROM Ward_Master wm inner join IP_Admission ip
         On wm.ward_id = ip.ward_id
WHERE (ip.Status='V' OR ip.Status='D')
Group by wm.BED_STRENGTH

This should work, but it might not depending on the schema. The advantage with this method is that it will find the value for all ward ids.

The simplest solution is:

SELECT (subquery1) - (subquery2)

This will only show the value for the ward id that you specify.

Upvotes: 1

Mahmad Khoja
Mahmad Khoja

Reputation: 463

You can use inner join like this. Please let me know if this doesn't work.

create table #Tmp1
(
    Ward_Id INT,
    Status Char(1)
)

create table #tmp2
(
    Ward_Id INT,
    BED_STRENGTH INT
)

INSERT INTO #Tmp1 VALUES (1,'V'),(1,'D')

INSERT INTO #tmp2 VALUES (1,6)

SELECT COUNT(WARD_id) 
FROM #Tmp1 
WHERE (Status='V' OR Status='D') AND WARD_ID=1

SELECT BED_STRENGTH FROM #Tmp2 WHERE Ward_ID=1

SELECT #tmp2.BED_STRENGTH -  COUNT(#tmp1.WARD_id) 
FROM #Tmp1 
INNER JOIN #tmp2 ON #tmp2.Ward_Id = #tmp1.Ward_Id 
WHERE (Status='V' OR Status='D') AND #tmp1.WARD_ID=1
GROUP BY #tmp2.BED_STRENGTH

DROP TABLE #Tmp1 

DROP TABLE #Tmp2

Upvotes: 1

Suraj Shrestha
Suraj Shrestha

Reputation: 1808

select count(i.ward_id) - w.bed_strength as Diff from 
ip_admission i join ward_master  w on w.ward_id=i.ward_id
where (status='V' or status='D') and i.ward_id=1
group by w.bed_strength

Upvotes: 1

sureshhh
sureshhh

Reputation: 1216

Try This for all ward ID's

SELECT M.Ward_ID,BED_STRENGTH -COUNT(WARD_id)  
FROM IP_Admission A  
     JOIN  Ward_Master M ON A.WARD_ID = M.WARD_ID 
WHERE (A.Status='V' OR A.Status='D') 
GROUP BY M.Ward_ID

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You may try like this:

declare @a int
declare @b int

SELECT @a = COUNT(WARD_id) FROM IP_Admission WHERE (Status='V' OR Status='D') AND WARD_ID=1

SELECT @b = BED_STRENGTH FROM Ward_Master WHERE Ward_ID=1

select @b - @a

Upvotes: 2

Related Questions