Select st. to result all 12 months even if no data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select st. to result all 12 months even if no data

Author

Topic

jazzyb
Starting Member

20 Posts

Posted - 2010-12-31 : 06:53:44

I have table names 'payroll' with following data

month , pay
January , 1200
March , 1500
December , 2000

I want the following result

Janury , 1200
February , 000
March , 1500
April , 000
May , 000
June , 000
July , 000
August , 000
September , 000
October , 000
November , 000
December , 2000

Please help to make the query.

Thanks in advance

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-31 : 07:26:47

Your payroll table doesn't have actual dates?

You would have to join to a reference table which contained all of the month names, which can be done on the fly in a derived table. One option is below..probably not the best. I presume your payroll table has a different structure.


Select b.Mon, SUM(isNull(pay,0)
FROM (Select datename(month,dateadd(m,number-1,'20100101')) as Mon
      FROM master..spt_values
      where number between 1 and 12
      GROUP BY number) b left join payroll on b.Mon = payroll.month
GROUP BY b.Mon

If you have SQL 2008 you can use a CTE instead of a inline derived table

Poor planning on your part does not constitute an emergency on my part.

Sachin.Nand

2937 Posts

Posted - 2010-12-31 : 07:31:37

Create a temp table with the same structure as payroll insert 12 month names and pay value=NULL
Then use a left join on payroll to select all the records from temp table and use payroll.month,isnull(temp.pay,payroll.pay)in the select list.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-12-31 : 07:34:02

Well mine is almost the same solution what dataguru posted.So can you try either one of them.

PBUH

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-31 : 07:41:05

Either way indeed, except I suspect the payroll table has an actual date column, not the text of the month, and likely has more rows than 1 entry per month. If the actual structure were known, or actual format of the data..there are likely other options.

Poor planning on your part does not constitute an emergency on my part.

jazzyb
Starting Member

20 Posts

Posted - 2011-01-01 : 11:39:28

Thanks for replying..

You are right, that my month column is of 'date' type. It store the paybill date in date format.

I am using sql express and vb.net. So now what is your suggestion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources