single query

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  September 22, 2011 at 4:16 am

  #248571

  can we make the following into single query?

  Select count(*) count1 FROM (SELECT DISTINCT tro.regno ,tro.Color

  FROM tle INNER JOIN

  tROD ON tle.Field = tROD.InvoiceId INNER JOIN

  tRO ON tROD.ROID = tRO.ROID

  Where tROD.JobType = @JobType And tRO.RoType = 1

  And tle.dateexecution Between @FromDate and @ToDate

  AND (TRO.ID= @WID)) TEMP

  Select count(*) count2 FROM (SELECT DISTINCT tro.regno , tro.Color

  FROM tle INNER JOIN

  tROD ON tle.Field = tROD.InvoiceId INNER JOIN

  tRO ON tROD.ROID = tRO.ROID

  Where tROD.JobType = @JobType AND tRO.RoType = 0

  And (tle.dateexecution Between @FromDate and @ToDate)

  AND (TRO.ID= @WID)) TEMP

  the only difference is rotype when it is 0 then i need a count separately and when it is 1 i need a count separately.

  Thanks,

  Regards,

  Ami

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 22, 2011 at 4:54 am

  #1385043

  Maybe this?

  Select RoType, count(*) count2 FROM (

  SELECT DISTINCT tro.regno , tro.Color ,tRO.RoType

  FROM tle INNER JOIN

  tROD ON tle.Field = tROD.InvoiceId INNER JOIN

  tRO ON tROD.ROID = tRO.ROID

  Where tROD.JobType = @JobType AND tRO.RoType IN (0 ,1)

  And (tle.dateexecution Between @FromDate and @ToDate)

  AND (TRO.ID= @WID)

  ) TEMP

  GROUP BY RoType

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  September 22, 2011 at 5:05 am

  #1385046

  I need both the counts individually,

  result should be kind of

  value, count1, count2

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 22, 2011 at 5:11 am

  #1385048

  Another guess...

  Select count(CASE WHEN RoType=0 THEN RoType END) count1,

  count(CASE WHEN RoType=1 THEN RoType END) count2 FROM (

  SELECT DISTINCT tro.regno , tro.Color ,tRO.RoType

  FROM tle INNER JOIN

  tROD ON tle.Field = tROD.InvoiceId INNER JOIN

  tRO ON tROD.ROID = tRO.ROID

  Where tROD.JobType = @JobType AND tRO.RoType IN (0 ,1)

  And (tle.dateexecution Between @FromDate and @ToDate)

  AND (TRO.ID= @WID)

  ) TEMP

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537