question

EmailAlerts avatar image
EmailAlerts asked

How to get the count of Open workorders for a date range

I am trying to output the count of Open workorders for a date range. I have two tables; workorder and wostatus table. Sample data is below. Any help is appreciated. thank you. **TABLE : WorKOrder** WoNum,Priority,UserID,Status,OpenDate,Description,StatusDate,Location A17047,12,AD201685359,WaitingMaterials,30-Jun-16,laptop fan not working,2-Jul-15,HR A55198,12,AD201658376,WaitingApproval,01-Jul-16,Need help with connecting two monitors,1-Jul-16,Finance A35508,4,AD201621226,Close,02-Jul-16,Upgrade memory to 8GB,8-Jul-16,Media **TABLE : wostatus** Wonum,Status,changedate,changedby A17047,InProgress,30-Jun-16,AD201659555 A17047,Approved,01-Jul-16,AD201659555 A17047,WaitingMaterials,02-Jul-15,AD201659555 A55198,InProgress,01-Jul-16,AD201659555 A55198,WaitingApproval,01-Jul-16,AD201659555 A35508,InProgress,02-Jul-16,AD201659555 A35508,WaitingApproval,02-Jul-16,AD201659555 A35508,Approved,03-Jul-16,AD201659555 A35508,Complete,08-Jul-16,AD201659555 A35508,Close,08-Jul-16,AD201659555 **Query Output / Want the output to look like this** Date Total P12 Open WorkOrders,Total P4 Open WorkOrders 30-Jun-16 1 0 01-Jul-16 2 0 02-Jul-16 2 1 03-Jul-16 2 1 04-Jul-16 2 1 05-Jul-16 2 1 06-Jul-16 2 1 07-Jul-16 2 1 08-Jul-16 2 0 [1]: /storage/temp/3588-script.sql
counts
sql-question.jpg (145.4 KiB)
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@EmailAlerts What does the "Open work order" mean? Is it a work order with Status = "Open" or something else? It looks like you have some procedure in place which updates the Status column of the WorKOrder table whenever the new row is inserted into WOStatus. Is this assumption correct? If so then you don't really need to have WOStatus table to participate in your query. Please provide some info.
0 Likes 0 ·
Any workorder that is not in a "close" or "complete" status is considered "Open" Whenever the status of a workorder changes, the wostatus table gets updated too with the status change.. the workorder table shows only the current status of a particular workorder, the wostatus table has the history of the workorder whenever a change happens.. I hope I cleared the confusion.. so, I am trying to find out how many workorders were in open status on a particular date, e.g 7-Jul-2016, likewise, the count of open workorders on a given range for each priority. Please check the sample output. Hope this clears what you asked. thanks again...
0 Likes 0 ·
How do I know which row is the last one i.e.: A55198,InProgress,01-Jul-16,AD201659555 A55198,WaitingApproval,01-Jul-16,AD201659555 Are you using only a date in change date or time, too ? Which date type are you using ? (DATETIME, DATE etc.)
0 Likes 0 ·
There is time too... sorry about that, I missed to put the time when I was creating the sample data..
0 Likes 0 ·
@EmailAlerts I added the answer which includes the query which works as expected. The answer went to moderation for review, but it should become available soon. Please let me know if this works for you.
0 Likes 0 ·
Show more comments
Oleg avatar image
Oleg answered
This is actually a very good question which I believe deserves some elaboration. The challenge here is that after the status of the work order changes but work order remains not closed, it needs to be considered as an open work order every contiguous day until either work order closes/completes or the last date of the report is reached. So, there is a need to create a set of contiguous days which starts with the earliest change date and ends with the latest change date in the work status table. This set cannot be inner/left joined to work status but needs to be cross joined first in order to "fill the gaps" and only then unwanted records need to be filtered out afterwards in the where clause. What needs to be considered is the following: 1. Records with report date for the specific work order earlier than the earliest work status record are filtered out 2. Records for the day when the work order becomes closed are filtered out. In other words, even if the work order has some not closed statuses in the work status table but then closes on the same day then all records for that day need to be disregarded. 3. Time part needs to be dropped from all datetime values. In the solution below this is done via casting the datetime value as date. If the SQL Server version is 2005 then **date** data type is not available, so the cast need to be restated via dateadd/datediff combination, i.e replace **cast(changedate as date)** with **dateadd(day, datediff(day, 0, changedate), 0)**. This expression removes the time part from the datetime value in the most efficient manner. 4. I believe that it is best to pre-collapse the records in the source before pivoting (thus the distinct is used) 5. If the other priorities besides 4 and 12 exist and need to be added as the additional column(s) to the results, they need to be coded into the IN part of the pivot and also to the final select list. Here is the script which works as expected. Please note the use of CTE which creates a set of contiguous dates with one record per day beginning from the earliest date in the work status table and ending with latest date for specified period (across all orders): /* Since the total date span is over 5 years, it is possible that you don't really need the report displaying the work orders counts for every day of every year for the last 5 years, 365 rows per year. This means that you might want to consider reducing the date range just to get the query to run fast enough and then we can come up with the ways to make it run reasonably fast for any time range. */ -- as an example, lets make the calendar for one full month (July of 2016) declare @days int, @firstDate date; select @firstDate = '20170701', @days = datediff(day, @firstDate, dateadd(month, 1, @firstDate)); ;with dates as ( -- This creates the set of days with one record per day. -- To accomodate those work orders which were initiated prior to the first date of the report -- it makes sense to add 10 days (or however many are needed to ensure that the work orders -- do not fall out of the report results). 10 days are added based on the assumption -- that the work orders' lifecycle is normally less than 10 days. select top(@days + 10) dateadd(day, row_number() over (order by Wonum) - 11, @firstDate) ReportDate from dbo.wostatus ) select pvt.ReportDate, [12] TotalP12OpenOrders, [4] TotalP4OpenOrders from ( select distinct dates.ReportDate, wo.WoNum, wo.[Priority] from dates cross join dbo.wostatus ws inner join dbo.WorkOrder wo on wo.Wonum = ws.Wonum where dates.ReportDate >= cast(wo.OpenDate as date) and not ( wo.[Status] in ('Close', 'Complete') and dates.ReportDate >= cast(wo.StatusDate as date) ) --and wo.persongroup = 'Network' /* uncomment if needs to be */ ) src pivot (count(WoNum) for [Priority] in ([12], [4])) pvt where pvt.ReportDate >= @firstDate; Based on the sample data in question, the query above produces the following result when executed:
ReportDate      TotalP12OpenOrders TotalP4OpenOrders
----------------       -----------------------------  ---------------------------
2016-06-30       1                             0
2016-07-01       2                             0
2016-07-02       2                             1
2016-07-03       2                             1
2016-07-04       2                             1
2016-07-05       2                             1
2016-07-06       2                             1
2016-07-07       2                             1
2016-07-08       2                             0
Interestingly enough, when using the table definitions from the script file attached to the question, I cannot see the column named OpenDate, so please change the script to accommodate it if needed. As far as the tables' design is concerned, barring the excessive number of column in one table, I believe that the following modifications will improve performance of this and many other queries against these tables: 1. Cluster the workorder table by wonum (if it is indeed a heap table now), optionally reducing the column size (no need to define it as size 50 when in reality the values are much shorter). If the wonum values are unique and most importantly, are ever-increasing, then the clustered index would be a good candidate for a clustered primary key 2. Find a suitable combination of columns to cluster the wostatus table if it is a heap table as seen from the table definition 3. Add the index on wonum to wostatus table 4. Consider indexing the OpenDate and StatusDate of the workorder table. 5. Formally relate the wostatus and workorder via foreign key on wonum column Please consider reading the excellent articles about indexes by Gail Shaw, it is 3-part series beginning from [Introduction to Indexes][1] article. Hope this helps, Oleg [1]: http://www.sqlservercentral.com/articles/Indexing/68439/
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg Thank you. I am hoping this works.. Its still running; might take a while I guess for the output.. Is there a way I can add more criteria into this.. e.g. if I want to filter it based off a group, "Network" just for their workorders ? there is a field in the workorder table for the persongroup Execution Time : 50 mins ------------------------------------------------------------- Error : Msg 1101, Level 17, State 10, Line 6 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
0 Likes 0 ·
@EmailAlerts This could be caused by a number of reasons, but I am sure there is a way to figure out the source of the problem and lower the execution time to seconds. Please script the table definitions (I don't need the data, just the create table scripts for 2 tables in question). You can attach the script file to your question. Also, please let me know what is the earliest record in the work status, how many records are in both tables, and how many days does it usually take to complete a work order, so I can mock up the data on my end more or less resembling what you have. If the data spans over many years then you get the report for every day of every year which might not be what you need. If this is the case then you can modify the part which creates the contiguous days set to not span over the entire range. If there are any records in the work status which have the zero date (1900-01-01) in changedate column then the date selection part definitely needs to be modified. If you need to add the filter for persongroup then just add the line reading and wo.persongroup = 'Network' to the end of the where clause of the src.
0 Likes 0 ·
@EmailAlerts Thank you for providing the table definitions. The work order table is pretty wide, it has 237 columns with up to 6.5K per single record which means that the table has pretty much just one row per page. This kind of explains why the script is so slow. Please let me know how many records you have in the workorder table, what is the min of changedate and what is the max of changedate in wostatus, as well as how many days does it normally take to complete one work order on average. Using this information I can mock up some sample data and run some tests. Another problem I see is that both tables are created as heaps (none of them has a clustered index). The fact that there is no formal relationship between the tables (by wonum column) is also a problem. Please let me know what was the reason for creating tables this way? How are the values for wonum column created for new inserts? Do you store the seed and then use some letter as prefix? Please provide the answers to the questions above and I am sure that the way can be found to considerably reduce the execution time.
0 Likes 0 ·
@Oleg workorder records = 12.5 million records wostatus min changedate = 2011-08-17 07:49:00.410 , max = 2016-08-04 04:17:29.707 I cant really give you an answer on the no of days it take to close a workorder, but lets say, 2 to 3 days for the sake of it. Relationships : To be honest, I have no idea... I was told temp relationships are created on the run. I just have RO access to the sql server, no application privileges. Sorry that I am unable to provide the answers to what you want..
0 Likes 0 ·
@EmailAlerts This is enough information for me, thank you. I will work on updating my answer now, will be done with my edit in about one hour. The workorder table is very wide so that the number of data pages is in millions even though the row count is pretty small (only 12.5 million records). To add to the trouble, it is a heap table which is huge no-no. Additionally, both work order and order status tables do not appear to have any indexes and do not have a relationship which they should (there is no such a thing as **temp relationships are created on the run** to the best of my knowledge) This explains why the query does not run to completion. Please check my updated answer when it is ready.
0 Likes 0 ·
Show more comments
datadude avatar image
datadude answered
I have something to start with: SELECT CONVERT(DATE,wo.OpenDate), wo.Status, COUNT(*) AS OpenWorkOrders, (SELECT COUNT(*) FROM dbo.WorkOrder subwo WHERE subwo.WoNum = wo.WoNum) As TotalWorkOrders FROM dbo.WorkOrder wo INNER JOIN dbo.wostatus AS wos ON (wo.WoNum = wos.WoNum AND wo.Status = wos.Status) GROUP BY CONVERT(DATE,wo.OpenDate), wo.Status WHERE wo.OpenDate BETWEEN '2016-01-01' AND GETDATE() AND wo.Status IN ('WaitingMaterials','WaitingApproval') -- not finished ones ORDER BY CONVERT(DATE,wo.OpenDate) DESC, wo.Status I wrote it without the tables and syntax check, I hope you can change it to your needs.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I ran this query , unfortunately it shows some error... ----- Msg 174, Level 15, State 1, Line 16 The COUNT function requires 1 argument(s). Msg 174, Level 15, State 1, Line 16 The COUNT function requires 1 argument(s).
0 Likes 0 ·
It look like this websites ignores stars(*), change COUNT() to COUNT(**)
0 Likes 0 ·
I've updated the formatting of your code so it appears as intended. For future reference - you highlight the code in the editor and click the "101010" button to tell the site it is code.
0 Likes 0 ·
@Dave_Green: Thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.