May 26, 2011 at 9:59 pm
i have a table with below structure
Create Table Status
( ID int Identity(1,1),
ClientID int,
StatusStartDate Datetime,
Status Int)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2010-01-28',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2010-03-20',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2010-05-30',2)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2011-04-30',3)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(200,'2011-02-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(300,'2010-01-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2010-02-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-30',2)
ID ClientID StatusStartDate Status
1 100 2010-01-28 1
2 100 2010-03-20 1
3 100 2010-05-30 2
4 100 2011-04-30 3
5 200 2011-02-30 1
6 300 2010-01-30 1
7 400 2010-02-30 1
8 400 2011-02-30 2
I have to write a stored procedure were it should have an input
parameter Client ID and procedure should return the result set with columns ClientID,StatusStartDate,StatusEndDate(to be generated dynamically)
and Status.
Below is the output
For Example: If I pass Client ID 100 then according the above table the Output should be like
ClientID StatusStartdate StatusEndDate Status
100 April 2011 Null 3
100 May 2010 March 2011 2
100 Jan 2010 April 2010 1
For Example: If I pass Client ID 200 then the Output should be like
200 Feb 2011 Null 1
For Example: If I pass Client ID 300 then the Output should be like
300 Jan 2010 Null 1
For Example: If I pass Client ID 400 then the Output should be like
400 Feb 2011 Null 2
400 Feb 2010 Jan 2011 1
Could someone please help me with the above Stored Procedure.
Thanks a lot for helping.
May 26, 2011 at 10:12 pm
Lucky9 (5/26/2011)
i have a table with below structureCreate Table Status
( ID int Identity(1,1),
ClientID int,
StatusStartDate Datetime,
Status Int)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2010-01-28',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2010-03-20',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2010-05-30',2)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(100,'2011-04-30',3)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(200,'2011-02-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(300,'2010-01-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2010-02-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-30',2)
ID ClientID StatusStartDate Status
1 100 2010-01-28 1
2 100 2010-03-20 1
3 100 2010-05-30 2
4 100 2011-04-30 3
5 200 2011-02-30 1
6 300 2010-01-30 1
7 400 2010-02-30 1
8 400 2011-02-30 2
I have to write a stored procedure were it should have an input
parameter Client ID and procedure should return the result set with columns ClientID,StatusStartDate,StatusEndDate(to be generated dynamically)
and Status.
Below is the output
For Example: If I pass Client ID 100 then according the above table the Output should be like
ClientID StatusStartdate StatusEndDate Status
100 April 2011 Null 3
100 May 2010 March 2011 2
100 Jan 2010 April 2010 1
For Example: If I pass Client ID 200 then the Output should be like
200 Feb 2011 Null 1
For Example: If I pass Client ID 300 then the Output should be like
300 Jan 2010 Null 1
For Example: If I pass Client ID 400 then the Output should be like
400 Feb 2011 Null 2
400 Feb 2010 Jan 2011 1
Could someone please help me with the above Stored Procedure.
Thanks a lot for helping.
Duplicate post.
Post here:
http://www.sqlservercentral.com/Forums/Topic1112997-391-1.aspx#bm1113054
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 26, 2011 at 10:15 pm
Reason i have posted again because i did not get help with the previous post...
so i just gave some additional information in this post and posted again...
sorry for the confusion
but i need urgent help with this task...please some one help me
May 26, 2011 at 11:40 pm
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(200,'2011-02-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2010-02-30',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-30',2)
With this insert you will get error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
because till date in history, February was not able to cross after 29th.
Also All your insert statements are incorrect, it should be
Insert into Status instead of Insert into Table Status
Ok, coming to your requirement
I have to write a stored procedure were it should have an input
parameter Client ID and procedure should return the result set with columns ClientID,StatusStartDate,StatusEndDate(to be generated dynamically)
and Status.
Here is the code for Procedure:-
create proc SP @id int as
select * from status where id = @id
----------
Ashish
May 27, 2011 at 6:59 am
Lucky9 (5/26/2011)
Reason i have posted again because i did not get help with the previous post...so i just gave some additional information in this post and posted again...
sorry for the confusion
but i need urgent help with this task...please some one help me
In the original post you were ask for information but you never provided it.
You still never added the StatusEndDate Column that you are missing.
[http://www.sqlservercentral.com/Forums/Topic1112997-391-1.aspx#bm1113054
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 7:36 am
CREATE PROCEDURE proc_ClientDetails_select(@ClientId int)
AS
SELECT ClientID ,StatusStartDate ,StatsuEndDate,[Status]
FROM [Status]
WHERE ClientID = @ClientId
GO
Now, to execute this stored procdure you would pass the parameter as follows
exec proc_ClientDetails_select 100
Hope this helps!
**Your table is missing the statusEndDate column though , you need to add that first.
May 27, 2011 at 7:44 am
Guras (5/27/2011)
**Your table is missing the statusEndDate column though , you need to add that first.
He has already been told to do this in his original post.
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 8:08 am
Lucky9 (5/26/2011)
Reason i have posted again because i did not get help with the previous post...
You call over 10 replies 'No help'? Really?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2011 at 8:10 am
Hi everyone
Am sorry if i confused everyone..
I am not getting the result with the code provided by some Sr developers.
There is no StatusEndDate in the table, I have to generate it dynamically when i return the result set to the Application side...
and I have to merge the results based on the data provided...
I cannot get it by passing just the parameter with a select statement within the stored procedure...
for example in the table i have
ID(PK) client id status start Date Status
1 100 04/04/2011 3
2 100 02/02/2011 2
3 100 05/05/2010 1
4 100 03/03/2010 1
Below should be my result output to Application side
Client ID Status Start Date Status End Date(Dynamically) Status
100 April 2011 Null(As it is latest status) 3
100 February 2011 March 2011 2
100 March 2010 January 2011 1
If you observe the above result set am merging the status 1 as it as
2 records....
Please let me know if someone needs more Information...
I am sorry for the dates provided in the February month...
May 27, 2011 at 8:19 am
What exactly is wrong with the approach posted by Jeffrey at the original thread?
The ROW_NUMBER approach is the right direction to dynamically calculate the StatusEndDate.
Please refer to the ROW_NUMBER solution already provided and tell us what's wrong with it. Use your own sample data (preferrably the ones that are actually valid dates) and show the current result vs. your expected result.
May 27, 2011 at 8:22 am
Oh, I forgot: an issue open since last Sunday is unlikely to be urgent. If it still is, you might want to consider calling a consultant in. Or you could help us help you by answering the question we have instead of opening a new thread.
Remember: neither we know your business case nor can we look over your shoulder to see what you see...
May 27, 2011 at 9:33 am
Lucky9 (5/27/2011)
There is no StatusEndDate in the table, I have to generate it dynamically when i return the result set to the Application side...
I'll try this again. You've been asked several times *how* to generate it dynamically. Not what SQL to use - but, if I had to figure out the StatusEndDate by hand, how would I do that?
Until you give us that information, you aren't going to get a usable answer for calculating that return result.
and I have to merge the results based on the data provided...
I cannot get it by passing just the parameter with a select statement within the stored procedure...
for example in the table i have
ID(PK) client id status start Date Status
1 100 04/04/2011 3
2 100 02/02/2011 2
3 100 05/05/2010 1
4 100 03/03/2010 1
Below should be my result output to Application side
Client ID Status Start Date Status End Date(Dynamically) Status
100 April 2011 Null(As it is latest status) 3
100 February 2011 March 2011 2
100 March 2010 January 2011 1
If you observe the above result set am merging the status 1 as it as
2 records....
So - looks like you want only the earliest start date for a particular status?
Please let me know if someone needs more Information...
We've asked. But you'll need to help us by answering the questions people are asking you.
-Ki
May 27, 2011 at 9:38 am
Yes sir, i need the earliest date of the particular status
May 27, 2011 at 10:32 am
Lucky9 (5/27/2011)
Yes sir, i need the earliest date of the particular status
Ok. And how do you calculate the StatusEndDate?
-Ki
May 27, 2011 at 11:03 am
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. 🙂
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 - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply