none
Query to find the difference between the last date and the second to the last date

    Question


    •  

      Hi all,

       

      Hope all is well.

      I am working on the following problem because I am trying to improve my MS SQL skills. But I am stuck at the moment and I wonder if you could provide some assistance please. Here is the issue:

       

      Table 1: Dividends

      divId

      ExDate

      RecordDate

      PayDate

      Amount

      Yield

      symId

      1

      2013-02-19

      2013-02-21

      2013-03-14

      0.23

      0.00000

      3930

      2

      2012-11-13

      2012-11-15

      2012-12-13

      0.23

      0.00849

      3930

      3

      2012-08-14

      2012-08-16

      2012-09-13

      0.20

      0.00664

      3930

      4

      2012-05-15

      2012-05-17

      2012-06-14

      0.20

      0.00662

      3930

      5

      2012-02-14

      2012-02-16

      2012-03-08

      0.20

      0.00661

      3930

      6

      2011-11-15

      2011-11-17

      2011-12-08

      0.20

      0.00748

      3930

      7

      2011-08-16

      2011-08-18

      2011-09-08

      0.16

      0.00631

      3930

      8

      2011-05-17

      2011-05-19

      2011-06-09

      0.16

      0.00653

      3930

      9

      2011-02-15

      2011-02-17

      2011-03-10

      0.16

      0.00594

      3930

      10

      2010-11-16

      2010-11-18

      2010-12-09

      0.16

      0.00620

      3930

      11

      2010-08-17

      2010-08-19

      2010-09-09

      0.13

      0.00526

      3930

      12

      2010-05-18

      2010-05-20

      2010-06-10

      0.13

      0.00455

      3930

      13

      2010-02-16

      2010-02-18

      2010-03-11

      0.13

      0.00459

      3930

        

      Table 2: Tickers

      symId

      Symbol

      Name

      Sector

      Industry

      1

      A

      Agilent Technologies Inc.

      Technology

      Scientific & Technical Instruments

      2

      AA

      Alcoa, Inc.

      Basic Materials

      Aluminum

      3

      AACC

      Asset Acceptance Capital Corp.

      Financial

      Credit Services

      4

      AADR

      WCM/BNY Mellon Focused Growth ADR ETF

      Financial

      Exchange Traded Fund

      5

      AAIT

      iShares MSCI AC Asia Information Tech

      Financial

      Exchange Traded Fund

      6

      AAME

      Atlantic American Corp.

      Financial

      Life Insurance

      7

      AAN

      Aaron's, Inc.

      Services

      Rental & Leasing Services

      8

      AAON

      AAON Inc.

      Industrial Goods

      General Building Materials

      9

      AAP

      Advance Auto Parts Inc.

      Services

      Auto Parts Stores

      10

      AAPL

      Apple Inc.

      Technology

      Personal Computers

      11

      AAT

      American Assets Trust, Inc.

      Financial

      REIT - Office

      12

      AAU

      Almaden Minerals Ltd.

      Basic Materials

      Industrial Metals & Minerals

         

      I am trying to check the last date (i.e. max date) and also check the penultimate date (i.e. the second to the last date).  And then find the difference between the two (i.e. last date minus penultimate date).

      I would like to do that for each of the companies listed in Table 2: Tickers.  I am able to do it for just one company (MSFT) using the queries below:

       
    SELECT        
     [First] = MIN(ExDate),
     [Last] = MAX(ExDate),
     [Diff] = DATEDIFF(DAY, MIN(ExDate), MAX(ExDate))
    FROM        (
                 SELECT TOP 2        Dividends.ExDate
                 FROM                Dividends, Tickers
                 WHERE  Dividends.symId=Tickers.symId
                 AND Tickers.Symbol='MSFT'
                 ORDER BY        ExDate DESC
                ) AS X 
     

          

      Outputs the following result:

      First

      Last

      Diff

      2012-11-13

      2013-02-19

      98

           

      But what I would like instead is to be able to output something like this:

       

      Symbol

      First

      Last

      Diff

      MSFT

      2012-11-13

      2013-02-19

      98

      AAN

      2012-11-13

      2012-12-14

      1

      X

      2012-11-13

      2012-12-14

      1

        

      Can anyone please let me know what do I need to add on my query in order to achieve the desired output?

      Any help would be greatly appreciated.

      Thanks in advance. 

       


       
        

    • Edited by dragontbone Sunday, January 13, 2013 12:20 PM
    Sunday, January 13, 2013 12:10 PM

Answers

  • Could you try this?

    /* 
    create table Ticker (SymbolId int identity primary key, Symbol varchar(4)) 
    insert into Ticker (Symbol) values ('MSFT'), ('ORCL'), ('GOOG') 
    
    create table Dividend (DividendId int identity, SymbolId int constraint FK_Dividend foreign key references Ticker(SymbolId), ExDate datetime, Amount decimal(18,4))
    insert into Dividend (SymbolId, ExDate, Amount) values 
    (1, '2012-10-1', 10), 
    (1, '2012-10-3', 1), 
    (1, '2012-10-7', 7), 
    (1, '2012-10-12', 2), 
    (1, '2012-10-23', 8), 
    (1, '2012-10-30', 5), 
    
    (2, '2012-10-1', 10), 
    (2, '2012-10-6', 1), 
    (2, '2012-10-29', 7), 
    
    (3, '2012-10-1', 22), 
    (3, '2012-10-3', 21), 
    (3, '2012-10-7', 3), 
    (3, '2012-10-12', 9) 
    */ 
    ; 
    WITH cte 
         AS (SELECT t.Symbol, 
                    d.ExDate, 
                    d.Amount, 
                    ROW_NUMBER() 
                      OVER ( 
                        partition BY Symbol 
                        ORDER BY ExDate DESC) AS rownum 
             FROM   Ticker AS t 
                    INNER JOIN Dividend AS d 
                            ON t.SymbolId = d.SymbolId), 
         ctedate 
         AS (SELECT Symbol, 
                    [1] AS maxdate, 
                    [2] AS penultimatedate 
             FROM   cte 
                    PIVOT( MIN(ExDate) 
                         FOR RowNum IN ([1], 
                                        [2]) ) AS pvtquery), 
         cteamount 
         AS (SELECT Symbol, 
                    [1] AS maxdateamount, 
                    [2] AS penultimatedateamount 
             FROM   cte 
                    PIVOT( MIN(Amount) 
                         FOR RowNum IN ([1], 
                                        [2]) ) AS pvtquery) 
    SELECT d.Symbol, 
           MIN(MaxDate)                                    AS maxdate, 
           MIN(penultimatedate)                            AS penultimatedate, 
           DATEDIFF(d, MIN(penultimatedate), MIN(MaxDate)) AS numberofdays, 
           MIN(MaxDateAmount)                              AS maxdateamount, 
           MIN(penultimatedateAmount)                      AS penultimatedateamount, 
           MIN(MaxDateAmount) - MIN(penultimatedateAmount) AS delta 
    FROM   ctedate AS d 
           INNER JOIN cteamount AS a 
                   ON d.Symbol = a.symbol 
    GROUP  BY d.Symbol 
    ORDER  BY d.Symbol 


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    • Marked as answer by dragontbone Sunday, January 20, 2013 7:06 PM
    Thursday, January 17, 2013 5:28 AM

All replies

  • Could you try this?

    /* 
    create table Ticker (SymbolId int identity primary key, Symbol varchar(4)) 
    insert into Ticker (Symbol) values ('MSFT'), ('ORCL'), ('GOOG') 
    
    create table Dividend (DividendId int identity, SymbolId int constraint FK_Dividend foreign key references Ticker(SymbolId), ExDate datetime, Amount decimal(18,4))
    insert into Dividend (SymbolId, ExDate, Amount) values 
    (1, '2012-10-1', 10), 
    (1, '2012-10-3', 1), 
    (1, '2012-10-7', 7), 
    (1, '2012-10-12', 2), 
    (1, '2012-10-23', 8), 
    (1, '2012-10-30', 5), 
    
    (2, '2012-10-1', 10), 
    (2, '2012-10-6', 1), 
    (2, '2012-10-29', 7), 
    
    (3, '2012-10-1', 22), 
    (3, '2012-10-3', 21), 
    (3, '2012-10-7', 3), 
    (3, '2012-10-12', 9) 
    */ 
    ; 
    WITH cte 
         AS (SELECT t.Symbol, 
                    d.ExDate, 
                    d.Amount, 
                    ROW_NUMBER() 
                      OVER ( 
                        partition BY Symbol 
                        ORDER BY ExDate DESC) AS rownum 
             FROM   Ticker AS t 
                    INNER JOIN Dividend AS d 
                            ON t.SymbolId = d.SymbolId), 
         ctedate 
         AS (SELECT Symbol, 
                    [1] AS maxdate, 
                    [2] AS penultimatedate 
             FROM   cte 
                    PIVOT( MIN(ExDate) 
                         FOR RowNum IN ([1], 
                                        [2]) ) AS pvtquery), 
         cteamount 
         AS (SELECT Symbol, 
                    [1] AS maxdateamount, 
                    [2] AS penultimatedateamount 
             FROM   cte 
                    PIVOT( MIN(Amount) 
                         FOR RowNum IN ([1], 
                                        [2]) ) AS pvtquery) 
    SELECT d.Symbol, 
           MIN(MaxDate)                                    AS maxdate, 
           MIN(penultimatedate)                            AS penultimatedate, 
           DATEDIFF(d, MIN(penultimatedate), MIN(MaxDate)) AS numberofdays, 
           MIN(MaxDateAmount)                              AS maxdateamount, 
           MIN(penultimatedateAmount)                      AS penultimatedateamount, 
           MIN(MaxDateAmount) - MIN(penultimatedateAmount) AS delta 
    FROM   ctedate AS d 
           INNER JOIN cteamount AS a 
                   ON d.Symbol = a.symbol 
    GROUP  BY d.Symbol 
    ORDER  BY d.Symbol 


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    • Marked as answer by dragontbone Sunday, January 20, 2013 7:06 PM
    Thursday, January 17, 2013 5:28 AM
  • Hi Aalam,

    It works! Many thanks for your help. 

    Sunday, January 20, 2013 7:07 PM
  • A wonderful solution to my problem, too.  A hearty thumbs up for this!!
    Friday, January 17, 2014 7:51 PM