Loop Count in Select Statement

• renvilo

  Mr or Mrs. 500

  Points: 522

  More actions

  August 31, 2012 at 5:25 am

  #281053

  Hi guys,

  ok sitting with something I have to do. This is what I have:

  Query (PART1):

  Select * ,(DateDiff(ss,ReportedDateTime,FixedDateTime)) as TimeDiffInSeconds

  From #Final

  BreakShift Equipment ReportedDateTime FixedShift FixedDateTime TimeDiffInSeconds

  20120821P2AAA123 8/21/2012 22:00 20120822P18/22/2012 10:00 43200

  20120821P2BBB123 8/21/2012 23:00 20120822P28/22/2012 22:00 82800

  The request I have is:

  Work Time is (These will be the shift like in Breakshift. P1 = Shift 1 and P2 = Shift 2)

  6AM - 6PM

  6PM - 6AM

  Actual Production Time (This is when they want the data to be calculated.)

  8:30am - 3:30pm

  8:30pm - 3:30am

  Query (Part2):

  I have another table where the shifts are stored in. Let's call it TblShifts.

  So let's say equipment AAA123 broke the 21/08/2012 on Shift 2 (P2) then the reportedDateTime is 10PM. The equipment was only fixed on the next shift (NB. Not next day, but next shift) at 10am. That is a 12 hours difference but it has to only show 7 hours that the equipment was done. So it's actually easy but it can get complicated.

  So this is the kind of query I was hoping to achive:

  Select *, DateDiff(ss,ReportedDateTime,FixedDateTime)) -- This get's the info like it is above. Next I want to calculate how many shift changes was between breakshift and fixed shift)

  Loop

  Count BreakShift-FixedShift

  From #Final

  Then the results will be like

  BreakShift Equipment ReportedDateTime FixedShift FixedDateTime TimeDiffInSeconds Shift Changes

  20120821P2AAA123 8/21/2012 22:00 20120822P18/22/2012 10:00 43200 1

  20120821P2BBB123 8/21/2012 23:00 20120822P28/22/2012 22:00 82800 1

  Then it's easy. Then I will say:

  TimeDiffInSeconds - (ShiftChanges*18000) and that will give me the duration 😀

  So in short, how do I count how many shift there was from breaking time to fixing time with my #Final temp table and my Shifts table?

  ps: The user will add in the parameter for the time period that the report will be ran

  Hope this makes sense 🙂

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 31, 2012 at 5:58 am

  #1532327

  renvilo (8/31/2012)

  ---

  ...So in short, how do I count how many shift there was from breaking time to fixing time with my #Final temp table and my Shifts table?...

  Start by setting up CREATE TABLE scripts for the two tables, and INSERT statements or something similar to populate them. Then post the whole script here, so folks can run them - having sample tables to test code against will make it much easier for folks to come up with a solution for you. There's a link in my sig ("here") to an article which describes a simple and quick way to do this.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden