Need a count by date including dates with a zero count

Question

Post reply

Need a count by date including dates with a zero count

jerry-621596

SSCertifiable

Points: 6096
More actions
August 31, 2010 at 10:10 am

#225491

The powers that be have requested a report that essentially shows each support rep and the number of tickets per day for that rep. The catch is that they want the result set to include a zero count for the date if the rep worked no tickets.
So a report should look like this:
Date RepTickets
2010-08-01AFO0
2010-08-01AGT1
2010-08-01ISC2
2010-08-01ZPC1
2010-08-02AFO0
2010-08-02AGT1
2010-08-02ISC1
2010-08-02ZPC0
and so on...
Here is a script to create sample tables and populate them:
--Create the ticket header table
CREATE TABLE [dbo].[TicketHdr](
[TicketID] [int] NOT NULL,
[TicketDate] [datetime] NULL,
CONSTRAINT [PK_TicketHdr] PRIMARY KEY CLUSTERED
(
[TicketID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Create the ticket details table
CREATE TABLE [dbo].[TicketDtl](
[TicketID] [int] NOT NULL,
[Rep] [varchar](10) NULL,
CONSTRAINT [PK_TicketDtl] PRIMARY KEY CLUSTERED
(
[TicketID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Populate TicketHdr
INSERT INTO TicketHdr(TicketID, TicketDate)
VALUES (1, '2010-8-1'),
(2, '2010-8-1'),
(3, '2010-8-1'),
(4, '2010-8-1'),
(5, '2010-8-2'),
(6, '2010-8-2'),
(7, '2010-8-3'),
(8, '2010-8-3'),
(9, '2010-8-4'),
(10, '2010-8-5'),
(11, '2010-8-6'),
(12, '2010-8-7'),
(13, '2010-8-8'),
(14, '2010-8-8'),
(15, '2010-8-8'),
(16, '2010-8-8'),
(17, '2010-8-8'),
(18, '2010-8-9'),
(19, '2010-8-10'),
(20, '2010-8-11'),
(21, '2010-8-11'),
(22, '2010-8-11'),
(23, '2010-8-11'),
(24, '2010-8-11'),
(25, '2010-8-11'),
(26, '2010-8-11'),
(27, '2010-8-11'),
(28, '2010-8-11'),
(29, '2010-8-11'),
(30, '2010-8-11'),
(31, '2010-8-12'),
(32, '2010-8-12'),
(33, '2010-8-12'),
(34, '2010-8-12'),
(35, '2010-8-15'),
(36, '2010-8-15'),
(37, '2010-8-15'),
(38, '2010-8-15'),
(39, '2010-8-15'),
(40, '2010-8-16'),
(41, '2010-8-17'),
(42, '2010-8-17'),
(43, '2010-8-17'),
(44, '2010-8-18'),
(45, '2010-8-18'),
(46, '2010-8-18'),
(47, '2010-8-18'),
(48, '2010-8-19'),
(49, '2010-8-19')
--Populate TicketDtl
INSERT INTO TicketDtl(TicketID, Rep)
VALUES (1, 'ISC'),
(2, 'ZPC'),
(3, 'AGT'),
(4, 'ISC'),
(5, 'ISC'),
(6, 'AGT'),
(7, 'AFO'),
(8, 'ZPC'),
(9, 'ISC'),
(10, 'ISC'),
(11, 'ISC'),
(12, 'AGT'),
(13, 'ZPC'),
(14, 'AGT'),
(15, 'AFO'),
(16, 'AGT'),
(17, 'ZPC'),
(18, 'ISC'),
(19, 'ZPC'),
(20, 'AFO'),
(21, 'AGT'),
(22, 'ZPC'),
(23, 'AFO'),
(24, 'AGT'),
(25, 'ISC'),
(26, 'ISC'),
(27, 'ISC'),
(28, 'AGT'),
(29, 'AFO'),
(30, 'AGT'),
(31, 'ISC'),
(32, 'ISC'),
(33, 'ISC'),
(34, 'AGT'),
(35, 'AGT'),
(36, 'AFO'),
(37, 'AFO'),
(38, 'AFO'),
(39, 'ZPC'),
(40, 'ZPC'),
(41, 'ISC'),
(42, 'AGT'),
(43, 'ISC'),
(44, 'AGT'),
(45, 'AFO'),
(46, 'ISC'),
(47, 'AGT'),
(48, 'ZPC'),
(49, 'AFO')
I am hoping there is a simple solution to this, but am just not getting it.
Thansk!
Jerry

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

WITH Reps AS (

SELECT DISTINCT Rep

FROM TicketDtl)

SELECT h.TicketDate,

r.Rep,

COUNT(d.Rep)

FROM TicketHdr h

CROSS JOIN Reps r

LEFT OUTER JOIN TicketDtl d ON d.Rep = r.Rep AND d.TicketID=h.TicketID

GROUP BY h.TicketDate,r.Rep

ORDER BY h.TicketDate,r.Rep;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 2

Wow! That did it! I knew it was simple, but just couldn't wrap my head around it.

Still learning about things like CROSS JOIN and CROSS APPLY!

Thanks a ton for the help!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

lets say for example that were no tickets at all on 2010-08-02.....do you want to see all reps with a zero count?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

WayneS SSC Guru Points: 95460 More actions · Answer 4

Mark did a good job.

If you also need dates to show if there were zero tickets for all reps for that date, then you need to utilize a calendar table. This solution creates a virtual one based on the min/max ticket dates in the system.

-- declare and initialize the variables needed

DECLARE @MinDate datetime,

@MaxDate datetime,

@DateDiff int;

SELECT @MinDate = MIN(TicketDate),

@MaxDate = MAX(TicketDate)

FROM dbo.TicketHdr;

SET @DateDiff = DATEDIFF(day, @MinDate, @MaxDate);

-- See Jeff Moden's article

-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.

-- NOTE! A permanent tally table will always be MUCH faster

-- than this inline one. See the above article to create your own!

;WITH

Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),

-- get all dates between the two dates

Dates (TicketDate) AS (SELECT DateAdd(day, N-1, @MinDate) FROM Tally WHERE N <= @DateDiff+1),

-- get the distinct reps in the system

Reps AS (SELECT DISTINCT Rep FROM dbo.TicketDtl)

SELECT d.TicketDate,

r.Rep,

Tickets = COUNT(td.Rep)

FROM Dates d

CROSS JOIN Reps r

LEFT JOIN dbo.TicketHdr th

ON d.TicketDate = th.TicketDate

LEFT JOIN dbo.TicketDtl td

ON th.TicketID = td.TicketID

AND td.Rep = r.Rep

GROUP BY d.TicketDate, r.Rep

ORDER BY d.TicketDate, r.Rep

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

WayneS (8/31/2010)
Mark did a good job.
If you also need dates to show if there were zero tickets for all reps for that date, then you need to utilize a calendar table. This solution creates a virtual one based on the min/max ticket dates in the system.
-- declare and initialize the variables needed
DECLARE @MinDate datetime,
@MaxDate datetime,
@DateDiff int;
SELECT @MinDate = MIN(TicketDate),
@MaxDate = MAX(TicketDate)
FROM dbo.TicketHdr;
SET @DateDiff = DATEDIFF(day, @MinDate, @MaxDate);
-- See Jeff Moden's article
-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.
-- NOTE! A permanent tally table will always be MUCH faster
-- than this inline one. See the above article to create your own!
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
-- get all dates between the two dates
Dates (TicketDate) AS (SELECT DateAdd(day, N-1, @MinDate) FROM Tally WHERE N <= @DateDiff+1),
-- get the distinct reps in the system
Reps AS (SELECT DISTINCT Rep FROM dbo.TicketDtl)
SELECT d.TicketDate,
r.Rep,
Tickets = COUNT(td.Rep)
FROM Dates d
CROSS JOIN Reps r
LEFT JOIN dbo.TicketHdr th
ON d.TicketDate = th.TicketDate
LEFT JOIN dbo.TicketDtl td
ON th.TicketID = td.TicketID
AND td.Rep = r.Rep
GROUP BY d.TicketDate, r.Rep
ORDER BY d.TicketDate, r.Rep

Also with an inline tally table, a million is a bit much for this sort of data. Ten thousand would probably do.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 6

Hadn't thought of that yet. Will probably use your additions to the script just in case.

Thanks!

WayneS SSC Guru Points: 95460 More actions · Answer 7

Mark-101232 (8/31/2010)
Also with an inline tally table, a million is a bit much for this sort of data. Ten thousand would probably do.

Probably right. I just have the million-row virtual tally table stored as a SQL Snippet (from www.ssmstoolspack.com), and just always use it. The code does restrict it's use to just the rows it actually needs.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2