SQL Server 2005 -I want duration from the condition below- without cursor would be preferred

15 pts.
Tags:
SQL Server
SQL Server 2005
I have the following table: CREATE TABLE [dbo].[DurationCalc]( [Id] [int] IDENTITY(1,1) NOT NULL, [ContractId] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Status] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ModDate] [smalldatetime] NULL ) ON [PRIMARY] And the following are the records INSERT INTO Tabletest1 values (5,'O','2009-01-01 00:00:00.000') INSERT INTO Tabletest1 values (5,'C','2009-01-02 00:00:00.000') INSERT INTO Tabletest1 values (5,'S','2009-01-04 00:00:00.000') INSERT INTO Tabletest1 values (5,'X','2009-02-25 00:00:00.000') INSERT INTO Tabletest1 values (5,'S','2009-02-28 00:00:00.000') The output needed is the total number of days that ContractId '5' is in 'O' Status (Status ='O') during the period 01-Jan-2009 to 30-Apr-2009

Answer Wiki

Thanks. We'll let you know when a new response is added.

While it is possible to do this without a cursor, the resulting query will be (IMHO) highly inefficient as the database grows. Here is a query that will return your answer:

select t.contractid, t.status, sum(datediff(d, t.fromdate, isnull(t.todate,getdate()))) days
from
(
select c1.contractid, c1.status, c1.moddate fromdate, c2.moddate todate
from DurationCalc c1
outer join DurationCalc c2 on (
c2.contractid = c1.contractid
and c2.id> c1.id
and not exists (
select 1 from DurationCalc c3
where c3.contractid = c1.contractid
and c3.id > c1.id
and c3.id < c2.id)
)
) t
where t.status = ‘O’
group by t.contractid, t.status

The concepts behind this are:
1. internally, collapse the separate rows into rows where the temporary row has both the beginning and ending date of the status period – this will let us use the datediff function on the fields in the row.
2. We want the “todate” field to be the “next” date after the start of the period, so we need moddate to be greater than the c1.moddate, but use the “not exists” to ensure there are no other records that fall between these.
3. Once we have the temporary table “t” containing the start and end of each period, we can grab all the “O” periods and compute the date difference between the start and end.
4. Note that if the latest “O” has no end date (no subsequent record), we need to use “getdate()” to provide the pseudo-end date for the last period.
5. The “sum” and “group by” will total the days for each contractid when the contract id had a status of “O”.

Personally, I would DEFINITELY write this as a function with a cursor, which would be dozens (if not hundreds) of times more efficient than the above expression.

At the very least, make sure you have an index on ContractID. I would be inclined to create an index as:

Create Unique Index … on DurationCalc (ContractId, Id);

This would at least ensure the above expression is evaluated in the most efficient manner possible.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SqlX
    Sorry there is an error in this insert statements: I have the following table: CREATE TABLE [dbo].[DurationCalc]( [Id] [int] IDENTITY(1,1) NOT NULL, [ContractId] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Status] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ModDate] [smalldatetime] NULL ) ON [PRIMARY] And the following are the records INSERT INTO DurationCalc values (5,'O','2009-01-01 00:00:00.000') INSERT INTO DurationCalc values (5,'C','2009-01-02 00:00:00.000') INSERT INTO DurationCalc values (5,'S','2009-01-04 00:00:00.000') INSERT INTO DurationCalc values (5,'X','2009-02-25 00:00:00.000') INSERT INTO DurationCalc values (5,'S','2009-02-28 00:00:00.000') The output needed is the total number of days that ContractId '5' is in 'O' Status (Status ='O') during the period 01-Jan-2009 to 30-Apr-2009
    15 pointsBadges:
    report
  • Denny Cherry
    A query like this will work.
    select ContractId, datediff(dd, ModDate, (select min(ModDate) 
    											FROM [DurationCalc] a 
    											WHERE a.ContractId = [DurationCalc].ContractId
    												AND a.ModDate > [DurationCalc].ModDate))
    FROM [DurationCalc]
    WHERE Status = 'O'
    
    However you'll want to test this against a cursor and see which is more efficient (as well as the other options as well). Having only 5 records of test data and no indexes doesn't exactly provide a good test ground.
    66,140 pointsBadges:
    report
  • Kccrosser
    MRDenny's query will work, except for current "open" records (ones where there is a starting "O" event, but no subsequent event, to give an end date). Those records would not be counted - one of the reasons I used the temp table approach.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following