Newbie: get months even if they don't exist in tbl

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

SQL Server 2008 Forums

Transact-SQL (2008)

Newbie: get months even if they don't exist in tbl

Author

Topic

jjmagro
Starting Member

5 Posts

Posted - 2010-08-30 : 07:16:51

Hello experts...
I'm new in this T-SQL issue, and after looking and googling for the right answer I couldn't get it

I have a table where I store dates and values, then I need to reach those values grouped by month for a given year
The point is that I store information, only when there are data

so, is possible that for example, I have information for january and march, but nothing for february.

I guess my question is very simple:
How can I run a query to show all the months in the year, even if they aren't in the table?

So, let's say that I have the following data:

Date Value
01/01/2010 200.00
02/01/2010 200.00
02/02/2010 100.00
10/05/2010 300.00

What query should I do to get this resultset?:

Month Value
-----------------
January 400.00
February 100.00
March 0.00
April 0.00
May 0.00
June 0.00
July 0.00
August 0.00
September 0.00
October 300.00
November 0.00
December 0.00

My query is very simple:


SELECT numcli,
CASE 
WHEN MONTH(fecdoc) = 1 THEN 'Jan'
WHEN MONTH(fecdoc) = 2 THEN 'Feb'
WHEN MONTH(fecdoc) = 3 THEN 'Mar'
WHEN MONTH(fecdoc) = 4 THEN 'Apr'
WHEN MONTH(fecdoc) = 5 THEN 'May'
WHEN MONTH(fecdoc) = 6 THEN 'Jun'
WHEN MONTH(fecdoc) = 7 THEN 'Jul'
WHEN MONTH(fecdoc) = 8 THEN 'Aug'
WHEN MONTH(fecdoc) = 9 THEN 'Sep'
WHEN MONTH(fecdoc) = 10 THEN 'Oct'
WHEN MONTH(fecdoc) = 11 THEN 'Nov'
WHEN MONTH(fecdoc) = 12 THEN 'Dec'
END AS [month],
SUM(ISNULL(imptot,0)) AS total
FROM facturas
WHERE YEAR(fecdoc) = YEAR(GETDATE())
AND numcli = 562
GROUP BY numcli, MONTH(fecdoc)
ORDER BY MONTH(fecdoc) ASC

But this query only returns me data from the months where I have info

Can anyone help this humble student please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 07:26:38

[code]SELECT 562 AS NumCli,
theMonth AS [Month]
SUM(ImpTot) AS Total
FROM (
SELECT LEFT(DATENAME(MONTH, FecDoc), 3) AS theMonth,
DATEPART(MONTH, FecDoc) AS thaMonth,
ISNULL(ImpTot, 0) AS ImpTot
FROM dbo.Facturas
WHERE FecDoc >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND FecDoc < DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0)
AND NumCli = 562

UNION ALL

SELECT DATENAME(MONTH, DATEADD(MONTH, Number, -1)) AS theMonth,
Number AS thaMonth,
0 AS ImpTot
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 12

) AS d
GROUP BY theMonth,
thaMonth
ORDER BY thaMonth[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 10:01:06

or use this function to generate calendar table and left join your table to it

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjmagro
Starting Member

5 Posts

Posted - 2010-08-30 : 10:08:42

That was 99.99% what I need :)))
My fault, in the prior query I forgot to delete the filter by customer id (the "numCli" field)
The problem is that I need that query for ALL the customers in the table :(((
So, the final resultset it should be something like:

NumCli Month Value
001 Jan 200
001 Feb 100
001 Mar 0.0
001 Apr 0.0
[...]
001 Dec 0.0
002 Jan 0.0
002 Feb 200
002 Mar 0.0
002 Apr 100
002 May 0.0
[...]
002 Dec 50
003 Jan 0.0
003 Feb 0.0
003 Mar 0.0
[...]
003 Dec 0.0

And so on

Just for curious people, let me say that the meaning of this query is to be part of a master / detail datasource in a windows application and it will be filtered by the customer id, so, there's a master dataTable, and when the user navigates through it's data, this datatable (this resultset) is being filtered as well.

So, once again, if somebody can put some light in here, I really will appreciate the help...

I was thinking in make some temp table to reach the data and use a cursor, but that will be endless...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 10:15:54

you just need to take distinct list of customer and cross join it with table generator function like


SELECT customerno,date,...other columns
FROM
(
SELECT m.customerno,d.date
FROM 
(SELECT DISTINCT CustomerID FROM Table) m
CROSS JOIN (SELECT Date FROM dbo.CalendarTable(your start date here,your end date here,0)) d
)t
LEFT JOIN Yourtable
....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-30 : 10:17:01

[code]SELECT NumCli,
theMonth AS [Month]
SUM(ImpTot) AS Total
FROM (
SELECT NumCli,
LEFT(DATENAME(MONTH, FecDoc), 3) AS theMonth,
DATEPART(MONTH, FecDoc) AS thaMonth,
SUM(ISNULL(ImpTot, 0)) AS ImpTot
FROM dbo.Facturas
WHERE FecDoc >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND FecDoc < DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0)
GROUP BY NumCli,
LEFT(DATENAME(MONTH, FecDoc), 3),
DATEPART(MONTH, FecDoc)

UNION ALL

SELECT f.NumCli,
v.theMonth,
v.thaMonth,
0 AS ImpTot
FROM (
SELECT NumCli
FROM dbo.Facturas
WHERE FecDoc >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND FecDoc < DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0)
GROUP BY NumCli
) AS f
CROSS JOIN (
SELECT DATENAME(MONTH, DATEADD(MONTH, Number, -1)) AS theMonth,
Number AS thaMonth
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 12
) AS v
) AS d
GROUP BY NumCli,
theMonth,
thaMonth
ORDER BY NumCli,
thaMonth[/code]

N 56°04'39.26"
E 12°55'05.63"

jjmagro
Starting Member

5 Posts

Posted - 2010-08-30 : 10:44:13

oohhhhhhhhhhhhhhhhhhh I'm experiencing an orgasm right now!!!! jajajajajajaja

visakh and peso, I really appreciate both your help, at the end, both solutions (yours and peso's solution) was right, but I decide to use peso's solution just because it was more "ready to go"
I need to deliver this one right now, I was curious about the implementation of calendar tables to reach the kind of data I need... I will take some time later to understand how to implement using your solution, but for now, I will use peso's solution...
Thank you again to both of you!!!

jjmagro
Starting Member

5 Posts

Posted - 2010-08-30 : 10:51:40

This is the final result of your help, a stored procedure who receives a year as parameter and gives back the query:

[CODE]
CREATE PROCEDURE spGetSumaMovimientosByClientePorMes
@year INT
AS
SELECT NumCli, theMonth AS [Month], SUM(ImpTot) AS Total
FROM (
SELECT NumCli, LEFT(DATENAME(MONTH, FecDoc), 3) AS theMonth,
DATEPART(MONTH, FecDoc) AS thaMonth,
SUM(ISNULL(ImpTot, 0)) AS ImpTot
FROM dbo.Facturas
WHERE YEAR(fecdoc) = @year
GROUP BY NumCli, LEFT(DATENAME(MONTH, FecDoc), 3), DATEPART(MONTH, FecDoc)

UNION ALL

SELECT f.NumCli, v.theMonth, v.thaMonth, 0 AS ImpTot
FROM (
SELECT NumCli
FROM dbo.Facturas
WHERE YEAR(fecdoc) = @year
GROUP BY NumCli
) AS f
CROSS JOIN (
SELECT LEFT(DATENAME(MONTH, DATEADD(MONTH, Number, -1)), 3) AS theMonth, Number AS thaMonth
FROM master.dbo.spt_values
WHERE Type = 'P' AND Number BETWEEN 1 AND 12
) AS v
) AS d
GROUP BY NumCli, theMonth, thaMonth
ORDER BY NumCli, thaMonth
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-30 : 11:23:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources