Converting Continuous Dates Into Work Shifts

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 2000 Forums

Transact-SQL (2000)

Converting Continuous Dates Into Work Shifts

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-14 : 12:25:16

We have multiple machines that record data that Operators input while manufacturing parts. When a record is written, it records the DateTime, Operator's ID Number as varchar(50), the System Id (machine name) as varchar(50), and the Test_Result as varchar(255).

I have been asked to determine the number of shifts an Operator has worked from this data. I can stub this out, but I don't know how to proceed.

Often, employees are asked to work a few hours extra, so we would like an employee's time to start with their first record of the day and go out 12 hours.

My problem did not show up until management started querying the data for a single date, which goes from Midnight to Midnight. People working 2rd shift start at 4 PM would work until 12:30 AM of the next day, so the query pulls up people that ended the day before's shift and adds the next day's shift as well.

How should I build this code?

// Using Visual Studio 2008 Professional with C#
string sqlText = "SELECT System_ID, Date_Time, OP_ID, Test_Result " +
  "FROM Table1 WHERE Date_Time BETWEEN @Date1 AND @Date2";
DataTable table = new DataTable();
List<string> People = new List<string>();
using (SqlDataAdapter da = new SqlDataAdapter(sqlText, mySqlConn))
{
  da.SelectCommand.Parameters.Add("@Date1", SqlDbType.DateTime).Value = dtStart;
  da.SelectCommand.Parameters.Add("@Date2", SqlDbType.DateTime).Value = dtEnd;
  da.Fill(table);
}
for (int i = 0; i < table.Rows.Count; i++)
{
 // ?
}

I've left out my code because I am worried this is going to lead someone to follow my steps - that are not working!

Regards,
~Joe

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:18:09

you store a single date value for each record or both in and outtime?

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-14 : 13:43:11

Every time an item is logged we enter a new row into the table:

INSERT INTO Table1 (System_ID, Date_Time, OP_ID, Test_Result)
VALUES (@System_ID, GetDate(), @OP_ID, @Test_Result)

This would be a single date value, if I understand you correctly.

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 13:45:59

so how do you decide if the entry corresponds to start or end of shift?

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-14 : 14:20:46

At the start of a query, the Person in my List has Count = 0.

I read in a row from the table.

If the person is not in the Person list, add them using the format:

string.Format("{0};{1};", OP_ID, Date_Time);

In words, what the code block below does is this: If the person is in the Person list, read in the DateTime string portion, convert it to a DateTime, and compare that to the Date_Time field that was read in. If (oldDate.AddHours(12) < newDate), tack an "@" symbol onto their name to indicate another day has started and set the oldDate equal to the newDate. When I am done, I count the number of "@" symbols to determine how many shifts they worked.

for (int i = 0; i < table.Rows.Count; i++) {
  bool found = false;
  DataRow r = table.Rows[i];
  DateTime newDate = (DateTime)r[1];
  string OP_ID = r[2].ToString();
  for (int j = 0; (j < People.Count) && (found == false); j++) {
    string[] split = People[j].Split(new char[] { ';' });
    if (split.Length == 3) {
      if (split[0] == OP_ID) {
        DateTime oldDate = MyGetDateFromString(split[1]);
        if (oldDate.AddHours(12) < newDate) {
          People[j] = string.Format("{0};{1};{2}@", OP_ID, newDate, split[2]);
        }
      }
    }
  }
  if (found == false) {
    People.Add(string.Format("{0};{1};@", OP_ID, newDate));
  }
}

It is a horrible approach, but that's the only way I know of to do it.

I really hate even showing anyone this technique, because I'm hoping someone can come up with a better working example.

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-14 : 14:24:42

Again, the problem comes whenever someone works just past midnight on one day and comes in the following day around 4 PM - the report shows them working 2 days instead of just 1 day.

We pay employees a little extra if they work on machines that are considered undesirable (very loud, hot, etc.) - so, the company really doesn't want to pay them for 2 days when they only worked 1 day on this machine.

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 14:29:48

quote:
Originally posted by jp2code

Again, the problem comes whenever someone works just past midnight on one day and comes in the following day around 4 PM - the report shows them working 2 days instead of just 1 day.

We pay employees a little extra if they work on machines that are considered undesirable (very loud, hot, etc.) - so, the company really doesn't want to pay them for 2 days when they only worked 1 day on this machine.

Avoid Sears Home Improvement

for that just take time diff b/w start and end rather day diff and convert it to days

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-14 : 15:07:52

Actually, we can't do that either.

One day, Employee A may work in the steel mill on Mondays, but sit in the air conditioned Supply Room the rest of the week.

Some employees don't mind the heat; some employees really want the extra pay. Other employees only want to work the minimum required time in those areas.

The company only wants to pay Employee A an extra $1/hour on the days they work in the very hot steel mill.

I don't know how to go about this.

Avoid Sears Home Improvement

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-14 : 17:37:53

So are you looking for a sql solution? If so, can you post the DDL, some sample data (DML) and the expected output based on your sample data?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-16 : 09:39:01

Sure. I can post some data.

These are records from this month, 06 Sept to 12 Sept; this is 1473 rows of data:

OP_ID	Date_Time
000713	2009-09-09 01:30:12.823
000713	2009-09-09 01:23:20.767
000713	2009-09-09 01:21:00.263
000713	2009-09-09 01:51:10.563
000713	2009-09-09 01:43:56.983
000713	2009-09-09 01:33:10.480
000713	2009-09-09 02:14:25.130
000713	2009-09-09 02:14:01.440
000713	2009-09-09 01:58:33.793
000713	2009-09-09 02:51:12.807
000713	2009-09-09 02:41:11.713
000713	2009-09-09 02:33:43.780
000713	2009-09-11 07:36:14.450
000713	2009-09-11 07:47:03.797
000713	2009-09-11 07:45:13.977
000713	2009-09-11 07:38:19.360
000713	2009-09-11 07:54:44.837
000713	2009-09-11 07:52:17.217
000713	2009-09-11 07:49:51.687
000713	2009-09-11 08:07:03.953
000713	2009-09-11 08:00:27.080
000713	2009-09-11 07:58:12.883
000713	2009-09-11 08:31:49.560
000713	2009-09-11 08:24:05.787
000713	2009-09-11 08:09:06.437
000713	2009-09-11 09:03:37.053
000713	2009-09-11 08:57:05.137
000713	2009-09-11 08:45:33.453
000803	2009-09-09 17:39:20.877
000803	2009-09-09 20:59:55.153
000803	2009-09-09 20:55:41.137
000803	2009-09-09 17:46:08.093
000803	2009-09-10 00:32:59.150
000803	2009-09-09 23:16:11.760
000803	2009-09-09 22:56:48.470
000803	2009-09-10 17:11:51.560
000803	2009-09-10 17:08:16.130
000803	2009-09-10 00:34:28.087
000803	2009-09-10 22:08:13.803
000803	2009-09-10 20:07:47.790
000803	2009-09-10 20:02:32.700
000803	2009-09-11 00:08:12.713
000803	2009-09-10 22:21:20.683
000803	2009-09-10 22:15:07.497
000887	2009-09-09 16:21:17.740
000887	2009-09-09 18:20:53.447
000887	2009-09-09 16:26:50.093
000887	2009-09-09 16:22:42.510
000887	2009-09-09 20:51:32.090
000887	2009-09-09 20:47:39.603
000887	2009-09-09 18:26:29.523
000887	2009-09-10 00:17:29.360
000887	2009-09-09 22:39:27.920
000887	2009-09-09 22:35:10.743
000887	2009-09-10 00:22:27.870
000887	2009-09-10 00:22:27.580
000887	2009-09-10 00:17:29.450
000887	2009-09-10 17:55:36.110
000887	2009-09-10 16:41:23.353
000887	2009-09-10 16:38:16.853
000887	2009-09-10 18:41:57.820
000887	2009-09-10 18:40:19.513
000887	2009-09-10 17:58:09.177
000887	2009-09-10 20:41:58.257
000887	2009-09-10 20:39:01.700
000887	2009-09-10 18:45:35.607
000887	2009-09-10 23:58:08.163
000887	2009-09-10 22:16:39.600
000887	2009-09-10 22:14:20.737
000887	2009-09-11 00:30:22.167
000887	2009-09-11 00:08:50.140
000887	2009-09-11 00:03:48.250
000913	2009-09-08 06:31:55.773
000913	2009-09-08 07:52:13.473
000913	2009-09-08 07:48:55.237
000913	2009-09-08 06:34:06.473
000913	2009-09-08 10:08:30.307
000913	2009-09-08 10:06:43.150
000913	2009-09-08 08:09:14.820
000913	2009-09-08 12:41:49.597
000913	2009-09-08 12:39:30.987
000913	2009-09-08 11:10:02.223
000913	2009-09-09 06:22:34.230
000913	2009-09-08 14:14:16.657
000913	2009-09-08 14:12:24.203
000913	2009-09-09 09:37:36.500
000913	2009-09-09 09:35:11.897
000913	2009-09-09 07:10:14.677
000913	2009-09-09 13:36:54.787
000913	2009-09-09 13:35:03.580
000913	2009-09-09 11:43:54.610
000913	2009-09-10 06:33:32.430
000913	2009-09-10 06:31:37.603
000913	2009-09-09 14:33:36.903
000913	2009-09-10 08:09:24.780
000913	2009-09-10 08:08:08.977
000913	2009-09-10 08:06:58.247
000913	2009-09-10 09:02:48.840
000913	2009-09-10 09:01:36.217
000913	2009-09-10 08:10:36.900
000913	2009-09-10 12:44:24.247
000913	2009-09-10 10:51:13.763
000913	2009-09-10 10:49:57.633
000913	2009-09-10 14:11:20.437
000913	2009-09-10 14:09:33.023
000913	2009-09-10 12:45:40.040
001060	2009-09-09 17:16:32.390
001060	2009-09-09 16:40:58.740
001060	2009-09-09 22:56:44.237
001060	2009-09-09 20:21:23.000
001060	2009-09-09 19:28:33.317
001060	2009-09-10 16:59:29.500
001060	2009-09-10 00:41:24.617
001060	2009-09-09 23:01:38.980
001060	2009-09-10 18:23:36.653
001060	2009-09-10 17:04:02.360
001060	2009-09-10 17:02:11.947
001060	2009-09-10 20:56:26.610
001060	2009-09-10 20:54:39.567
001060	2009-09-10 18:26:42.887
001060	2009-09-10 22:52:16.750
001060	2009-09-10 22:42:13.700
001060	2009-09-10 20:58:43.683
001060	2009-09-11 00:33:43.380
001060	2009-09-11 00:29:32.590
001060	2009-09-11 00:27:02.363
001093	2009-09-08 22:09:32.093
001093	2009-09-08 22:15:05.373
001093	2009-09-08 22:12:49.457
001093	2009-09-08 22:10:07.793
001093	2009-09-08 23:40:09.353
001093	2009-09-08 23:38:09.530
001093	2009-09-08 23:36:06.893
001093	2009-09-09 16:30:35.310
001093	2009-09-09 00:21:10.410
001093	2009-09-09 00:19:39.430
001093	2009-09-09 19:59:57.937
001093	2009-09-09 18:02:01.203
001093	2009-09-09 16:32:19.790
001093	2009-09-09 21:23:32.407
001093	2009-09-09 21:19:03.870
001093	2009-09-09 21:17:40.260
001093	2009-09-10 00:18:20.683
001093	2009-09-10 00:13:14.230
001093	2009-09-09 22:25:42.700
001093	2009-09-10 19:39:15.970
001093	2009-09-10 17:15:08.553
001093	2009-09-10 00:19:56.520
001093	2009-09-10 23:56:41.370
001093	2009-09-10 21:18:00.050
001093	2009-09-10 20:30:12.457
001103	2009-09-09 14:16:38.617
001103	2009-09-10 13:11:31.893
001103	2009-09-10 08:52:19.140
001103	2009-09-10 08:49:50.747
001232	2009-09-08 17:49:30.620
001232	2009-09-08 16:28:02.110
001232	2009-09-08 16:25:53.717
001232	2009-09-08 19:48:11.950
001232	2009-09-08 19:45:03.580
001232	2009-09-08 17:52:47.363
001232	2009-09-08 21:47:23.723
001232	2009-09-08 20:23:40.360
001232	2009-09-08 20:21:32.547
001232	2009-09-08 23:48:37.743
001232	2009-09-08 23:45:18.187
001232	2009-09-08 21:50:35.120
001232	2009-09-09 16:15:20.847
001232	2009-09-09 16:11:30.033
001232	2009-09-09 00:29:16.500
001232	2009-09-09 18:17:07.590
001232	2009-09-09 16:19:32.717
001232	2009-09-09 16:17:59.993
001232	2009-09-09 18:24:16.847
001232	2009-09-09 18:21:59.810
001232	2009-09-09 18:20:36.340
001232	2009-09-09 22:34:15.987
001232	2009-09-09 20:55:37.747
001232	2009-09-09 20:50:38.257
001232	2009-09-10 00:19:10.327
001232	2009-09-09 22:39:59.060
001232	2009-09-09 22:36:15.157
001232	2009-09-10 00:23:54.407
001232	2009-09-10 00:22:19.070
001232	2009-09-10 00:20:53.277
001232	2009-09-10 16:37:20.880
001232	2009-09-10 16:35:44.560
001232	2009-09-10 16:33:51.380
001232	2009-09-10 17:47:36.617
001232	2009-09-10 17:45:34.137
001232	2009-09-10 16:39:47.550
001232	2009-09-10 20:25:40.160
001232	2009-09-10 18:28:58.410
001232	2009-09-10 17:50:17.660
001232	2009-09-10 20:31:45.560
001232	2009-09-10 20:28:42.043
001232	2009-09-10 20:26:48.330
001232	2009-09-10 23:53:27.700
001232	2009-09-10 21:56:27.187
001232	2009-09-10 21:54:08.170
001232	2009-09-11 00:03:54.207
001232	2009-09-10 23:56:12.230
001232	2009-09-10 23:55:08.837
001561	2009-09-08 16:46:25.203
001561	2009-09-08 18:12:45.483
001561	2009-09-08 16:57:15.947
001561	2009-09-08 16:50:16.513
001561	2009-09-08 18:30:00.103
001561	2009-09-08 18:16:20.500
001561	2009-09-08 18:13:29.470
001561	2009-09-08 18:40:24.690
001561	2009-09-08 18:32:47.080
001561	2009-09-08 18:31:16.600
001561	2009-09-08 20:33:45.750
001561	2009-09-08 20:30:32.833
001561	2009-09-08 19:01:51.547
001561	2009-09-08 23:45:44.550
001561	2009-09-08 22:03:01.460
001561	2009-09-08 22:00:09.447
001561	2009-09-09 16:34:23.677
001561	2009-09-09 00:31:59.837
001561	2009-09-08 23:49:21.140
001561	2009-09-09 18:30:54.877
001561	2009-09-09 18:29:58.020
001561	2009-09-09 16:37:42.160
001561	2009-09-09 18:46:20.410
001561	2009-09-09 18:44:05.867
001561	2009-09-09 18:42:11.363
001561	2009-09-09 22:29:47.753
001561	2009-09-09 22:27:37.397
001561	2009-09-09 20:29:03.663
001561	2009-09-10 16:14:12.237
001561	2009-09-10 00:22:21.650
001561	2009-09-09 22:33:14.390
001561	2009-09-10 16:19:04.800
001561	2009-09-10 16:17:15.590
001561	2009-09-10 16:15:21.690
001561	2009-09-10 17:31:44.337
001561	2009-09-10 17:29:41.363
001561	2009-09-10 16:20:11.283
001561	2009-09-10 18:54:59.597
001561	2009-09-10 17:34:39.160
001561	2009-09-10 17:33:05.790
001561	2009-09-10 18:57:59.407
001561	2009-09-10 18:56:14.630
001561	2009-09-10 18:55:52.570
001561	2009-09-10 19:22:06.297
001561	2009-09-10 19:20:34.770
001561	2009-09-10 18:59:23.793
001561	2009-09-10 21:24:49.560
001561	2009-09-10 21:20:14.967
001561	2009-09-10 19:29:23.753
001561	2009-09-11 00:13:42.430
001561	2009-09-11 00:11:40.917
001561	2009-09-11 00:10:38.640
001614	2009-09-10 09:29:43.863
001614	2009-09-10 09:05:50.587
001751	2009-09-09 05:36:33.373
001751	2009-09-09 05:41:17.653
001751	2009-09-09 05:39:10.810
001751	2009-09-09 05:37:40.437
003206	2009-09-08 16:21:09.680
003206	2009-09-08 17:20:51.133
003206	2009-09-08 17:18:31.313
003206	2009-09-08 16:23:26.557
003206	2009-09-08 18:18:30.583
003206	2009-09-08 18:11:42.583
003206	2009-09-08 18:09:10.567
003206	2009-09-08 21:25:30.547
003206	2009-09-08 20:13:12.597
003206	2009-09-08 20:10:55.490
003206	2009-09-08 23:22:25.953
003206	2009-09-08 21:30:23.607
003206	2009-09-08 21:27:48.663
003206	2009-09-09 00:33:11.200
003206	2009-09-09 00:31:09.390
003206	2009-09-08 23:24:38.883
003206	2009-09-09 17:01:53.770
003206	2009-09-09 16:58:44.680
003206	2009-09-09 16:56:03.023
003206	2009-09-09 18:18:48.257
003206	2009-09-09 18:16:43.930
003206	2009-09-09 18:16:42.867
003206	2009-09-09 18:22:00.990
003206	2009-09-09 18:21:59.027
003206	2009-09-09 18:18:49.160
003206	2009-09-09 19:51:52.380
003206	2009-09-09 18:23:49.860
003206	2009-09-09 18:23:48.897
003206	2009-09-09 19:56:47.877
003206	2009-09-09 19:56:46.800
003206	2009-09-09 19:51:53.370
003206	2009-09-09 20:02:05.460
003206	2009-09-09 19:59:24.460
003206	2009-09-09 19:59:24.270
003206	2009-09-09 20:05:22.953
003206	2009-09-09 20:05:22.050
003206	2009-09-09 20:02:17.750
003206	2009-09-09 22:30:52.620
003206	2009-09-09 20:53:36.417
003206	2009-09-09 20:51:49.043
003206	2009-09-10 00:06:44.167
003206	2009-09-10 00:04:12.370
003206	2009-09-09 22:41:15.400
003206	2009-09-10 00:12:21.997
003206	2009-09-10 00:09:49.180
003206	2009-09-10 00:08:42.910
003206	2009-09-10 16:29:20.490
003206	2009-09-10 16:26:56.370
003206	2009-09-10 00:18:43.413
003206	2009-09-10 18:02:18.023
003206	2009-09-10 17:59:50.930
003206	2009-09-10 16:31:52.460
003206	2009-09-10 20:02:07.587
003206	2009-09-10 18:11:48.593
003206	2009-09-10 18:05:37.010
003206	2009-09-10 21:31:58.663
003206	2009-09-10 20:06:28.000
003206	2009-09-10 20:04:24.647
003206	2009-09-10 23:28:59.640
003206	2009-09-10 23:26:18.573
003206	2009-09-10 21:34:39.677
003206	2009-09-11 00:25:51.490
003206	2009-09-10 23:34:38.070
003206	2009-09-10 23:32:49.610
003468	2009-09-09 09:19:59.353
003468	2009-09-09 09:01:22.567
003468	2009-09-09 10:07:09.423
003468	2009-09-09 09:54:23.700
003468	2009-09-09 09:42:20.167
003468	2009-09-09 10:55:21.813
003468	2009-09-09 10:30:27.447
003468	2009-09-09 10:16:14.183
003468	2009-09-10 09:50:36.190
003468	2009-09-10 09:20:13.530
003468	2009-09-09 14:00:58.390
003468	2009-09-10 10:17:43.733
003468	2009-09-10 10:07:59.490
003468	2009-09-10 09:59:24.967
003468	2009-09-10 10:44:47.447
003468	2009-09-10 10:36:15.093
003468	2009-09-10 10:27:07.133
003468	2009-09-10 11:35:30.597
003468	2009-09-10 11:04:13.743
003468	2009-09-10 10:54:09.860
003468	2009-09-10 12:01:36.997
003468	2009-09-10 11:53:11.973
003468	2009-09-10 11:44:36.853
003468	2009-09-10 12:27:55.040
003468	2009-09-10 12:24:57.073
003468	2009-09-10 12:10:55.570
003468	2009-09-10 12:43:10.590
003468	2009-09-10 12:34:10.143
003468	2009-09-10 12:31:05.443
003468	2009-09-10 13:11:21.190
003468	2009-09-10 13:02:43.887
003468	2009-09-10 12:51:13.340
003468	2009-09-10 13:39:09.070
003468	2009-09-10 13:30:17.433
003468	2009-09-10 13:20:05.320
003468	2009-09-10 14:07:30.640
003468	2009-09-10 13:56:50.430
003468	2009-09-10 13:48:02.500
003468	2009-09-10 14:41:40.050
003468	2009-09-10 14:32:05.840
003468	2009-09-10 14:22:50.083
003795	2009-09-08 05:23:20.233
003795	2009-09-08 05:31:08.733
003795	2009-09-08 05:31:08.690
003795	2009-09-08 05:23:20.497
003795	2009-09-08 06:00:54.480
003795	2009-09-08 05:52:29.950
003795	2009-09-08 05:44:41.900
003795	2009-09-08 06:31:13.473
003795	2009-09-08 06:17:52.903
003795	2009-09-08 06:09:22.810
003795	2009-09-08 06:56:43.803
003795	2009-09-08 06:48:27.447
003795	2009-09-08 06:39:47.583
003795	2009-09-08 07:34:13.817
003795	2009-09-08 07:25:31.827
003795	2009-09-08 07:17:18.160
003795	2009-09-08 07:58:58.180
003795	2009-09-08 07:50:53.983
003795	2009-09-08 07:42:33.313
003795	2009-09-08 08:25:38.580
003795	2009-09-08 08:16:10.950
003795	2009-09-08 08:07:40.450
003795	2009-09-08 08:40:40.093
003795	2009-09-08 08:40:39.733
003795	2009-09-08 08:33:15.253
003795	2009-09-08 08:48:29.173
003795	2009-09-08 08:48:29.093
003795	2009-09-08 08:47:53.800
003795	2009-09-08 09:08:51.163
003795	2009-09-08 09:04:01.307
003795	2009-09-08 08:56:37.883
003795	2009-09-08 09:31:52.993
003795	2009-09-08 09:22:43.197
003795	2009-09-08 09:18:32.330
003795	2009-09-08 10:11:37.253
003795	2009-09-08 10:01:16.590
003795	2009-09-08 09:51:29.280
003795	2009-09-08 10:41:55.997
003795	2009-09-08 10:30:19.947
003795	2009-09-08 10:20:36.297
003795	2009-09-08 11:49:41.387
003795	2009-09-08 11:36:45.790
003795	2009-09-08 10:54:58.823
003795	2009-09-08 12:54:25.597
003795	2009-09-08 12:45:57.940
003795	2009-09-08 12:12:39.337
003795	2009-09-08 14:02:07.997
003795	2009-09-08 13:39:13.750
003795	2009-09-08 13:29:19.517
003795	2009-09-08 14:32:58.950
003795	2009-09-08 14:16:57.230
003795	2009-09-08 14:08:27.320
003795	2009-09-09 05:31:56.350
003795	2009-09-09 05:22:12.973
003795	2009-09-09 05:15:55.310
003795	2009-09-09 06:32:38.353
003795	2009-09-09 06:18:03.513
003795	2009-09-09 05:59:12.463
003795	2009-09-09 07:59:56.660
003795	2009-09-09 07:42:55.530
003795	2009-09-09 07:22:40.907
003795	2009-09-09 09:09:00.313
003795	2009-09-09 08:46:56.433
003795	2009-09-09 08:20:06.223
003795	2009-09-09 10:10:56.250
003795	2009-09-09 09:56:13.287
003795	2009-09-09 09:28:36.983
003795	2009-09-09 13:36:01.480
003795	2009-09-09 10:42:41.840
003795	2009-09-09 10:22:24.510
003795	2009-09-10 05:24:55.813
003795	2009-09-09 14:35:19.233
003795	2009-09-09 13:53:20.470
003795	2009-09-10 05:45:11.727
003795	2009-09-10 05:36:31.390
003795	2009-09-10 05:31:22.263
003795	2009-09-10 06:44:47.723
003795	2009-09-10 06:35:40.340
003795	2009-09-10 06:05:40.780
003795	2009-09-10 07:58:53.863
003795	2009-09-10 07:36:36.487
003795	2009-09-10 06:58:46.670
003795	2009-09-10 08:36:06.060
003795	2009-09-10 08:27:15.640
003795	2009-09-10 08:11:13.100
003795	2009-09-10 09:24:36.603
003795	2009-09-10 09:13:36.020
003795	2009-09-10 09:03:15.073
003795	2009-09-10 10:12:35.430
003795	2009-09-10 09:44:18.560
003795	2009-09-10 09:33:47.207
003795	2009-09-10 11:46:49.183
003795	2009-09-10 10:52:14.777
003795	2009-09-10 10:31:34.840
003795	2009-09-10 12:27:29.233
003795	2009-09-10 12:20:57.183
003795	2009-09-10 12:06:30.587
003795	2009-09-10 13:32:55.420
003795	2009-09-10 12:41:30.143
003795	2009-09-10 12:31:05.837
003795	2009-09-10 14:18:13.503
003795	2009-09-10 13:53:33.660
003795	2009-09-10 13:45:38.253
004539	2009-09-08 06:50:55.763
004539	2009-09-08 06:17:08.567
004539	2009-09-08 06:15:31.037
004539	2009-09-08 06:54:40.133
004539	2009-09-08 06:52:25.690
004539	2009-09-08 06:51:47.287
004539	2009-09-09 08:28:37.477
004539	2009-09-09 08:26:32.370
004539	2009-09-09 08:19:01.907
004539	2009-09-09 12:24:27.000
004539	2009-09-09 12:22:44.747
004539	2009-09-09 09:57:42.360
004539	2009-09-09 14:21:13.187
004539	2009-09-09 13:32:27.477
004539	2009-09-09 12:30:32.377
004539	2009-09-09 14:36:12.513
004539	2009-09-09 14:29:34.993
004539	2009-09-09 14:23:48.013
004539	2009-09-10 06:05:16.127
004539	2009-09-10 06:03:20.890
004539	2009-09-10 06:01:51.817
004539	2009-09-10 06:50:36.673
004539	2009-09-10 06:13:52.187
004539	2009-09-10 06:11:11.737
004539	2009-09-10 07:32:06.487
004539	2009-09-10 07:30:50.950
004539	2009-09-10 06:52:39.833
004539	2009-09-10 08:20:18.100
004539	2009-09-10 07:58:51.117
004539	2009-09-10 07:57:06.233
004539	2009-09-10 10:56:50.313
004539	2009-09-10 09:47:06.850
004539	2009-09-10 09:45:37.347
004539	2009-09-10 12:43:42.960
004539	2009-09-10 12:42:39.343
004539	2009-09-10 10:58:20.147
004539	2009-09-10 14:02:09.420
004539	2009-09-10 14:00:16.547
004539	2009-09-10 12:45:56.123
004545	2009-09-09 09:32:50.237
004545	2009-09-09 08:49:56.567
004545	2009-09-09 08:28:41.053
004545	2009-09-09 12:28:37.670
004545	2009-09-09 12:19:58.733
004545	2009-09-09 12:18:34.333
004545	2009-09-10 11:41:31.503
004545	2009-09-10 08:33:00.150
004545	2009-09-09 12:29:53.910
004545	2009-09-10 11:44:40.377
004580	2009-09-08 12:04:39.273
004580	2009-09-08 12:58:40.583
004580	2009-09-08 12:56:11.790
004580	2009-09-08 12:06:49.207
004580	2009-09-08 14:49:35.693
004580	2009-09-08 14:05:59.830
004580	2009-09-08 13:33:27.417
004580	2009-09-09 06:19:21.960
004580	2009-09-09 06:04:33.170
004580	2009-09-09 05:51:54.637
004580	2009-09-09 07:23:22.020
004580	2009-09-09 06:51:09.820
004580	2009-09-09 06:36:11.253
004580	2009-09-09 08:16:20.803
004580	2009-09-09 08:02:43.163
004580	2009-09-09 07:41:12.807
004580	2009-09-09 08:54:43.033
004580	2009-09-09 08:50:52.220
004580	2009-09-09 08:46:51.790
004580	2009-09-09 10:39:34.580
004580	2009-09-09 09:36:38.323
004580	2009-09-09 09:13:06.457
004580	2009-09-09 13:51:27.863
004580	2009-09-09 13:20:13.473
004580	2009-09-09 11:52:58.563
004580	2009-09-10 05:56:36.393
004580	2009-09-10 05:53:54.010
004580	2009-09-09 14:30:13.530
004580	2009-09-10 07:43:05.473
004580	2009-09-10 07:39:37.593
004580	2009-09-10 05:59:28.960
004580	2009-09-10 10:25:25.037
004580	2009-09-10 09:20:24.537
004580	2009-09-10 08:58:42.303
004580	2009-09-10 13:25:17.450
004580	2009-09-10 12:02:53.230
004580	2009-09-10 11:35:49.427
004580	2009-09-10 14:44:30.370
004580	2009-09-10 13:33:55.270
004580	2009-09-10 13:30:09.117
004580	2009-09-10 14:52:35.580
004580	2009-09-10 14:51:51.453
004580	2009-09-10 14:49:12.367
005134	2009-09-11 01:11:55.643
005134	2009-09-11 01:04:44.497
005280	2009-09-08 17:55:31.640
005280	2009-09-08 16:35:19.590
005280	2009-09-08 16:33:44.073
005280	2009-09-08 19:32:22.443
005280	2009-09-08 19:30:43.247
005280	2009-09-08 17:57:06.757
005280	2009-09-08 22:32:49.093
005280	2009-09-08 21:01:23.117
005280	2009-09-08 20:59:48.077
005280	2009-09-09 00:14:55.863
005280	2009-09-09 00:13:14.190
005280	2009-09-08 22:34:37.030
005280	2009-09-09 17:02:21.470
005280	2009-09-09 17:00:11.933
005280	2009-09-09 00:54:44.043
005280	2009-09-09 18:57:53.740
005280	2009-09-09 18:55:22.003
005280	2009-09-09 17:09:26.437
005280	2009-09-09 19:20:24.043
005280	2009-09-09 19:18:18.320
005280	2009-09-09 18:59:45.400
005280	2009-09-09 21:05:57.110
005280	2009-09-09 21:04:00.663
005280	2009-09-09 19:26:39.013
005280	2009-09-10 00:53:15.500
005280	2009-09-10 00:06:09.677
005280	2009-09-10 00:04:06.550
005280	2009-09-10 17:04:30.630
005280	2009-09-10 17:02:15.863
005280	2009-09-10 00:53:15.610
005280	2009-09-10 21:21:32.010
005280	2009-09-10 18:42:40.220
005280	2009-09-10 18:40:28.773
005280	2009-09-10 21:29:07.170
005280	2009-09-10 21:26:44.570
005280	2009-09-10 21:22:17.323
005280	2009-09-10 23:34:21.350
005280	2009-09-10 23:33:02.760
005280	2009-09-10 23:29:03.517
005280	2009-09-11 00:54:22.913
005280	2009-09-10 23:40:33.627
005280	2009-09-10 23:36:17.837
005483	2009-09-09 09:26:37.717
005483	2009-09-09 08:18:41.437
005483	2009-09-09 07:18:00.940
005483	2009-09-09 13:01:10.640
005483	2009-09-09 09:40:46.633
005483	2009-09-09 09:29:19.933
005483	2009-09-10 06:10:04.387
005483	2009-09-09 14:02:18.133
005483	2009-09-09 13:15:44.320
005483	2009-09-10 06:18:48.120
005483	2009-09-10 06:17:26.617
005483	2009-09-10 06:15:38.387
005483	2009-09-10 07:44:53.947
005483	2009-09-10 07:43:17.127
005483	2009-09-10 07:41:43.537
005483	2009-09-10 07:50:32.493
005483	2009-09-10 07:48:50.343
005483	2009-09-10 07:46:20.517
005483	2009-09-10 09:32:23.187
005483	2009-09-10 09:31:06.800
005483	2009-09-10 09:29:55.973
005483	2009-09-10 11:47:14.373
005483	2009-09-10 09:35:22.603
005483	2009-09-10 09:34:11.193
005483	2009-09-10 14:09:46.307
005483	2009-09-10 14:04:51.427
005483	2009-09-10 11:51:45.497
005782	2009-09-08 18:46:56.423
005782	2009-09-08 18:45:24.363
005791	2009-09-08 15:32:19.747
005791	2009-09-08 16:07:07.810
005791	2009-09-08 15:55:19.653
005791	2009-09-08 15:43:59.423
005791	2009-09-08 16:47:24.840
005791	2009-09-08 16:37:35.137
005791	2009-09-08 16:21:59.807
005791	2009-09-08 17:21:56.893
005791	2009-09-08 17:06:05.430
005791	2009-09-08 16:56:35.743
005791	2009-09-08 17:50:13.623
005791	2009-09-08 17:40:11.887
005791	2009-09-08 17:31:09.540
005791	2009-09-08 18:18:49.500
005791	2009-09-08 18:09:48.723
005791	2009-09-08 18:00:29.460
005791	2009-09-08 18:54:16.780
005791	2009-09-08 18:39:27.420
005791	2009-09-08 18:27:19.937
005791	2009-09-08 19:56:03.840
005791	2009-09-08 19:45:09.277
005791	2009-09-08 19:35:38.340
005791	2009-09-08 20:48:43.010
005791	2009-09-08 20:16:32.387
005791	2009-09-08 20:05:14.683
006494	2009-09-08 21:25:22.513
006494	2009-09-08 21:55:33.303
006494	2009-09-08 21:44:46.803
006494	2009-09-08 21:33:39.243
006494	2009-09-08 22:35:54.700
006494	2009-09-08 22:24:27.403
006494	2009-09-08 22:10:08.917
006494	2009-09-08 23:23:49.930
006494	2009-09-08 22:58:50.903
006494	2009-09-08 22:47:58.153
006494	2009-09-09 00:05:18.463
006494	2009-09-08 23:48:52.900
006494	2009-09-08 23:37:26.230
006494	2009-09-09 00:50:04.903
006494	2009-09-09 00:35:39.290
006494	2009-09-09 00:18:29.510
006494	2009-09-09 15:58:52.370
006494	2009-09-09 15:50:56.307
006494	2009-09-09 15:42:25.100
006494	2009-09-09 16:51:44.253
006494	2009-09-09 16:20:24.193
006494	2009-09-09 16:08:41.200
006494	2009-09-09 18:04:22.623
006494	2009-09-09 17:26:29.840
006494	2009-09-09 17:13:14.400
006494	2009-09-09 18:41:03.950
006494	2009-09-09 18:26:12.990
006494	2009-09-09 18:15:06.440
006494	2009-09-09 19:40:00.157
006494	2009-09-09 19:32:21.727
006494	2009-09-09 18:51:27.390
006494	2009-09-09 20:15:34.007
006494	2009-09-09 20:01:57.703
006494	2009-09-09 19:47:53.323
006494	2009-09-09 20:38:10.533
006494	2009-09-09 20:38:10.270
006494	2009-09-09 20:27:57.480
006494	2009-09-09 20:48:50.857
006494	2009-09-09 20:45:06.293
006494	2009-09-09 20:45:06.217
006494	2009-09-09 21:27:42.323
006494	2009-09-09 21:09:11.917
006494	2009-09-09 20:56:53.253
006494	2009-09-09 21:59:10.973
006494	2009-09-09 21:51:28.277
006494	2009-09-09 21:44:04.920
006494	2009-09-09 22:44:09.140
006494	2009-09-09 22:21:38.203
006494	2009-09-09 22:09:59.353
006494	2009-09-09 23:21:28.377
006494	2009-09-09 23:17:44.177
006494	2009-09-09 22:58:56.177
006494	2009-09-09 23:51:15.527
006494	2009-09-09 23:39:26.333
006494	2009-09-09 23:30:26.120
006494	2009-09-10 00:29:13.577
006494	2009-09-10 00:12:51.040
006494	2009-09-10 00:02:25.317
006494	2009-09-10 15:59:07.977
006494	2009-09-10 15:52:46.827
006494	2009-09-10 00:42:43.597
006494	2009-09-10 16:52:58.920
006494	2009-09-10 16:43:21.710
006494	2009-09-10 16:27:32.767
006494	2009-09-10 17:24:59.817
006494	2009-09-10 17:14:19.933
006494	2009-09-10 17:03:05.977
006494	2009-09-10 17:54:35.140
006494	2009-09-10 17:46:06.360
006494	2009-09-10 17:35:06.533
006494	2009-09-10 18:27:10.440
006494	2009-09-10 18:17:30.130
006494	2009-09-10 18:04:39.823
006494	2009-09-10 19:33:07.993
006494	2009-09-10 18:57:46.833
006494	2009-09-10 18:43:44.120
006494	2009-09-10 20:05:15.543
006494	2009-09-10 19:54:37.000
006494	2009-09-10 19:42:33.787
006494	2009-09-10 20:36:03.610
006494	2009-09-10 20:27:20.207
006494	2009-09-10 20:16:58.710
006494	2009-09-10 21:02:29.223
006494	2009-09-10 20:55:23.447
006494	2009-09-10 20:45:32.857
006494	2009-09-10 21:31:54.947
006494	2009-09-10 21:23:03.263
006494	2009-09-10 21:10:17.487
006494	2009-09-10 22:04:59.557
006494	2009-09-10 21:54:39.237
006494	2009-09-10 21:42:35.270
006494	2009-09-10 22:44:23.647
006494	2009-09-10 22:30:49.940
006494	2009-09-10 22:15:01.343
006494	2009-09-10 23:23:13.990
006494	2009-09-10 23:06:10.777
006494	2009-09-10 22:55:13.320
006494	2009-09-11 00:00:14.493
006494	2009-09-10 23:45:13.277
006494	2009-09-10 23:33:32.603
006494	2009-09-11 00:44:57.490
006494	2009-09-11 00:33:53.613
006494	2009-09-11 00:11:29.030
006650	2009-09-08 07:20:18.683
006650	2009-09-08 07:30:13.840
006650	2009-09-08 07:25:49.850
006650	2009-09-08 07:23:27.417
006650	2009-09-08 08:11:51.223
006650	2009-09-08 07:32:16.127
006650	2009-09-08 12:35:14.250
006650	2009-09-08 10:20:06.630
006650	2009-09-08 10:18:04.957
006650	2009-09-08 14:21:34.630
006650	2009-09-08 12:45:24.180
006650	2009-09-08 12:37:58.513
006650	2009-09-09 06:50:31.530
006650	2009-09-09 06:48:53.657
006650	2009-09-08 14:23:23.463
006650	2009-09-09 09:06:05.590
006650	2009-09-09 09:08:37.763
006650	2009-09-09 09:06:43.217
006650	2009-09-09 11:52:38.303
006650	2009-09-09 12:45:42.870
006650	2009-09-09 12:01:34.180
006650	2009-09-09 11:54:35.047
006650	2009-09-09 13:47:08.570
006650	2009-09-09 13:45:18.570
006650	2009-09-10 06:13:57.230
006650	2009-09-10 06:12:29.180
006650	2009-09-10 06:10:31.777
006650	2009-09-10 08:35:49.253
006650	2009-09-10 08:50:59.050
006650	2009-09-10 10:19:18.043
006650	2009-09-10 10:17:20.040
006650	2009-09-10 12:07:53.257
006650	2009-09-10 12:06:22.193
006650	2009-09-10 10:21:52.213
006650	2009-09-10 14:14:33.820
006650	2009-09-10 14:20:05.920
006650	2009-09-10 14:16:28.410
006990	2009-09-09 17:18:10.660
006990	2009-09-09 17:15:38.410
006990	2009-09-09 16:06:56.890
006990	2009-09-09 18:29:18.193
006990	2009-09-09 20:27:34.613
006990	2009-09-09 18:31:43.573
006990	2009-09-09 20:40:22.937
006990	2009-09-09 20:35:28.000
006990	2009-09-09 23:23:03.153
006990	2009-09-09 23:20:34.833
006990	2009-09-10 00:26:50.970
006990	2009-09-10 16:46:02.733
006990	2009-09-10 16:43:36.757
006990	2009-09-10 18:02:14.873
006990	2009-09-10 17:59:47.367
006990	2009-09-10 18:38:31.180
006990	2009-09-10 20:39:47.447
006990	2009-09-10 18:39:51.913
006990	2009-09-10 20:41:25.247
006990	2009-09-10 22:16:24.507
006990	2009-09-10 22:15:11.943
006990	2009-09-10 22:12:35.583
006990	2009-09-11 00:01:29.880
006990	2009-09-11 00:13:08.310
006990	2009-09-11 00:04:24.883
007705	2009-09-08 09:21:23.860
007705	2009-09-09 08:34:09.033
007705	2009-09-09 08:26:31.550
007705	2009-09-09 10:06:44.353
007705	2009-09-09 10:15:23.253
007705	2009-09-09 10:11:00.607
007705	2009-09-09 13:23:31.100
007705	2009-09-10 07:22:58.550
007705	2009-09-10 07:20:54.000
007705	2009-09-10 10:03:17.397
007705	2009-09-10 10:01:36.087
007705	2009-09-10 10:05:40.093
007705	2009-09-10 12:47:37.643
007705	2009-09-10 12:41:07.547
007705	2009-09-10 14:20:46.280
007705	2009-09-10 14:24:23.000
007705	2009-09-10 14:23:24.100
007705	2009-09-10 14:22:04.327
007715	2009-09-08 11:20:37.140
007715	2009-09-08 11:11:40.600
007715	2009-09-08 11:46:22.150
007715	2009-09-08 11:38:00.103
007715	2009-09-08 11:29:23.820
007715	2009-09-08 12:11:19.673
007715	2009-09-08 12:02:53.550
007715	2009-09-08 11:54:30.457
007715	2009-09-08 12:35:29.620
007715	2009-09-08 12:27:08.387
007715	2009-09-08 12:19:42.797
007715	2009-09-08 13:00:33.927
007715	2009-09-08 12:52:50.673
007715	2009-09-08 12:44:27.537
007715	2009-09-08 13:18:07.987
007715	2009-09-08 13:09:42.627
007715	2009-09-08 13:08:27.500
007715	2009-09-08 13:37:26.540
007715	2009-09-08 13:29:11.383
007715	2009-09-08 13:24:18.370
007715	2009-09-08 13:52:17.353
007715	2009-09-08 13:47:18.157
007715	2009-09-08 13:38:42.967
007715	2009-09-08 14:13:23.377
007715	2009-09-08 14:09:51.377
007715	2009-09-08 14:02:17.667
007715	2009-09-08 14:32:44.613
007715	2009-09-08 14:24:10.860
007715	2009-09-08 14:20:06.080
007715	2009-09-09 07:28:57.173
007715	2009-09-08 14:49:32.390
007715	2009-09-08 14:40:13.297
007715	2009-09-09 07:32:18.713
007715	2009-09-09 07:32:18.663
007715	2009-09-09 07:28:57.523
007715	2009-09-09 07:45:21.320
007715	2009-09-09 07:40:42.477
007715	2009-09-09 07:35:28.910
007715	2009-09-09 07:57:52.510
007715	2009-09-09 07:53:59.450
007715	2009-09-09 07:49:48.853
007715	2009-09-09 08:12:44.180
007715	2009-09-09 08:07:48.687
007715	2009-09-09 08:03:40.610
007715	2009-09-09 08:26:12.403
007715	2009-09-09 08:21:52.200
007715	2009-09-09 08:16:55.790
007715	2009-09-09 08:49:50.620
007715	2009-09-09 08:36:29.540
007715	2009-09-09 08:30:57.377
007715	2009-09-09 09:19:28.807
007715	2009-09-09 09:10:50.853
007715	2009-09-09 09:07:35.053
007715	2009-09-09 09:59:03.027
007715	2009-09-09 09:29:21.620
007715	2009-09-09 09:19:56.460
007715	2009-09-09 10:13:15.147
007715	2009-09-09 10:11:57.557
007715	2009-09-09 10:01:39.720
007715	2009-09-09 10:51:41.793
007715	2009-09-09 10:33:16.003
007715	2009-09-09 10:24:18.510
007715	2009-09-09 11:05:29.897
007715	2009-09-09 11:05:02.200
007715	2009-09-09 10:54:12.770
007715	2009-09-09 11:38:43.543
007715	2009-09-09 11:23:51.490
007715	2009-09-09 11:13:57.807
007715	2009-09-09 12:10:38.953
007715	2009-09-09 12:02:38.747
007715	2009-09-09 11:58:56.387
007715	2009-09-09 12:39:25.930
007715	2009-09-09 12:33:18.583
007715	2009-09-09 12:25:46.720
007715	2009-09-09 12:57:56.823
007715	2009-09-09 12:53:52.807
007715	2009-09-09 12:46:40.163
007715	2009-09-09 13:25:02.773
007715	2009-09-09 13:16:50.427
007715	2009-09-09 13:01:52.247
007715	2009-09-09 13:49:04.670
007715	2009-09-09 13:37:30.900
007715	2009-09-09 13:25:32.197
007715	2009-09-09 14:05:18.870
007715	2009-09-09 14:00:54.797
007715	2009-09-09 13:56:08.640
007715	2009-09-09 14:26:45.953
007715	2009-09-09 14:16:10.777
007715	2009-09-09 14:14:07.890
007715	2009-09-10 07:27:25.853
007715	2009-09-10 07:23:56.123
007715	2009-09-09 14:32:02.423
007715	2009-09-10 07:37:36.097
007715	2009-09-10 07:30:14.053
007715	2009-09-10 07:27:26.187
007715	2009-09-10 07:49:46.873
007715	2009-09-10 07:45:44.490
007715	2009-09-10 07:38:09.560
007715	2009-09-10 08:00:47.150
007715	2009-09-10 07:58:53.787
007715	2009-09-10 07:52:42.683
007715	2009-09-10 08:16:37.123
007715	2009-09-10 08:09:51.377
007715	2009-09-10 08:07:50.090
007715	2009-09-10 08:27:58.820
007715	2009-09-10 08:25:41.740
007715	2009-09-10 08:18:35.070
007715	2009-09-10 08:43:31.813
007715	2009-09-10 08:36:53.720
007715	2009-09-10 08:34:39.933
007715	2009-09-10 08:55:49.593
007715	2009-09-10 08:52:10.943
007715	2009-09-10 08:46:49.487
007715	2009-09-10 09:10:14.890
007715	2009-09-10 09:04:57.630
007715	2009-09-10 09:01:21.027
007715	2009-09-10 09:23:33.347
007715	2009-09-10 09:19:43.710
007715	2009-09-10 09:13:53.370
007715	2009-09-10 09:40:05.717
007715	2009-09-10 09:31:33.767
007715	2009-09-10 09:28:35.277
007715	2009-09-10 09:51:10.087
007715	2009-09-10 09:49:26.753
007715	2009-09-10 09:42:45.370
007715	2009-09-10 10:14:09.833
007715	2009-09-10 10:05:07.600
007715	2009-09-10 10:04:49.563
007715	2009-09-10 10:24:56.670
007715	2009-09-10 10:23:35.790
007715	2009-09-10 10:15:39.780
007715	2009-09-10 10:42:22.207
007715	2009-09-10 10:34:49.187
007715	2009-09-10 10:33:22.500
007715	2009-09-10 10:52:12.820
007715	2009-09-10 10:51:10.383
007715	2009-09-10 10:44:02.207
007715	2009-09-10 11:08:39.753
007715	2009-09-10 11:01:15.937
007715	2009-09-10 10:59:54.263
007715	2009-09-10 11:18:36.927
007715	2009-09-10 11:17:21.603
007715	2009-09-10 11:10:10.260
007715	2009-09-10 11:58:55.347
007715	2009-09-10 11:27:30.867
007715	2009-09-10 11:26:04.453
007715	2009-09-10 12:12:13.790
007715	2009-09-10 12:10:30.247
007715	2009-09-10 12:00:41.040
007715	2009-09-10 12:28:34.807
007715	2009-09-10 12:21:17.203
007715	2009-09-10 12:19:08.350
007715	2009-09-10 12:39:53.057
007715	2009-09-10 12:37:32.047
007715	2009-09-10 12:30:42.473
007715	2009-09-10 12:59:50.347
007715	2009-09-10 12:48:40.467
007715	2009-09-10 12:46:42.393
007715	2009-09-10 13:13:33.550
007715	2009-09-10 13:09:42.367
007715	2009-09-10 13:04:26.103
007715	2009-09-10 13:28:52.000
007715	2009-09-10 13:23:04.653
007715	2009-09-10 13:18:56.917
007715	2009-09-10 13:43:36.433
007715	2009-09-10 13:38:18.753
007715	2009-09-10 13:33:22.880
007715	2009-09-10 14:04:50.787
007715	2009-09-10 13:57:42.060
007715	2009-09-10 13:50:13.990
007715	2009-09-10 14:36:39.040
007715	2009-09-10 14:36:00.547
007715	2009-09-10 14:24:59.563
007719	2009-09-08 12:36:35.600
007719	2009-09-08 12:34:44.573
007719	2009-09-09 06:14:20.313
007719	2009-09-08 14:06:05.010
007719	2009-09-08 14:03:10.057
007719	2009-09-09 09:39:28.880
007719	2009-09-09 09:29:18.940
007719	2009-09-09 07:17:42.067
007719	2009-09-09 12:31:31.120
007719	2009-09-09 12:28:39.660
007719	2009-09-09 12:25:46.307
007719	2009-09-10 06:02:55.257
007719	2009-09-09 13:42:39.630
007719	2009-09-09 12:35:51.047
007719	2009-09-10 06:35:56.407
007719	2009-09-10 06:34:46.373
007719	2009-09-10 06:04:45.987
007719	2009-09-10 08:00:23.217
007719	2009-09-10 07:58:09.677
007719	2009-09-10 07:57:00.427
007719	2009-09-10 08:59:00.140
007719	2009-09-10 08:57:49.360
007719	2009-09-10 08:01:33.660
007719	2009-09-10 12:48:54.097
007719	2009-09-10 10:42:55.477
007719	2009-09-10 10:39:23.610
007719	2009-09-10 14:07:15.997
007719	2009-09-10 14:03:32.917
007719	2009-09-10 12:51:20.357
007838	2009-09-08 12:20:53.223
007838	2009-09-08 12:20:52.873
007838	2009-09-08 12:47:32.477
007838	2009-09-08 12:36:36.810
007838	2009-09-08 12:29:00.957
007838	2009-09-08 12:55:42.760
008037	2009-09-08 10:31:41.210
008037	2009-09-08 10:29:17.797
008037	2009-09-08 10:24:27.620
008037	2009-09-08 10:48:47.017
008037	2009-09-08 10:40:49.350
008037	2009-09-08 10:37:14.410
008037	2009-09-08 12:46:17.993
008037	2009-09-08 12:43:48.120
008037	2009-09-08 10:50:25.520
008037	2009-09-08 14:04:22.490
008037	2009-09-08 13:09:54.210
008037	2009-09-08 13:08:23.620
008037	2009-09-08 14:30:02.633
008037	2009-09-08 14:07:22.007
008037	2009-09-08 14:05:44.827
008037	2009-09-09 10:56:19.903
008037	2009-09-09 07:14:44.187
008037	2009-09-09 06:11:22.150
008037	2009-09-10 06:02:31.093
008037	2009-09-09 13:06:37.690
008037	2009-09-09 10:58:55.133
008037	2009-09-10 06:07:32.577
008037	2009-09-10 06:05:34.527
008037	2009-09-10 06:03:46.967
008037	2009-09-10 08:00:54.770
008037	2009-09-10 07:58:13.180
008037	2009-09-10 07:55:53.807
008037	2009-09-10 08:08:04.593
008037	2009-09-10 08:05:19.717
008037	2009-09-10 08:03:23.577
008037	2009-09-10 09:23:22.650
008037	2009-09-10 09:21:24.507
008037	2009-09-10 09:19:47.373
008037	2009-09-10 11:55:29.603
008037	2009-09-10 09:27:37.460
008037	2009-09-10 09:25:12.280
008037	2009-09-10 12:00:24.570
008037	2009-09-10 11:59:01.217
008037	2009-09-10 11:57:32.323
008037	2009-09-10 14:14:51.810
008037	2009-09-10 14:13:25.740
008037	2009-09-10 14:11:37.620
008173	2009-09-09 04:44:06.250
008173	2009-09-09 04:27:15.717
008173	2009-09-09 04:17:36.203
008173	2009-09-09 05:25:37.243
008173	2009-09-09 05:11:25.577
008173	2009-09-09 04:55:57.643
008173	2009-09-09 06:23:27.060
008173	2009-09-09 05:56:45.340
008173	2009-09-09 05:40:35.413
008173	2009-09-10 02:24:20.820
008173	2009-09-09 06:46:57.910
008173	2009-09-09 06:34:01.683
008173	2009-09-10 02:42:26.760
008173	2009-09-10 02:32:35.510
008173	2009-09-10 02:32:35.240
008173	2009-09-10 03:48:21.937
008173	2009-09-10 03:35:25.840
008173	2009-09-10 02:57:49.310
008173	2009-09-10 04:30:16.440
008173	2009-09-10 04:15:25.757
008173	2009-09-10 04:05:06.390
008173	2009-09-10 05:01:44.383
008173	2009-09-10 04:48:38.523
008173	2009-09-10 04:40:12.597
008173	2009-09-10 05:30:23.073
008173	2009-09-10 05:20:47.057
008173	2009-09-10 05:11:08.163
008173	2009-09-10 05:59:33.743
008173	2009-09-10 05:49:54.540
008173	2009-09-10 05:40:07.223
008173	2009-09-10 06:31:31.660
008173	2009-09-10 06:19:50.520
008173	2009-09-10 06:10:34.793
008173	2009-09-11 01:40:24.383
008173	2009-09-11 01:40:24.133
008173	2009-09-10 06:43:13.220
008173	2009-09-11 02:10:14.260
008173	2009-09-11 01:59:19.200
008173	2009-09-11 01:49:55.573
008173	2009-09-11 03:35:53.140
008173	2009-09-11 02:31:19.030
008173	2009-09-11 02:20:09.227
008173	2009-09-11 04:58:05.973
008173	2009-09-11 04:46:22.063
008173	2009-09-11 04:36:00.350
008173	2009-09-11 05:27:56.540
008173	2009-09-11 05:17:52.520
008173	2009-09-11 05:08:41.940
008337	2009-09-09 16:34:36.077
008337	2009-09-09 16:33:55.047
008337	2009-09-09 16:41:47.777
008337	2009-09-09 16:37:57.817
008337	2009-09-09 16:35:39.017
008337	2009-09-09 18:03:34.637
008337	2009-09-09 18:01:47.790
008337	2009-09-09 18:00:17.150
008337	2009-09-09 20:12:35.690
008337	2009-09-09 20:10:23.310
008337	2009-09-09 20:10:03.373
008337	2009-09-09 20:14:44.787
008337	2009-09-09 20:14:44.237
008337	2009-09-09 20:12:36.680
008337	2009-09-09 23:22:20.663
008337	2009-09-09 23:20:14.847
008337	2009-09-09 23:17:52.367
008337	2009-09-10 00:38:56.753
008337	2009-09-10 00:36:24.927
008337	2009-09-09 23:24:46.620
008337	2009-09-10 16:39:55.987
008337	2009-09-10 16:33:58.430
008337	2009-09-10 16:31:59.937
008337	2009-09-10 16:49:47.890
008337	2009-09-10 16:45:09.020
008337	2009-09-10 16:43:08.543
008337	2009-09-10 17:00:59.867
008337	2009-09-10 16:59:55.557
008337	2009-09-10 16:56:02.733
008337	2009-09-10 18:28:17.000
008337	2009-09-10 17:06:59.473
008337	2009-09-10 17:05:25.573
008337	2009-09-10 18:31:20.487
008337	2009-09-10 18:30:34.463
008337	2009-09-10 18:28:49.307
008337	2009-09-10 18:37:27.277
008337	2009-09-10 18:34:32.363
008337	2009-09-10 18:33:20.193
008337	2009-09-10 20:30:51.633
008337	2009-09-10 20:28:01.520
008337	2009-09-10 18:38:34.050
008337	2009-09-10 22:54:00.133
008337	2009-09-10 22:45:46.277
008337	2009-09-10 20:33:21.513
008503	2009-09-09 09:45:08.170
008503	2009-09-09 06:35:54.340
008503	2009-09-09 06:34:23.750
008503	2009-09-09 12:30:54.163
008503	2009-09-09 09:54:56.753
008503	2009-09-09 09:52:59.700
008503	2009-09-09 14:19:51.540
008503	2009-09-09 14:17:35.460
008503	2009-09-09 12:32:30.950
008503	2009-09-10 06:57:48.723
008503	2009-09-10 06:56:45.817
008503	2009-09-09 14:25:18.540
008503	2009-09-10 08:56:10.960
008503	2009-09-10 08:54:45.203
008503	2009-09-10 07:13:55.020
008503	2009-09-10 09:01:33.573
008503	2009-09-10 08:58:25.680
008503	2009-09-10 08:57:16.600
008503	2009-09-10 10:27:27.120
008503	2009-09-10 10:26:14.677
008503	2009-09-10 10:24:58.707
008503	2009-09-10 12:09:52.853
008503	2009-09-10 10:29:44.757
008503	2009-09-10 10:28:37.870
008503	2009-09-10 14:07:50.267
008503	2009-09-10 14:06:12.133
008503	2009-09-10 12:11:13.540
008504	2009-09-10 16:40:31.260
008504	2009-09-10 22:02:19.427
008504	2009-09-10 21:58:53.013
008504	2009-09-10 16:42:05.220
009210	2009-09-10 19:23:08.277
009475	2009-09-08 21:16:16.543
009475	2009-09-08 21:29:50.727
009475	2009-09-08 21:27:14.480
009475	2009-09-08 21:20:31.223
009475	2009-09-08 21:48:53.097
009475	2009-09-08 21:42:21.517
009475	2009-09-08 21:37:38.597
009475	2009-09-08 22:09:31.107
009475	2009-09-08 21:59:12.867
009475	2009-09-08 21:53:06.693
009475	2009-09-08 23:03:39.667
009475	2009-09-08 22:46:14.153
009475	2009-09-08 22:21:58.390
009475	2009-09-09 00:06:13.987
009475	2009-09-08 23:51:58.763
009475	2009-09-08 23:36:52.343
009475	2009-09-09 00:53:10.013
009475	2009-09-09 00:38:31.760
009475	2009-09-09 00:24:45.937
009475	2009-09-09 15:59:27.773
009475	2009-09-09 15:48:02.967
009475	2009-09-09 15:47:35.523
009475	2009-09-09 16:36:47.390
009475	2009-09-09 16:17:15.167
009475	2009-09-09 16:01:16.527
009475	2009-09-09 16:52:35.843
009475	2009-09-09 16:47:30.480
009475	2009-09-09 16:37:50.690
009475	2009-09-09 17:26:14.660
009475	2009-09-09 17:10:12.483
009475	2009-09-09 16:59:25.660
009475	2009-09-09 17:43:24.000
009475	2009-09-09 17:35:55.087
009475	2009-09-09 17:30:59.943
009475	2009-09-09 18:25:36.013
009475	2009-09-09 18:10:49.520
009475	2009-09-09 17:45:27.737
009475	2009-09-09 18:53:53.723
009475	2009-09-09 18:47:33.517
009475	2009-09-09 18:34:31.997
009475	2009-09-09 19:44:33.200
009475	2009-09-09 19:42:40.690
009475	2009-09-09 19:36:04.303
009475	2009-09-09 20:14:04.660
009475	2009-09-09 20:09:17.067
009475	2009-09-09 19:56:07.570
009475	2009-09-09 20:35:29.950
009475	2009-09-09 20:28:54.550
009475	2009-09-09 20:19:16.693
009475	2009-09-09 20:59:31.713
009475	2009-09-09 20:50:58.620
009475	2009-09-09 20:38:09.260
009475	2009-09-09 21:26:14.520
009475	2009-09-09 21:09:52.520
009475	2009-09-09 21:05:21.690
009475	2009-09-09 21:47:26.670
009475	2009-09-09 21:42:49.350
009475	2009-09-09 21:37:06.350
009475	2009-09-09 22:13:07.197
009475	2009-09-09 22:02:53.070
009475	2009-09-09 22:01:15.267
009475	2009-09-09 22:48:06.387
009475	2009-09-09 22:30:36.753
009475	2009-09-09 22:16:39.437
009475	2009-09-09 23:44:00.623
009475	2009-09-09 23:26:50.470
009475	2009-09-09 23:04:20.463
009475	2009-09-10 00:33:32.190
009475	2009-09-10 00:18:11.570
009475	2009-09-10 00:00:59.140
009475	2009-09-10 16:00:42.707
009475	2009-09-10 15:48:57.353
009475	2009-09-10 15:48:11.557
009475	2009-09-10 16:49:05.980
009475	2009-09-10 16:33:00.273
009475	2009-09-10 16:29:58.010
009475	2009-09-10 17:07:00.060
009475	2009-09-10 17:00:03.667
009475	2009-09-10 16:59:08.693
009475	2009-09-10 17:13:13.193
009475	2009-09-10 17:10:58.990
009475	2009-09-10 17:10:41.760
009475	2009-09-10 17:49:45.697
009475	2009-09-10 17:39:01.530
009475	2009-09-10 17:28:37.210
009475	2009-09-10 18:02:53.700
009475	2009-09-10 17:59:51.987
009475	2009-09-10 17:56:26.050
009475	2009-09-10 18:20:01.177
009475	2009-09-10 18:11:42.910
009475	2009-09-10 18:08:54.197
009475	2009-09-10 18:51:48.880
009475	2009-09-10 18:42:27.290
009475	2009-09-10 18:31:02.177
009475	2009-09-10 19:58:15.160
009475	2009-09-10 19:47:04.987
009475	2009-09-10 19:02:43.820
009475	2009-09-10 20:29:26.783
009475	2009-09-10 20:17:54.227
009475	2009-09-10 20:08:38.667
009475	2009-09-10 21:15:33.670
009475	2009-09-10 20:51:16.057
009475	2009-09-10 20:40:24.450
009475	2009-09-10 21:49:07.197
009475	2009-09-10 21:37:37.563
009475	2009-09-10 21:26:38.227
009475	2009-09-10 22:21:06.357
009475	2009-09-10 22:11:31.670
009475	2009-09-10 22:00:12.073
009475	2009-09-10 22:38:32.390
009475	2009-09-10 22:28:32.597
009475	2009-09-10 22:26:24.433
009475	2009-09-10 23:03:25.277
009475	2009-09-10 22:55:59.473
009475	2009-09-10 22:51:41.543
009475	2009-09-10 23:31:06.620
009475	2009-09-10 23:19:22.383
009475	2009-09-10 23:10:59.410
009475	2009-09-10 23:50:12.143
009475	2009-09-10 23:41:24.160
009475	2009-09-10 23:38:29.377
009475	2009-09-11 00:21:40.730
009475	2009-09-11 00:04:17.730
009475	2009-09-10 23:51:41.163
009475	2009-09-11 00:55:00.943
009475	2009-09-11 00:39:55.727
009475	2009-09-11 00:31:09.020
009476	2009-09-09 04:17:13.960
009476	2009-09-09 04:35:07.607
009476	2009-09-09 04:25:14.090
009476	2009-09-09 04:17:14.257
009476	2009-09-09 05:29:12.457
009476	2009-09-09 05:09:27.987
009476	2009-09-09 04:47:27.740
009476	2009-09-10 01:48:48.920
009476	2009-09-10 01:48:48.640
009476	2009-09-09 05:48:40.867
009476	2009-09-10 02:16:21.633
009476	2009-09-10 02:06:50.047
009476	2009-09-10 01:58:13.540
009476	2009-09-10 02:52:28.653
009476	2009-09-10 02:43:18.473
009476	2009-09-10 02:34:16.353
009476	2009-09-10 03:56:21.243
009476	2009-09-10 03:46:14.817
009476	2009-09-10 03:34:19.920
009476	2009-09-10 05:40:15.947
009476	2009-09-10 05:32:15.530
009476	2009-09-10 04:07:29.910
009476	2009-09-10 06:10:17.940
009476	2009-09-10 05:59:29.167
009476	2009-09-10 05:50:16.533
009476	2009-09-10 06:37:58.277
009476	2009-09-10 06:28:31.487
009476	2009-09-10 06:19:43.620
009476	2009-09-11 01:42:52.030
009476	2009-09-11 01:42:51.767
009476	2009-09-10 06:48:37.220
009476	2009-09-11 02:12:59.857
009476	2009-09-11 02:02:43.900
009476	2009-09-11 01:51:24.803
009476	2009-09-11 03:54:45.050
009476	2009-09-11 02:34:37.040
009476	2009-09-11 02:23:55.237
009476	2009-09-11 04:33:53.803
009476	2009-09-11 04:20:22.060
009476	2009-09-11 04:05:25.457
009476	2009-09-11 05:06:32.220
009476	2009-09-11 04:56:54.847
009476	2009-09-11 04:43:29.273
009567	2009-09-08 13:53:10.920
009567	2009-09-09 12:10:48.773
009567	2009-09-09 08:14:42.253
009567	2009-09-09 08:09:59.103
009567	2009-09-09 13:39:03.430
009567	2009-09-09 13:35:35.467
009567	2009-09-09 12:15:27.227
009567	2009-09-10 06:00:37.140
009567	2009-09-10 05:57:08.700
009567	2009-09-09 14:14:53.610
009567	2009-09-10 07:43:09.920
009567	2009-09-10 07:11:35.110
009567	2009-09-10 06:56:58.033
009567	2009-09-10 08:18:04.767
009567	2009-09-10 08:15:33.720
009567	2009-09-10 07:44:18.393
009567	2009-09-10 12:04:17.910
009567	2009-09-10 10:06:13.593
009567	2009-09-10 10:02:49.350
009567	2009-09-10 14:07:09.393
009567	2009-09-10 14:00:25.360
009567	2009-09-10 12:07:01.147
009768	2009-09-08 22:17:34.950
009768	2009-09-08 22:15:46.270
009768	2009-09-08 23:50:25.347
009768	2009-09-08 23:48:46.317
009768	2009-09-08 22:19:53.960
009768	2009-09-09 00:48:45.350
009768	2009-09-09 00:47:15.730
009768	2009-09-08 23:51:35.597
009768	2009-09-09 17:57:23.213
009768	2009-09-09 17:35:33.790
009768	2009-09-09 19:55:58.667
009768	2009-09-09 21:32:37.603
009768	2009-09-09 21:35:09.880
009768	2009-09-10 00:16:38.500
009768	2009-09-10 00:12:59.577
009768	2009-09-10 16:56:24.090
009768	2009-09-10 16:59:14.600
009768	2009-09-10 18:26:34.510
009768	2009-09-10 18:23:18.293
009768	2009-09-10 18:45:47.483
009768	2009-09-10 21:05:35.763
009768	2009-09-10 21:02:14.010
009768	2009-09-10 23:45:14.040
009768	2009-09-10 23:42:48.357
009768	2009-09-11 00:50:33.400
009837	2009-09-08 06:40:56.100
009837	2009-09-08 10:08:07.340
009837	2009-09-08 06:45:39.667
009837	2009-09-08 06:42:27.650
009837	2009-09-08 10:10:15.567
009837	2009-09-08 13:11:07.190
009837	2009-09-08 13:09:27.717
009837	2009-09-08 14:31:19.500
009837	2009-09-08 13:20:18.960
009837	2009-09-08 13:18:08.267
009837	2009-09-09 07:24:32.047
009837	2009-09-09 07:22:15.877
009837	2009-09-08 14:33:20.493
009837	2009-09-09 11:34:42.330
009837	2009-09-09 09:47:53.740
009837	2009-09-09 09:40:36.010
009837	2009-09-09 14:02:58.820
009837	2009-09-09 12:47:07.753
009837	2009-09-09 11:46:37.063
009837	2009-09-10 06:16:30.610
009837	2009-09-09 14:13:19.170
009837	2009-09-09 14:12:00.773
009837	2009-09-10 06:24:11.323
009837	2009-09-10 06:18:50.210
009837	2009-09-10 06:17:39.057
009837	2009-09-10 08:21:44.407
009837	2009-09-10 06:32:10.893
009837	2009-09-10 06:25:57.487
009837	2009-09-10 08:26:00.260
009837	2009-09-10 08:24:53.783
009837	2009-09-10 08:23:23.363
009837	2009-09-10 11:32:35.127
009837	2009-09-10 09:19:32.137
009837	2009-09-10 09:18:12.440
009837	2009-09-10 12:51:05.810
009837	2009-09-10 11:35:15.737
009837	2009-09-10 11:33:57.807
009837	2009-09-10 14:22:56.297
009837	2009-09-10 14:21:38.697
009837	2009-09-10 12:52:30.823

The DDL to create the table above (after removing unnecessary information like temperature, press speed, etc.) is simply this:

CREATE TABLE [dbo].[Test_Results] (
	[Test_Number] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Serial_Number] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Date_Time] [datetime] NOT NULL ,
	[System_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Op_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Test_Result] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

The query that created the data above was:


select op_id, date_time
from test_results
where date_time between '09/06/2009' and '09/12/2009'
order by op_id

Avoid Sears Home Improvement

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-16 : 10:22:41

Please change you sample data statement to this so we don't have to doctor up 1400 lines of insert statements.
Then also post the expected results based on whatever sample data you end up providing so we can test our solution(s).


select
'insert Test_Results (OP_ID	Date_Time) values (''' + op_id + ''', ''' + convert(varchar(23), date_time, 121) + ''')'
from test_results
where date_time between '09/06/2009' and '09/12/2009'
order by op_id

EDIT:
better make sure my output works as valid insert statements first

Be One with the Optimizer
TG

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-16 : 13:29:31

How exactly would I change my sample data statement to include the INSERT routine?

The only thing I can think of is to create a statement with 1400 INSERT lines in it. Is that what you mean? (I hope not) Keep in mind that I'm a software developer, not a DBA, though.

Sample output that I'm trying to get is this:


OP_ID	Days Worked
000713	4
000814	4
...	n

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-16 : 13:37:40

quote:
Originally posted by jp2code

Actually, we can't do that either.

One day, Employee A may work in the steel mill on Mondays, but sit in the air conditioned Supply Room the rest of the week.

Some employees don't mind the heat; some employees really want the extra pay. Other employees only want to work the minimum required time in those areas.

The company only wants to pay Employee A an extra $1/hour on the days they work in the very hot steel mill.

I don't know how to go about this.

Avoid Sears Home Improvement

what determine the hot work place?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-16 : 14:20:27

quote:
Originally posted by jp2code

How exactly would I change my sample data statement to include the INSERT routine?

I included the statement you need to run in my previous post.

Be One with the Optimizer
TG

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-16 : 14:59:06

quote:
Originally posted by visakh16
what determine the hot work place?

Hi visakh16,

The hot work place (or other hazardous areas) are determined by the System_ID.

I can filter by System_ID, so I did not show that step in the example query.

Again, I am looking for a technique to determine the number of shifts someone worked. For calculation purposes, a shift begins when someone logs an initial record. All records from that moment out to 12 hours are considered part of the same shift. Records after 12 hours are counted with the next shift.

quote:
Originally posted by TG
I included the statement you need to run in my previous post.

Hi TG,

Forgive my ignorance! This is the query you listed:

select
'insert Test_Results (OP_ID	Date_Time) values (''' + op_id + ''', ''' + convert(varchar(23), date_time, 121) + ''')'
from test_results
where date_time between '09/06/2009' and '09/12/2009'
order by op_id

I do not fully understand what this query does. It looks to me like it inserts a lot of records. If I insert 1400 screwy values into my live database, I certainly won't have to worry about this problem anymore. I think I'd get fired.

Is there a way to test that before simply running it?

Avoid Sears Home Improvement

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-16 : 15:06:31

>>Forgive my ignorance!
That is OK! I'm glad to see you don't blindly run stuff without knowing what it does :)

My statement does not actually insert to anything. It just generates insert statement code which you can paste into a post here. I will paste output from the statement into a query window to insert into MY version of Test_Results.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-16 : 16:35:20

quote:
My problem did not show up until management started querying the data for a single date, which goes from Midnight to Midnight. People working 2rd shift start at 4 PM would work until 12:30 AM of the next day, so the query pulls up people that ended the day before's shift and adds the next day's shift as well.

This is why we need expected results but one thing I was curious about is:

So when a person finishes their previous shift at 12:30 AM (after midnight) and starts the next shift at 4:00 PM. How many shifts should be counted for that midnight-to-midnight day? One or Two? Seems like the answer should be two.

Be One with the Optimizer
TG

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-16 : 18:04:08

Ah! After running it, now I see what your script did. Neat-O!

I had our DBA help with that query, and he insisted on running that on a backup version of our database - which I was happy with doing. Only problem is we don't backup very often, so I had to use the month of June. That doesn't screw up the rest of the information I posted, does it?

I can't see a way to attach a text file, and the SQL Server Forums server doesn't like something in the output, so I've got it here:

[url]https://docs.google.com/View?docID=dgqbhbvj_88fp9w2cgj&revision=_latest[/url]

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-16 : 18:06:47

OH! And, payroll wants 1 day when searching between '09/09/2009' and '09/10/2009'.

For a single day, I could hard code the results. For 2 or more days, I don't know how to accurately determine how many days a person worked.

Avoid Sears Home Improvement

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-17 : 12:24:06

Still waiting for either the expected results for your sample data
OR at least an answer to my earlier question:

if a person ends a previous shift on the same calender day that their next shift starts, how many shifts has the person worked on that calender day? I would think the answer is two.

And a related question, if a person works "one shift" from 8:00 PM to 4:00 AM, which calender day would that be associated with? I would think it is both days.

Based on your previous posts I'm guess you disagree with that although I would argue that what I said is correct. Here is one way to get a shift count including optional From and To date parameters by op_id. But a shift on any part of a day would count towards that day. And any two shifts on the same day would count as 2 shifts for that day. If you want to do that differently then you need to better define the rules.


declare @fDate datetime
       ,@tDate datetime

--select @fDate = '2009-06-10'
--       ,@tDate = '2009-06-11'

select d.op_id
       ,count(*) as [ShiftCount]
from   (
       select tr.op_id
              ,datediff(hour, d.minDate, tr.date_time) / 12    as shiftGroup
              ,count(*)                                        as [itemCount]
       from   test_results tr
       inner join (
              select op_id, min(date_time) minDate
              from test_results
              where  date_time >= isNull(@fDate, '1900-01-01')
              and    date_time < isNull(@tDate, '2999-12-31')
              group by op_id
              ) d
              on d.op_id = tr.op_id
       where  tr.date_time >= isNull(@fDate, '1900-01-01')
       and    tr.date_time < isNull(@tDate, '2999-12-31')
       group by tr.op_id
              ,datediff(hour, d.minDate, tr.date_time) / 12 
       ) d
group by d.op_id

Be One with the Optimizer
TG

jp2code
Posting Yak Master

175 Posts

Posted - 2009-09-17 : 16:22:42

quote:
Originally posted by TG

Still waiting for either the expected results for your sample data OR at least an answer to my earlier question:

if a person ends a previous shift on the same calender day that their next shift starts, how many shifts has the person worked on that calender day? I would think the answer is two.

And a related question, if a person works "one shift" from 8:00 PM to 4:00 AM, which calender day would that be associated with? I would think it is both days.

Hi TG,

Sorry about that. I thought I had answered the question. HR needs to know how many shifts a person worked in a given area. So, working from 4 PM to 1 AM on one day needs to be treated as a different day than the "next day's" 4 PM to 1 AM.

Your second question is certainly the more important one, as I see it. I had to talk to HR about this to get an answer, and here is how they answered it: Say there is a holiday coming up, and the plant is going to be closed (Christmas, Memorial Day, etc.). The day before is a work day, and 2nd shift still works from 4 PM to 1 AM; hence, the time *after* the end date should be included in that person's quota.

Your script is nice! Hard for me to understand all of it, but nice!

I'll have to modify this in two ways, I think.

First: If I am about to add a day, I'll have to look at two times - when the period started and how long that period spans. If the period starts less than 12 hours from the Start Date and spans less than 12 hours, it is excluded.

Second: I'll have to look at two other times - When the last period was started and if that period plus 12 hours is greater than the End Date. If it is, 12 hours is added to that End Date.

I'm thinking out loud. I don't have anything formulated.

Avoid Sears Home Improvement

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources