Case when inside of partition by?

• drew.allen

  SSC Guru

  Points: 76984

  More actions

  December 7, 2018 at 7:14 am

  #2015753

  Data Cruncher - Friday, December 7, 2018 4:42 AM

  Thanks Drew, actually your solution works well for the given dataset, but it assumes facts about data:
  If tomorrow my user decides to rename the states State_Z and State_A instead of State_1 and State_2, or if a user violates the assumption about the dates (State_1 date < State_2 date), I'm busted.

  It actually doesn't assume that.  It assumes that you have some field (possibly computed) used to order the various states.  You just use whatever that field is for the first part of the sort.  If you don't have such a field, then this problem is intractable.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  December 10, 2018 at 8:54 am

  #2015890

  Data Cruncher - Wednesday, December 5, 2018 9:35 AM

  Hi smugson,
  I'm sorry but it doesn't do the trick:
  
  CREATE TABLE #TAB1(
  ID INT
  ,MY_STATE VARCHAR(10)
  ,EVENT_TS DATETIME
  )

  INSERT INTO #TAB1(ID, MY_STATE, EVENT_TS)
       VALUES (1, 'STATE_1', GETDATE())
               ,(1, 'STATE_1', DATEADD(DAY,-1, GETDATE()))
               ,(1, 'STATE_2', DATEADD(DAY,-2, GETDATE()))
               ,(1, 'STATE_2', DATEADD(DAY,2, GETDATE()))
               ,(1, 'STATE_3', DATEADD(DAY,-5, GETDATE()))
               ,(2, 'STATE_1', NULL)
               ,(2, 'STATE_2', GETDATE())
               ,(2, 'STATE_3', DATEADD(DAY,2,GETDATE()))

  Your query returns STATE_3 for the ID 2, while I'd like to see STATE_2 paired with ID: 2

  Then you'll have to be anally specific about the rules to be followed as to which state to choose.
  Those rules will have to work correctly for ALL possible scenarios.   As it was, you were already
  not specific enough, so I had to guess at the rules.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Data Cruncher

  SSC-Addicted

  Points: 499

  More actions

  December 10, 2018 at 9:23 am

  #2015899

  Now, I don't want to be rude but:

  Having sample data that explains the full scenario at the start is always helpful. Smile

  Which is why you should provide all necessary 'facts' up front instead of drip-feeding them. If you don't mention them, how is anyone (apart from you) going to know?

  Then you'll have to be anally specific about the rules to be followed as to which state to choose.
  Those rules will have to work correctly for ALL possible scenarios. As it was, you were already
  not specific enough, so I had to guess at the rules.

  I got it. I'll try to be more precise the next time.

• uravindarreddy

  Ten Centuries

  Points: 1390

  More actions

  December 12, 2018 at 3:37 am

  #2016077

  Hi Data Cruncher,

  I guess what you are looking at is the below

  SELECT TOP(1) WITH TIES ID, MY_STATE, EVENT_TS
FROM #TAB1
ORDER BY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY  CASE WHEN EVENT_TS IS NOT NULL THEN 0 ELSE 1 END, MY_STATE,  EVENT_TS DESC);


  Try this and let me know. Do you want me to explain the code?