Is there a better way to do this sort of Case with counts, trying to re-organize to run faster

• gfry

  SSC Rookie

  Points: 44

  More actions

  May 4, 2017 at 7:58 am

  #104414

  SELECT 
  count(*),
     (CASE
        WHEN  wfstate =0   THEN 'Submitted' 
        WHEN wfstate =30  THEN 'Duplicates' 
        WHEN wfstate=100 THEN 'Processing' 
     END)"State",
     (CASE 
        WHEN (wfstate=0 
        OR wfstate=30) 
       AND wfstatus=0 THEN 'Queued' 
        WHEN wfstate=100 
       AND wfstatus=0   THEN 'Retry' 
        WHEN wfstatus=1 THEN 'Dispatched' 
        WHEN wfstatus=2 THEN 'Sending' 
     END)"Status"
      
  FROM fms.xfms_ftx_out 
  WHERE complete=0 
  AND appflags=0 
  GROUP BY wfstate,wfstatus

• Thom A

  SSC Guru

  Points: 99126

  More actions

  May 4, 2017 at 8:19 am

  #1941114

  Fixed your formatting for you...
  SELECT count(*),
       (CASE WHEN wfstate =0 THEN 'Submitted'
             WHEN wfstate =30 THEN 'Duplicates'
             WHEN wfstate=100 THEN 'Processing'
        END)"State",
       (CASE WHEN (wfstate=0 OR wfstate=30) AND wfstatus=0 THEN 'Queued'
             WHEN wfstate=100 AND wfstatus=0 THEN 'Retry'
             WHEN wfstatus=1 THEN 'Dispatched'
             WHEN wfstatus=2 THEN 'Sending'
      END)"Status"
FROM fms.xfms_ftx_out
WHERE complete=0
AND appflags=0
GROUP BY wfstate,wfstatus;


  Your Group by statement won't be operating as your expect. This is because you have parts of your case statement that group items together (for example (wfstate=0 OR wfstate=30)).

  The following, for example, would get around that (alternative is have your CASE statements in your GROUP BY, but for readability, I prefer the CTE method):
  WITH CTE AS (
  SELECT complete,
        (CASE WHEN wfstate = 0 THEN 'Submitted'
              WHEN wfstate = 30 THEN 'Duplicates'
              WHEN wfstate = 100 THEN 'Processing'
         END) AS [State],
        (CASE WHEN (wfstate = 0 OR wfstate = 30) AND wfstatus=0 THEN 'Queued'
              WHEN wfstate = 100 AND wfstatus = 0 THEN 'Retry'
              WHEN wfstatus = 1 THEN 'Dispatched'
              WHEN wfstatus = 2 THEN 'Sending'
          END) AS [Status]
  FROM fms.xfms_ftx_out
  WHERE complete = 0
    AND appflags = 0)

  SELECT COUNT(complete) AS recordCount,
       [State],[Status]
FROM CTE
GROUP BY [State],[Status];


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  May 4, 2017 at 8:37 am

  #1941117

  Thom A - Thursday, May 4, 2017 8:19 AM

  Fixed your formatting for you...
  SELECT count(*),
       (CASE WHEN wfstate =0 THEN 'Submitted'
             WHEN wfstate =30 THEN 'Duplicates'
             WHEN wfstate=100 THEN 'Processing'
        END)"State",
       (CASE WHEN (wfstate=0 OR wfstate=30) AND wfstatus=0 THEN 'Queued'
             WHEN wfstate=100 AND wfstatus=0 THEN 'Retry'
             WHEN wfstatus=1 THEN 'Dispatched'
             WHEN wfstatus=2 THEN 'Sending'
      END)"Status"
FROM fms.xfms_ftx_out
WHERE complete=0
AND appflags=0
GROUP BY wfstate,wfstatus;


  Your Group by statement won't be operating as your expect. This is because you have parts of your case statement that group items together (for example (wfstate=0 OR wfstate=30)).

  The following, for example, would get around that (alternative is have your CASE statements in your GROUP BY, but for readability, I prefer the CTE method):
  WITH CTE AS (
  SELECT complete,
        (CASE WHEN wfstate = 0 THEN 'Submitted'
              WHEN wfstate = 30 THEN 'Duplicates'
              WHEN wfstate = 100 THEN 'Processing'
         END) AS [State],
        (CASE WHEN (wfstate = 0 OR wfstate = 30) AND wfstatus=0 THEN 'Queued'
              WHEN wfstate = 100 AND wfstatus = 0 THEN 'Retry'
              WHEN wfstatus = 1 THEN 'Dispatched'
              WHEN wfstatus = 2 THEN 'Sending'
          END) AS [Status]
  FROM fms.xfms_ftx_out
  WHERE complete = 0
    AND appflags = 0)

  SELECT COUNT(complete) AS recordCount,
       [State],[Status]
FROM CTE
GROUP BY [State],[Status];


  I made some tests, and the original query is working fine. I would prefer the original query as it will allow to use indexes if there are any available.
  I'd also use COUNT(*) instead of COUNT(complete). The reason is that I understand the first as count the rows and the second one as count the values from this column. Since the column won't be null, then counting the rows is the way to go.
  Here's my test.
  
CREATE TABLE xfms_ftx_out(
  wfstate int,
  wfstatus int,
  complete int,
  appflags int);
INSERT INTO xfms_ftx_out
SELECT (ABS(CHECKSUM(NEWID()))%3)*30, ABS(CHECKSUM(NEWID()))%3, 0,0
FROM sys.all_columns;

  UPDATE xfms_ftx_out SET wfstate = 100 WHERE wfstate = 60;

  SELECT count(*), --wfstate,wfstatus --Include for clarity.
  CASE
   WHEN wfstate =0 THEN 'Submitted'
   WHEN wfstate =30 THEN 'Duplicates'
   WHEN wfstate=100 THEN 'Processing'
  END AS "State",
  CASE
   WHEN (wfstate=0 OR wfstate=30) AND wfstatus=0 THEN 'Queued'
   WHEN wfstate=100 AND wfstatus=0 THEN 'Retry'
   WHEN wfstatus=1 THEN 'Dispatched'
   WHEN wfstatus=2 THEN 'Sending'
  END AS "Status"
FROM xfms_ftx_out
WHERE complete=0
AND appflags=0
GROUP BY wfstate,wfstatus
ORDER BY [State], [Status] ;

  SELECT
  count(*),
  CASE
   WHEN wfstate =0 THEN 'Submitted'
   WHEN wfstate =30 THEN 'Duplicates'
   WHEN wfstate=100 THEN 'Processing'
  END AS "State",
  CASE
   WHEN (wfstate=0 OR wfstate=30) AND wfstatus=0 THEN 'Queued'
   WHEN wfstate=100 AND wfstatus=0 THEN 'Retry'
   WHEN wfstatus=1 THEN 'Dispatched'
   WHEN wfstatus=2 THEN 'Sending'
  END AS "Status"
FROM xfms_ftx_out
WHERE complete=0
AND appflags=0
GROUP BY CASE
   WHEN wfstate =0 THEN 'Submitted'
   WHEN wfstate =30 THEN 'Duplicates'
   WHEN wfstate=100 THEN 'Processing'
  END,
  CASE
   WHEN (wfstate=0 OR wfstate=30) AND wfstatus=0 THEN 'Queued'
   WHEN wfstate=100 AND wfstatus=0 THEN 'Retry'
   WHEN wfstatus=1 THEN 'Dispatched'
   WHEN wfstatus=2 THEN 'Sending'
  END
ORDER BY [State], [Status];

  GO
DROP TABLE xfms_ftx_out

  SELECT n, n%3 FROM Tally


  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