none
Using an ID against dates for picking - problem when lastWeekEnd and lastMonthEnd are the same date! RRS feed

  • Question

  • Hi

    I have a query that appeared to work well at first but needs a slight tweak. Where I am right in the middle of it I've got a total mind block on the tweak so I'm throwing it out there for assistance! :)

    As part of a larger stored proc, this section goes away to a MarketDataHistory table to get columns for lastBusnessDay, lastWeekEnd (|friday / business day) and lastMonthEnd. I've used a CASE to appoint an integer (1 for lastBusinessDay, 2 for lastWeek and 3 for lastMonthEnd and then 4 for anything else such that we can select only  IN (1,2,3) on the outer select.

    This seemed cool except I then noticed in testing last week that lastWeek and lastMonthEnd were both 31 May and then I had missing values further down the line (we always need columns for all 3 later).

    DECLARE @date DATETIME = GETDATE()
    SELECT @lastBusinessDay = (CONVERT(VARCHAR(10), DATEADD(DAY,
                        (CASE DATENAME(WEEKDAY, convert(date,@date))
    WHEN 'Sunday' THEN -2 
                            WHEN 'Monday' THEN -3 
                            ELSE -1
                          END),
                          CONVERT(DATE, @date))) + ' 00:00:00.000')


    DECLARE @mydate datetime
    SELECT @lastWeek = DATEADD(day, (DATEDIFF (day, '19990102', @DATE) / 7) * 7, '19990101') as friday_before_mydate


    SELECT @lastMonthEnd = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1);

    Ranked AS (SELECT ISIN --,[DATE] ,GRP ,ROW_NUMBER() OVER (PARTITION BY date, grp ORDER BY t.isin) AS [ROWNUMBER] ,COALESCE(ISNULL(TRY_CAST(BID_SPREAD AS FLOAT) + TRY_CAST(ASK_SPREAD AS FLOAT),0) / 2, 0) AS CLOSE_BIDASKAVG ,COALESCE(ISNULL(TRY_CAST(ASK AS FLOAT) + TRY_CAST(BID AS FLOAT),0) / 2, 0) AS CLOSE_PRICEAVG ,COALESCE(ISNULL(TRY_CAST(ASK_YIELD AS FLOAT) + TRY_CAST(BID_YIELD AS FLOAT),0) / 2, 0) AS CLOSE_YIELDAVG FROM (SELECT *, (CASE WHEN date = @lastBusinessDay THEN 1 WHEN date = @lastWeek THEN 2 WHEN date = @lastMonthEnd THEN 3 ELSE 4 END) as GRP --1 for yesterday, 2 for last week, 3 for last month end and ignore 4 on outer FROM marketdatahistory WHERE DATE >= @lastMonthEnd ) t WHERE GRP IN (1,2,3) AND isin <> ''), LastValues AS ( SELECT ISIN ,LastBusinessDayCloseBidAskAVG = COAlESCE(MAX(CASE WHEN GRP = 1 THEN R.CLOSE_BIDASKAVG END),'') , LastWeekCloseBidAskAVG = COAlESCE(MAX(CASE WHEN GRP = 2 THEN R.CLOSE_BIDASKAVG END),'') , LastMonthEndCloseBidAskAVG = COAlESCE(MAX(CASE WHEN GRP = 3 THEN R.CLOSE_BIDASKAVG END),'') , LastBusinessDayClosePriceAVG = COAlESCE(MAX(CASE WHEN GRP = 1 THEN R.CLOSE_PRICEAVG END),'') , LastWeekClosePriceAVG = COAlESCE(MAX(CASE WHEN GRP = 2 THEN R.CLOSE_PRICEAVG END),'') , LastMonthEndClosePriceAVG = COAlESCE(MAX(CASE WHEN GRP = 3 THEN R.CLOSE_PRICEAVG END),'') , LastBusinessDayCloseYieldAVG = COAlESCE(MAX(CASE WHEN GRP = 1 THEN R.CLOSE_YIELDAVG END),'') , LastWeekCloseYieldAVG = COAlESCE(MAX(CASE WHEN GRP = 2 THEN R.CLOSE_YIELDAVG END),'') , LastMonthEndCloseYieldAVG = COAlESCE(MAX(CASE WHEN GRP = 3 THEN R.CLOSE_YIELDAVG END),'') FROM Ranked AS R GROUP BY R.isin), Final AS ( SELECT segTextMidSpread.ISIN ,segTextMidSpread.MID_SPREAD ,COALESCE(segTextMidSpread.SEG_TEXT, '') AS SEG_TEXT ,LastBusinessDayCloseBidAskAVG ,LastWeekCloseBidAskAVG ,LastMonthEndCloseBidAskAVG ,CASE WHEN SEG_TEXT='Benchmark' THEN MID_SPREAD - LastBusinessDayCloseBidAskAVG ELSE '' END AS MOVE1DS ,CASE WHEN SEG_TEXT='Benchmark' THEN MID_SPREAD - LastWeekCloseBidAskAVG ELSE '' END AS MOVE1WS ,CASE WHEN SEG_TEXT='Benchmark' THEN MID_SPREAD - LastMonthEndCloseBidAskAVG ELSE '' END AS MOVE1MS ,CASE WHEN SEG_TEXT='Manual Price' THEN MID_SPREAD - LastBusinessDayClosePriceAVG ELSE '' END AS MOVE1DP ,CASE WHEN SEG_TEXT='Manual Price' THEN MID_SPREAD - LastWeekClosePriceAVG ELSE '' END AS MOVE1WP ,CASE WHEN SEG_TEXT='Manual Price' THEN MID_SPREAD - LastMonthEndClosePriceAVG ELSE '' END AS MOVE1MP ,CASE WHEN SEG_TEXT='Manual Yield' THEN MID_SPREAD - LastBusinessDayCloseYieldAVG ELSE '' END AS MOVE1DY ,CASE WHEN SEG_TEXT='Manual Yield' THEN MID_SPREAD - LastWeekCloseYieldAVG ELSE '' END AS MOVE1WY ,CASE WHEN SEG_TEXT='Manual Yield' THEN MID_SPREAD - LastMonthEndCloseYieldAVG ELSE '' END AS MOVE1MY ,CASE WHEN SEG_TEXT='Discount Margin' THEN MID_SPREAD - LastBusinessDayCloseYieldAVG ELSE '' END AS MOVE1DM ,CASE WHEN SEG_TEXT='Discount Margin' THEN MID_SPREAD - LastWeekCloseYieldAVG ELSE '' END AS MOVE1WM ,CASE WHEN SEG_TEXT='Discount Margin' THEN MID_SPREAD - LastMonthEndCloseYieldAVG ELSE '' END AS MOVE1MM ,GetDate() AS DATE FROM LastValues JOIN segTextMidSpread on segTextMidSpread.ISIN = LastValues.ISIN )


    I'd appreciate someone helping me past my mind block, haha, with a tweak on how I cope with 2 of 3 variables sometimes being the same but always retrieving all 3.

    Thanks

    Leigh Tilley (TilleyTech Ltd)



    Monday, June 10, 2019 9:43 AM

Answers

  • I also posted this on StackOverflow and got a quick reply:

    Solution proposed is:

    FROM (SELECT M.*, G.GRP
    FROM marketdatahistory AS M
    INNER JOIN (
    --1 for yesterday, 2 for last week, 3 for last month end
        SELECT 1 AS GRP, @lastbusinessday as D UNION ALL
        SELECT 2 AS GRP, @lastWeek as D UNION ALL
        SELECT 3 AS GRP, @lastMonthEnd as D
    ) AS G
    ON M.DATE >= @lastMonthEnd
    AND M.DATE = G.D
    ) t

    Monday, June 10, 2019 2:15 PM