Select All Months and related data

VishwaKL

Hi guys I have a problem to select query. For yearly report i am getting by my query

SELECT YEAR(RequestedDate) as Years, Month(RequestedDate) as MonthInNumbers,Count(Month(RequestedDate)) as MonthCount

FROM MYTABLE

GROUP BY YEAR(RequestedDate),Month(RequestedDate)

Year Month Total 2012 2 13 2012 3 61 2012 4 46 2012 5 48 2012 6 63 2012 10 54 2012 12 11 for this i want Year Month Total 2012 1 0 2012 2 13 2012 3 61 2012 4 46 2012 5 48 2012 6 63 2012 7 0 2012 8 0 2012 9 0 2012 10 54 2012 11 0 2012 12 11 any help

Blue_Boy

Here it is

create table #tempMonths (monthNr int)
insert into #tempMonths (monthNr) values (1)
insert into #tempMonths (monthNr) values (2)
insert into #tempMonths (monthNr) values (3)
insert into #tempMonths (monthNr) values (4)
insert into #tempMonths (monthNr) values (5)
insert into #tempMonths (monthNr) values (6)
insert into #tempMonths (monthNr) values (7)
insert into #tempMonths (monthNr) values (8)
insert into #tempMonths (monthNr) values (9)
insert into #tempMonths (monthNr) values (10)
insert into #tempMonths (monthNr) values (11)
insert into #tempMonths (monthNr) values (12)

SELECT isnull( YEAR(RequestedDate) ,YEAR(getdate())) as Years,
isnull( Month(RequestedDate),temp.monthNr) as MonthInNumbers,temp.monthNr,
Count(Month(RequestedDate)) as MonthCount

FROM tblMYTABLE
right join #tempMonths temp on temp.monthNr = Month(RequestedDate)
GROUP BY YEAR(RequestedDate),Month(RequestedDate),temp.monthNr

drop table #tempMonths

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

Mycroft Holmes

Why not use @TableVar instead of a temptable#, I wonder if I have asked you this question before!

Never underestimate the power of human stupidity RAH

VishwaKL

Hi Blue_Boy Thank you so much dude. :-D ;)

VishwaKL

Hi Can you Explain me the usage of @TableVar and how that is helpful for my question

Mycroft Holmes

It makes no difference to the solution, Blue Boy has given you the definitive solution. A temp table creates a table object in the temp database and writes the data to the hard drive. A table variable does this in memory, there are some benefits to both. A temp table can have indexes applied and a global temp table can be shared between procedures. I default to table vars, BB probably defaults to temp tables, I was wondering if there was a reason.

Never underestimate the power of human stupidity RAH

VishwaKL

Thanks for your reply, Will check the differences

Deepak Kr1

Another option is the USE of Decode function that will also give u the correct result with out using the temp table or variable, if u can share the data of yr mytable then will try to give u the sql query.

VishwaKL

I want to select closed requests per month and total request per month both my table is like RequestID,RequesterName,RequestStatus(OPen/close),RequestedDate. From these fields i have to fetch that data,

Deepak Kr1

Hope this will work

select years, month, sum(MonthCount) from
(
SELECT to_char(trunc(RequestedDate, 'Year'), 'YYYY') as Years, to_char(trunc(RequestedDate, 'Month'), 'MON') as Month, count(to_char(trunc(RequestedDate, 'Month'), 'MON')) as MonthCount
FROM MYTABLE
group by to_char(trunc(RequestedDate, 'Year'), 'YYYY'), to_char(trunc(RequestedDate, 'Month'), 'MON')
union
select '2012' as years, 'JAN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'FEB' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'APR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JUN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JULY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'AUG' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'SEPT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'OCT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'NOV' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'DEC' as Month, 0 as MonthCount from dual
) group by years, month

VishwaKL

Thanks deepak, But i getting 2 errors after executing , i changed mytable to my real table, but it giving below mentioned errors 1) 'trunc' is not a recognized built-in function name and 2)Incorrect syntax near ')'.

Deepak Kr1

Hey i just used the trunc method for oracle, u can replace to yr old year and month method to get the result, but the concept is that we will use the union clause to get the data for those month for which we dont have the data than we will do a union with yr old query then on the top of that put a sum for monthcount with group by month and year. so yr query will be

select years, month, sum(MonthCount) from
(
SELECT YEAR(RequestedDate) as Years, Month(RequestedDate) as MonthInNumbers,Count(Month(RequestedDate))
FROM MYTABLE
group by YEAR(RequestedDate),Month(RequestedDate)
union
select '2012' as years, 'JAN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'FEB' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'APR' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'MAY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JUN' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'JULY' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'AUG' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'SEPT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'OCT' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'NOV' as Month, 0 as MonthCount from dual
union
select '2012' as years, 'DEC' as Month, 0 as MonthCount from dual
) group by years, month