Count Help

• Steven Dyson

  SSC Enthusiast

  Points: 162

  More actions

  October 4, 2007 at 9:07 am

  #198765

  Not too sure if this should be in this section or not, but i will let the mods decide.

  I have a database that i am creating to calculate Downtime on one of our machines, I am using Crystal reports to write the report. All was going well until i needed to count haow many short stops we were having.

  Scenario.

  We create Coils of wire and collect the data into an access Database, i then import the data to SQL.

  I have 3 Tables

  Table 1

  tblShiftData = Shift Id, Shift Date Etc

  Table 2

  tblCoilData = Coil ID, Shift ID, Coil Start Date, Coil Number, Time Taken, Etc

  Table 3

  tblDowntime = Downtime ID, Coil ID, Shift ID, Start Time, Duration, Reason Code

  Tables are linked on table1.shift_ID ---> table2.shift_ID, Table2.Coil_ID ----> table3.Coil_ID

  What i need to do is have a field in table 2 (CoilDATA) that is the count of the of the downtime occurences that are less than 15 mins from Table 3 (Duration).

  I have created a view that links the 2 tables together which shows all occurences that occur between between Coil Start time and Coil Finish Time, But i cannot figure out how to Count these per coil.

  Here is what i have written

  SELECT dbo.tblCoilData.Coil_ID, dbo.tblCoilData.[Batch Start], dbo.tblCoilData.[Batch End], dbo.tblDowntime.[DTC Level 3], dbo.tblDowntime.[Start Time],

  dbo.tblDowntime.Duration

  FROM dbo.tblDowntime INNER JOIN

  dbo.tblCoilData ON dbo.tblDowntime.Coil_ID = dbo.tblCoilData.Coil_ID

  WHERE (dbo.tblDowntime.Duration < '900') // 900 is seconds

  This gives me

  611/07/2007 20:51:0011/07/2007 20:51:005811/07/2007 20:51:004.73699998855591

  611/07/2007 20:51:0011/07/2007 20:51:00-111/07/2007 20:51:00140.736999988556

  812/07/2007 05:23:0012/07/2007 05:23:005812/07/2007 05:23:005.23699998855591

  812/07/2007 05:23:0012/07/2007 05:23:00-112/07/2007 05:23:00175.736999988556

  1112/07/2007 12:40:0012/07/2007 12:40:005812/07/2007 12:40:005.00200009346008

  1513/07/2007 11:00:0013/07/2007 13:16:003213/07/2007 11:39:00710.081000089645

  Thank you for looking at this and i hope i can do this as it would make my life so much easier

  Steve

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 4, 2007 at 2:41 pm

  #739337

  SELECT dbo.tblCoilData.Coil_ID, count(*) as shortoutages

  FROM dbo.tblDowntime INNER JOIN

  dbo.tblCoilData ON dbo.tblDowntime.Coil_ID = dbo.tblCoilData.Coil_ID

  WHERE (dbo.tblDowntime.Duration < '900')

  group by dbo.tblCoilData.Coil_ID

  Also - no way should duration be char value - that will cause problems, since '1000'<'900'. duration needs to be stored as a numeric value.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Steven Dyson

  SSC Enthusiast

  Points: 162

  More actions

  October 5, 2007 at 4:51 am

  #739453

  Thanks That work a treat, i have now been able to finish my report, Looks good too.