SQL Server Query to Fill Empty Record with null or 0 Values

Question

I have a code in SQL Server Query that list a tabular presentation of sales of a particular tenant in PER YEAR (Column) and PER MONTH (rows) form.

This is the part code which i think is relevant to post

SELECT tenantcode
       ,datename(month, date) [month]
       ,isnull(sum(case when year(DATE) = @Year1  then sales end), 0) as 'Year1'
       ,isnull(sum(case when year(DATE) = @Year2 then sales end), 0) as 'Year2'
       ,isnull(sum(case when year(DATE) = @Year3 then sales end), 0) as 'Year3'
       ,isnull(sum(case when year(DATE) =  @Year4 then sales end), 0) as 'Year4'
       ,isnull(sum(case when year(DATE) = @Year5 then sales end), 0) as 'Year5'
FROM TenantSales 
GROUP BY  datename(month,date), tenantcode
ORBDER BY   datepart(MM,DATENAME(MONTH, DATE) + '01 2000')

Please note that @Year are variables formulated to get the 5 years based on user selection. In this example 2008-2012. The particular tenant started on MAY 2008, so the sales are made available only starting May 2008. The code produce this output. (For illustration)

What I desire to achieve is to include all the months like this

When the sales of the tenant starts or available on January onwards, the code works just fine, however in an instance like what I mentioned, it does not.

I am using SQL Server 2008

Gordon Linoff · Accepted Answer · 2015-10-22 01:21:21Z

2

You need a list of all months. For a single tenant (as asked in the question), you can do:

with months as (
      select cast('2015-01-01' as date) as d
      union all
      select dateadd(month, 1, months.d)
      from months
      where months.d < cast('2015-12-01' as date)
     )
SELECT tenantcode,
       datename(m.d, date) [month],
       isnull(sum(case when year(DATE) = @Year1  then sales end), 0) as Year1,
       isnull(sum(case when year(DATE) = @Year2 then sales end), 0) as Year2,
       isnull(sum(case when year(DATE) = @Year3 then sales end), 0) as Year3,
       isnull(sum(case when year(DATE) = @Year4 then sales end), 0) as Year4,
       isnull(sum(case when year(DATE) = @Year5 then sales end), 0) as Year5
FROM months m LEFT JOIN
     TenantSales CROSS JOIN
     ON month(date) = month(m.d)
GROUP BY datename(month, m.d), tenantcode
ORBDER BY month(m.d);

Note that you should only use single quotes for string and date names. You should not use single quotes for column aliases, because this can lead to confusion.

edited Oct 22, 2015 at 1:21

answered Oct 21, 2015 at 10:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rickyProgrammer Over a year ago

an error like a multi-part identifier on d.d @Gordon

rickyProgrammer Over a year ago

Been trying this and do some workaround but it doesn't work still :(

Wibbler · Accepted Answer · 2015-10-21 14:15:56Z

1

You would like to include all months, but are running a query against data which does not contain all months. You will need to join to another table containing all months:

SELECT tenants.tenantcode
       ,months.monthname
       ,isnull(sum(case when year(t.DATE) = @Year1  then sales end), 0) as 'Year1'
       ,isnull(sum(case when year(t.DATE) = @Year2 then sales end), 0) as 'Year2'
       ,isnull(sum(case when year(t.DATE) = @Year3 then sales end), 0) as 'Year3'
       ,isnull(sum(case when year(t.DATE) =  @Year4 then sales end), 0) as 'Year4'
       ,isnull(sum(case when year(t.DATE) = @Year5 then sales end), 0) as 'Year5'
FROM 
(
SELECT
    Number
    ,DATENAME(MONTH, '2015-' + CAST(Number as varchar(2)) + '-1') monthname
FROM master..spt_values
WHERE Type = 'P' and Number between 1 and 12
) months

CROSS JOIN
(
    SELECT DISTINCT tenantcode
    FROM TenantSales
) tenants    

LEFT JOIN TenantSales t
ON months.monthname = datename(month,t.date)
AND tenants.tenantcode = t.tenantcode

GROUP BY  months.monthname, months.number, tenants.tenantcode
ORDER BY   months.number

Month table generation taken from https://social.msdn.microsoft.com/Forums/sqlserver/en-US/697f4ee6-35d1-403d-a9f7-caecaf1ba479/all-monthnames-and-month-numbers-in-sql-server

edited Oct 21, 2015 at 14:15

answered Oct 21, 2015 at 10:40

Wibbler

1,0439 silver badges17 bronze badges

2 Comments

Wibbler Over a year ago

Try my edit, this will cross join all months to all tenants and then pull data through for each tenant and month where available

rickyProgrammer Over a year ago

This work, I have just adjusted some to achieve my desired output.

t-clausen.dk · Accepted Answer · 2015-10-21 12:07:24Z

1

Try this instead:

;WITH months(d) as
(
  SELECT dateadd(m, x, 0)
  FROM (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) x(x)
)
SELECT 
  tenantcode,
  datename(month, m.d) [month],
  isnull(sum(case when year(DATE) = @Year1  then sales end), 0) as Year1,
  isnull(sum(case when year(DATE) = @Year2 then sales end), 0) as Year2,
  isnull(sum(case when year(DATE) = @Year3 then sales end), 0) as Year3,
  isnull(sum(case when year(DATE) = @Year4 then sales end), 0) as Year4,
  isnull(sum(case when year(DATE) = @Year5 then sales end), 0) as Year5
FROM
  months m
LEFT JOIN
  TenantSales ts
ON
  month(date) = month(m.d)
GROUP BY
  datename(month, m.d),m.d, tenantcode
ORDER BY
  m.d

answered Oct 21, 2015 at 12:07

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

1 Comment

rickyProgrammer Over a year ago

same result with the previous answers... but doing some workaround by including cross join, this would work. So I would vote this as useful.

Collectives™ on Stack Overflow

SQL Server Query to Fill Empty Record with null or 0 Values

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related