Inserting zeros when COUNT is zero

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inserting zeros when COUNT is zero

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 04:50:58

Hello,

I am trying to do a count of how many enquiries we got on each day of the week ("EnquiryDate"). I want to group them by "TypeOfEnquiry". The problem I am having is on some days we had no enquires.

I want something that has in the left column date (1/1/14, 2/1/14 etc). My other column headings would be like "Marketing Enquiry" and "Sales Enquiry" and the data below would be counts of the "enquirydate"

Any ideas?

J

Jim

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-06-26 : 05:09:35

please post the table structure along with some sample data

Javeed Ahmed

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 05:20:11

SELECT Tbl_Enquiries.DateOfEnquiries,
COUNT(*) AS [Marketing]
FROM Tbl_Enquiries
Where Tbl_Enquiries.TypeOfEnquiry LIKE '%marketing%'
GROUP BY dateOfEnquiry

Which outputs (sample):

Date Marketing
31/03/2014 4
30/03/2014 4
29/03/2014 9
28/03/2014 12
27/03/2014 6
26/03/2014 3
25/03/2014 5
24/03/2014 7
23/03/2014 4
22/03/2014 11
21/03/2014 23
20/03/2014 5
19/03/2014 10
18/03/2014 9

I want to add other columns based on type of enquiry but I cant get it to run. I think because on some days there are no enquiries/enquiry dates.

Thank you,

J

Jim

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 05:21:28

*Group By DateofEnquiries

Jim

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-06-26 : 05:39:13

you can add the other columns too,just add them in the select list and group by list.you would be getting counts for the dates present in the table only

Javeed Ahmed

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 07:51:20

Hi Javeed,

I cant add to the select as I need in different columns different kinds of enquiries, whereas what it does presently is only bring through 1 kind of enquiry.

Thanks

J

Jim

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-06-26 : 07:52:49

remove the where clause from your query

Javeed Ahmed

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 08:31:53

But the wehre clause defines the type of enquiry. I need the different enquiries separate on the same query.

Thanks

J

Jim

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-26 : 12:08:46

You need to use a LEFT OUTER JOIN from the dates, something like this:



DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = <first date to report on>
SET @end_date = <last date to report on>

--force time on @start_date to midnight (00:00:00.000)
SET @start_date = DATEADD(DAY, DATEDIFF(DAY, 0, @start_date), 0)

;WITH
cteDigits AS (
    SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteDigits c1
    CROSS JOIN cteDigits c2
)
SELECT
    ca1.[Date Marketing],
    COUNT(*) AS [Marketing]
FROM cteTally100 t
CROSS APPLY (
    SELECT DATEADD(DAY, tally, @start_date) AS [Date Marketing]
) AS ca1
LEFT OUTER JOIN Tbl_Enquiries e ON
    e.TypeOfEnquiry LIKE '%marketing%' AND
    e.dateOfEnquiry >= [Date Marketing] AND
    e.dateOfEnquiry < DATEADD(DAY, 1, [Date Marketing])
WHERE
    t.tally BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date) - 1
GROUP BY
    ca1.[Date Marketing]

Edit: Code above handles up to 100 days -- if you want to allow a longer range, we need to increase the number of rows in the "tally" table/CTE.

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 16:09:38

Scott, thank you for all this, this is really kind of you.

I'll give it a try in the morning and see if I can get it to work.

Thanks again, I've really struggled with this.

j

Jim

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-26 : 16:12:20

One question Scott, can I use the same code to do different type of enquiry counts? E.g we have an enquiries stored as sales enquiry. How would I get it to link, if it is possible?

Jim

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-26 : 18:00:42

We should be able to do that fairly easily, like so:



DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = <first date to report on>
SET @end_date = <last date to report on>

--force time on @start_date to midnight (00:00:00.000)
SET @start_date = DATEADD(DAY, DATEDIFF(DAY, 0, @start_date), 0)

;WITH
cteDigits AS (
    SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteDigits c1
    CROSS JOIN cteDigits c2
)
SELECT
    ca1.[Date Marketing],
    SUM(CASE WHEN e.TypeOfEnquiry LIKE '%marketing%' THEN 1 ELSE 0 END) AS [Marketing],
    SUM(CASE WHEN e.TypeOfEnquiry LIKE '%sales' THEN 1 ELSE 0 END) AS [Sales]
FROM cteTally100 t
CROSS APPLY (
    SELECT DATEADD(DAY, tally, @start_date) AS [Date Marketing]
) AS ca1
LEFT OUTER JOIN Tbl_Enquiries e ON
    (e.TypeOfEnquiry LIKE '%marketing%' OR
     e.TypeOfEnquiry LIKE '%sales%) AND
    e.dateOfEnquiry >= [Date Marketing] AND
    e.dateOfEnquiry < DATEADD(DAY, 1, [Date Marketing])
WHERE
    t.tally BETWEEN 0 AND DATEDIFF(DAY, @start_date, @end_date) - 1
GROUP BY
    ca1.[Date Marketing]

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-06-27 : 08:08:18

Thank you thank you thank you! I cant believe you wrote all this for me. May good things come your way :)

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources