how to get the data in a table based on month and year(including both month and year) ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I have a requirement that I have to retrieve data base on month and year.I am using the below query

SQL

SELECT  SUM(areasft),DATEPART(year, sdate),DATEPART(month, sdate)  FROM storedata
GROUP BY DATEPART(year, sdate) ,DATEPART(month, sdate)
ORDER BY DATEPART(year, sDate) ,DATEPART(month, sDate)

The above query is retrieve data like below.
SUM YEAR MONTH
51013 2000 1
30970 2007 1
NULL 2007 3
900 2007 6
807 2007 8
NULL 2007 9
1756 2007 10
7535 2007 11
NULL 2008 1
1193 2008 2
4230 2008 3
350 2008 4
2200 2008 5
4660 2008 6
6685 2008 8

But I need to display including all the months of the year along with year irrespective of SUM . My query only displays only some months in a year.

Please solve this problem.

Regards,

N.SRIRAM

Posted 7-Jan-11 22:03pm

SRIRAM 2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Estys · Answer 1 · 2011-01-07T23:40:00

Solution 1

Maybe create an auxillary table to fill the gaps :
http://ask.metafilter.com/43840/Filling-Sparse-Data-In-An-SQL-Query[^]

Cheers

Posted 7-Jan-11 23:40pm

Estys

#realJSOP · Answer 2 · 2011-01-08T00:07:00

I'm not a DBA, nor really that skilled in it, but this is the way I'd approach it. For parts you need to research, google is definitely your friend:

0) Create a temporary table with all of the months in it - call it TEMP_YEARS.

Null 0 1
NULL 0 2
NULL 0 3
..
NULL 0 12

1) Create a stored procedure that updates that temp table with a given year (that you specify as a parameter) - call it sp_YearUpdate

2) Read all the data from your real table into a 2nd temp table (call it TEMP_DATA)

3) Determine the earliest year represented in the TEMP_DATA table.

4) Call your sp_YearUpdate with the earliest year

5) Merge TEMP_YEARS with your real table (using a union?)

6) Increment the year

7) Go back to step 4 (repeat as necessary)