September 7, 2012 at 5:34 am
Hi
I need to desin a fucntion which takes three parameters
calculateTime(@threadStatusId,@fromDate,@toDate)
I have table Report which contains the folowwing data
There are 10 types of possible frmStatusId and toStatusId.
PrdId RptId ThrdId ChngdDate frmStatusId toStatusId
1 101 201 2012-08-01 40 41
1 101 201 2012-08-03 41 56
1 101 201 2012-08-04 56 56
1 101 201 2012-08-06 56 44
1 101 201 2012-08-08 44 47
1 102 202 2012-08-20 40 41
1 102 202 2012-08-22 41 42
1 102 202 2012-08-23 42 47
1 102 202 2012-08-25 47 56
and so on .........
I want to calculate the time in which status of the thread is in 56
Moreover I want it for all dates between fromDate and toDate.
Suppose fromDate='2012-09-01' and toDate='2012-09-04' then result should be like
PrdId RptId ThrdId TimeAssigned Date
1 101 201 ---- 2012-09-01
1 101 201 ---- 2012-09-02
1 101 201 ---- 2012-09-03
1 101 201 ---- 2012-09-04
It means that till the considered date time it spent in state 56.
I am completely new and this looks very complex for me. Kindly help how to do this in function.
Is it possible to do in normal SELECT sql Kindly suggest.
September 7, 2012 at 8:09 am
Hi and welcome to SSC.
This can certainly be done but in order to help you need to first help us. You will need to post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature about best practices. Then we can help pretty quickly.
_______________________________________________________________
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/
September 7, 2012 at 8:24 am
tribhuwan.tiwari (9/7/2012)
HiI need to desin a fucntion which takes three parameters
calculateTime(@threadStatusId,@fromDate,@toDate)
I have table Report which contains the folowwing data
There are 10 types of possible frmStatusId and toStatusId.
PrdId RptId ThrdId ChngdDate frmStatusId toStatusId
1 101 201 2012-08-01 40 41
1 101 201 2012-08-03 41 56
1 101 201 2012-08-04 56 56
1 101 201 2012-08-06 56 44
1 101 201 2012-08-08 44 47
1 102 202 2012-08-20 40 41
1 102 202 2012-08-22 41 42
1 102 202 2012-08-23 42 47
1 102 202 2012-08-25 47 56
and so on .........
I want to calculate the time in which status of the thread is in 56
Moreover I want it for all dates between fromDate and toDate.
Suppose fromDate='2012-09-01' and toDate='2012-09-04' then result should be like
PrdId RptId ThrdId TimeAssigned Date
1 101 201 ---- 2012-09-01
1 101 201 ---- 2012-09-02
1 101 201 ---- 2012-09-03
1 101 201 ---- 2012-09-04
It means that till the considered date time it spent in state 56.
I am completely new and this looks very complex for me. Kindly help how to do this in function.
Is it possible to do in normal SELECT sql Kindly suggest.
Also, in addtion to what Sean mentioned, it would help if your examples matched your sampe data. Above you started by showing data with dates in August, but when you showed what you were trying to accomplish you showed dates in September. Doesn't help with the understanding as we can't look back at the raw data. When you post your sample data and expected results please be sure that the expected results are based on the sample data.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply