SQL Query To select result for monthly

[VishwaKL]

Thanks for the reply, i am trying to achieve that, but i not able to do that, i tried all possibilities by searching google, so i want query to get the totla number of request raised, solved request month wise for a yearly report

[Lost User]

Google is not an online repository where you can "get queries". I suggest trying (!) to write a select-query, group it by month, and use a subquery to get the correct data.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[VishwaKL]

i agree that google is not a repository, i said i am trying with google help only, i am able to get either solved request or total request, but i want them to be combined as i showed in my question,

[Lost User]

VishwaKL wrote:

i said i am trying with google help only

The documentation on SQL is a bit more helpful. Google is mostly used for finding examples, not solutions.

VishwaKL wrote:

i am able to get either solved request or total request, but i want them to be combined as i showed in my question,

Aight, show us what you got so far :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[VishwaKL]

thank you for your support

[David Mujica]

Since you did not post the structure of your table, I will have to give a very general asnwer. Something like: select month(ticket_created_date),count(ticket_created_date),count(ticket_closed_date) from myTickets group by month(ticket_created_date) If this helps, Remeber to vote. :thumbsup:

[VishwaKL]

hi i am not having closed date field, i have only requesteddate, and status of the ticket like open/close

[Lost User]

The Group By hint should be more than enough for you to get started.

[MS SQL DEVELOPER]

Select [MONTH],Count([Total Requests]),COUNT([Solved Request]) From Temp Group By [MONTH]

[J4amieC]

VishwaKL wrote:

i have only requesteddate, and status of the ticket like open/close

This information was VITAL to the question. Do us all a favour,m in future include all the relevant information for a question - if you're asking for help writing a SQL Query, if you dont tell us the structure of your table, its IMPOSSIBLE to answer. So given that info. The first place to start is to generate numbers 1-12 so you have something to join on (even for those months with zero requests). A recursive common table expression can do this easily:

with months (num) as
(
SELECT 1
UNION ALL
SELECT num+1
from months
where num<12
)
select * from months

You'll also need a subquery to pull together the ticket data:

select
month(requesteddate) as month,
count(*) as count,
sum(case when status='Closed' THEN 1 ELSE 0 END) as resolved
from tickets
group by month(requesteddate)

You can then use this to LEFT JOIN your month table, and produce the final output you want:

with months (num) as
(
SELECT 1
UNION ALL
SELECT num+1
from months
where num<12
)
select m.num as Month,
isnull(t.count,0) as Total,
isnull(t.resolved,0) as Totalresolved
from months m
left join (
select
month(requesteddate) as month,
count(*) as count,
sum(case when status='Closed' THEN 1 ELSE 0 END) as resolved
from tickets
group by month(requesteddate)
) t
on m.num = t.month

Here's a live example: http://www.sqlfiddle.com/#!6/a8a7b/11[^] Input data:

create table tickets
(
requesteddate datetime,
status varchar(10)
)

insert into tickets
values
('2012-01-10','closed'),
('2012-01-20','closed'),
('2012-01-30','closed'),
('2012-02-10','closed'),
('2012-02-20','closed'),
('2012-03-10','closed'),
('2012-03-20','closed'),
('2012-03-30','open')

Output:

MONTH TOTAL TOTALRESOLVED
1 3 3
2 2 2
3 3 2
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0

[VishwaKL]

thank you so much J4amieC, it worked for me,