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

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  October 24, 2016 at 12:53 pm

  #1908448

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

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

  ---

• Y.B.

  SSChampion

  Points: 11652

  More actions

  October 24, 2016 at 1:39 pm

  #1908465

  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