动态迭代值

Question

我使用以下查询来动态计算预算值意味着迭代所选日期值。

SUM(case when Name = 'Budget' then  Value + ((Value/@TotaldaysinMonth) * 
@DaysPastinMonth) end) as [Budget]

这里的变量@DaysPastinMonth应该是动态的。意味着如果我选择一个日期作为03/31/2017.然后，查询应运行到上个月的值。另一个例子是，如果我选择 8 月，那么我需要从 1 月到 8 月运行查询。

For Jan

SUM(case when Name = 'Budget' then  Value + ((Value/@TotaldaysinMonth) * 
@DaysPastinJanMonth) end) as [Budget]

For Feb

SUM(case when Name = 'Budget' then  Value + ((Value/@TotaldaysinMonth) * 
@DaysPastinFebMonth) end) as [Budget]

For Mar

SUM(case when Name = 'Budget' then  Value + ((Value/@TotaldaysinMonth) * 
@DaysPastinMarMonth) end) as [Budget]

此外，我还为持有DaysPastinMonth的所有12个月创建了变量。

任何人都可以建议如何使用案例陈述来实现这一点。

Answer 1

当您可以使用基于集合的操作进行操作时，您正在循环中考虑这一点。

----------------------------------------------------------
--Create a table of dates for testing
----------------------------------------------------------
if object_id('tempdb..#dates') is not null 
drop table #dates
create table #dates(d date
                    ,RN bigint)
declare @sdate datetime='2017-01-01 00:00'
declare @edate datetime='2017-7-31 00:00'
insert into #dates
select 
    DATEADD(d,number,@sdate)
    ,row_number() over (order by  (select null)) as RN
from 
    master..spt_values 
where 
    type='P' 
    and number<=datediff(d,@sdate,@edate)
declare @numOfDays int = (select count(*) from #dates)

----------------------------------------------------------
--Populate Test Data
----------------------------------------------------------
if object_id('tempdb..#testTable') is not null 
drop table #testTable
create table #testTable([Name] varchar(64), 
                        [Value] decimal (16,4),
                        DT datetime)

insert into #testTable ([Name],[Value],DT)
select
    'Budget'
    ,r.randomNumber
    ,d.d
from
    #dates d
inner join
(SELECT TOP (select @numOfDays) 
    randomNumber,
    row_number() over (order by (select null)) as RN
FROM (
    SELECT CAST(ABS(CAST(NEWID() AS binary(6)) %100000) + RAND() AS DECIMAL (16,4)) + 1  randomNumber
    FROM sysobjects) sample
    GROUP BY randomNumber
    ORDER BY randomNumber DESC) r on r.RN = d.RN
union all
select
    'Not The Budget'
    ,r.randomNumber
    ,d.d
from
    #dates d
inner join
(SELECT TOP (select @numOfDays) 
    randomNumber,
    row_number() over (order by (select null)) as RN
FROM (
    SELECT CAST(ABS(CAST(NEWID() AS binary(6)) %100000) + RAND() AS DECIMAL (16,4)) + 1  randomNumber
    FROM sysobjects) sample
    GROUP BY randomNumber
    ORDER BY randomNumber DESC) r on r.RN = d.RN


----------------------------------------------------------
--Instead of making your variables "dynamic" which
--would likely consist of some loop, just pass in the
--month you care about and let SQL do the work
----------------------------------------------------------

declare @month datetime = '2016-03-31' 
select 
    DT
    ,[Value]
    ,[Name]
    ,sum(case when [Name] = 'Budget' 
                                    then [Value] + 
                                                (([Value] / (DATEDIFF(day,DATEADD(month, DATEDIFF(month, 0, @month), 0),@month))) 
                                                *  
                                                (DATEDIFF(DAY,DATEADD(MONTH, DATEDIFF(MONTH, 0, @month)-1, 0),DATEADD(MONTH, DATEDIFF(MONTH, -1, @month)-1, -1))))  end) as Budget
from 
    #testTable
where
    DT >= DATEADD(yy, DATEDIFF(yy, 0, @month), 0) --this is Jan 1 of the year associated with your vairable
group by 
    DT
    ,[Name]
    ,[Value]