Skip to main content

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)


Popular posts from this blog

My Twitter Followers Have Gone Down!

If you're a Twitter user you might have heard or seen on your account the number of followers go down.  For some of us, those numbers might be minimal.  I have a Twitter account @NKYSports which I use in connection with one of my High School sports website  Last week I had about 6,070 followers.  That number today is down about 100.  But I have heard users who have followers over 50K, 100K, etc. losing thousands and thousands of "so-called" followers.

So what happened?  Twitter has started cleaning accounts due to fake news scandals and data privacy.  That equates to about 1 million accounts the past quarter (2nd Q of 2018).  Twitter CEO Jack Dorsey says improving the health of the platform is critical for future growth.

Twitter has been a great tool for my website, but I also like getting updates sent directly to my phone to get the latest news, sports, weather, etc.  I know what to believe and not to believe as well!  Fake news is every where, always wil…

Book Review: The New Psycho-Cybernetics

The book The New Psycho-Cybernetics by Dr. Maxwell Maltz is truly an amazing book!  This book should be read by everyone!  Whether your self-image is good or bad this book talks about taking steps to improve your self-image, live a longer life, and ultimately be happy!

I myself feel like I have a good self-image, my self-esteem is high and overall I'm an optimistic and happy person.  But I can see how a book like this can help me strive for higher achievements and help me to reach those higher goals!

For those with poor self-images and low esteem this book will benefit you immensely.  The book talks about several examples where people have been down, sick, lonely.  But have gone through the exercises in the book and come out with a much better self-image and becoming much more happy.

There are so many examples in the book its hard to cover them all and that's why I will definitely be reading the book again, as well as a reference for future inspirations.  Towards the end of th…

Love the XMLHttpRequest Object

I was reading up on some XML and AJAX stuff the other day and came across the XMLHttpRequest Object - when I come across snippets of code like the example in the link, I always try to figure a way to incorporate it into my work.  I always ask myself is this something I can use and make my software more efficient?

This was something I most definitely could use as we have a system of part numbers in our database.  We have users that select part numbers to enter Selling Opportunities so I was able to use the code to look up the part numbers more quickly, showing the part numbers, qty on hand, and some other pertinent info.
Below is an example of some code:
<html> <head> <script> function showHint(str) {     if (str.length == 0) {         document.getElementById("txtHint").innerHTML = "";         return;     } else {         var xmlhttp = new XMLHttpRequest();         xmlhttp.onreadystatechange = function() {             if (this.readyState =…