Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Query Help Expand / Collapse
Author
Message
Posted Thursday, August 21, 2014 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:53 PM
Points: 17, Visits: 53
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
Post #1605879
Posted Thursday, August 21, 2014 1:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 110, Visits: 358
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.
Post #1605980
Posted Thursday, August 21, 2014 1:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,246, Visits: 3,686
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
Post #1605990
Posted Thursday, August 21, 2014 9:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:53 PM
Points: 17, Visits: 53
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.

Post #1606095
Posted Thursday, August 21, 2014 10:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:53 AM
Points: 2,228, Visits: 6,024
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 HardDrink


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;

Post #1606104
Posted Friday, August 22, 2014 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:53 PM
Points: 17, Visits: 53
Hi Eirikur ,

Thanks for taking time out and replying.

Still i am not able to move forward.
Post #1606211
Posted Friday, August 22, 2014 7:46 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
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
Post #1606293
Posted Friday, August 22, 2014 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:53 PM
Points: 17, Visits: 53
Thank you very much Chris.

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

Best Regards.
Post #1606343
Posted Saturday, August 23, 2014 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:53 PM
Points: 17, Visits: 53
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

Post #1606594
Posted Saturday, August 23, 2014 4:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 8:53 PM
Points: 17, Visits: 53
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. ?
Post #1606606
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse