query get 0 when month not exist in MSSQL

Question

Post reply

query get 0 when month not exist in MSSQL

stino

SSC Enthusiast

Points: 179
More actions
October 24, 2016 at 12:37 pm

#314751

I have table names 'payroll' in MSSQL with following data
month , pay
January , 1200
March , 1500
December , 2000
I want the following result
Janury , 1200
February , 00
March , 1500
April , 00
May , 00
June , 00
July , 00
August , 00
September , 00
October , 00
November , 00
December , 00
Please help to make the query.
Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

A LEFT JOIN from a calendar table is probably what you need.

Take a read of this link and see whether it helps you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Y.B. SSChampion Points: 11649 More actions · Answer 2

Just expanding on what Phil said, this can be done quite easily with a LEFT JOIN.

DECLARE @months TABLE ([month] VARCHAR(12))

INSERT INTO @months

VALUES ('January'), ('February'), ('March'), ('April'), ('May'), ('June'), ('July'), ('August'), ('September'), ('October'), ('November'), ('December')

DECLARE @payroll TABLE ([month] VARCHAR(12), pay NUMERIC)

INSERT INTO @payroll

VALUES ('January', 1200), ('March', 1500), ('December', 2000)

SELECT

m.[month],

CASE WHEN p.pay IS NULL THEN 00 ELSE p.pay END AS pay

FROM @months m

LEFT JOIN @payroll p ON m.[month] = p.[month]

SELECT quote FROM brain WHERE original = 1
0 rows returned