Geek asks and answers

SQL Query - How do I compare the date range in the Where section? (with proper illustrated example)

I searched for a solution and tried enough, and found nothing. Look at the two tables and the results table carefully and patiently, and you will understand the problem -

The following is table 1:

Table 1

Table 2 below:

Table2

The following is the RESULT result table:

Result_Table

Now the ordering in the results table was performed in accordance with the date of the decision-making change, that is, in the results table there is the identifier "order by" and the date the decision was made. This means that Active_Status needs to be adjusted according to the period of the decision date, reporting the result.

, " " " ", 1 " "? , - .

- Chirayu D

+3
2

, :

DECLARE @Result TABLE
(   
  TICKR_SYMB VARCHAR (15) NOT NULL
  ,fromReviewStatus char(10)
  ,toReviewStatus char(10)
  ,ReviewStatusChangeDate DATETIME
  ,fromRestrictionStatus char(10)
  ,toRestrictionStatus char(10)
  ,RestrictionStatusChangeDate DATETIME
  ,fromCoverageStatus char(10)
  ,toCoverageStatus char(10)
  ,CoverageStatusChangeDate DATETIME
  ,fromRating VARCHAR(20)
  ,toRating VARCHAR(20)
  ,RatingChangeDate DATETIME
)

/* Rating History */
;WITH DecisionsHistory AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY H.TICKR_SYMB ORDER BY H.[Rating Change Date]) AS Row
        ,H.TICKR_SYMB
        ,H.[to Rating] AS toRating
        ,H.[Rating Change Date]
    FROM tblTickerRatingHistory H
)
INSERT @Result
(
    TICKR_SYMB
    ,fromRating
    ,toRating
    ,RatingChangeDate
)
SELECT
     CurrentHistory.TICKR_SYMB
    ,LastHistory.toRating AS fromRating
    ,CurrentHistory.toRating
    ,CurrentHistory.[Rating Change Date]
FROM DecisionsHistory CurrentHistory
LEFT JOIN DecisionsHistory LastHistory
    ON LastHistory.Row = (CurrentHistory.Row - 1)
    AND LastHistory.TICKR_SYMB = CurrentHistory.TICKR_SYMB

/* ReviewStatus */
;WITH ReviewStatusHistory AS 
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY R.TICKR_SYMB, R.RatingChangeDate ORDER BY H.ReviewStatusChangeDate DESC) AS Row
    ,R.TICKR_SYMB 
    ,R.RatingChangeDate
    ,H.ReviewStatus AS ToReviewStatus
    ,H.ReviewStatusChangeDate
  FROM @Result R
  LEFT JOIN tblTickerStatusHistory H
    ON H.TICKR_SYMB = R.TICKR_SYMB
    AND H.ReviewStatusChangeDate < R.RatingChangeDate
)
UPDATE R
SET 
  fromReviewStatus = LastActiveHistory.toReviewStatus
  ,toReviewStatus = CurrentActiveHistory.toReviewStatus
  ,ReviewStatusChangeDate = CurrentActiveHistory.ReviewStatusChangeDate
FROM @Result R
LEFT JOIN ReviewStatusHistory CurrentActiveHistory
  ON CurrentActiveHistory.TICKR_SYMB = R.TICKR_SYMB
  AND CurrentActiveHistory.RatingChangeDate = R.RatingChangeDate
  AND CurrentActiveHistory.Row = 1
LEFT JOIN ReviewStatusHistory LastActiveHistory
    ON LastActiveHistory.TICKR_SYMB = R.TICKR_SYMB
    AND LastActiveHistory.RatingChangeDate = R.RatingChangeDate
    AND LastActiveHistory.Row = 2

/* CoverageStatus */
;WITH CoverageStatusHistory AS 
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY R.TICKR_SYMB, R.RatingChangeDate ORDER BY H.CoverageStatusChangeDate DESC) AS Row
    ,R.TICKR_SYMB 
    ,R.RatingChangeDate
    ,H.CoverageStatus AS ToCoverageStatus
    ,H.CoverageStatusChangeDate
  FROM @Result R
  LEFT JOIN tblTickerStatusHistory H
    ON H.TICKR_SYMB = R.TICKR_SYMB
    AND H.CoverageStatusChangeDate < R.RatingChangeDate
)
UPDATE R
SET 
  fromCoverageStatus = LastActiveHistory.toCoverageStatus
  ,toCoverageStatus = CurrentActiveHistory.toCoverageStatus
  ,CoverageStatusChangeDate = CurrentActiveHistory.CoverageStatusChangeDate
FROM @Result R
LEFT JOIN CoverageStatusHistory CurrentActiveHistory
  ON CurrentActiveHistory.TICKR_SYMB = R.TICKR_SYMB
  AND CurrentActiveHistory.RatingChangeDate = R.RatingChangeDate
  AND CurrentActiveHistory.Row = 1
LEFT JOIN CoverageStatusHistory LastActiveHistory
    ON LastActiveHistory.TICKR_SYMB = R.TICKR_SYMB
    AND LastActiveHistory.RatingChangeDate = R.RatingChangeDate
    AND LastActiveHistory.Row = 2

 /*RestrictionStatus */
;WITH RestrictionStatusHistory AS 
(
  SELECT
    ROW_NUMBER() OVER(PARTITION BY R.TICKR_SYMB, R.RatingChangeDate ORDER BY H.RestrictionStatusChangeDate DESC) AS Row
    ,R.TICKR_SYMB 
    ,R.RatingChangeDate
    ,H.RestrictionStatus AS ToRestrictionStatus
    ,H.RestrictionStatusChangeDate
  FROM @Result R
  LEFT JOIN tblTickerStatusHistory H
    ON H.TICKR_SYMB = R.TICKR_SYMB
    AND H.RestrictionStatusChangeDate < R.RatingChangeDate
)
UPDATE R
SET 
  fromRestrictionStatus = LastActiveHistory.toRestrictionStatus
  ,toRestrictionStatus = CurrentActiveHistory.toRestrictionStatus
  ,RestrictionStatusChangeDate = CurrentActiveHistory.RestrictionStatusChangeDate
FROM @Result R
LEFT JOIN RestrictionStatusHistory CurrentActiveHistory
  ON CurrentActiveHistory.TICKR_SYMB = R.TICKR_SYMB
  AND CurrentActiveHistory.RatingChangeDate = R.RatingChangeDate
  AND CurrentActiveHistory.Row = 1
LEFT JOIN RestrictionStatusHistory LastActiveHistory
    ON LastActiveHistory.TICKR_SYMB = R.TICKR_SYMB
    AND LastActiveHistory.RatingChangeDate = R.RatingChangeDate
    AND LastActiveHistory.Row = 2


SELECT
  R1.TICKR_SYMB
  ,R1.fromCoverageStatus
  ,R1.toCoverageStatus
  ,R1.CoverageStatusChangeDate
  ,R1.fromReviewStatus
  ,R1.toReviewStatus
  ,R1.ReviewStatusChangeDate
  ,R1.fromRestrictionStatus
  ,R1.toRestrictionStatus
  ,R1.RestrictionStatusChangeDate
  ,R1.fromRating
  ,R1.toRating
  ,R1.RatingChangeDate
FROM @Result R1
ORDER BY TICKR_SYMB, RatingChangeDate
+3

-, .

    create table t1 (
  id int, act char(1), scd date
  )

create table t2 (
  id int, decs varchar(4), dcd date
  )

insert into t1 values

( 1, 'y', '20000115'),
( 1, 'n', '20030120'),
( 2, 'n', '20020125'),
( 2, 'y', '20030115'),
( 2, 'n', '20100120'),
( 3,'y','20050125'),
( 3,'y','20070120'),
( 3,'n','20110115')

insert into t2 values

(1,'buy','20000515' ),
(1,'sell', '20100520' ),
(1,'sell', '20120525' ),
(2,'hold', '20040515'),
(2,'buy', '20110510' ),
(3,'sell', '20080515'),
(3,'buy','20110525' )

with decisions as (

  select row_number() over (partition by id order by dcd) as rn,
   id, decs, dcd from t2
  ),
activities as 
(
  select row_number() over (partition by id order by scd) as rn,
  id, act, scd from t1
  )

select dec_to.id, x.from_act, x.to_act, x.scd, x.from_act, x.to_act, x.scd as scd, dec_from.decs as from_dec, dec_to.decs as to_dec, dec_to.dcd from decisions dec_from
right outer join decisions dec_to on dec_from.id = dec_to.id and 
dec_to.rn = dec_from.rn + 1
outer apply (
  select top 1 act_to.id, act_from.act as from_act, act_to.act as to_act, act_to.scd
  from activities act_to
  left outer join activities as act_from
  on act_from.id = act_to.id and act_from.rn = act_to.rn - 1
  where act_to.id = dec_to.id and act_to.scd <= dec_to.dcd
  order by act_to.scd desc
) x  

order by dec_to.id, dec_to.rn

, . , . , , , , .

, , "" .

ID  FROM_ACT    TO_ACT  SCD     FROM_DEC    TO_DEC  DCD
1           y   2000-01-15          buy 2000-05-15
1   y       n   2003-01-20  buy     sell    2010-05-20
1   y       n   2003-01-20  sell        sell    2012-05-25
2   n       y   2003-01-15          hold    2004-05-15  
2   y       n   2010-01-20  hold        buy 2011-05-10
3   y       y   2007-01-20          sell    2008-05-15
3   y       n   2011-01-15  sell        buy 2011-05-25

SQLFiddle

+2

More articles:


All Articles