CODEWITHSUNDEEP
sql-server
exelesem
exelesem

Reputation: 1

Is there a more effecient way to run this sql script

I have daily data in the 'portfoliodata' table. Not all days have data so some month ends don't have data. I want to extract data from the latest day in each month that has data

declare @Portfolio as float
,@Date as date
,@Date12 as date
,@Date11 as date
,@Date10 as date
,@Date9 as date
,@Date8 as date
,@Date7 as date
,@Date6 as date
,@Date5 as date
,@Date4 as date
,@Date3 as date
,@Date2 as date
,@Date1 as date

set @Portfolio = 97900
select @Date = max(date) from portfoliodata  
set @Date12 = EOMONTH(DATEADD(MONTH,-1,@Date))
set @Date11 = EOMONTH(DATEADD(MONTH,-2,@Date))
set @Date10 = EOMONTH(DATEADD(MONTH,-3,@Date))
set @Date9 = EOMONTH(DATEADD(MONTH,-4,@Date))
set @Date8 = EOMONTH(DATEADD(MONTH,-5,@Date))
set @Date7 = EOMONTH(DATEADD(MONTH,-6,@Date))
set @Date6 = EOMONTH(DATEADD(MONTH,-7,@Date))
set @Date5 = EOMONTH(DATEADD(MONTH,-8,@Date))
set @Date4 = EOMONTH(DATEADD(MONTH,-9,@Date))
set @Date3 = EOMONTH(DATEADD(MONTH,-10,@Date))
set @Date2 = EOMONTH(DATEADD(MONTH,-11,@Date))
set @Date1 = EOMONTH(DATEADD(MONTH,-12,@Date))

select * from portfoliodata 
where Portfolio = @Portfolio and (
date = (select max(date) from portfoliodata  where date < @Date12 and date > @Date11) or
date = (select max(date) from portfoliodata  where date < @Date11 and date > @Date10) or
date = (select max(date) from portfoliodata  where date < @Date10 and date > @Date9) or
date = (select max(date) from portfoliodata  where date < @Date9 and date > @Date8) or
date = (select max(date) from portfoliodata  where date < @Date8 and date > @Date7) or
date = (select max(date) from portfoliodata  where date < @Date7 and date > @Date6) or
date = (select max(date) from portfoliodata  where date < @Date6 and date > @Date5) or
date = (select max(date) from portfoliodata  where date < @Date5 and date > @Date4) or
date = (select max(date) from portfoliodata  where date < @Date4 and date > @Date3) or
date = (select max(date) from portfoliodata  where date < @Date3 and date > @Date2) or
date = (select max(date) from portfoliodata  where date < @Date2 and date > @Date1) or
date = (select max(date) from portfoliodata  where date < @Date1))

The above code takes a long time to run

Upvotes: 0

Views: 72

Answers (2)

Anton Grig
Anton Grig

Reputation: 1719

Just another way is to use a subquery in the where clause to get all the target dates.

Select *
From portfoliodata
Where Portfolio = 97900
      And [date] In (Select Max([date]) From portfoliodata 
                     Where Portfolio = 97900
                     Group by Year([date]), Month([date]))

you can limit the number of months as follows:

Declare @n Int = 12

Select *
From portfoliodata
Where Portfolio = 97900
      And [date] In (Select Top (@n) Max([date]) From portfoliodata 
                     Where Portfolio = 97900
                     Group by Year([date]), Month([date])
                     Order by 1 Desc)

Upvotes: 0

Austin
Austin

Reputation: 2265

If you just need the max day of each month, just put it into a CTE with ROW_NUMBER(), and partition by the month of your date field. We can eliminate the need for querying against date at all:

with recent as
(
    select *
        , ROW_NUMBER() over (partition by month(t.date) order by t.date desc) rn
    from portfoliodata t
)
select *
from recent
where rn = 1
    and portfolio = 97900

The where rn = 1 clause will give us the most recent date for each month. However, if you have multiple rows with the same date, this will arbitrarily order those.

If you want this to work over multiple years, include a partition on the year as well:

with recent as
(
    select *
        , ROW_NUMBER() over (partition by month(t.date), year(t.date) order by t.date desc) rn
    from portfoliodata t
)
select *
from recent
where rn = 1
    and portfolio = 97900

Upvotes: 3

Related Questions