如何动态获取当前值和过去值 [英] How to get current and past values dynamically

问题描述

嗨专家，

我的问题只能根据一个例子来解释。



tblMain

Hi Experts,
my problem can be only explained based on an example.

tblMain

 Year       ProjectNr  cur.yr  cur.yr+1   cur.yr+2   cur.yr+3    cur.yr+4
03.2012      Z.123      10       20        30         40          50
03.2012      D.489      300      400       500        600         700
04.2014      R.345      70       80        90         95          98
04.2014      Z.123      100      150       200       250          180
06.2016      D.489      45       55        65         75          80
06.2016      Z.123      25       35        45         95          105

PS：cur.year =当前年份（2016年）。所以+1 = 2017等等

我的目的是创建一个带有两个参数的商店程序来保存年份。

PS: cur.year = current year (2016). So +1 = 2017, etc
My intention is to create a store procedure with two parameters to hold the years.

CREATE PROC spTest
 (
  @Year1 VARCHAR (150)
  @Year2 VARCHAR (150)
 )

然后是一个逻辑，以获得cur.yr（2016）中的值作为[Current]和2016年的假设值：

预期结果

And then a logic to get the value in cur.yr (2016) as [Current] and the suppose value of 2016 in year 2014:
EXPECTED RESULT

@Year1 = 03.2012, @Year2 = 06.2016

ProjectNr.   Current period (2016)   Supposed period (2016)
 Z.123               25                            50

@Year1 = 04.2014, @Year2 = 06.2016

ProjectNr.   Current period (2016)   Supposed period (2016)
 Z.123               25                            200

And if I set the two parameters to @yaer1 = '04.2014', @year2 = '06.2016' Then
ProjectNr. Current period (2016) Supposed period (2016)
Z.123          25                        200

So @year2 is the cuurent year so it get the value 24 form the column cur.yr.
@year1 is not now (2016), so it goes to @year1 and then search for the current year(2016) and then give me the value.

For 2012 it goes to the cloumn cur.yr+4 (2012 + 4 = 2016)
2014 it goes to the column cur.yr+2 (2014 + 2 = 2016)

我希望它有点可以理解。



我尝试了什么：

I hope it is a little bit understandable.

What I have tried:

CREATE PROC spTest
 (
  @Year1 VARCHAR (150)
  @Year2 VARCHAR (150)
 )
DECLARE YEAR INT
DECLARE YEAR+1
DECLARE YEAR+2
DECLARE YEAR+3

SET YEAR = SELECT (year(getdate())
SET YEAR+1 = YEAR + 365
SET YEAR +2 = YEAR +730
....

但我认为我的想法并不好。

But I think my thinking isn't good.

推荐答案

此查询获得您为项目Z.123列出的预期结果

This query gets the expected results you listed for project Z.123

DECLARE @delta INT = DATEPART(YY, GETDATE()) - CAST(SUBSTRING(@Year1, 4, 4) AS INT)

;WITH q AS
(
	SELECT 'Supposed' AS Period, ProjectNr,  
		CASE	WHEN @delta = 0 THEN curyr
				WHEN @delta = 1 THEN yr1
				WHEN @delta = 2 THEN yr2
				WHEN @delta = 3 THEN yr3
				ELSE yr4 END as Val
	FROM Test 
	WHERE [Year] = @Year1
	UNION
	SELECT 'Current', ProjectNr, curyr FROM Test 
	WHERE [Year] = @Year2
)
SELECT A.ProjectNr, A.Val as [Current], B.Val as [Supposed]
FROM q A
INNER JOIN q B ON A.ProjectNr=B.ProjectNr AND B.Period = 'Supposed'
WHERE A.Period = 'Current'

对于第一组参数我得到了这些结果

For you first set of parameters I got these results

D.489	45	700
Z.123	25	50

和第二个例子

Z.123	25	200

如果这不是您想要的，您将不得不找到更好的方法来解释您的问题

If this isn't what you wanted you'll have to find a better way to explain your problem

这篇关于如何动态获取当前值和过去值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！