Click here to Skip to main content
11,437,713 members (50,710 online)
   

Database

 
GeneralRe: SQL Query To select result for monthly Pin
Eddy Vluggen10-Dec-12 0:18
memberEddy Vluggen10-Dec-12 0:18 
GeneralRe: SQL Query To select result for monthly Pin
VishwaKL10-Dec-12 0:20
memberVishwaKL10-Dec-12 0:20 
GeneralRe: SQL Query To select result for monthly Pin
Eddy Vluggen10-Dec-12 0:24
memberEddy Vluggen10-Dec-12 0:24 
GeneralRe: SQL Query To select result for monthly Pin
VishwaKL10-Dec-12 0:27
memberVishwaKL10-Dec-12 0:27 
AnswerRe: SQL Query To select result for monthly Pin
David Mujica10-Dec-12 4:16
memberDavid Mujica10-Dec-12 4:16 
GeneralRe: SQL Query To select result for monthly Pin
VishwaKL10-Dec-12 15:48
memberVishwaKL10-Dec-12 15:48 
GeneralRe: SQL Query To select result for monthly Pin
Shameel10-Dec-12 22:34
memberShameel10-Dec-12 22:34 
GeneralRe: SQL Query To select result for monthly Pin
J4amieC11-Dec-12 1:11
memberJ4amieC11-Dec-12 1: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:
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

GeneralRe: SQL Query To select result for monthly Pin
VishwaKL11-Dec-12 15:54
memberVishwaKL11-Dec-12 15:54 
AnswerRe: SQL Query To select result for monthly Pin
MS SQL DEVELOPER11-Dec-12 0:35
memberMS SQL DEVELOPER11-Dec-12 0:35 
QuestionHow to get the closing balance to be the opening balance of the following month Pin
vusamozi9-Dec-12 20:23
membervusamozi9-Dec-12 20:23 
AnswerRe: How to get the closing balance to be the opening balance of the following month Pin
Eddy Vluggen10-Dec-12 0:13
memberEddy Vluggen10-Dec-12 0:13 
AnswerRe: How to get the closing balance to be the opening balance of the following month Pin
@AmitGajjar11-Dec-12 8:17
member@AmitGajjar11-Dec-12 8:17 
QuestionSelect All Months and related data Pin
VishwaKL6-Dec-12 23:21
memberVishwaKL6-Dec-12 23:21 
AnswerRe: Select All Months and related data Pin
Blue_Boy7-Dec-12 2:18
memberBlue_Boy7-Dec-12 2:18 
GeneralRe: Select All Months and related data Pin
Mycroft Holmes8-Dec-12 21:57
memberMycroft Holmes8-Dec-12 21:57 
GeneralRe: Select All Months and related data Pin
VishwaKL9-Dec-12 20:06
memberVishwaKL9-Dec-12 20:06 
GeneralRe: Select All Months and related data Pin
Mycroft Holmes9-Dec-12 20:15
memberMycroft Holmes9-Dec-12 20:15 
GeneralRe: Select All Months and related data Pin
VishwaKL9-Dec-12 20:19
memberVishwaKL9-Dec-12 20:19 
GeneralRe: Select All Months and related data Pin
VishwaKL9-Dec-12 19:37
memberVishwaKL9-Dec-12 19:37 
GeneralRe: Select All Months and related data Pin
Deepak Kr110-Dec-12 20:27
memberDeepak Kr110-Dec-12 20:27 
GeneralRe: Select All Months and related data Pin
VishwaKL10-Dec-12 20:31
memberVishwaKL10-Dec-12 20:31 
GeneralRe: Select All Months and related data Pin
Deepak Kr110-Dec-12 22:02
memberDeepak Kr110-Dec-12 22:02 
GeneralRe: Select All Months and related data Pin
VishwaKL10-Dec-12 22:46
memberVishwaKL10-Dec-12 22:46 
GeneralRe: Select All Months and related data Pin
Deepak Kr110-Dec-12 23:25
memberDeepak Kr110-Dec-12 23:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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


Advertise | Privacy | Mobile
Web04 | 2.8.150506.1 | Last Updated 6 May 2015
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid