Post reply

Summary report

  • 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.

  • 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;

  • 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.

  • 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'))

  • February 26, 2022 at 3:49 pm

    #3989593

    I found my problem.  Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply