locked
same table for two applications RRS feed

  • Question

  • Hi,

     im using two tables  for two diferent   applications because fields in both applications are common. except in one application has one column extra ClientID

    below are two tables

    CREATE TABLE [dbo].[EmployeeTSDates](
    	[TSID] [int] IDENTITY(1,1) NOT NULL,
    	[ClientID] [int] NULL,
    	[EmployeeName] [varchar](50) NULL,
    	[TSStartDate] [datetime] NULL,
    	[TSEndDate] [datetime] NULL,
     CONSTRAINT [PK_EmployeesTable]
    
    
    
    
    
    CREATE TABLE [dbo].[EmpTimesheet](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[ClientID] [int] NULL,
    	[EmpID] [int] NOT NULL,
    	[EmpName] [varchar](50) NULL,
    	[StartDate] [datetime] NULL,
    	[EndDate] [datetime] NULL,
    	[day] [varchar](50) NULL,
    	[date] [datetime] NULL,
    	[Service] [nvarchar](max) NULL,
    	[Venue] [varchar](50) NULL,
    	[Project] [varchar](50) NULL,
    	[StartTime] [varchar](50) NULL,


    In one applicaion i dont need ClientID so CLientID isalways empty  but in another application it has ClientID =32

    below storeprocedure for  without clientid

    ALTER PROCEDURE [dbo].[Sp_EmployeeTSDates_InsertGet]@empName varchar(50),
    	@tsStartDate datetime,
    	@tsEnddate datetime  
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    if exists
    	(SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate)
    begin 
    SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate
    end
    else
    begin 
    insert into EmployeeTSDates (Employeename, TSStartDate, TSEndDate) values (@empName, @tsStartDate, @tsEnddate)
    SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate
    
    end
    
    
    
    END
    

    below procedure wih client ID

    ALTER PROCEDURE [dbo].[Sp_AEmployeeTSDates_InsertGet] -- Add the parameters for the stored procedure here @empName varchar(50), @clientId int, @tsStartDate datetime, @tsEnddate datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here if exists (SELECT TSID ,ClientID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate) begin SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate end else begin insert into EmployeeTSDates (Employeename,ClientID, TSStartDate, TSEndDate) values (@empName,@clientId, @tsStartDate, @tsEnddate) SELECT TSID,ClientID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate end END

    my issue is  do i  need to create another  two more tables(emptimesheet1,emtsdates2) for without clientID application

    Monday, February 17, 2014 10:11 PM

Answers

  • Ex:angie works for organization1  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000//it creates one id

    angie works for organization2  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000    with clientid =32 // i want to  create another id with same dates

    TSID ClientID name

    21 NULL angie 2014-02-16 00:00:00.000 2014-02-22 00:00:00.000 22 NULL Angie 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 23 NULL Alex 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 24 32 test2 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 25 32 test3 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000

    Hi hemaak,

    According to your description, you want to use two store procedures  to insert the records about employee. When a user enters name , startdate, enddata  then tsid is created auto-increase  for timesheet1 application;   When a user enters name, clientid, startdate, enddata  then tsid is created auto-increase  for timesheet2 application. If it need two more tables for different application. Personally, I recommend you can use one table about timesheet. Just take the dbo.EmployeeTSDates as an example.

    1. you can use the store procedure of [dbo].[Sp_EmployeeTSDates_InsertGet] to insert records into the table dbo.EmployeeTSDates. And now the clientid is null.

    2. Then use the store procedure of [dbo].[Sp_AEmployeeTSDates_InsertGet] to insert records into the table dbo.EmployeeTSDates. And now the clientid is not null.

    You should to note that there is some modification about the store procedure of [dbo].[Sp_AEmployeeTSDates_InsertGet], you can refer to the following statement.

    if exists              
      (SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate and clientId =@clientId)
    Begin..

    3. You can return the sub-table for different application via the following statement.

    With application1 as
    (select * from dbo.EmployeeTSDates where ClientID is not null);
    With application2 as
    (select * from dbo.EmployeeTSDates where ClientID is null);

    Regards,
    Sofiya Li



    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, February 24, 2014 2:41 AM
    Wednesday, February 19, 2014 7:04 AM

All replies

  • What is the problem you are facing the in the current approach?

    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, February 18, 2014 2:36 AM
    Answerer
  • To specific to you procedure, I could not find any issues. First of all you are not using ClientID in your both of the procedures as predicate or search argument.

    Now, there would be some other functionality using the Client ID in your application whcih is not a scope of your question, but thats very important.

    How many records would be there with ClientID as NULL and Not NULL?

    Do you have any index on the Client ID?

    Tuesday, February 18, 2014 2:59 AM
    Answerer
  • Hi,

    In my application employess dont have id numbers so i created timesheetid(TSID) is unique id  .when user enters Name and startdate then tsid is created autocrement  for timesheet1 application.timesheet2 application is also following same procedure but they have clientid.but there is problem any user enters name is common two times dates are not possible.In my application some employees work for  more than one project . both timesheets are for two different oranizations.so same employee works for oranization1 and oranization2 .

    Ex:angie works for organization1  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000//it creates one id

    angie works for organization2  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000    with clientid =32 // i want to  create another id with same dates

    TSID ClientID name

    21 NULL angie 2014-02-16 00:00:00.000 2014-02-22 00:00:00.000 22 NULL Angie 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 23 NULL Alex 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 24 32 test2 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 25 32 test3 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000


    Tuesday, February 18, 2014 2:27 PM
  • Ex:angie works for organization1  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000//it creates one id

    angie works for organization2  she enters  2014-02-16 00:00:00.000 2014-02-22 00:00:00.000    with clientid =32 // i want to  create another id with same dates

    TSID ClientID name

    21 NULL angie 2014-02-16 00:00:00.000 2014-02-22 00:00:00.000 22 NULL Angie 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 23 NULL Alex 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 24 32 test2 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000 25 32 test3 2014-02-02 00:00:00.000 2014-02-08 00:00:00.000

    Hi hemaak,

    According to your description, you want to use two store procedures  to insert the records about employee. When a user enters name , startdate, enddata  then tsid is created auto-increase  for timesheet1 application;   When a user enters name, clientid, startdate, enddata  then tsid is created auto-increase  for timesheet2 application. If it need two more tables for different application. Personally, I recommend you can use one table about timesheet. Just take the dbo.EmployeeTSDates as an example.

    1. you can use the store procedure of [dbo].[Sp_EmployeeTSDates_InsertGet] to insert records into the table dbo.EmployeeTSDates. And now the clientid is null.

    2. Then use the store procedure of [dbo].[Sp_AEmployeeTSDates_InsertGet] to insert records into the table dbo.EmployeeTSDates. And now the clientid is not null.

    You should to note that there is some modification about the store procedure of [dbo].[Sp_AEmployeeTSDates_InsertGet], you can refer to the following statement.

    if exists              
      (SELECT TSID from EmployeeTSDates nolock where Employeename = @empName and TSStartDate = @tsStartDate and clientId =@clientId)
    Begin..

    3. You can return the sub-table for different application via the following statement.

    With application1 as
    (select * from dbo.EmployeeTSDates where ClientID is not null);
    With application2 as
    (select * from dbo.EmployeeTSDates where ClientID is null);

    Regards,
    Sofiya Li



    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Monday, February 24, 2014 2:41 AM
    Wednesday, February 19, 2014 7:04 AM