Database Discussion Boards

VishwaKL9-Dec-12 22:43

9-Dec-12 22:43

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

Re: SQL Query To select result for monthly

Eddy Vluggen9-Dec-12 23:12

Eddy Vluggen

9-Dec-12 23:12

VishwaKL wrote:
please help me

With what? As it is now, there's not even a starting point.

Here's one[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL Query To select result for monthly

VishwaKL9-Dec-12 23:15

VishwaKL

9-Dec-12 23:15

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

Re: SQL Query To select result for monthly

Eddy Vluggen9-Dec-12 23:18

Eddy Vluggen

9-Dec-12 23:18

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL Query To select result for monthly

VishwaKL9-Dec-12 23:20

VishwaKL

9-Dec-12 23:20

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,

Re: SQL Query To select result for monthly

Eddy Vluggen9-Dec-12 23:24

Eddy Vluggen

9-Dec-12 23:24

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: SQL Query To select result for monthly

VishwaKL9-Dec-12 23:27

VishwaKL

9-Dec-12 23:27

thank you for your support

Re: SQL Query To select result for monthly

David Mujica10-Dec-12 3:16

David Mujica

10-Dec-12 3:16

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.
Thumbs Up | :thumbsup:

Re: SQL Query To select result for monthly

VishwaKL10-Dec-12 14:48

VishwaKL

10-Dec-12 14:48

hi i am not having closed date field,

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

Re: SQL Query To select result for monthly

Shameel10-Dec-12 21:34

Shameel

10-Dec-12 21:34

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

Re: SQL Query To select result for monthly

J4amieC11-Dec-12 0:11

J4amieC

11-Dec-12 0:11

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:

SQL

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:

SQL

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:

SQL

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:

SQL

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

Re: SQL Query To select result for monthly

VishwaKL11-Dec-12 14:54

VishwaKL

11-Dec-12 14:54

thank you so much J4amieC,

it worked for me,

Re: SQL Query To select result for monthly

MS SQL DEVELOPER10-Dec-12 23:35

MS SQL DEVELOPER

10-Dec-12 23:35

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.