Post reply

Select vaue column for one date and another for another date

  • October 24, 2017 at 8:47 am

    #325909

    Hi Masters, how can I select a value from a column for a date and another value from another column for another date

      ID        |    DATE           | Value for A  | Value for B
      aaaa    |    24/10/2017   | 10               | 15
      bbbb    |    23/10/2017   | 50               | 20
      ........
      cccc    |    01/01/2017   | 25               | 60

    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

    #1964958

    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

    #1964963

    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

    #1965061

    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

    #1965077

    d_martins - Wednesday, October 25, 2017 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

    What have you tried so far?

  • October 25, 2017 at 4:11 am

    #1965080

    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

    #1965081

    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

    #1965082

    I'm looking toretrieve 1 row per ID.
    Thanks

  • October 25, 2017 at 7:16 am

    #1965119

    d_martins - Wednesday, October 25, 2017 4:26 AM

    I'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

    #1965203

    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://qa.sqlservercentral.com/articles/tSQLt/152358/) for a few rows and then futz with the query.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply