Summary report

• ashatimjohn

  SSC Veteran

  Points: 247

  More actions

  February 25, 2022 at 10:32 pm

  #3989188

  I need to create a summary report with a bunch of counts off different criteria.

  I have created a rectangle with 4 textboxes for the 4 different counts I need.

  I can't figure out how to get count of cases before 4:30 pm on the same report as total case count.

• Ed B

  Hall of Fame

  Points: 3683

  More actions

  February 25, 2022 at 10:54 pm

  #3989191

  Can you create a column in your data set that is a 1 if the case is before 4:40 and a zero if after, then sum that column as your "before 4:30 count" and count(*) as your total case count? Assuming one row per case.

  SELECT SUM(IIF(CONVERT(TIME(0), create_date) > '16:30:00', 1, 0)) AS 'EarlyCount',
         SUM(  CASE 
                 WHEN CONVERT(TIME(0), create_date) > '16:30:00' 
                 THEN 1 
                 ELSE 0 
               END) AS 'IfYouPreferCaseStatements',
         COUNT(*) AS TotalCaseCount
  FROM   sys.objects;

• ashatimjohn

  SSC Veteran

  Points: 247

  More actions

  February 25, 2022 at 11:01 pm

  #3989204

  Thank you! I'll give it a try and let you know how it worked for me.  Looks like it should work fine.

• ashatimjohn

  SSC Veteran

  Points: 247

  More actions

  February 26, 2022 at 3:44 pm

  #3989591

  The case statement in this SQL statement is not returning the correct results. It is returning all 1's.

  Can someone point me in the direction where I am going wrong with this. It run's so it's not a syntax error.

  When I run this query, it always returns 1. (Even though I have records that have a created_date after 4:30 pm.

  SELECT [accession_no] AS [Accession Number],

  [created_date] AS [Date Case Created]/

  ( CASE

  WHEN CONVERT(TIME(0), created_date) <= '16:30:00'

  THEN 1

  ELSE 0

  END) AS 'Casesbefore430'

  FROM [PowerPath].[dbo].[accession_2] a

  WHERE (created_date >= '02/01/2022' AND created_date <= DATEADD(dd, 1, '2/22/2022'))

• ashatimjohn

  SSC Veteran

  Points: 247

  More actions

  February 26, 2022 at 3:49 pm

  #3989593

  I found my problem.  Thank you!