How to get current and past values dynamically

Question

2.00/5 (1 vote)

See more:

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 = current year (2016). So +1 = 2017, etc
My intention is to create a store procedure with two parameters to hold the years.

SQL

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

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

C#

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:

SQL

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.

Posted 25-May-16 9:40am

mikybrain1

Updated 27-May-16 5:24am

v3

Add a Solution

Comments

CHill60 25-May-16 16:44pm

Your thinking is not good. You will get "Incorrect syntax near '+'." on your DECLARE statements.
Is that the part you are stuck on or are you stuck on getting the expected results - because the algorithm for returning the expected results is not at all clear

mikybrain1 26-May-16 5:32am

Hi Chill60,
Lets forget what I have tried so far. Let say I am stucked in getting the expected results.
My intention is to get the value in this year (2016) which is the current year and get the value of the year (either 2012 or 2014) which I thougt will accrued in the cuurent year (2016)
Example
if I set the two parameters to @yaer1 = '03.2012', @year2 = '06.2016' Then
ProjectNr. Current period (2016) Supposed period (2016)
Z.123 25 50

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 somehow clear :)

Herman<T>.Instance 26-May-16 7:07am

You write:
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

Why is current period not 2014 (based on parm1)?

mikybrain1 26-May-16 8:18am

Because I want compare parm1 and parm2 and then get the Delta.

The value now (2016) and the value I planed in 2014 for 2016 and then the Delta.

So in this case:
ProjectNr Current period (2016) Supposed period (2016) Delta
Z.123 25 200 -175

Herman<T>.Instance 26-May-16 10:36am

I still have too less information to be able to help you

Peter Leow 26-May-16 3:51am

Try re-thinking.

Herman<T>.Instance 26-May-16 3:55am

and what do you select in the SP? That is where the problem is I believe. Because Year(Field) between start and end wouldn't be hard thinking...

mikybrain1 26-May-16 5:41am

Hi digimanus,
My SP is wired. Can u please take a look at my reply to Chill60 and see if u can help?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-05-27T05:24:00

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

SQL

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

and for the second example

Z.123	25	200

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