A record for each month, even there is no data
-
I need to sumarize information for each month, and have a result for the 12 months. Is there a solution without a join to a Months table?
-
How are you currently summarizing?
Please post your query, and some sample data, and how you would like the output to look. Have a look at this.
Use Pubs
select Stor_ID,
sum(case when datepart(mm,ord_Date) = 1 then qty else 0 end) as 'Jan',
sum(case when datepart(mm,ord_Date) = 2 then qty else 0 end) as 'Feb',
sum(case when datepart(mm,ord_Date) = 3 then qty else 0 end) as 'Mar',
sum(case when datepart(mm,ord_Date) = 4 then qty else 0 end) as 'Apr'
from sales
where ord_Date between '1992-01-1 00:00:00.000' and '1993-12-1 00:00:00.000'
group by Stor_ID
-
I need a result like the following
YEAR MONTH VALUE
2005 1 1234
2005 2
2005 3 432.22
2005 4 23.00
2005 5
2005 6 127.99
and so on ....
-
Without seeing any of your data, or the query you are using,
the way you are presenting your results I would think that you have to join to some kind of calendar table.
-
I am trying to avoid the creation of a calendar table !!!
-
Well, If you post an example of your table,
and some sample data, and also the query you are using, I'm sure someone can look at it, and try to help out.
Without enough information its kind of hard for anyone to give you an answer.
-
I guess this would be cheating, but I can see two ways around joining to a true calendar table.
One is to make a dummy list of numbers on the fly in a table variable, and left join using that:
declare @intervals table (
i int
)
insert @intervals values ( 1 )
while ( select max(i) from @intervals ) < 12 insert @intervals select max(i) + 1 from @intervals
select [ your data ]
from @intervals left join [ your stuff ]
Or, slow and ugly, you could explicitly loop using "while," and stuff the output into a table variable:
declare @yourOutput table(
... columns ...
)
declare @i int
set @i = 1
while @i <= 12
begin
insert into @yourOutput values (i, [ your report data ])
set @i = @i + 1
end
select * from @yourOutput
-
As Merril has stated if you want to show the month even if you have no data within your table for that particular month, by design the only way is to use a left (or right) outer join, where the base table is the month table.
Now either you have a calendar table or you will have to construct it on the fly which can be a function returning a table or created/declared temporary table a derived table, what ever, but a table must be there for an outer join
Bye
Gabor -
As already stated you need a base table to join to get the range you require and without the data and query your using it is difficult to give a good answer.
If you do not wish to create another table you could use the MS table spt_values (although this may not be present in future editions of sql)
SELECT 2005 AS [YEAR], n.[number] AS [MONTH], ISNULL(a.[VALUE],0)
FROM master.dbo.spt_values n
LEFT OUTER JOIN [yourtable] a
ON a.[MONTH] = n.[number]
AND a.[YEAR] = 2005
WHERE n.[type] = 'P'
AND n.[number] BETWEEN 1 AND 12
Far away is close at hand in the images of elsewhere.
Anon. -
From BOL :
GROUP BY Clause
Specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT clause , calculates a summary value for each group. When GROUP BY is specified, either each column in any non-aggregate expression in the select list should be included in the GROUP BY list, or the GROUP BY expression must match exactly the select list expression.
Note If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order. It is recommended that you always use the ORDER BY clause to specify a particular ordering of the data.
Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
Arguments
ALL
Includes all groups and result sets, even those that do not have any rows that meet the search condition specified in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.
GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in the query.
Does this help? Obviously, you need to have a list of all the months somewhere or SQL Server won't know that they exist. If your main table does contain all months but not for your particular selection conditions, then you could join on an inner query of (SELECT DISTINCT [Month] FROM dbo.MyTable).
-
A months table is probably the easiest and most elegant way to do this, but, for whatever your reasons are, if you insist on doing this WITHOUT a month table, you can try this.
select your data into a temp table (month, sum).
if not exists (select * from #temp where month = 1)
insert # values 1, O
instead of 1, use a variable, and loop through this 12 times.
As everyone else recommends, use a month table!
Good Luck,
Sara
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply