February 11, 2014 at 1:08 pm
Hello,
My table is like this:
ID int
Ref Varchar(10)
CreatedOn datetime
State Varchar(10)
I need to write a query to generate data like this:
Count QuarterOfYear State
3 2013Q1 Open
4 2013Q1 Close
3 2013Q2 Open
4 2013Q2 Close
3 2013Q3 Open
4 2013Q3 Close
3 2013Q4 Open
4 2013Q4 Close
3 2014Q1 Open
4 2014Q1 Close
3 2014Q2 Open
4 2014Q2 Close
3 2014Q3 Open
4 2014Q3 Close
3 2014Q4 Open
4 2014Q4 Close
I really like to generate some sample data here but couldn't find the post on how to do it, thank you in advance if you can post it here and sorry for the inconvenience.
February 11, 2014 at 1:27 pm
You can use the DATEPART function to get the quarter of a datetime value. Next you need to GROUP BY on the DATEPART part. Below I have generated some sample code and the final query:
create table #Test
(ID int
,Ref Varchar(10)
,CreatedOn datetime
,State Varchar(10)
)
insert into #Test
values(1, 'x', '20130101', 'Open')
, (2, 'x', '20130201', 'Open')
, (3, 'x', '20130301', 'Closed')
, (4, 'x', '20130401', 'Open')
, (5, 'x', '20130501', 'Open')
, (6, 'x', '20130601', 'Closed')
, (7, 'x', '20130701', 'Open')
, (8, 'x', '20130801', 'Open')
, (9, 'x', '20130901', 'Closed')
, (10, 'x', '20131001', 'Open')
, (11, 'x', '20131101', 'Closed')
, (12, 'x', '20131201', 'Open')
, (13, 'x', '20130123', 'Closed')
, (14, 'x', '20130223', 'Open')
, (15, 'x', '20130323', 'Open')
, (16, 'x', '20130423', 'Closed')
, (17, 'x', '20130523', 'Open')
, (18, 'x', '20130623', 'Open')
, (19, 'x', '20130723', 'Closed')
, (20, 'x', '20130823', 'Open')
, (21, 'x', '20130923', 'Closed')
select
COUNT(ID) as 'Count'
, CONVERT(char(4), datepart(yyyy, CreatedOn)) + 'Q' + convert(char(1), datepart(q, CreatedOn)) as QuarterOfYear
, State
from #test
group by
CONVERT(char(4), datepart(yyyy, CreatedOn)) + 'Q' + convert(char(1), datepart(q, CreatedOn))
, State
order by
2
drop table #test
February 11, 2014 at 1:32 pm
Please refer to the code to generate sample table/data, thanks.
CREATE TABLE [dbo].[Ticket](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ref] [varchar](10) NULL,
[CreatedOn] [datetime] NULL,
[State] [varchar](10) NULL
) ON [PRIMARY]
GO
--Open data for each quarter in 2013
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-01-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-04-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-06-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-08-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-09-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-10-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-11-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Open')
--Closed data for each quarter in 2013
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-01-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-04-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-06-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-08-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-09-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-10-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-11-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Closed')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-01-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-02-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-03-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-04-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-05-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-06-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-07-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-08-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-09-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-10-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-11-01', 'Open')
insert into Ticket (Ref, CreatedOn, State) Values ('whatever', '2013-12-01', 'Open')
February 11, 2014 at 1:33 pm
HanShi (2/11/2014)
You can use the DATEPART function to get the quarter of a datetime value. Next you need to GROUP BY on the DATEPART part. Below I have generated some sample code and the final query:
Hoop, our posts overlapped, thank you and I will take a look.
February 11, 2014 at 1:37 pm
With your provided sample data the blelow query will get the desired results.
Query:
select
COUNT(ID) as 'Count'
, CONVERT(char(4), datepart(yyyy, CreatedOn)) + 'Q' + convert(char(1), datepart(q, CreatedOn)) as QuarterOfYear
, State
from Ticket
group by
CONVERT(char(4), datepart(yyyy, CreatedOn)) + 'Q' + convert(char(1), datepart(q, CreatedOn))
, State
order by
2
Output:
82013Q1Closed
112013Q1Open
112013Q2Closed
142013Q2Open
172013Q3Closed
202013Q3Open
52013Q4Closed
82013Q4Open
February 11, 2014 at 1:41 pm
Thank you very much for the quick and neat solution
February 11, 2014 at 5:45 pm
Here's another way.
SELECT [Count]=COUNT(*), [State]
,CreatedOn=STUFF(y, 5, 1, 'Q' + q)
FROM dbo.Ticket a
CROSS APPLY
(
SELECT LEFT(DATEPART(year, CreatedOn), 4) + ' '
,LEFT(DATEPART(quarter, CreatedOn), 1)
) b (y, q)
GROUP BY y, q, [State]
ORDER BY y, q, [State];
Oftentimes I like to isolate intermediate value calculations in a CROSS APPLY to add clarity.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2014 at 10:38 am
Thank you dwain
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply