TSQL Challenge

Transact-SQL

dot NET developer


Im calling all the TSQL gurus to help me with this problem.

Tables:
Contracts (ContractID,Lastname, Firstname, ContractValue, Date Created
Amendments (AmmendmentID, ContractID, Lastname, Firstname, ContractValue, EffectivityDate, DateCreated)

Amendments table is related to contracts thru ContractID.

Changes to the contract table is made by creating a record to the ammendments table. when this

amendment becomes effective it should take effect on the contract. When correcting the contractvalue

the user must input the amount to be added or subtracted.

For Example:

Current Contract: C1, PETRELLI, PITER, 150.00, created on Jan. 01, 2007
Ammendment: A1, C1, PETRELLI, PETER, 20.00, effective on Jan. 20, 2007, created on Jan. 10, 2007
Ammendment: A2, C1, PETRELLI, PETER, 15.00, effective on Mar. 01, 2007, Created on Feb. 01, 2007

The currect Contract:
As of Feb.01, 2007: C1, PETRELLI, PETER, 170.00
As of Mar.05, 2007: C1, PETRELLI, PETER, 185.00


The Challenge here is this:
How can you make an SQL VIEW that will show the list of Current Contracts
and use this view to create a stored procedure (with a parameter @CurrentDate)
The SP will show the current Contracts based on the supplied parameter.

Example:
ShowCurrentContracts(Jan. 05, 2007)
Current Contract: C1, PETRELLI, PITER, 150.00

ShowCurrentContracts(Feb. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 170.00

ShowCurrentContracts(Jun. 05, 2007)
Current Contract: C1, PETRELLI, PETER, 185.00




Top

Re: TSQL Challenge

dot NET developer


Here are the SQL Scripts.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Amendments_Contracts]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Amendments] DROP CONSTRAINT FK_Amendments_Contracts
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Amendments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Amendments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Contracts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Contracts]
GO

CREATE TABLE [dbo].[Amendments] (
[AmendmentID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [int] NOT NULL ,
[Lastname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Firstname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContractValue] [decimal](18, 2) NOT NULL ,
[EffectivityDate] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contracts] (
[ContractID] [int] IDENTITY (1, 1) NOT NULL ,
[Lastname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Firstname] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContractValue] [decimal](18, 2) NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO







Top

Re: TSQL Challenge

hunchback

Try:

declare @d datetime

set @d = '20070205'

select

c.ContractID,

c.Lastname,

c.Firstname,

c.ContractValue +

isnull(

(

select sum(a.ContractValue)

from [dbo].[Amendments] as a

where a.ContractID = c.ContractID and a.EffectivityDate <= @d

), 0) as current_ContractValue

from

[dbo].[Contracts] as c

where

c.DateCreated <= @d

GO

BTW, why are you duplicating contract data in the amendments table, like LastName and FirstName

AMB



Top

Re: TSQL Challenge

dot NET developer

Views do not allow the "DECLARE" keyword.

When the amendment is effective the data from the amendments table will be selected instead of the contract data . Amendments table preserve the historical changes of the Contract.

It should have 2 outputs, 1 view and 1 stored procedure.

View: Current_Contracts - This is the list of contracts on its current state(with effective amendment).
Stored Proc.: GetContractByDate(Date) - This is the current contract on the time of the given date.





Top

Re: TSQL Challenge

Manivannan.D.Sekaran

I think creating a view is very expensive here.

BTW, if you want to create the view you have to use the following tasks.

- Create a calendar table

- Update the data on calendar table periodically

- Join with the original tables

Code Block

Create table MyCalendar

(

Date Datetime

)

Declare @date as Datetime

Set @date='2007-01-01'

While @date <= '2007-12-31'

Begin

Insert Into MyCalendar Select @date where not exists(select 1 from MyCalendar where date=@date)

Set @date = dateadd(dd,1,@date)

End

Code Block

create view myview

as

select

ContractID,

lastName,

Firstname,

date,

Sum(case when aefd <= date then v end) as ContractValue

from

(

select

C.ContractID,

C.lastName,

C.Firstname,

a.EffectivityDate aefd,

a.ContractValue v

from

contracts c

left outer Join amendments a on a.ContractID = c.ContractID

Union All

select

C.ContractID,

C.lastName,

C.Firstname,

c.[DateCreated] aefd,

c.ContractValue v

from

contracts c

) as D

cross join MyCalendar

group By ContractID,

lastName,

Firstname,

date





Top

Re: TSQL Challenge

hunchback

> Views do not allow the "DECLARE" keyword.

I just gave you the statement. Can you create the view

Try:

Code Block

CREATE PROCEDURE dbo.usp_p1

@d DATETIME

AS

SET NOCOUNT ON

SELECT

COALESCE(t.ContractID, c.ContractID) AS ContractID,

COALESCE(t.Lastname, c.Lastname) AS Lastname,

COALESCE(t.Firstname, c.Firstname) AS Firstname,

c.ContractValue +

isnull(

(

SELECT sum(b.ContractValue)

FROM [dbo].[Amendments] as b

WHERE b.ContractID = c.ContractID and b.EffectivityDate <= @d

), 0) as current_ContractValue

FROM

[dbo].[Contracts] as c

OUTER APPLY

(

SELECT TOP (1) a.ContractID, a.Lastname, a.Firstname

FROM [dbo].[Amendments] as a

WHERE a.ContractID = c.ContractID AND a.EffectivityDate <= @d

ORDER BY a.EffectivityDate DESC

) AS t

WHERE

c.DateCreated <= @d

GO

CREATE VIEW dbo.vw_v1

AS

SELECT

COALESCE(t.ContractID, c.ContractID) AS ContractID,

COALESCE(t.Lastname, c.Lastname) AS Lastname,

COALESCE(t.Firstname, c.Firstname) AS Firstname,

c.ContractValue +

isnull(

(

select sum(b.ContractValue)

from [dbo].[Amendments] as b

where b.ContractID = c.ContractID and b.EffectivityDate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

), 0) as current_ContractValue

from

[dbo].[Contracts] as c

OUTER APPLY

(

SELECT TOP (1) a.ContractID, a.Lastname, a.Firstname

FROM [dbo].[Amendments] as a

WHERE a.ContractID = c.ContractID AND a.EffectivityDate <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

ORDER BY a.EffectivityDate DESC

) AS t

WHERE

c.DateCreated <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

GO

AMB




Top

Re: TSQL Challenge

dot NET developer

Mr Hunchback you are almost there. In the stored procedure i need to use the view instead of the table. some thing like this.

CREATE PROCEDURE dbo.usp_p1

@d DATETIME

AS

SET NOCOUNT ON

SELECT

Column1,

Column2

FROM

dbo.vw_v1 c

WHERE

c.DateCreated <= @d

GO




Top

Re: TSQL Challenge

hunchback

You can not parameterize a view, so you will have to use a table-valued user defined funcion instead. Use the code from sp and create the function, then use the function inside the sp.

AMB


Top