October 24, 2016 at 12:37 pm
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
October 24, 2016 at 12:53 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 24, 2016 at 1:39 pm
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]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply