May 22, 2011 at 8:24 am
i have a table with below structure
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 output parameter will be like a table
parameter with columns ClientID,StatusStartDate,StatusEndDate(to be generated dynamically)
and Status.
Below is the output
For Example: If I pass Client ID 100 then the Output should be like
ClientID StatusStartdate StatusEndDate Status
100 April 2011 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 1
For Example: If I pass Client ID 300 then the Output should be like
300 Jan 2010 1
For Example: If I pass Client ID 400 then the Output should be like
400 Feb 2011 2
400 Feb 2010 Jan 2011 1
Could someone please help me with the above Stored Procedure.
Thanks a lot for helping.
May 22, 2011 at 8:39 am
Lucky9 (5/22/2011)
i have a table with below structureID 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 output parameter will be like a table
parameter with columns ClientID,StatusStartDate,StatusEndDate(to be generated dynamically)
and Status.
Below is the output
For Example: If I pass Client ID 100 then the Output should be like
ClientID StatusStartdate StatusEndDate Status
100 April 2011 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 1
For Example: If I pass Client ID 300 then the Output should be like
300 Jan 2010 1
For Example: If I pass Client ID 400 then the Output should be like
400 Feb 2011 2
400 Feb 2010 Jan 2011 1
Could someone please help me with the above Stored Procedure.
Thanks a lot for helping.
Table structure?
Follow the guidelines listed in the following link for a better response:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
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 22, 2011 at 8:46 am
I am sorry, I forgot about posting the scripts.
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)
May 22, 2011 at 9:12 am
How are you going to return StatusEndDate when it is not in your table?
I would not use a reserved words when naming an object, i.e. Table and Column name is status.
You have some bad dates in your sample data.
Insert into Status(ClientID,StatusStartdate,Status)
Values(200,'2011-02-30',1) -- Bad Date
Insert into Status(ClientID,StatusStartdate,Status)
Values(400,'2010-02-30',1) -- Bad Date
Insert into Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-30',2) -- Bad Date
Have you tried to create an SP or are you just looking for someone to write it for you.
You need to create an SP with a @ClientID input paramater
Then perform a SELECT Statement on the table and use the @ClientID input paramater
in the WHERE Clause to filter the records.
Another option would be to create a Table Variable, Insert the records into the Table variable and return contents of the Table.
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 22, 2011 at 9:52 am
This is a very simple example.
Perhaps you can make it better?
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'usp_return_Client' AND type = 'P')
DROP PROCEDURE usp_return_Client
GO
CREATE PROCEDURE usp_return_Client
@ClientID int
AS
SELECT ClientID,StatusStartDate,Status
FROM Status
WHERE ClientID = @ClientID
GO
EXEC usp_return_Client @ClientID = 100
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 22, 2011 at 10:19 am
I am sorry again, for the wrong dates
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-22',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-22',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-22',2)
Even though there is no Status End Date, i have to generate the Status End date dynamically while
returning the values in the select statement to the application..
I am beginer of SQL Server and i tried to create the stored procedure but i cannot make it..
i am trying from past 2 days..
I need someone who can help in writing the stored procedure..
May 22, 2011 at 10:25 am
Lucky9 (5/22/2011)
I am sorry again, for the wrong datesCreate 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-22',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-22',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-22',2)
Even though there is no Status End Date, i have to generate the Status End date dynamically while
returning the values in the select statement to the application..
I am beginer of SQL Server and i tried to create the stored procedure but i cannot make it..
i am trying from past 2 days..
I need someone who can help in writing the stored procedure..
You are still missing StatusEndDate.
You need to add the column and populate it.
The Stored Procedure that I provided you is very basic and has been tested.
That you give you a start.
Do you want to provide your Stored Procedure and I can tell you what you are doing wrong?
EXEC usp_return_Client @ClientID = 100
ClientID StatusStartDate Status
----------- ----------------------- -----------
100 2010-01-28 00:00:00.000 1
100 2010-03-20 00:00:00.000 1
100 2010-05-30 00:00:00.000 2
100 2011-04-30 00:00:00.000 3
(4 row(s) affected)
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 22, 2011 at 12:47 pm
Lucky9 (5/22/2011)
I am sorry again, for the wrong datesCreate 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-22',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-22',1)
Insert into Table Status(ClientID,StatusStartdate,Status)
Values(400,'2011-02-22',2)
Even though there is no Status End Date, i have to generate the Status End date dynamically while
returning the values in the select statement to the application..
I am beginer of SQL Server and i tried to create the stored procedure but i cannot make it..
i am trying from past 2 days..
I need someone who can help in writing the stored procedure..
First, I would recommend that you stop focusing on the creation of a stored procedure and focus instead on writing the query that you need. Once you have a working query - or set of code, we can then work on building the stored procedure.
Second, since you do not have a column that contains the StatusEndDate and you need to generate it on output - we need to understand how that is going to be generated.
Will the StatusEndDate be the same date as the 'next rows' StatusStartDate? Or, will it be one day less than the next rows StatusStartDate? What would the StatusEndDate be if there is no next row?
And remember, since a table has no inherent order - we have to define that order. By next row I am assuming that is logically the row that has the next StatusStartDate in ascending order. I could be wrong - but it appears to be what you are looking for.
Here is one approach to get the 'next' StatusStartDate:
With myCTE(ID ,ClientID ,StatusStartDate ,Status ,rownum)
As (
Select s.ID
,s.ClientID
,s.StatusStartDate
,s.[Status]
,row_number() Over(Partition By ClientID Order By StatusStartDate) As rownum
From #Status s
)
Select a.ID
,a.ClientID
,a.StatusStartDate
,b.StatusStartDate As StatusEndDate
,a.Status
From myCTE a
Left Join myCTE b On b.ClientID = a.ClientID
And b.rownum = a.rownum + 1;
This assumes that the StatusEndDate is the same as the next logical row for the ClientID. If you need that to be one day less, then you would use the following:
With myCTE(ID ,ClientID ,StatusStartDate ,Status ,rownum)
As (
Select s.ID
,s.ClientID
,s.StatusStartDate
,s.[Status]
,row_number() Over(Partition By ClientID Order By StatusStartDate) As rownum
From #Status s
)
Select a.ID
,a.ClientID
,a.StatusStartDate
,dateadd(day, -1, b.StatusStartDate) As StatusEndDate
,a.Status
From myCTE a
Left Join myCTE b On b.ClientID = a.ClientID
And b.rownum = a.rownum + 1;
Once we have the query working, then we can work on creating a stored procedure. For the stored procedure we will need to know what you want the procedure to do and how it is going to be called. We need to know what input parameters are going to be supplied, what output parameters are required and what the resultset should look like for the caller.
Edit: I used a temp table (#Status) instead of the permanent table called Status. If that is the real name for your table, I recommend that you change that to a different name. Status is a keyword...
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 22, 2011 at 12:53 pm
I noticed that you have not defined a Primary Key or a Clustered Index.
Why do you need the Surrogate Key ID when you have a Natural Key ClientID?
How do you derive the StausEndDate?
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 22, 2011 at 1:20 pm
Welsh Corgi (5/22/2011)
I noticed that you have not defined a Primary Key or a Clustered Index.Why do you need the Surrogate Key ID when you have a Natural Key ClientID?
How do you derive the StausEndDate?
The ClientID does not uniquely identify the row in this case. A compound primary key would be required which would include at least ClientID and StatusStartDate.
I would hope (assume?) that the PK is defined on the surrogate ID with the clustered index defined on the ID column. The PK could be defined on both ClientID and StatusStartDate - supported by a non-clustered unique index, with a unique clustered index on the ID column. But, most people wouldn't consider that type of structure - mostly because that would mean they would have to include both columns in FK relationships and this would *complicate* the joins.
If both the ClientID and StatusStartDate were queried in most (80/20 rule) queries on related tables - I probably would use that structure to simplify those other queries. It would really depend on the usage and requirements in the system.
Having both columns in the related tables would allow querying of those related tables without having to join back to the primary table - for example, to get the ClientID or the StatusStartDate. However, since this also has a status column - that might not work so well since I would have to assume the status of the data would be needed a lot.
So - I probably would use the surrogate key as the PK with a clustered index and a unique non-clustered index on ClientID and StatusStartDate and suffer the extra performance hit by having to always join to this table to get the client and status start date.
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 22, 2011 at 1:28 pm
Jeffrey Williams-493691 (5/22/2011)
Welsh Corgi (5/22/2011)
IThe ClientID does not uniquely identify the row in this case. A compound primary key would be required which would include at least ClientID and StatusStartDate.
Dah, what was I thinking... I know better than that.
A Primary Key (Unique Identifier) should be defined on the ID Column. A Clustered Index should be defined and a non-clustered index on the ClientID Column should be defined as well.
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 25, 2011 at 9:15 pm
Hello Jeff,
I am sorry for the late reply, thanks a lot for your reply
But actually if values in the table are like
ClientID StatusStartDate Status
----------- ----------------------- -----------
100 2010-01-28 1
100 2010-03-20 1
100 2010-05-30 2
100 2011-04-30 3
from the above table values then i need a SQL code which provides the result set as belows
clientID startdate enddate(generatedynamically) Status
100 April 2011 Null 3
100 May 2010 March 2011 2
100 January 2010 April 2010 1
According to your code it displays me like below
100 2010-01-28 2010-02-20 1
100 2010-03-20 2010-04-30 1
100 2010-05-30 2011-03-20 2
100 2011-04-30 Null 3
Thanks again everyone..please someone help me with the task.
May 26, 2011 at 10:43 pm
Hey, first let us know the logic of generating the end date.
May 27, 2011 at 7:41 am
sqlzealot-81 (5/26/2011)
Hey, first let us know the logic of generating the end date.
He decided not to answer your question and opened up a new post.:hehe:
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply