SQL Query To select result for monthly
-
Hi I want to select result for yearly report, i want total number of request raised per month and how many request solved per month, it should show '0' if the month not having request and solved request. Like Month Total Requests Solved Request 1 0 0 2 34 30 3 38 38 4 5 5 5 78 67 6 10 10 7 10 9 8 12 11 9 90 89 10 24 23 11 12 10 12 11 09 please help me
-
Hi I want to select result for yearly report, i want total number of request raised per month and how many request solved per month, it should show '0' if the month not having request and solved request. Like Month Total Requests Solved Request 1 0 0 2 34 30 3 38 38 4 5 5 5 78 67 6 10 10 7 10 9 8 12 11 9 90 89 10 24 23 11 12 10 12 11 09 please help me
-
-
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
Google is not an online repository where you can "get queries". I suggest trying (!) to write a select-query,
groupitbymonth, 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![^]
-
Google is not an online repository where you can "get queries". I suggest trying (!) to write a select-query,
groupitbymonth, 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![^]
-
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,
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 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![^]
-
Hi I want to select result for yearly report, i want total number of request raised per month and how many request solved per month, it should show '0' if the month not having request and solved request. Like Month Total Requests Solved Request 1 0 0 2 34 30 3 38 38 4 5 5 5 78 67 6 10 10 7 10 9 8 12 11 9 90 89 10 24 23 11 12 10 12 11 09 please help me
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:
-
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:
-
hi i am not having closed date field, i have only requesteddate, and status of the ticket like open/close
-
Hi I want to select result for yearly report, i want total number of request raised per month and how many request solved per month, it should show '0' if the month not having request and solved request. Like Month Total Requests Solved Request 1 0 0 2 34 30 3 38 38 4 5 5 5 78 67 6 10 10 7 10 9 8 12 11 9 90 89 10 24 23 11 12 10 12 11 09 please help me
Select [MONTH],Count([Total Requests]),COUNT([Solved Request]) From Temp Group By [MONTH]
-
hi i am not having closed date field, i have only requesteddate, and status of the ticket like open/close
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 monthsYou'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 JOINyour 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.monthHere'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 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 monthsYou'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 JOINyour 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.monthHere'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