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

Sunday, January 13, 2013 12:10 PM

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

All Replies

Thursday, January 17, 2013 5:28 AM

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

Sunday, January 20, 2013 7:07 PM

0

Hi Aalam,

It works! Many thanks for your help.
- Reply
- Quote

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

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?