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)
GROUP BY d.PartNo

Comments

Popular posts from this blog

Alexa Is Listening, Recording, and Sending!

Check out this story where a couple in Oregon was having a private conversation and Alexa recorded and sent the conversation to one of their contacts!  They were not aware until the contact contacted them about what had happened.  The couple contacted Amazon and they verified the series of events.  But what's even more bizarre is the couple wanted to return their Amazon home devices but Amazon has not agreed to return the costs for the devices! Check out the article here  by Bruce Brown on  Digital Trends

Nonclustered Index in SQL Server

So, I've run into an issue with one of my queries because it takes about 40 seconds to run and according to the Recent Expensive Queries section of the Activity Monitor in SQL Server, there is a section of code taking up about 51% of the query.  First, let me say that I've been writing Transact SQL for quite awhile but have never ventured into the Clustered and NonClustered areas of forming Indexes.  I do use indexes on all my tables but that's just the regular Primary Unique key.  We've been using this query for awhile and it does work but when the result set is about 5K records it takes a little while to run.  So here's the actual query itself SELECT PartNo , SerialNo , HeciCode , ScanDate , OrderType , PoNo , po . IssueDate , SoNo FROM ScanHistory sh WITH ( NOLOCK ) LEFT OUTER JOIN trkRWPOHeader po WITH ( NOLOCK ) ON sh . PoNo = po . PONum WHERE len ( PoNo ) > 2 AND ScanDate >

File Size Upload Limits using Godaddy Hosting

I've been working on a new project for a friend of mine and came across an issue with trying to upload photos using the infamous freeASPUpload code.  I've used the code in the past for some past projects but don't recall having issues with limits on uploading files. I'm posting this on my blog b/c there's nothing on the internet that discusses this (or at least I wasn't able to find any explanations), and hopefully this can be a resource to someone else that comes across the same issue. Now I know there are other options, utilities, etc. for uploading pics/files/etc. but  freeASPUpload  is simple, I've used it in the past, and this project is pretty basic.  So, if you want to change the upload file size limit you will first want to go into your Godaddy's hosting account.  Next go into the Virtual Directories option, then click on Directory Properties.  Towards the bottom there is a section called "Maximum size of entity body of an ASP request