October 24, 2017 at 8:47 am
Hi Masters, how can I select a value from a column for a date and another value from another column for another date
I'm looking to retrieve, the Value for A of 23/10/2017 and the Value for B of 01/01/2017.
This is for a a SSRS report, by default the date parameter value will be YESTERDAY where DATE = @p_date
but the user is allowed to change. But for the Value for B, the date must be and remain the first day of the year 01/01/current year
How can we achieve this?
THANKS in advance
October 24, 2017 at 9:11 am
What specifies what days show column B. Is it purely that it has a value of your parameter @p_date? If so, what you're looking for is a CASE Expression:CASE [date] WHEN @p_date THEN [Value for B] ELSE [Value for A] END
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2017 at 9:33 am
I read it as [Value for A] is defined by parameter @p_date, while [Value for B] is defined by the static first of the current year.
DECLARE @p_date DATE = '10/23/2017'
SELECT
MAX(CASE WHEN [date] = @p_date THEN [Value for A] END) AS [Value for A],
MAX(CASE WHEN [date] = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN [Value for B] END) AS [Value for B]
FROM TestTable1 WHERE DATE = @p_date or [date] = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
October 25, 2017 at 1:58 am
Almost there.
This returns two rows per ID, I'm not showing the DATE, so can we have the VALUE for A defined by @p_date and the VALUE for B defined by the static first day of the current year in 1 row?
Thanks in advance guys
October 25, 2017 at 4:01 am
d_martins - Wednesday, October 25, 2017 1:58 AMAlmost there.
This returns two rows per ID, I'm not showing the DATE, so can we have the VALUE for A defined by @p_date and the VALUE for B defined by the static first day of the current year in 1 row?Thanks in advance guys
What have you tried so far?
October 25, 2017 at 4:11 am
I'm here, pretty much where you left off (added Value for C)
SELECT ID, [Value for A], [Value for B], [Value for C]
CASE WHEN DATE = @p_date THEN [Value for A] + [Value for B] END AS "A+C",
CASE WHEN DATE = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN [Value for B] END AS "B"
FROM TABLE WHERE DATE = @p_date or DATE = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
October 25, 2017 at 4:24 am
If you need two rows per ID then you're going to need something like a CROSS JOIN or CROSS APPLY. But I'm afraid I still don't understand your requirement. Please will you post table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and the expected results based on your sample data?
John
October 25, 2017 at 4:26 am
I'm looking toretrieve 1 row per ID.
Thanks
October 25, 2017 at 7:16 am
d_martins - Wednesday, October 25, 2017 4:26 AMI'm looking toretrieve 1 row per ID.
Thanks
Right now you've been given two guesses as to what you want based on what you've told us. At this point, providing us with the information John requested is the fastest way for us to help you.
October 25, 2017 at 12:12 pm
Your requirements are very ambiguous.
"a value from a column for a date and another value from another column for another date"
This is vague and a reason why software often has issues. Use column names, parameter names, and then show logical values.
If @pdate is a date, then you can say you want to compare @pdate to the column value for Date (horrible column name) . If this is equal, I want to return A + b. If it equals the first day of the year, return b, else ...
You should ensure you specify all parameters, and you need a better starting set of test data. Pick enough rows that describe the various permutations of data. Ideally, you'd write a test like this (http://www.sqlservercentral.com/articles/tSQLt/152358/) for a few rows and then futz with the query.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply