Group By day count 0

[former_member202170]

Hi All,

Is there any way that we can set values to 0 as count

Example

Group By day

Day            Count

Monday        23

Tuesday        15

Wednesday      12

Thursday        22

Friday              0

Saturday          0

Sunday            0

Even though the data not present for Friday,Saturday,Sunday

we have to show the count as 0 is this possible

Please advise

Thanks

Krishna

[DellSC · Accepted solution]

This can be a challenge if you don't have a table that has the dates in it without the data.  I have done this a couple of ways:

1.  Create a "calendar" table that has a list of dates in it.  I'll frequently create this with the following structure:

Calendar_Date     DateTime

Week_Start          DateTime

Month_Start          DateTime

Year_Start            DateTime

Weekend              bit

Holiday                 bit

You then left join from the Calendar date in this table to the date field in whatever data you need for your report.  You then group by the date from the calendar table on your report instead of the date from your data.

2.  Use a command for your report.  A command is nothing more than a SQL Select statement that will pull ALL of the data you need for your report.  You don't mention what type of database you're connecting to, but in SQL Server and Oracle, you can use a "with" clause to pull this type of information.  Here's the type of thing I usually do with this (SQL Server syntax):

With Dates as(

     SELECT

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()), GetDate()) as day1,

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()) + 1, GetDate()) as day2,

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()) + 2, GetDate()) as day3,

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()) + 3, GetDate()) as day4,

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()) + 4, GetDate()) as day5,

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()) + 5, GetDate()) as day6,

       DATEADD(DAY, 1-DATEPART(WEEKDAY, GetDate()) + 6, GetDate()) as day7)

Select

  d.day1,

  d.day2,

  d.day3,

  d.day4,

  d.day5,

  d.day6,

  d.day7,

  SUM(case when t.DateField = d.day1 then 1 else 0) day1Count,

  SUM(case when t.DateField = d.day2 then 1 else 0) day2Count,

  SUM(case when t.DateField = d.day3 then 1 else 0) day3Count,

  SUM(case when t.DateField = d.day4 then 1 else 0) day4Count,

  SUM(case when t.DateField = d.day5 then 1 else 0) day5Count,

  SUM(case when t.DateField = d.day6 then 1 else 0) day6Count,

  SUM(case when t.DateField = d.day7 then 1 else 0) day7Count

From MyTable t

  Left Join Dates on 1 = 1
Where <conditions>

Group by

  d.day1,

  d.day2,

  d.day3,

  d.day4,

  d.day5,

  d.day6,

  d.day7,

  t.DateField

The great thing about this technique is that it pushes the count down to the database where it can be processed more efficiently, which makes the report faster.  If you'd like to go this route and you're not experienced at using commands, see my blog post for more information: 

-Dell

[abhilash_kumar · Accepted solution]

I recently had to do something similar and I used a Recursive CTE that works well with SQL Server:

DECLARE @DateFrom DATETIME

SET @DateFrom = '2015-01-04' --Dummy Date to set the start of week to Sunday

DECLARE @DateTo DATETIME

SET @DateTo = '2015-01-10' --End of Week, Saturday

;WITH DateRanges AS

(

    SELECT @DateFrom AS 'DateValue'

    UNION ALL

    SELECT DATEADD(DAY, 1, DateValue)

    FROM DateRanges

    WHERE DateValue < @DateTo

)

SELECT DateName(weekday,DateRanges.DateValue) Day_ofWeek,

Count(Table.Field) Counts 

FROM DateRanges

LEFT JOIN Table ON DateName(weekday,DateRanges.DateValue) = DateName(weekday,Table.DateField)

Group By DateName(weekday,DateRanges.DateValue)

-Abhilash

[former_member202170]

thank you i am working on that ....