February 25, 2022 at 10:32 pm
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
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
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
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
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