SQL Server Views

As a Full Stack Developer I write a lot of Transact SQL and sometimes I get into the habit of writing regular queries, and if it becomes more complex I might write an .ASP page that has queries within queries (which I hate to do) but sometimes it's a quick report and you're just wanting to get it done.

But today, for whatever reason I was like, why don't you try using a SQL View.  A SQL View is a virtual table based on the result-set of a SQL statement.  I've written Views here and there in the past but sometimes I just forget how useful they can be.

So when looking at my scenarios for this report, I was trying to get my creative juices flowing.  How can I make this query simple and efficient?  How can I limit my code to get this particular task completed?  How can I compare the value in one table to values in another that had some business logic behind it (that being the View).

I was pretty happy with the end result as it only took me about one hour to complete and it runs fast!  Here's my final result:


SELECT s.ItemNum AS ItemNum , s.QtyOnHand - isnull(v.InProcessQty,0) AS TotalQtyOnHand
FROM Table1 s
INNER JOIN Table2 i ON s.ItemNum = i.ItemNum
LEFT OUTER JOIN vw_Script v ON i.ItemNum = v.PartNo
WHERE i.QtyONhand > 0 and BinLocationCnt = 0
GROUP by s.ItemNum, s.QtyOnHand - isnull(v.InProcessQty,0) 
HAVING s.QtyOnHand - isnull(v.InProcessQty,0) > 0
ORDER BY s.itemNum

View script:
SELECT     ISNULL(SUM(d.Qty), 0) AS InProcessQty, d.PartNo
FROM         Table3 d INNER JOIN
                      Table4 s ON d.SkidNo = s.SkidNo
WHERE     (s.SkidStatus <> 'Shipped') AND (s.Deleted = 0) OR
                      (s.SkidStatus IS NULL) AND (s.Deleted = 0)
GROUP BY d.PartNo