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/
- 😀
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/
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/
April 18, 2012 at 2:35 pm
It looks like the first line is a grand total for Civil.
Part of the problem is that you're using T-SQL as a reporting tool and it's not. You'd be much better off using a true reporting tool like SSRS, Crystal Reports, or even Excel. Sure, you need to do some pre-processing in T-SQL, but you shouldn't try to do everything in T-SQL.
First you need to aggregate based on each case in order to determine the length of time it was in each status. Here is a sample of that:
SELECT Case_ID, Legal_Omn, PPL_Area
,MIN(CASE WHEN Review_Status = 'Proposed' THEN Change_Date END) AS Proposed
,MIN(CASE WHEN Review_Status = 'Active' THEN Change_Date END) AS Active
,MIN(CASE WHEN Review_Status = 'Resolved' THEN Change_Date END) AS Resolved
,MIN(CASE WHEN Review_Status = 'Closed' THEN Change_Date END) AS Closed
FROM #Case_Review
GROUP BY Case_ID, Legal_Omn, PPL_Area
From that, you should be able to easily calculate your length of time in each status.
After that, you should ship it off to your reporting tool to do rest. You are going to have to pivot the results to get your counts based on status, but each of the reporting tools has a feature that will do that easily.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply