SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
JackTimber
JackTimber
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 64
Hi All Experts,

I am a newbie in SQL Server and would like your help on the following query.

Please consider the below sample data



/*
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 no pepsi record would be displayed
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 Drink Value CreatedDate
ABC Pepsi 50 2014-01-14
ABC Pepsi 510 2014-02-25
ABC Fanta 136 2014-02-26


*/





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.

So my output would be like
UserName Drink Value CreatedDate
ABC Pepsi 50 2014-01-14
ABC Pepsi 510 2014-02-25
ABC Fanta 136 2014-02-26
Xedni
Xedni
Say Hey Kid
Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)Say Hey Kid (691 reputation)

Group: General Forum Members
Points: 691 Visits: 716
It's not clear to me what the transformation is that you're trying to achieve here. Also, iterating over names doesn't make a whole lot of sense. Are you trying to sum up the values of each drink type? are you trying to eliminate values from the set?

Executive Junior Cowboy Developer, Esq.
Ed Wagner
Ed Wagner
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17027 Visits: 10089
I don't see what you're after either. Good job posting consumable DDL and sample data. Could you please explain the logic behind how you get the output you're trying to produce?


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
JackTimber
JackTimber
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 64
Ed Wagner (8/21/2014)
I don't see what you're after either. Good job posting consumable DDL and sample data. Could you please explain the logic behind how you get the output you're trying to produce?


Well the logic is to traverse through Coke/Fanta to HardDrink. In Between this if you find Pepsi Display it if not then dont display it. If there are multiple entries of Pepsi between (Coke or Fanta) to HardDrink then display the one which is latest, which we can find out with the help of createdDate column.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15588 Visits: 18623
Quick though, first move towards an SQL set based solution is to change the word Loop to Group. Each Group starts with Coke/Fanta and ends in a HardDrinkDoze
Cool

Here is an example to get you started, it is not a full solution.
CREATE TABLE #TEMP (T_ID INT IDENTITY(1,1),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 no pepsi record would be displayed
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')

;WITH DRINK_WEIGHT(WEIGHT_VAL,DRINK_NAME) AS
(SELECT WEIGHT_VAL,DRINK_NAME FROM (
VALUES (1,'Coke'),(2,'Fanta'),(3,'Pepsi'),(4,'XXX'),(5,'HardDrink')
) AS X( WEIGHT_VAL,DRINK_NAME))
,BASE_DATA AS
(
SELECT
T_ID
,T.UserName
,T.Drink
,T.Value
,T.CreatedDate
,DW.WEIGHT_VAL
FROM #TEMP T
OUTER APPLY DRINK_WEIGHT DW
WHERE T.Drink = DW.DRINK_NAME
)
SELECT
BD.T_ID
,BD.UserName
,BD.Drink
,BD.Value
,BD.CreatedDate
,BD.WEIGHT_VAL
FROM BASE_DATA BD

DROP TABLE #TEMP;


JackTimber
JackTimber
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 64
Hi Eirikur ,

Thanks for taking time out and replying.

Still i am not able to move forward.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16800 Visits: 19557
SELECT UserName, Drink, Value, CreatedDate
FROM ( -- d
SELECT UserName, Drink, Value, CreatedDate,
rn = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY CreatedDate DESC)
FROM #TEMP t
OUTER APPLY ( -- ou
SELECT TOP 1 grp
FROM (SELECT UserName, CreatedDate, grp = ROW_NUMBER() OVER(ORDER BY CreatedDate) FROM #TEMP WHERE Drink = 'HardDrink') ti
WHERE ti.UserName = t.UserName
AND ti.CreatedDate > t.CreatedDate
AND t.Drink <> 'HardDrink'
ORDER BY ti.CreatedDate ASC
) ou
WHERE (t.Drink = 'Pepsi' AND grp > 0)
OR (t.Drink IN ('Fanta','Coke') AND grp IS NULL)
) d
WHERE rn = 1
ORDER BY UserName, CreatedDate



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
JackTimber
JackTimber
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 64
Thank you very much Chris.

Could i know your twitter handel, so that i can follow you.

Best Regards.
JackTimber
JackTimber
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 64
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 Drink Value CreatedDate
ABC Pepsi 50 2014-01-14
ABC Fanta 36 2014-01-20
ABC Pepsi 510 2014-02-25
ABC Fanta 136 2014-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 :-)
JackTimber
JackTimber
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 64
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 Drink Value CreatedDate
ABC Pepsi 50 2014-01-14
ABC Fanta 36 2014-01-20
ABC Pepsi 510 2014-02-25
ABC Fanta 136 2014-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 :-)



Anybody would like to help me with this. ? Doze
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search