JackTimber (8/23/2014)

---

Suppose if i decided to tweak my result as

/*

I want to traverse through a loop. Loop starts with Coke/Fanta and Ends With HardDrink.

After HardDrink it is neccessarily Coke or Fanta.

Now over here i want the value from the column "value" for Pepsi.

There can be a suituation where Pepsi can occur more then once in the loop between

Coke/Fanta and HardDrink.

*/

CREATE TABLE #TEMP (UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)

INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12') --Just one pepsi record in this loop would be displayed

INSERT INTO #TEMP VALUES ('ABC','Fanta',50,'2014-01-13')

INSERT INTO #TEMP VALUES ('ABC','Pepsi',50,'2014-01-14')

INSERT INTO #TEMP VALUES ('ABC','HardDrink',75,'2014-01-18')

INSERT INTO #TEMP VALUES ('ABC','Fanta',36,'2014-01-20') -- No Pepsi in this loop so DISPLAY THE RECORD WITH COKE/FANTA

INSERT INTO #TEMP VALUES ('ABC','XXX',45,'2014-01-28')

INSERT INTO #TEMP VALUES ('ABC','XXX',75,'2014-02-04')

INSERT INTO #TEMP VALUES ('ABC','XXX',96,'2014-02-06')

INSERT INTO #TEMP VALUES ('ABC','HardDrink',12,'2014-02-10')

INSERT INTO #TEMP VALUES ('ABC','Fanta',93,'2014-02-22') -- 2 Pepsi in this loop but the one with the value 510 would be shown because it is latest

INSERT INTO #TEMP VALUES ('ABC','XXX',101,'2014-02-22')

INSERT INTO #TEMP VALUES ('ABC','Pepsi',150,'2014-02-23')

INSERT INTO #TEMP VALUES ('ABC','Pepsi',510,'2014-02-24')

INSERT INTO #TEMP VALUES ('ABC','HardDrink',175,'2014-02-25')

INSERT INTO #TEMP VALUES ('ABC','Fanta',136,'2014-02-26') --Since there is no end, Hence Either Fanta or Coke which ever latest record would be displayed. In this case Fanta

INSERT INTO #TEMP VALUES ('ABC','XXX',145,'2014-02-27')

INSERT INTO #TEMP VALUES ('ABC','XXX',175,'2014-02-28')

INSERT INTO #TEMP VALUES ('ABC','XXX',196,'2014-03-01')

INSERT INTO #TEMP VALUES ('ABC','XXX',112,'2014-03-02')

INSERT INTO #TEMP VALUES ('ABC','XXX',193,'2014-03-03')

SELECT * FROM #TEMP

/*

So my output would be like

UserName DrinkValueCreatedDate

ABC Pepsi502014-01-14

ABC Fanta362014-01-20

ABC Pepsi5102014-02-25

ABC Fanta1362014-02-26

*/

That while in a group of Coke/Fanta to HardDrink if no Pepsi record found then return Coke/Fanta record.

I am trying on this, lets see 🙂