Fetching dates monthly from the table

Question

Can you help out with a problem

I have table price table which has daily prices starting 31st Dec 2010 till todays date.The table contains daily prices

2009-12-31 00:00:00.000	1.0020945351
2010-01-01 00:00:00.000	1.0021009300
2010-01-04 00:00:00.000	1.0021910181
2010-01-05 00:00:00.000	1.0022005986
2010-01-06 00:00:00.000	1.0022428696
2010-01-07 00:00:00.000	1.0022647147
2010-01-08 00:00:00.000	1.0022842726
2010-01-11 00:00:00.000	1.0023374302
2010-01-12 00:00:00.000	1.0023465374
2010-01-13 00:00:00.000	1.0023638081
2010-01-14 00:00:00.000	1.0023856533
2010-01-00 00:00:00.000	1.0024083955
2010-01-18 00:00:00.000	1.0024779677
2010-01-19 00:00:00.000	1.0025020553
2010-01-20 00:00:00.000	1.002521135
2010-01-21 00:00:00.000	1.0025420688
2010-01-22 00:00:00.000	1.0025593397
2010-01-25 00:00:00.000	1.0026180146
2010-01-26 00:00:00.000	1.002637573
2010-01-27 00:00:00.000	1.0026648447
2010-01-28 00:00:00.000	1.0026957934
2010-01-29 00:00:00.000	1.0027267421
2010-02-01 00:00:00.000	1.0028195885
2010-02-02 00:00:00.000	1.0028573523
2010-02-03 00:00:00.000	1.0028964611
2010-02-04 00:00:00.000	1.00293557
2010-02-05 00:00:00.000	1.002973334
2010-02-08 00:00:00.000	1.0030879717
2010-02-09 00:00:00.000	1.0031279777
2010-02-10 00:00:00.000	1.003171166
2010-02-11 00:00:00.000	1.0032007452
2010-02-12 00:00:00.000	1.0032575895
2010-02-00 00:00:00.000	1.0033749191
2010-02-1 00:00:00.000	1.0034140292
2010-02-17 00:00:00.000	1.003452691
2010-02-18 00:00:00.000	1.0034918013
2010-02-19 00:00:00.000	1.0035395633
2010-02-22 00:00:00.000	1.0036664439
2010-02-23 00:00:00.000	1.0037042097
2010-02-24 00:00:00.000	1.0037510759
2010-02-25 00:00:00.000	1.0038001834
2010-02-26 00:00:00.000	1.003850077

I need to write a query to get index based on (Last day of current month/Previous month last day) - 1 * 100.So that output comes something like this

31-Jan-10	0.01%
28-Feb-10	0.02%
31-Mar-10	0.00%

Following is one of the solution I thought about however please share best ideas to implement this problem

Extract last day of all the months with values into a temp table and then order by dates so that they subtract and put the values into another temp table

Looking forward to your help.

Answer 1

You can use the LAG function introduced in SQL2012 to make it a bit easier:

WITH DataWithOrder AS
(
    SELECT DateField, PriceField,
        ROW_NUMBER() OVER(PARTITION BY YEAR(DateField), Month(DateField) ORDER BY DateField DESC) AS Pos
    FROM PriceTable
)
SELECT
  DateField,
  PriceField,
  LAG(PriceField) OVER(ORDER BY DateField) AS PriceLastMonth,
  ((PriceField / LAG(PriceField) OVER(ORDER BY DateField)) - 1) * 100 AS PCIncrease
FROM DataWithOrder
WHERE Pos = 1
ORDER BY DateField

Answer 2

Try this....

DECLARE @StartDate  DATETIME = '2010-01-01',
    @EndDate    DATETIME = GETDATE();

WITH data AS (
SELECT 1 AS i, CONVERT(DATETIME, NULL) AS StartDate, DATEADD(MONTH, 0, @StartDate) - 1 AS EndDate
UNION ALL
SELECT i + 1, data.EndDate, DATEADD(MONTH, i, @StartDate) - 1 AS EndDate
FROM data
WHERE DATEADD(MONTH, i, @StartDate) - 1 < @EndDate
) 

SELECT (
((SELECT TOP 1 Rate FROM RateTable WHERE Date <= data.EndDate ORDER BY Date DESC) /
 (SELECT TOP 1 Rate FROM RateTable WHERE Date <= data.StartDate ORDER BY Date DESC)- 1) * 100)
FROM DATA                                             -- parenthesis were causing issues
WHERE data.StartDate IS NOT NULL
OPTION (MAXRECURSION 10000);

You'll need to replace the

(SELECT Rate FROM RateTable WHERE Date = data.StartDate)
and
(SELECT Rate FROM RateTable WHERE Date = data.EndDate)

With the values for your rate table. as you didn't mention column and table names in your question.

rwking indicated that there might be gaps in the rates table that would cause issues. I've modified the subquery to bring back the first rate on or nearest the start and end dates.

Hope that helps

Answer 3

I took a very different approach than the other guy. His is more elegant and would work better if the daily data does represent every single day of every month. If there are gaps in days, however, as your sample data represents, you can try the following code.

with cte as (select mydate
                  , price
                  , ROW_NUMBER() over(partition by YEAR(mydate), MONTH(mydate) 
                                      order by day(mydate) desc) row_n
             from #temp)
select mydate, price, ROW_NUMBER() over(order by mydate desc) row_num
into #temp2
from cte
where row_n = 1          

alter table #temp2
add idx float

declare @counter int = 1
while @counter < (select MAX(row_num)+1 from #temp2)

begin
update t2
set t2.idx = ((t2.price/t3.price)-1)*100
from #temp2 t2 left join 
     #temp2 t3 on 1 = 1 
where t2.row_num = @counter and t3.row_num = @counter + 1

set @counter = @counter + 1
end

select mydate, idx
from #temp2

As the other poster mentioned, you didn't provide column or table names. My process was to insert your data into a table called [#temp] with column names [mydate] and [price].

Also, the data sample you provided contains two invalid dates that I changed to arbitrary dates just for the purposes of getting code to run. (2010-01-00 and 2010-02-00)