Need Help Refining A Query

Question

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 |
| ...          | ...           | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   | ...   |

Answer 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;

Answer 2

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.)