CODEWITHSUNDEEP
sqlt-sql
Hurricane
Hurricane

Reputation: 163

SQL outer apply with conditions

I have a table:

CREATE TABLE [dbo].[t]
(
[ID] [uniqueidentifier] NOT NULL,
[RoomID] [BIGINT] NOT NULL,
[RateID] [BIGINT] NOT NULL,
[Discount] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[CreationDate] [datetime] NOT NULL
)

and some of data :

   RoomID  RateID  Discount    StartDate   EndDate
 103716801   3011657 20.00   2014-09-30 00:00:00.000 2014-10-05 00:00:00.000
 103716801   3011657 0.00    2014-09-28 00:00:00.000 2014-10-26 00:00:00.000
 103716801   3011657 40.00   2014-09-29 00:00:00.000 2014-10-03 00:00:00.000

My request :

DECLARE
@iBeginDate DATE,
@iEndDate DATE

SELECT    
@iBeginDate = getdate()
,@iEndDate = Dateadd(dd, 30, getdate());

WITH Calendar AS (
SELECT @iBeginDate AS tDate
UNION ALL
SELECT dateadd(DAY , 1, tDate) AS tDate
FROM Calendar
WHERE dateadd (DAY, 1, tDate) <= @iEndDate
)

SELECT c.tDate, t.*
FROM Calendar c
OUTER APPLY (select top 1 * from t where startdate <= c.tDate and  c.tDate between startdate and enddate order by StartDate DESC) t 
OPTION (MAXRECURSION 0)

this is some of response:

2014-10-25  103716801   3011657 0.00    2014-09-28 00:00:00.000 2014-10-26 00:00:00.000
2014-10-26  103716801   3011657 0.00    2014-09-28 00:00:00.000 2014-10-26 00:00:00.000
2014-10-27  NULL    NULL    NULL    NULL    NULL    NULL
2014-10-28  NULL    NULL    NULL    NULL    NULL    NULL
2014-10-29  NULL    NULL    NULL    NULL    NULL    NULL
2014-10-30  NULL    NULL    NULL    NULL    NULL    NULL

If i can do the condition in apply i will change (flip) another statement - but i cant.

I need to save apply and add something else

THE QUESTION: How to put last valued row in nullable rows? Any ideas...

FOR EXAMPLE:

select case when exists( /*outer apply*/ )
then OUTER APPLY (select top 1 * from roomratesrelation where startdate <= c.tDate and c.tDate between startdate and enddate order by StartDate desc) t
else OUTER APPLY (select top 1 * from roomratesrelation where startdate <= c.tDate and order by StartDate desc) t1

Upvotes: 2

Views: 1105

Answers (1)

guildsbounty
guildsbounty

Reputation: 3361

Okay, I hate this answer, I feel bad for writing it, but it might work. If anyone else comes up with a better solution, I will gleefully delete this.

SELECT c.tDate, 
ISNULL(t1.RoomID, t2.RoomID) AS RoomID, 
ISNULL(t1.RateID, t2.RateID) AS RateID
ISNULL(t1.Discount, t2.Discount) AS Discount
ISNULL(t1.StartDate, t2.StartDate) AS StartDate,
ISNULL(t1.EndDate, t2.EndDate) AS EndDate
FROM Calendar c
OUTER APPLY (
    select top 1 * 
    from t 
    where startdate <= c.tDate 
    and  c.tDate between startdate and enddate 
    order by StartDate DESC
) t1 
OUTER APPLY (
    SELECT t3.RoomID, t3.RateID, t3.Discount, t3.StartDate, t3.EndDate 
    FROM t t3 
    WHERE t3.ID = (
        SELECT MAX(t4.ID)
        FROM t t4
    ) 
)t2
OPTION (MAXRECURSION 0)

I haven't tested this, so please let me know if/how it fails and I'll see if I can update it.

Upvotes: 2

Related Questions