How to fill the record for missing month

Question

Post reply

How to fill the record for missing month

halifaxdal

SSCoach

Points: 19741
More actions
March 13, 2008 at 9:07 pm

#69993

Hello,
Let's say I have a table:
month count
1 10
2 20
5 50
8 90
11 100
12 1000
How do I write a query to get a full result for every month? if there is no record for that month, then give it count = 0
month count
1 10
2 20
3 0
4 0
5 50
6 0
7 0
8 90
9 0
10 0
11 100
12 1000
Thanks.

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

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Heh... two words...

TALLY TABLE!

I'll be back in a minute with an example...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

First things first... please see the link in my signature line for the way to post table descriptions and data in the form of executable code... makes life real easy for the people trying to help you.

Second, here's the code that solves your problem complete with a demo table and data.

--======================================

-- Create and populate a test table.

-- THIS IS NOT PART OF THE SOLUTION!!!

--======================================

--===== Create the test table

DECLARE @demo TABLE

(

Month INT,

Count INT

)

--===== Populate the test table with

-- sample data from the post.

INSERT INTO @demo

(Month,Count)

SELECT 1,10 UNION ALL

SELECT 2,20 UNION ALL

SELECT 5,50 UNION ALL

SELECT 8,90 UNION ALL

SELECT 11,100 UNION ALL

SELECT 12,1000

--======================================

-- HERE'S THE SOLUTION!

--======================================

SELECT t.N,ISNULL(d.Count,0) AS Count

FROM dbo.Tally t

LEFT OUTER JOIN @demo d

ON t.N = d.Month

WHERE t.N BETWEEN 1 AND 12

Ok... what's a Tally table and how do you make one? You guessed it... I have a link for that, as well. I apologize that the "spacing" didn't "stick" when I posted the script...

http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

There's a thousand things you can do with a Tally table in SQL Server 2000. Most folks use ROW_NUMBER() in 2k5 to replace it, but for shear performance, it's a very rare thing to beat the Tally table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

halifaxdal SSCoach Points: 19741 More actions · Answer 3

halifaxdal

SSCoach

Points: 19741

March 14, 2008 at 7:47 am

#789630

Thank you Jeff.

tosscrosby SSCoach Points: 18045 More actions · Answer 4

Jeff, Checked out your article and besides complimenting you on it, I have a question..how did you get this article into the future? Is that another of your many skills? I know you're good but this is getting crazy!

This is the heading of the article:

Create a Tally or Numbers Table

By Jeff Moden, 2008/04/30

-- You can't be late until you show up.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Heh... although I'd like to take credit for it, I think that was when they were having date problems on the forum. And thanks for the compliment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

halifaxdal (3/14/2008)
Thank you Jeff.

You bet... thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

tosscrosby (3/14/2008)
Jeff, Checked out your article and besides complimenting you on it, I have a question..how did you get this article into the future? Is that another of your many skills? I know you're good but this is getting crazy!
This is the heading of the article:

Create a Tally or Numbers Table
By Jeff Moden, 2008/04/30

I think you may have run up against a "sneak preview". Our friend and fearless moderator Steve Jones apparently has the ability to "pre-publish" certain articles, so that they're ready to go on a future date and just "appear"/release on whatever schedule he determines. If he doesn't get around to setting the "I don't want to to show this article yet", you get the "future date" to happen. I apparently started commenting on an Andy Leonard Leonard article a few weeks before it was due to be released...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?