September 4, 2014 at 6:34 am
Hi all experts,
I am sorry to post the problem of the same type , but i am not able to logically interpret how to traverse through the queries 🙁
Please see this code snippet under which i am trying to solve my problem , if possible pls help :crying:
/*
I want to traverse through the loop and find the event before HardDrink.
*/
CREATE TABLE #TEMP (UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12') --Starts with coke or fanta (1st Cycle)
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') --ends with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',36,'2014-01-20') --Starts with Fanta (2nd Cycle)
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') --ends with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',93,'2014-02-22') --Starts with Fanta (3rd Cycle)
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') --end with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',136,'2014-02-26') --Starts with Fanta (4th Cycle)
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')
/*If harddrink present then the drink above it,
for the last cycle , hardrink may or may not be present so if hardrink present then take the take the drink before it
else the last drink.
Output:
UserNameDrinkValue
ABCpepsi50
ABCxxx96
ABCpepsi510
ABCxxx193
*/
September 4, 2014 at 6:59 am
JackTimber (9/4/2014)
Hi all experts,I am sorry to post the problem of the same type , but i am not able to logically interpret how to traverse through the queries 🙁
Please see this code snippet under which i am trying to solve my problem , if possible pls help :crying:
I've always called this kind of problem a "data smudge". Essentially, you want to smudge the data together :hehe:
Try: -
SELECT ad.UserName, ad.Drink, ad.Value, ad.CreatedDate
FROM (
SELECT UserName, Drink, Value, CreatedDate,
MAX(CreatedDate) OVER ( ) AS MaxDate
FROM #TEMP
) a
CROSS APPLY (
SELECT TOP 1
b.UserName, b.Drink, b.Value, b.CreatedDate
FROM #TEMP b
WHERE (
a.MaxDate = a.CreatedDate
AND a.Drink <> 'HardDrink'
AND a.CreatedDate = b.CreatedDate
)
OR (
a.Drink = 'HardDrink'
AND b.[CreatedDate] < a.[CreatedDate]
)
--== THIS ORDER BY IS VERY IMPORTANT!
ORDER BY b.[CreatedDate] DESC
) ad;
Returns: -
UserName Drink Value CreatedDate
-------------------- -------------------- ----------- -----------
ABC Pepsi 50 2014-01-14
ABC XXX 96 2014-02-06
ABC Pepsi 510 2014-02-24
ABC XXX 193 2014-03-03
September 4, 2014 at 7:02 am
Not really sure what you are trying to do here - but this is a simple way to assign numbers to the Cycles in your data set:
;WITH CycleEnds AS (
SELECT UserName, CreatedDate, Cycle = ROW_NUMBER() OVER(ORDER BY CreatedDate)
FROM #TEMP
WHERE Drink = 'HardDrink')
SELECT t.*, ou.Cycle
FROM #TEMP t
OUTER APPLY (
SELECT TOP 1 Cycle
FROM CycleEnds c
WHERE c.UserName = t.UserName AND c.CreatedDate >= t.CreatedDate
ORDER BY CreatedDate
) ou
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
September 4, 2014 at 9:54 pm
Cadavre (9/4/2014)
JackTimber (9/4/2014)
Hi all experts,I am sorry to post the problem of the same type , but i am not able to logically interpret how to traverse through the queries 🙁
Please see this code snippet under which i am trying to solve my problem , if possible pls help :crying:
I've always called this kind of problem a "data smudge". Essentially, you want to smudge the data together :hehe:
Try: -
SELECT ad.UserName, ad.Drink, ad.Value, ad.CreatedDateFROM (
SELECT UserName, Drink, Value, CreatedDate,
MAX(CreatedDate) OVER ( ) AS MaxDate
FROM #TEMP
) a
CROSS APPLY (
SELECT TOP 1
b.UserName, b.Drink, b.Value, b.CreatedDate
FROM #TEMP b
WHERE (
a.MaxDate = a.CreatedDate
AND a.Drink <> 'HardDrink'
AND a.CreatedDate = b.CreatedDate
)
OR (
a.Drink = 'HardDrink'
AND b.[CreatedDate] < a.[CreatedDate]
)
--== THIS ORDER BY IS VERY IMPORTANT!
ORDER BY b.[CreatedDate] DESC
) ad;
Returns: -
UserName Drink Value CreatedDate-------------------- -------------------- ----------- -----------
ABC Pepsi 50 2014-01-14
ABC XXX 96 2014-02-06
ABC Pepsi 510 2014-02-24
ABC XXX 193 2014-03-03
Thanks for the help 🙂
since yesterday i am trying to tiled your solution to solve my problem.
As in my case there would be different user , so i put the join on UserName . Consider below for it.
SELECT ad.UserName, ad.Drink, ad.Value, ad.CreatedDate
FROM (
SELECT UserName, Drink, Value, CreatedDate,
MAX(CreatedDate) OVER ( ) AS MaxDate
FROM #TEMP
) a
CROSS APPLY (
SELECT TOP 1
b.UserName, b.Drink, b.Value, b.CreatedDate
FROM #TEMP b
WHERE a.UserName =b.UserName /*Change over here but not working,apart from cross applyi tried Out with Outer apply but it is also not working */
(
a.MaxDate = a.CreatedDate
AND a.Drink <> 'HardDrink'
AND a.CreatedDate = b.CreatedDate
)
OR (
a.Drink = 'HardDrink'
AND b.[CreatedDate] < a.[CreatedDate]
)
--== THIS ORDER BY IS VERY IMPORTANT!
ORDER BY b.[CreatedDate] DESC
September 5, 2014 at 12:23 am
JackTimber (9/4/2014)
Thanks for the help 🙂since yesterday i am trying to tiled your solution to solve my problem.
As in my case there would be different user , so i put the join on UserName . Consider below for it.
SELECT ad.UserName, ad.Drink, ad.Value, ad.CreatedDate
FROM (
SELECT UserName, Drink, Value, CreatedDate,
MAX(CreatedDate) OVER ( ) AS MaxDate
FROM #TEMP
) a
CROSS APPLY (
SELECT TOP 1
b.UserName, b.Drink, b.Value, b.CreatedDate
FROM #TEMP b
WHERE a.UserName =b.UserName /*Change over here but not working,apart from cross applyi tried Out with Outer apply but it is also not working */
(
a.MaxDate = a.CreatedDate
AND a.Drink <> 'HardDrink'
AND a.CreatedDate = b.CreatedDate
)
OR (
a.Drink = 'HardDrink'
AND b.[CreatedDate] < a.[CreatedDate]
)
--== THIS ORDER BY IS VERY IMPORTANT!
ORDER BY b.[CreatedDate] DESC
Can you include some sample data with a second user, a long with expected results please?
I'm currently on my phone, so this is untested but I suspect you want something like this: -
SELECT ad.UserName, ad.Drink, ad.Value, ad.CreatedDate
FROM (
SELECT UserName, Drink, Value, CreatedDate,
MAX(CreatedDate) OVER ( PARTITION BY UserName ) AS MaxDate
FROM #TEMP
) a
CROSS APPLY (
SELECT TOP 1
b.UserName, b.Drink, b.Value, b.CreatedDate
FROM #TEMP b
WHERE a.UserName = b.UserName
AND (
a.MaxDate = a.CreatedDate
AND a.Drink <> 'HardDrink'
AND a.CreatedDate = b.CreatedDate
)
OR (
a.Drink = 'HardDrink'
AND b.[CreatedDate] < a.[CreatedDate]
)
--== THIS ORDER BY IS VERY IMPORTANT!
ORDER BY b.[CreatedDate] DESC
) ad;
September 5, 2014 at 4:02 am
Thank u Cadavre for quick response.
I am sorry to pluzzel you with my requirement. But i thought of giving myself a day and try to come up with a solution but 🙁
To understand please find my inline comments in the below code snippet.
CREATE TABLE #TEMP (UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12') --Starts with coke or fanta (1st Cycle)
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') --ends with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',36,'2014-01-20') --Starts with Fanta (2nd Cycle)
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') --ends with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',93,'2014-02-22') --Starts with Fanta (3rd Cycle)
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') --end with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',136,'2014-02-26') --Starts with Fanta (4th Cycle)
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
/*
1. Take record before last , from each cycle , and Value 1 and Value2 should be same if HardDrink is present
If HardDrink is not present then consider the last record such that Value1 column should contain last
record value and Value2 column should contain cycle start value (i.e. Coke or Fanta value)
The Output should be as,
UserName DrinkValue1Value2
ABCPepsi 5050
ABCXXX 9696
ABCpepsi 510510
ABCXXX193145
*/
September 6, 2014 at 9:10 am
Not much of a change needed from last time, adding a second conditional in the last part to allow for missing HardDrink did the trick.;-)
😎
USE tempdb;
GO
CREATE TABLE #TEMP (UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12') --Starts with coke or fanta (1st Cycle)
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') --ends with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',36,'2014-01-20') --Starts with Fanta (2nd Cycle)
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') --ends with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',93,'2014-02-22') --Starts with Fanta (3rd Cycle)
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') --end with HardDrink
INSERT INTO #TEMP VALUES ('ABC','Fanta',136,'2014-02-26') --Starts with Fanta (4th Cycle)
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 BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY T.CreatedDate) AS T_RID
,T.UserName
,T.Drink
,T.Value
,T.CreatedDate
FROM #TEMP T
)
SELECT
BD.UserName
,BD.Drink
,BD.Value
,BD.CreatedDate
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA BLEAD
ON BD.T_RID = BLEAD.T_RID - 1
WHERE BLEAD.Drink = 'HardDrink'
OR BLEAD.Drink IS NULL;
DROP TABLE #TEMP
Results
UserName Drink Value CreatedDate
-------------------- -------------------- ----------- -----------
ABC Pepsi 50 2014-01-14
ABC XXX 96 2014-02-06
ABC Pepsi 510 2014-02-24
ABC XXX 193 2014-03-03
September 8, 2014 at 6:19 am
JackTimber (9/5/2014)
Thank u Cadavre for quick response.I am sorry to pluzzel you with my requirement. But i thought of giving myself a day and try to come up with a solution but 🙁
To understand please find my inline comments in the below code snippet.
Sorry for the late reply.
You haven't actually included the hypothetical second user in your new sample data, my original code works on this new sample data.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply