April 18, 2012 at 11:32 am
I need to capture two statistics:
1) The total count of legal Case_IDs that take <30, 30-60, and >60 days to go from Proposed to Resolved status, aggregated by Legal Area (WA_Tenant, WA_Traffic, WA_Consumer)
2) The total count of legal Case_IDs sitting in Proposed, Active, Resolved status for <30days, 30-60days, >60 days (which will show where in the flow cases are sitting the longest).
The DDL to create the table.
create table Case_Review
(Case_ID int,
Legal_Omn varchar(30),
PPL_Area varchar(30),
Revision int,
Review_Status varchar(30),
Change_Date datetime);
insert into Case_Review
values
(220, 'Civil', 'WA_Tenant', 1, 'Proposed', '2012-04-17 12:17'),
(220, 'Civil', 'WA_Tenant', 2, 'Proposed', '2012-04-17 14:05'),
(220, 'Civil', 'WA_Tenant',3, 'Proposed', '2012-04-18 17:03'),
(220, 'Civil', 'WA_Tenant',4, 'Proposed', '2012-04-18 17:05'),
(220, 'Civil', 'WA_Tenant', 5, 'Active', '2012-04-19 12:20'),
(220,'Civil', 'WA_Tenant', 6, 'Active', '2012-04-19 12:45'),
(220, 'Civil', 'WA_Tenant',7, 'Active', '2012-04-20 08:05'),
(220,'Civil', 'WA_Tenant', 8, 'Resolved', '2012-05-27 12:21'),
(220, 'Civil', 'WA_Tenant',9, 'Resolved', '2012-05-27 12:45'),
(220, 'Civil', 'WA_Tenant',10, 'Resolved', '2012-05-28 12:00'),
(220, 'Civil', 'WA_Tenant',11, 'Resolved', '2012-05-28 12:01'),
(230, 'Civil', 'WA_Traffic', 1, 'Proposed', '2012-04-17 01:00'),
(230, 'Civil', 'WA_Traffic', 2, 'Proposed', '2012-05-29 07:00'),
(230, 'Civil', 'WA_Traffic', 3, 'Active', '2012-05-29 12:00'),
(230,'Civil', 'WA_Traffic', 4, 'Active', '2012-05-29 12:05'),
(230, 'Civil', 'WA_Traffic', 5, 'Active', '2012-05-29 12:00'),
(230, 'Civil', 'WA_Traffic', 6, 'Closed', '2012-06-01 13:00'),
(230, 'Civil', 'WA_Traffic', 7, 'Closed', '2012-06-01 13:01'),
(260, 'Civil', 'WA_Consumer', 1, 'Proposed', '2012-04-17 14:00'),
(260, 'Civil', 'WA_Consumer', 2, 'Active', '2012-05-20 14:25'),
(260, 'Civil', 'WA_Consumer',3, 'Active', '2012-07-10 15:00'),
(260, 'Civil', 'WA_Consumer',4, 'Closed', '2012-10-20 15:20');
IN TERMS OF T-SQL NEED TO DO THIS
1.eliminate the rows containing redundant transition statuses which don’t represent actual transition.
2.count the number of WA_Tenant case_ids that take <30days to go from proposed->active status and put under <30. Do same for WA_Traffic, and WA_Consumer
3.SUM(of date times of all WA_Tenant case_ids that go from proposed status->closed in under 30 days and put under <30), then SUM(of date times of all WA_Tenant case_ids that go from proposed status->closed in 30-60 days and put under 30-60, and SUM(of date times of all WA_Tenant case_ids that go from proposed status->closed in more than 60 days and put under >60). Do same for WA_Traffice, and WA_Consumer.
Outcome will look like this
Civil<30 days30-60 days>60 days
WA_Tenant1
Proposed1
Active 1
WA_Traffic1
Proposed1
Active1
WA_Consumer11
Proposed1
Active1
Outcome to look like this:
http://www.flickr.com/photos/54066173@N08/7091063891/">
http://www.flickr.com/photos/54066173@N08/7091063891/
Can SOMEONE please show me how to do this?
--Quote me
April 18, 2012 at 12:37 pm
Excellent job posting ddl and sample!!! However I don't even being to understand what you want for output. I can't view your image. One solution is to create a temp table and then use inserts to demonstrate what your desired output would be.
A clear explanation of the desired results would be really helpful too.
_______________________________________________________________
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/
April 18, 2012 at 1:04 pm
Not sure when the thread starter will be back to the thread, but it seems that the link is messed up somehow; it points to Flickr, but appends SSC's URL to the front of it for some reason. Here's the fixed link:
http://www.flickr.com/photos/54066173@N08/7091063891/"> http://www.flickr.com/photos/54066173@N08/7091063891/
- 😀
April 18, 2012 at 1:10 pm
thank you very much. That's it.
I just realized I need to update the image on the link as it isn't correct, entirely!!! wait.
--Quote me
April 18, 2012 at 1:20 pm
Please post it in a format that is not on flickr. I am not the only person who has a net nanny at work that will not allow them to view your image. That effectively removes some people from the possible list of people willing to help.
_______________________________________________________________
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/
April 18, 2012 at 1:26 pm
Here's how it's supposed to look.
http://www.flickr.com/photos/54066173@N08/">
http://www.flickr.com/photos/54066173@N08/
You'll see when you create the table using the DDL, that based on the dates of the transitions, two case_id (220, 230) took over 30 days to go from Proposed to Resolved Status. One case_id (260) took >60days. That's overall the performance for all case_ids in the Legal_Omn: Civil.
That is why on the horizontal axis, for Legal_Omn:Civil there is a 2 in the 30-60 days column and a 1 in the >60 days column.
Next, per legal category WA_Tenant, there was one ticket sitting for <30 days in Proposed status, and that same ticket was then in Active status for 30-60. So, a 1 is under each respective column.
Does the link help?
--Quote me
April 18, 2012 at 1:27 pm
can someone tell me how to embedd an actual image?? here on this forum?
--Quote me
April 18, 2012 at 1:29 pm
When posting...scroll down...there is a button down there for attachments. 😀
_______________________________________________________________
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/
April 18, 2012 at 1:31 pm
polkadot (4/18/2012)
can someone tell me how to embedd an actual image?? here on this forum?
What we are trying to tell you is to NOT use an image. Create a table with the columns for the report. Create a series of INSERT INTO statements that populates that table with the expected results. When we run the code to create and populate the table and then run a select from that table what we see is what you are expecting based on the sample data you also provided.
We can then use this table to test the results of our work.
April 18, 2012 at 1:46 pm
There's my attachment. Bingo. Thanks Carpal.
Lyn, I've already provided the DDL, which is what I think you're asking me to do. If I could create a query that provided the intended results, I wouldn't be posting the question. I certainly am not able to create a visual using T-SQL without your help. Pleease!!!
--Quote me
April 18, 2012 at 1:48 pm
polkadot (4/18/2012)
There's my attachment. Bingo. Thanks Carpal.Lyn, I've already provided the DDL, which is what I think you're asking me to do. If I could create a query that provided the intended results, I wouldn't be posting the question. I certainly am not able to create a visual using T-SQL without your help. Pleease!!!
ok thats even more confusing. on the first line you have a blank for < 30 a 2 for 30-60 and a 1 for > 60. the only one that makes sense is the > 60. check over it and if that correct please explain a little more on how you want your totals.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 1:56 pm
polkadot (4/18/2012)
There's my attachment. Bingo. Thanks Carpal.Lyn, I've already provided the DDL, which is what I think you're asking me to do. If I could create a query that provided the intended results, I wouldn't be posting the question. I certainly am not able to create a visual using T-SQL without your help. Pleease!!!
Yes, you provided the DDL for the tables, sample data. For the expected results, instead of a picture, I was trying to get you to create a table that would hold the data as if it were your report, and to populate it with your expected results based on the sample data you provided.
This would give us something to actually test against using SQL instead of our eyes. It gives you something to test against as well.
I was not asking you to create a query that provided the intended results. Creating the expected results would be a manual process.
April 18, 2012 at 1:59 pm
polkadot (4/18/2012)
If I could create a query that provided the intended results, I wouldn't be posting the question. I certainly am not able to create a visual using T-SQL without your help. Pleease!!!
As I said...
One solution is to create a temp table and then use inserts to demonstrate what your desired output would be.
Something like this.
create table #Result
(
Col1 varchar(50),
Col2 varchar(50),
Col3 varchar(50),
Col4 varchar(50)
)
insert #Result
select '', '<30 days', '30-60 days', '>60 days' union all
select 'Civil', '', '2', '1' union all
select 'WA_Tenant', '', '', ''
...
This makes your question and desired output extremely clear.
Regardless of the format of your desired output it is totally unclear how to get from your sample data to this output. I don't mean the query logic, I mean what is the "formula" or business rules for what you want.
_______________________________________________________________
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/
April 18, 2012 at 2:13 pm
YOU'RE RIGHT. That 1 next to WA_Consumer under <30 days was errant. There were many errors. I'm removing and updating attachment.
OK, highlevel, better explained.
There are two things this report needs to show. How many cases (in the CIVIL category, which is the only one in this table) are taking <30, 30-60, and > 60 days to resolve. That's the first line next to Civil. 2 cases took more than 30 days to resolve, and 1 case more than 60. That's 3 out of the 3 cases, which is all that I have in the table.
Now, of those three cases, WA_Tenant spent <30 days in Proposed status before transitioning to Active. Then it took an additional 30-60 days to resolve.
WA_Traffic spend <30 days in Proposed status before transitioning to Active. Then it took an additional. >60 days to resolve.
WA_Consumer took 30-60 days in Proposed status before transitioning to Active. Then it took an additional >60 to resolve.
--Quote me
April 18, 2012 at 2:28 pm
You are going to have to do a better job explaining what you want here.
What are the numbers in the first row??
We are not familiar with your business or your needs and your explanation just doesn't make sense to me.
Are the number in the grids the count of cases that are in that status? So for example you get the count of cases where the datediff between min and max (Change_Date) is less than 30 days and status = 'Proposed'?
Then for the second column it would be datediff of min and max (Change_Date) where status = 'Active'?
Where does 'Resolved' come into this? Need some details before we can go any further.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 59 total)
You must be logged in to reply to this topic. Login to reply