Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Get Consecutive Records

Author  Topic 

MrMNew
Starting Member

1 Post
Posted - 2014-05-28 : 12:50:18
I am needing to get average and total downtime hours from a table. I have two source tables, DOWN (records with recorded downtime hours) and PROD (records with production values). For every record in my DOWN table, I need to get the most recent 7 consecutive records from PROD table whose RECORDDATE is less than the RECORDDATE in DOWN. I am not looking to get the last 7 historical record, mind you, but the most recent 7 historical records from PROD table whose HOURSFLOWED value is >= 18. I hope this makes sense. I have a solution that works, nut I was hoping to get input from the community to see if there is a faster solution. I am including a small set of data for each table - when I run this on a larger set of data (DOWN record count at 450K+ and PROD record count at 4.5 million +), the sql takes a long time to perform.
THE FOLLOWING IS THE SQL THAT BUILDS THE RECORDSET AND INCLUDESMY CURRENT SOLUTION


/*
CREATE TABLES

CREATE TABLE PROD (MASTERID INT,RECORDDATE DATETIME,HOURSFLOWED REAL)
CREATE TABLE DOWN (MASTERID INT,RECORDDATE DATETIME,DAILYDOWNTIME REAL)


*/

/*
POPULATE TABLES


INSERT INTO PROD
SELECT 6166 MASTERID,'05/27/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/26/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/25/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/24/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/23/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/22/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/21/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/20/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/19/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/18/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/17/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/16/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/15/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/14/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/13/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/12/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/11/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/10/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/09/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/08/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/07/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/06/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/05/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/04/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/03/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/02/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/01/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/30/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/29/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/28/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/27/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/26/2014' RECORDDATE,22 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/25/2014' RECORDDATE,18 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/24/2014' RECORDDATE,18 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/23/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/22/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/21/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/20/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/19/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/18/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/17/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/16/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/15/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/14/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/13/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/12/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/11/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/10/2014' RECORDDATE,11 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/09/2014' RECORDDATE,20 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/08/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/07/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/06/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/05/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/04/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/03/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/02/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/01/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/31/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/30/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/29/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/28/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/27/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/26/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/25/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/24/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/23/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/22/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/21/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/20/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/19/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/18/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/17/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/16/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/15/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/14/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/13/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/12/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/11/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/10/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/09/2014' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/08/2014' RECORDDATE,12 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/07/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/06/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/05/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/04/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/03/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/02/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/01/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/28/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/27/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/26/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/23/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/22/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/21/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/20/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/19/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/18/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/17/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/16/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/15/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/14/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/13/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/12/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/11/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/10/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/09/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/08/2014' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/07/2014' RECORDDATE,13 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/23/2014' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/19/2014' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'11/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'11/05/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'11/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'11/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'11/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'11/01/2013' RECORDDATE,9 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/23/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/22/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/14/2013' RECORDDATE,4 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/12/2013' RECORDDATE,14 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/09/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/08/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/05/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'10/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/23/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/22/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/19/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/18/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/17/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/16/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/14/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/13/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/09/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/08/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/05/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'09/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/31/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/23/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/22/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/19/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/18/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/17/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/16/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/14/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/13/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/09/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/08/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/05/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'08/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/31/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/23/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/22/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/19/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/18/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/17/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/16/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/14/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/13/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/09/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/08/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/05/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'07/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/23/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/22/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/20/2013' RECORDDATE,10 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/14/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/13/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/09/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/08/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/06/2013' RECORDDATE,9 HOURSFLOWED UNION
SELECT 6166 MASTERID,'06/01/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/30/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/23/2013' RECORDDATE,10 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/16/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/14/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/13/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/09/2013' RECORDDATE,9 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/02/2013' RECORDDATE,14 HOURSFLOWED UNION
SELECT 6166 MASTERID,'05/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/23/2013' RECORDDATE,15.9167 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/18/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/17/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/16/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/14/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/13/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/04/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'04/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/31/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/27/2013' RECORDDATE,12 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/21/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/19/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/18/2013' RECORDDATE,0.5 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/17/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/16/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/05/2013' RECORDDATE,9 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'03/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/19/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/18/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/17/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/16/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/15/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/14/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/13/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/12/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/11/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/10/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/09/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/08/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/07/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/06/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/05/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/04/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/03/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/02/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'02/01/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/31/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/30/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/29/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/28/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/27/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/26/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/25/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/24/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/23/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/22/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/21/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/20/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/19/2013' RECORDDATE,24 HOURSFLOWED UNION
SELECT 6166 MASTERID,'01/18/2013' RECORDDATE,24 HOURSFLOWED


INSERT INTO DOWN
SELECT 6166 MASTERID,'12/20/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/21/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/22/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/23/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/24/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/25/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/26/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/27/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/28/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/29/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/30/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/31/2012' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/01/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/02/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/03/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/04/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/05/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/06/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/07/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/08/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/09/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/10/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/11/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/12/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/13/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/14/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/15/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/16/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/17/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/22/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/23/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/24/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/25/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/26/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/27/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/05/2013' RECORDDATE,15 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/08/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/09/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/10/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/11/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/12/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/13/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/14/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/18/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/21/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/22/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/23/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/24/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/25/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/26/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/27/2013' RECORDDATE,12 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/04/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/05/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/06/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/07/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/08/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/09/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/10/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/16/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/17/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/18/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/19/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/20/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/21/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/22/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/23/2013' RECORDDATE,8.08333 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/24/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/02/2013' RECORDDATE,10 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/03/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/04/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/05/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/06/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/07/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/08/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/09/2013' RECORDDATE,15 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/16/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/17/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/18/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/19/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/20/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/21/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/22/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/23/2013' RECORDDATE,14 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/30/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'05/31/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/01/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/02/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/03/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/04/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/05/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/06/2013' RECORDDATE,15 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/13/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/14/2013' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/15/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/16/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/17/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/18/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/19/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'06/20/2013' RECORDDATE,14 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/12/2013' RECORDDATE,10 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/13/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/14/2013' RECORDDATE,20 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/15/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/16/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/17/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/18/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/19/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/25/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/26/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/27/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/28/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/29/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/30/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'10/31/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/01/2013' RECORDDATE,15 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/07/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/08/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/09/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/10/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/11/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/12/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/13/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/14/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/15/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/16/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/17/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/18/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/19/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/20/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/21/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/22/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/23/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/24/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/25/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/26/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/27/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/28/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/29/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'11/30/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/01/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/02/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/03/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/04/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/05/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/06/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/07/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/08/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/09/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/10/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/11/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/12/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/13/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/14/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/15/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/16/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/17/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/18/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/19/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/20/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/21/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/22/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/23/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/24/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/25/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/26/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/27/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/28/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/29/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/30/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'12/31/2013' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/01/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/02/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/03/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/04/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/05/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/06/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/07/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/08/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/09/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/10/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/11/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/12/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/13/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/14/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/15/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/16/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/17/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/18/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/19/2014' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/20/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/21/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/22/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/23/2014' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/24/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/25/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/26/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/27/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/28/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/29/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/30/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'01/31/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/01/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/02/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/03/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/04/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/05/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/06/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/07/2014' RECORDDATE,11 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/24/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'02/25/2014' RECORDDATE,24 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/08/2014' RECORDDATE,12 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'03/09/2014' RECORDDATE,23.5 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/09/2014' RECORDDATE,4 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/10/2014' RECORDDATE,13 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/24/2014' RECORDDATE,6 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/25/2014' RECORDDATE,6 DAILYDOWNTIME UNION
SELECT 6166 MASTERID,'04/26/2014' RECORDDATE,2 DAILYDOWNTIME

*/



/*
CREATE INTERPRETED COLUMNS:
ID: ORDER RECORDS BY RECORDDATE TO USE IN SELF JOIN LATER
INCLUDE: INDICATES WHETHER THE RECORD CAN BE INCLUDE IN THE "CONSECUTIVE RECORD" COUNT
*/
;WITH CTE_PROD AS (
SELECT
ID = ROW_NUMBER() OVER (PARTITION BY MASTERID ORDER BY RECORDDATE DESC)
,INCLUDE = CASE WHEN HOURSFLOWED >= 18 THEN 1 ELSE 0 END
,A.*
FROM PROD A
)

/*
CREATE INTERPRETED COLUMNS:
MIN_DATE: MOST HISTORICAL RECORD WHERE PROD RECORD CAN BE INCLUDED IN THE CONSECUTIVE COUNT
MAX_DATE: LEAST HISTORICAL RECORD WHERE PROD RECORD CAN BE INCLUDED IN THE CONSECUTIVE COUNT
CPD: COUNT OF PROD RECORDS INCLUDED IN THE CONSECUTIVE COUNT
DAY_RNG: NUMBER OF DAYS DIFFERENCE BETWEEN THE MIN AND MAX DAYS
*/

,PROD_DAYS AS (
SELECT DISTINCT
A.MASTERID
,A.ID
,A.RECORDDATE
,CPD = SUM(B.INCLUDE) OVER (PARTITION BY A.MASTERID, A.ID)
,MIN_DATE = MIN(B.RECORDDATE) OVER (PARTITION BY A.MASTERID,A.ID)
,MAX_DATE = MAX(B.RECORDDATE) OVER (PARTITION BY A.MASTERID,A.ID)
,DAY_RNG = DATEDIFF(DAY,MIN(B.RECORDDATE) OVER (PARTITION BY A.MASTERID,A.ID,A.RECORDDATE),MAX(B.RECORDDATE) OVER (PARTITION BY A.MASTERID,A.ID,A.RECORDDATE)+1)
FROM CTE_PROD A
LEFT JOIN CTE_PROD B ON A.MASTERID = B.MASTERID AND B.RECORDDATE <= A.RECORDDATE AND B.ID BETWEEN A.ID AND A.ID + 6
)



/*
CREATE INTERPRETED COLUMNS:
MINID: THE BEGINNING ID OF THE FIRST SET OF 7 HISTORICAL RECORDS
*/


,A AS (
SELECT D2.MASTERID
,D2.RECORDDATE
,D2.DAILYDOWNTIME
,MIN(PD2.ID) AS MINID
FROM DOWN D2
LEFT JOIN PROD_DAYS PD2
ON D2.MASTERID = PD2.MASTERID
AND D2.RECORDDATE > PD2.RECORDDATE
AND PD2.CPD >= 7
GROUP BY D2.MASTERID
,D2.RECORDDATE
,D2.DAILYDOWNTIME
)

/*
FINAL SELECT
*/
SELECT DISTINCT
A.MASTERID AS MASTERID
,A.RECORDDATE AS RECORDDATE
,PD.MIN_DATE AS MINDATE
,PD.MAX_DATE AS MAXDATE
,PD.DAY_RNG AS DAY_RNG
,AVG(P.HOURSFLOWED) AS AVG_FLOW_HRS
,SUM(P.HOURSFLOWED) AS SUM_FLOW_HRS
FROM A
LEFT JOIN PROD_DAYS PD ON A.MASTERID = PD.MASTERID AND A.MINID = PD.ID
LEFT JOIN PROD P ON PD.MASTERID = P.MASTERID AND P.RECORDDATE BETWEEN PD.MIN_DATE AND PD.MAX_DATE
GROUP BY
A.MASTERID
,A.RECORDDATE
,PD.MIN_DATE
,PD.MAX_DATE
,A.DAILYDOWNTIME
,PD.DAY_RNG
ORDER BY A.MASTERID,A.RECORDDATE DESC

Michael New

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-05-28 : 15:25:29
First thing I'd try is putting indexes on your MASTERID columns
Go to Top of Page

sanoj_av
Posting Yak Master

118 Posts
Posted - 2014-05-28 : 19:06:49
Your question indicates you need only consecutive 7 dates from PROD table. If so, what is the significance of DAY_RNG in the result set? shouldn't it always be 7? Also I see the result set of your query has records with DAY_RNG >7.for example look at 167th row. MINDATE is 2013-02-20 and MAXDATE is 2013-03-04. Obviously not consecutive 7 days. So I assume that the date range may not necessarily be consecutive. If so, the query can be as simple as below.




Select
D_MASTERID as MASTERID,
D_RECORDDATE as RECORDDATE,
MIN(P_RECORDDATE) as MINDATE,
MAX(P_RECORDDATE) as MAXDATE,
DATEDIFF(DAY,MIN(P_RECORDDATE),MAX(P_RECORDDATE))+1 as DAY_RNG,
AVG(P_HOURSFLOWED) as AVG_FLOW_HRS,
SUM(P_HOURSFLOWED) as SUM_FLOW_HRS
from
(
Select
D.MASTERID as D_MASTERID,
D.RECORDDATE as D_RECORDDATE,
D.DAILYDOWNTIME as D_DAILYDOWNTIME,
row_number() over(partition by D.RECORDDATE order by P.RECORDDATE desc) as SrNo,
P.MASTERID as P_MASTERID,
P.RECORDDATE as P_RECORDDATE,
P.HOURSFLOWED as P_HOURSFLOWED
From
DOWN D,
PROD P
Where
D.MASTERID=P.MASTERID
AND D.RECORDDATE >P.RECORDDATE
AND P.HOURSFLOWED >=18
)A
Where
SrNo <=7
Group by
D_MASTERID,D_RECORDDATE
order by
D_RECORDDATE desc




If you need to filter in only consecutive days, I believe your current solution is inaccurate. Records from 167th row to 182nd row show DAY_RNG 13. The date range should be as below if you need only consecutive dates. In other words, all the DAY_RNG values should be 7.



with cte as
(Select
*
from
(
Select
D.MASTERID as D_MASTERID,
D.RECORDDATE as D_RECORDDATE,
row_number() over(partition by D.RECORDDATE order by P.RECORDDATE desc) as SrNo,
P.RECORDDATE as P_RECORDDATE,
P.HOURSFLOWED as P_HOURSFLOWED
From
DOWN D,
PROD P
Where
D.MASTERID=P.MASTERID
AND D.RECORDDATE >P.RECORDDATE
AND P.HOURSFLOWED >=18
)A
)
Select
a.D_MASTERID as MASTERID,
a.D_RECORDDATE as RECORDDATE,
MAX(b.P_RECORDDATE) as MINDATE,
MAX(a.P_RECORDDATE) as MAXDATE,
datediff(day,MAX(b.P_RECORDDATE),MAX(a.P_RECORDDATE))+1 AS DAY_RNG
from
cte a,
cte b
where
a.D_MASTERID=b.D_MASTERID and a.D_RECORDDATE=b.D_RECORDDATE and a.SrNo=b.SrNo-6 and datediff(DAY,b.P_RECORDDATE,a.P_RECORDDATE)=6
group by
a.D_MASTERID,a.D_RECORDDATE
order by
a.D_RECORDDATE desc
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site