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 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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy