2

This is my first time posting here on StackExchange. I am studying how to write queries using SQL so that at my workplace we can write more in-depth custom reports for our inventory software. I work in the Shop of our school district's Transportation Department ordering parts and monitoring the stocks thereof.

I only started studying SQL via online resources such as Google, Microsoft articles, and video series(es?) on YouTube, so please forgive me if my SQL syntax is poor; I have much more time to draft reports at home (i.e. AWAY from the database on which I need to test these reports!). I was able to obtain from the manufacturer of our software a PDF detailing which tables in the database connect to which and where.

However, as is to expected of one so new to this, I'm struggling a lot with syntax.

(For those of you wondering if I've even reached out to the software manufacture's tech support, I have- I have spoken with them literally dozens of time over many, many subjects, one of which being to get help writing a custom report, and in that department I am at least 90% sure that only ONE person there who actually cares enough to learn how to navigate SQL.)

Anyway- The goal of one of the reports I'm drafting (which is also what brings me here today) is to allow us to forecast the usage of parts in the coming seasons. It would use what I guess could be described as recursive queries and would sum the quantities of each distinct part used going back at three years in three month intervals. (But, of course, this interval could be scaled out to two or even three months for a better representation of use over long periods of time.) When completed, an aspect of this query could also then be copied for use in the writing of a second report which would quarterly automatically update the desired minimum and maximum stock of each part- another much-needed feature for this software!

I get the feeling that I can actually write a procedure and declare variables such that I won't have to write the subquery back out all twelve times, but need only define the interval between summations, a positive whole-number.

Here are the two tables I'm going to have to use and the pertinent columns:

WorkOrders

RecordID LONG (PK)
RelDateTime Date/Time

WoPart

RecordID LONG (FK)
PartNumber TEXT
Description TEXT
Qty DECIMAL
InstDate Date/Time

Parts

PartNumber TEXT
Descrip TEXT

(There is another field in WoPart, WoPart.InstDate, which I originally thought meant I wouldn't need the WorkOrders table, and I spent a lot of time trying to figure out how to properly write my query so it would actually give me anything but a null value- alas, that column doesn't seem to really do anything; I couldn't find a single non-null value for that column in any row in that table. I thought then that maybe it had to do with when the part was installed if the part in question was ever defined as having a warranty (a feature we're only finally barely pushing into in our database!). Unfortunately, additional queries concluded that this does not seem to be the case either. Again- this is why I am here.)

At this point I believe I need to write an inner query that first searches tbl.WorkOrders for those values of RelDateTime within the desired time frame (let's say Dec. 1, 2016 through Feb. 1, 2017). From there tbl.WorkOrders has an inner join with tbl.WoPart on their RecordID columns such that we can have the outer query pull up those WoPart RecordIDs which used the part in question, and use the expression SUM(Qty).

This is what I have thus far. It's unfinished and I'm sure looks confounded (especially considering I have had to start all over again today!) Maybe it just needs to get...turned inside out... :

DECLARE @Interval INT > 0

SET @Interval = 1

SELECT      WoPart.PartNumber AS [Part Number]
           ,Parts.Descript AS [Description]
           ,SUM(SELECT Qty
                FROM   WoPart
                WHERE  PartNumber = PartNumber) AS [MONTH(GETDATE()) + '/' + RIGHT(YEAR(GETDATE()),2)]
FROM       WoPart
INNER JOIN WorkOrders
ON         WorkOrders.RecordID = WoPart.RecordID
INNER JOIN Parts
ON         WoPart.PartNumber = Parts.PartNumber
WHERE      RelDateTime BETWEEN DATEADD(MONTH, -2 * @Interval, GETDATE())
                               AND
                               DATEADD(MONTH, -1 * @Interval, GETDATE())

The desired output should look something like this:

+--------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| Part Number  |  Description  | Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | 2014  | Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | 2015  | Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | 2016  |
+--------------+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| BD7250       | OIL FILTER    |    12 |    13 |    11 |    14 |    50 |    14 |    12 |    15 |    13 |    54 |    14 |    16 |    13 |    17 |    60 |
| ...          | ...           | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   |
  • The edit was intentional. Since no one had posted a comment yet I went ahead and edited the details of my question. Now it's 'locked in', so to speak. Haha :) – Ben Williamson Mar 22 '17 at 14:26
  • OK, good. What about the quotes? – ypercubeᵀᴹ Mar 22 '17 at 14:35
  • Now- you mentioned the DATEADD in quotes... isn't that needed so the query will look for the strings whose values fall between the two parameters, or does SQL already understand that DATEADD returns a specially-formatted string (making the quotes redundant)? – Ben Williamson Mar 22 '17 at 14:36
  • DATEADD returns a date (or datetime). If RelDateTime is also a date (or datetime), then no conversion to strings is done or needed. – ypercubeᵀᴹ Mar 22 '17 at 14:37
  • Gotcha. (Correcting) – Ben Williamson Mar 22 '17 at 14:38
1

Add as many SUM(CASE as you need according selected dates.

SELECT      WoPart.PartNumber AS [Part Number]
           ,Parts.Descript AS [Description]
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 THEN Qty ELSE 0 END) AS '2014'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 THEN Qty ELSE 0 END) AS '2015'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 THEN Qty ELSE 0 END) AS '2016'
FROM       WoPart
INNER JOIN WorkOrders
ON         WorkOrders.RecordID = WoPart.RecordID
INNER JOIN Parts
ON         WoPart.PartNumber = Parts.PartNumber
WHERE      RelDateTime >= CAST('20140101' AS DATETIME)
AND        RelDateTime <  CAST('20170101' AS DATETIME)
GROUP BY   WoPart.PartNumber, Parts.Descript;
  • Are we able to comment with actual pictures, or are we restricted to text only? because when I ran this query all quarterly sums rang up as 0 for several pages of results... – Ben Williamson Mar 23 '17 at 19:16
  • When I ran your query this is the output I received: docs.google.com/document/d/… I know for a fact that we've used these parts. There were only three pages of results, too, whereas we have about +3,000 parts on record. – Ben Williamson Mar 23 '17 at 20:25
  • I updated the desired output layout in my question so that it's more explicit. Hopefully that will clear up some confusion. – Ben Williamson Mar 23 '17 at 21:29
  • I had to make some very minor amendments (add an ORDER BY statement and another WHERE condition to filter out parts not in the desired site), and it seems to be working great! :) – Ben Williamson Mar 24 '17 at 18:03
1

You can solve this with a GROUP BY statement.

Let's start simple by calculating the result by quarter:

To get the quarter, we use the DATEPART() function, it can extract information from a date, DATEPART(yy,date) returns the year and DATEPART(qq,date) returns the quarter.

Try this query:

SELECT      WoPart.PartNumber AS [Part Number]
           , Parts.Descript AS [Description]
           , DATEPART(yy,RelDateTime) AS [Year]
           , DATEPART(qq,RelDateTime) AS [Quarter]
           , SUM(WoPart.Qty) AS [TotalQuantity]
FROM       WoPart
INNER JOIN WorkOrders
ON         WorkOrders.RecordID = WoPart.RecordID
INNER JOIN Parts
ON         WoPart.PartNumber = Parts.PartNumber
WHERE      RelDateTime >= DATEADD(YEAR, -3, GETDATE())
GROUP BY   WoPart.PartNumber, Parts.Descript, DATEPART(yy,RelDateTime), DATEPART(qq,RelDateTime)
ORDER BY   WoPart.PartNumber, Parts.Descript, DATEPART(yy,RelDateTime) DESC, DATEPART(qq,RelDateTime) DESC

This will return the subtotals for every part in every quarter.

If you want a different interval that DATEPART() can not provide (like, for instance bi-monthly), you could implement a function that takes a date and returns the same result for all dates in the same interval. You can then use that function to group your data. (But keep in mind that the function will be called for each row, you should consider storing the result in a computed column and put an index over it.)

  • FYI - error in SQL statement - SUM(WoPart,Qty) needs a period, not a comma (too small for me to just edit, sorry). – RDFozz Mar 22 '17 at 17:08
  • I know our Fleet Manager is wanting to start making quarterly stock purchases, so for the time being we need to be able to view at least quarterly use. The yearly totals are a nice touch, too. :) Luckily our inventory software allows us to save the reports we put together. I'm curious- Is there a way to write a query such that when executed it will personally prompt you for filtering information, or such that it will make the displayed headers into buttons for toggling how the query is sorted? Would you have to write some sort of... 'dynamic' view in order to make that happen? – Ben Williamson Mar 22 '17 at 18:59
  • @Ben Williamson - The ability to prompt for certain parameters, or have access to buttons for sorting will lie in the application issuing the query, not in the query itself. I want to say SSMS can prompt for values in some way, but there it's usually simplest to plug in what you want. Honestly, I'd copy the results to an Excel sheet for filtering/re-sorting of the results themselves. – RDFozz Mar 22 '17 at 21:07
  • @Ben Williamson - Also, note that there are simple tricks you can use to tweak this query - for instance, if you wanted quarterly results that ran December through February instead of January through March, just add a month to the dates when picking the quarters (DATEPART(qq, DATEADD(month, 1, RelDateTime))). – RDFozz Mar 22 '17 at 21:15
  • By the way, the reason I only wrote a single column with the summation expression is because I was trying to imply that there would be a column for each quarter (which would then followed by the yearly total. This means that the actual output should look more like... – Ben Williamson Mar 23 '17 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.