CTE query help

• chillirollercoaster

  Grasshopper

  Points: 10

  More actions

  August 1, 2018 at 7:50 am

  #359196

  Hi am trying to obtain the average by days and I suspect I need to write a CTE

  I CURRENTLY HAVE THE FOLLOWING QUERY

  SELECT SUM (DATEDIFF ……... (ENTERDATE, FINALDATE) AS TOTALDAYS, TRANSACTION_CODE, COUNT (TRANSACTION_NUMBER) AS COUNT, YEAR (ENTERDATE), MONTH (ENTERDATE),CURRENT_STAT,
FROM INNER JOIN
TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID
GROUP BY TRANSACTION_CODE,YEAR,MONTH,CURRENT_STAT
HAVING CURRENT_STAT LIKE 'X'


  THE OUTPUT LOOKS LIKE
  TRANSACTION_CODE | YEAR | MONTH | TOTALDAYS | COUNT

   I WOULD LIKE TO PUT A CALCULATION IN A COLUMN NEXT TO THE COUNT COLUMN THAT DIVIDES THE TOTAlDAYS / COUNT WHICH EQUALS THE AVERAGE.
  CAN ANYONE PROVIDE ASSISTANCE? WOULD THIS BE THROUGH A CTE?
  SORRY FOR THE CAPS

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 1, 2018 at 7:58 am

  #1999873

  chillirollercoaster - Wednesday, August 1, 2018 7:50 AM

  Hi am trying to obtain the average by days and I suspect I need to write a CTE

  I CURRENTLY HAVE THE FOLLOWING QUERY

  SELECT SUM (DATEDIFF ……... (ENTERDATE, FINALDATE) AS TOTALDAYS, TRANSACTION_CODE, COUNT (TRANSACTION_NUMBER) AS COUNT, YEAR (ENTERDATE), MONTH (ENTERDATE),CURRENT_STAT,
FROM INNER JOIN
TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID
GROUP BY TRANSACTION_CODE,YEAR,MONTH,CURRENT_STAT
HAVING CURRENT_STAT LIKE 'X'


  THE OUTPUT LOOKS LIKE
  TRANSACTION_CODE | YEAR | MONTH | TOTALDAYS | COUNT

   I WOULD LIKE TO PUT A CALCULATION IN A COLUMN NEXT TO THE COUNT COLUMN THAT DIVIDES THE TOTAlDAYS / COUNT WHICH EQUALS THE AVERAGE.
  CAN ANYONE PROVIDE ASSISTANCE? WOULD THIS BE THROUGH A CTE?
  SORRY FOR THE CAPS

  Making some assumptions with your query that is full of errors, you could simply add the calculation as a column or use AVG.
  Can you notice the difference when reading your code and mine?
  
SELECT
      TRANSACTION_CODE,
      YEAR (ENTERDATE),
      MONTH (ENTERDATE),
      CURRENT_STAT,
      SUM (DATEDIFF(DD,ENTERDATE, FINALDATE)) AS TOTALDAYS,
      COUNT (TRANSACTION_NUMBER) AS COUNT,
      SUM (DATEDIFF(DD,ENTERDATE, FINALDATE)) / COUNT (TRANSACTION_NUMBER),
      AVG (DATEDIFF(DD,ENTERDATE, FINALDATE))
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID
GROUP BY TRANSACTION_CODE,YEAR,MONTH,CURRENT_STAT
HAVING CURRENT_STAT LIKE 'X'


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  August 1, 2018 at 8:02 am

  #1999875

  chillirollercoaster - Wednesday, August 1, 2018 7:50 AM

  Hi am trying to obtain the average by days and I suspect I need to write a CTE

  I CURRENTLY HAVE THE FOLLOWING QUERY

  SELECT SUM (DATEDIFF ……... (ENTERDATE, FINALDATE) AS TOTALDAYS, TRANSACTION_CODE, COUNT (TRANSACTION_NUMBER) AS COUNT, YEAR (ENTERDATE), MONTH (ENTERDATE),CURRENT_STAT,
FROM INNER JOIN
TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID
GROUP BY TRANSACTION_CODE,YEAR,MONTH,CURRENT_STAT
HAVING CURRENT_STAT LIKE 'X'


  THE OUTPUT LOOKS LIKE
  TRANSACTION_CODE | YEAR | MONTH | TOTALDAYS | COUNT

   I WOULD LIKE TO PUT A CALCULATION IN A COLUMN NEXT TO THE COUNT COLUMN THAT DIVIDES THE TOTAlDAYS / COUNT WHICH EQUALS THE AVERAGE.
  CAN ANYONE PROVIDE ASSISTANCE? WOULD THIS BE THROUGH A CTE?
  SORRY FOR THE CAPS

  SELECT SUM (DATEDIFF(dd,ENTERDATE, FINALDATE)) AS TOTALDAYS,
   TRANSACTION_CODE,
   COUNT (TRANSACTION_NUMBER) AS COUNT,
   SUM(DATEDIFF(dd,ENTERDATE, FINALDATE))/COUNT(TRANSACTION_NUMBER) AS AVERAGEDAYS,
   YEAR (ENTERDATE),
   MONTH (ENTERDATE),CURRENT_STAT,
FROM TABLE1
INNER JOIN TABLE2
   ON TABLE1.TRANSACTION_ID = TABLE2.TRANSACTION_ID
GROUP BY TRANSACTION_CODE, YEAR(ENTERDATE),MONTH(ENTERDATE), CURRENT_STAT
HAVING CURRENT_STAT LIKE 'X'

  PS: Keyboards normally have a button on the left hand side a entitled "Caps Lock". If you press this button it will toggle caps on and off.