Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2011-02-01 : 11:37:07
|
| how do I split open and closed dates in the same record..so that I can produce a monthly count of tickets opened..and closed?I have a table that lists trouble call tickets..each ticket has a create date..and a resolved date (if it is not still open)...I use excel pivot tables with chart to display a barchart with open ticket count and closed ticket count per month...my problem is the row contains both the open date..and the resolved date...when I use a pivot table with the opendate, its counts the open and resolved (ignoring the date, but recognizing that there is an entry in the field, so it counts it...it doesnt matter that the closed date was in a following month..it just counts it in the same month....example:Create_Time Resolved_Time 07/12/07 04/28/09 in the pivot table, if create_time is a row label and create_time and resolved_time are summation (count) values... the pivot will show july with a create and resolved count as 2...the closest I have come is putting create_time and Resolved_time in columns...it will the split them into july of 07 and april of 09...but how do I count the columns?current solution is to split the record into two records (one for open..one for closed) and filter for open or closed tickets..but that doubles the size of the table....HELP! |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-01 : 17:40:12
|
I assume your Resolved_Time field can contain nulls when not resolved so you canchange your count of that field to a SUM(Case when Resolved_Time is not null then 1 else 0 end )I am guessing your condition is "When Resolved_Time is not null"
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
crosstab ?...or pivot?