May 27, 2011 at 11:59 am
Welsh Corgi (5/27/2011)
Kiara (5/27/2011)
Lucky9 (5/27/2011)
Yes sir, i need the earliest date of the particular statusOk. And how do you calculate the StatusEndDate?
You are confusing him. :hehe:
He was already asked that question and Jeffrey proved him with a potential solution in the inital post but he decided not to answer and open a new thread. 🙂
I read the initial thread. I also noticed that he tends to only answer the last question in a list of questions... so I figured I'd try to ask differently. *shrug* Either he will answer it or he won't.
-Ki
May 27, 2011 at 12:23 pm
Lucky9 (5/27/2011)
Yes sir, i need the earliest date of the particular status
I hope that this urgent requirement is only a homework assignment? :w00t:
What you have stated so far is unconventional.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 27, 2011 at 12:42 pm
Hello all,
I am very sorry for the late replies...
i been busy at work and have to attend the meetings...whenever i get time
am answering the questions...
Status End Date is calculated based on the status Start date of the previous status...If you look at the table data and output am expecting
you can observe the status start date of latest status and status end date
of the previous has some connection.
May 27, 2011 at 12:49 pm
Lucky9 (5/27/2011)
Hello all,I am very sorry for the late replies...
i been busy at work and have to attend the meetings...whenever i get time
am answering the questions...
Status End Date is calculated based on the status Start date of the previous status...If you look at the table data and output am expecting
you can observe the status start date of latest status and status end date
of the previous has some connection.
Do you have a crystal ball that you are not telling us about? 😎
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 27, 2011 at 12:52 pm
Ok Sir am working on the development of the SQL code... Am trying on it...
Thanks everyone for the help and sorry for your time...
Thanks a lot again to everyone for help...
May 27, 2011 at 1:00 pm
Welsh Corgi (5/27/2011)
Lucky9 (5/27/2011)
Yes sir, i need the earliest date of the particular statusI hope that this urgent requirement is only a homework assignment? :w00t:
What you have stated so far is unconventional.
Sorry I did not get back to you on that other thread. I have been quite busy and wasn't able to respond.
Now, in the code I provided before - is the calculated Status End date correct? I provided 2 examples, one where the Status End Date is calculated as the Status Start Date of the 'next' logical row and one where it is one day less.
Looking at that solution provided - the only thing I see as a problem is that you now have 2 rows for status = 1. The end dates appear correct where there is only a single row for a status.
If you take the minimum StatusStartDate - and the maximum StatusEndDate, grouped by the ClientID and Status - would that not get you the correct dates?
If so, you could look at adding a MIN(StatusStartDate) OVER(Partition By ClientID, Status) to the CTE. Or, you could just use a GROUP BY on the final select. Not sure which is going to be best for you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2011 at 1:03 pm
Yes Sir- Now i got it...
Thank you so much i have used the Group by clause...thanks sir
you are so great...thanks a lot again
May 27, 2011 at 1:04 pm
Lucky9 (5/27/2011)
Ok Sir am working on the development of the SQL code... Am trying on it...
If you ask the provide answers to the right questions it is highly probable that you get a solution to your problem. You need to specify the requirements and respond to the questions that you are asked by those that are trying to help you.
Does that sound fair?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 27, 2011 at 1:09 pm
I am sorry Welsh...
I didn't mean not to answer the questions...I was very busy and didn't find time to respond..
Yes you are right if i respond to the questions...then only i can get the solution...
Sorry sir..will make sure that i don't repeat it again...
sorry and apologize for the time...
May 27, 2011 at 1:17 pm
Jeffrey Williams-493691 (5/27/2011)
Welsh Corgi (5/27/2011)
Lucky9 (5/27/2011)
If so, you could look at adding a MIN(StatusStartDate) OVER(Partition By ClientID, Status) to the CTE. Or, you could just use a GROUP BY on the final select. Not sure which is going to be best for you.
Jeffrey,
Not everyone that has worked with SQL Server 2000 and below is familiar with or proficient with CTE, OVER Partition By, RowNumber, etc.
Would you please consider adding some comments to your code that you previously provided ?
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 27, 2011 at 2:46 pm
EDIT: Removing my rude and unnecessary comment.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 27, 2011 at 3:02 pm
ok, I will be careful what I post to this forum.
If someone is stating that they do not know how to create a Stored Procedure as in this thread perhaps you should insist that they post to another forum as well.:-)
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 27, 2011 at 3:52 pm
Welsh Corgi (5/27/2011)
ok, I will be careful what I post to this forum.If someone is stating that they do not know how to create a Stored Procedure as in this thread perhaps you should insist that they post to another forum as well.:-)
Thank you.
<opens mouth, shuts it> Good point. :blush: Apologies.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 27, 2011 at 4:30 pm
Welsh Corgi (5/27/2011)
Jeffrey Williams-493691 (5/27/2011)
Welsh Corgi (5/27/2011)
Lucky9 (5/27/2011)
If so, you could look at adding a MIN(StatusStartDate) OVER(Partition By ClientID, Status) to the CTE. Or, you could just use a GROUP BY on the final select. Not sure which is going to be best for you.Jeffrey,
Not everyone that has worked with SQL Server 2000 and below is familiar with or proficient with CTE, OVER Partition By, RowNumber, etc.
Would you please consider adding some comments to your code that you previously provided ?
Thank you.
Honestly, any questions on those can be much better answered by looking them up in books online than anything I can add.
As for how I am using the CTE - it is really just a derived table (inline view) so I can get the value from the row_number (or other windowed aggregate functions). One of the limitations of the windowing functions is that they cannot be used directly in a where clause - so, we have to use them in a CTE/derived table and then access the results from the outer query.
And - I am by no means proficient in their usage. 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2011 at 5:50 pm
ok...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply