February 21, 2014 at 2:55 pm
I am sorry to ask a similar question here again because requirement changed, below is the script to create the sample table and populate the table.
The result I am looking for is:
Owner Quarter State Rating1 Rating2 Rating3 Rating4 Rating5
Owner1 2013Q1 Open 8 3 2 4 5
Owner1 2013Q1 Closed 5 6 7 1 0
Owner2 2013Q1 Open 8 3 2 4 5
Owner2 2013Q1 Closed 5 6 7 1 0
Owner1 2013Q2 Open 8 3 2 4 5
Owner1 2013Q2 Closed 5 6 7 1 0
Owner2 2013Q2 Open 8 3 2 4 5
Owner2 2013Q2 Closed 5 6 7 1 0
Quarter doesn't have to be in the result if it can be used as a parameter in a stored procedure, all numbers are count based on the rating.
Thank you very much in advance. I can only think of some cumbersome loop method to do it, I believe there should be a more efficient way to get the result.
CREATE TABLE [dbo].[Ticket](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Owner] [varchar](10) NULL,
[CreatedOn] [datetime] NULL,
[Rating] [varchar](10) NULL,
[State] [varchar](10) NULL
) ON [PRIMARY]
GO
--Open data for each quarter in 2013
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-01-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-02-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-02-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-02-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-04-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-05-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-06-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-08-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-09-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-10-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-11-01', 'Rating2', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-12-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-12-01', 'Rating3', 'Open')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-12-01', 'Rating3', 'Open')
--Closed data for each quarter in 2013
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-01-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-02-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-02-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-02-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-03-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-04-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner1', '2013-05-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-05-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-06-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner2', '2013-07-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating1', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner3', '2013-07-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-08-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-09-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-10-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-11-01', 'Rating2', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-12-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-12-01', 'Rating3', 'Closed')
insert into Ticket (Owner, CreatedOn, State) Values ('Owner4', '2013-12-01', 'Rating3', 'Closed')
February 21, 2014 at 3:34 pm
Can you explain the desired output? If this is a count of values for each period I am a bit lost. You have a total of 82 rows in the table but the sum of all your values posted is 164. That means either I don't understand what you are trying to do or you are somehow counting some rows more than once.
Also, you might want to look at your insert statements. They don't have all the columns listed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2014 at 4:50 pm
I, too, got different totals; mine add up to 82, the number of sample rows of data.
SELECT
Owner, CAST(YEAR(Quarter) AS char(4)) + 'Q' + CAST(DATENAME(QUARTER, Quarter) AS char(1)) AS Quarter, State,
Rating1, Rating2, Rating3, Rating4, Rating5
FROM (
SELECT
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0) AS Quarter, State,
SUM(CASE WHEN Rating = 'Rating1' THEN 1 ELSE 0 END) AS Rating1,
SUM(CASE WHEN Rating = 'Rating2' THEN 1 ELSE 0 END) AS Rating2,
SUM(CASE WHEN Rating = 'Rating3' THEN 1 ELSE 0 END) AS Rating3,
SUM(CASE WHEN Rating = 'Rating4' THEN 1 ELSE 0 END) AS Rating4,
SUM(CASE WHEN Rating = 'Rating5' THEN 1 ELSE 0 END) AS Rating5
FROM dbo.Ticket
GROUP BY
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0), State
) AS derived
ORDER BY
Owner, Quarter, State
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2014 at 5:31 pm
Sorry if the fake data confused you.
February 21, 2014 at 6:01 pm
ScottPletcher (2/21/2014)
I, too, got different totals; mine add up to 82, the number of sample rows of data.
SELECT
Owner, CAST(YEAR(Quarter) AS char(4)) + 'Q' + CAST(DATENAME(QUARTER, Quarter) AS char(1)) AS Quarter, State,
Rating1, Rating2, Rating3, Rating4, Rating5
FROM (
SELECT
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0) AS Quarter, State,
SUM(CASE WHEN Rating = 'Rating1' THEN 1 ELSE 0 END) AS Rating1,
SUM(CASE WHEN Rating = 'Rating2' THEN 1 ELSE 0 END) AS Rating2,
SUM(CASE WHEN Rating = 'Rating3' THEN 1 ELSE 0 END) AS Rating3,
SUM(CASE WHEN Rating = 'Rating4' THEN 1 ELSE 0 END) AS Rating4,
SUM(CASE WHEN Rating = 'Rating5' THEN 1 ELSE 0 END) AS Rating5
FROM dbo.Ticket
GROUP BY
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0), State
) AS derived
ORDER BY
Owner, Quarter, State
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
Just don't understand why you have
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
at the end of the query?
February 21, 2014 at 6:02 pm
Sean Lange (2/21/2014)
Also, you might want to look at your insert statements. They don't have all the columns listed.
Yes you are right, sorry about it, fake data.
February 21, 2014 at 9:03 pm
halifaxdal (2/21/2014)
ScottPletcher (2/21/2014)
I, too, got different totals; mine add up to 82, the number of sample rows of data.
SELECT
Owner, CAST(YEAR(Quarter) AS char(4)) + 'Q' + CAST(DATENAME(QUARTER, Quarter) AS char(1)) AS Quarter, State,
Rating1, Rating2, Rating3, Rating4, Rating5
FROM (
SELECT
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0) AS Quarter, State,
SUM(CASE WHEN Rating = 'Rating1' THEN 1 ELSE 0 END) AS Rating1,
SUM(CASE WHEN Rating = 'Rating2' THEN 1 ELSE 0 END) AS Rating2,
SUM(CASE WHEN Rating = 'Rating3' THEN 1 ELSE 0 END) AS Rating3,
SUM(CASE WHEN Rating = 'Rating4' THEN 1 ELSE 0 END) AS Rating4,
SUM(CASE WHEN Rating = 'Rating5' THEN 1 ELSE 0 END) AS Rating5
FROM dbo.Ticket
GROUP BY
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0), State
) AS derived
ORDER BY
Owner, Quarter, State
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
Just don't understand why you have
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
at the end of the query?
Here is the BOL entry for COMPUTE. http://technet.microsoft.com/en-us/library/ms181708.aspx
Please note this has been removed and this will not work with sql 2012.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 21, 2014 at 9:04 pm
halifaxdal (2/21/2014)
Sean Lange (2/21/2014)
Also, you might want to look at your insert statements. They don't have all the columns listed.
Yes you are right, sorry about it, fake data.
No problem. It was easy enough to fix. I was mostly confused by the output but it seems Scott figured out what you wanted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2014 at 8:29 am
halifaxdal (2/21/2014)
ScottPletcher (2/21/2014)
I, too, got different totals; mine add up to 82, the number of sample rows of data.
SELECT
Owner, CAST(YEAR(Quarter) AS char(4)) + 'Q' + CAST(DATENAME(QUARTER, Quarter) AS char(1)) AS Quarter, State,
Rating1, Rating2, Rating3, Rating4, Rating5
FROM (
SELECT
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0) AS Quarter, State,
SUM(CASE WHEN Rating = 'Rating1' THEN 1 ELSE 0 END) AS Rating1,
SUM(CASE WHEN Rating = 'Rating2' THEN 1 ELSE 0 END) AS Rating2,
SUM(CASE WHEN Rating = 'Rating3' THEN 1 ELSE 0 END) AS Rating3,
SUM(CASE WHEN Rating = 'Rating4' THEN 1 ELSE 0 END) AS Rating4,
SUM(CASE WHEN Rating = 'Rating5' THEN 1 ELSE 0 END) AS Rating5
FROM dbo.Ticket
GROUP BY
Owner, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CreatedOn), 0), State
) AS derived
ORDER BY
Owner, Quarter, State
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
Just don't understand why you have
COMPUTE SUM(Rating1), SUM(Rating2), SUM(Rating3), SUM(Rating4), SUM(Rating5)
at the end of the query?
Sorry, I should of explained that. It was just to do quick-and-dirty totals of all the Ratingn counts. You can remove the COMPUTE.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 24, 2014 at 11:56 am
Things getting complicated now:
The script help me get the sum for the specific reporting period, now I am asked to include the ""so far" sum:
for example, currently the script will generate result of:
Owner12013Q1Closed44000
Owner12013Q1Open 44000
Owner12013Q2Closed41400
Owner12013Q2Open 41400
I was asked to generate result this way (be accumulated):
Owner12013Q1Closed44000
Owner12013Q1Open 44000
Owner12013Q2Closed85400
Owner12013Q2Open 85400
For each rating, 2013Q2 will include result of 2013Q1, 2013Q3 will include result of 2013Q2, and so on.
I wonder other than creating a temp table to hold the interim result, if there is a simpler way to get that?
Thank you very much in advance.
February 25, 2014 at 5:09 pm
Sounds like you want a rolling 3 quarters total, is that right?
Various methods exist and this article compares the performance you can expect:
Calculating Values within a Rolling Window in Transact SQL[/url]
The example in the article is rolling 12 months but can be adapted to your case. The fastest method is going to end up being putting it into a temp table band using a Quirky Update.
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
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply