August 28, 2014 at 9:51 am
Hi All Expert,
I need help with the below.Could somebody pls help me with this.
/*
I want to traverse through the loop and find the event before HardDrink.
If no hardDrink then blank.
*/
CREATE TABLE #TEMP (UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12')
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')
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')
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')
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 DrinkValue
ABC Pepsi50
ABC xxx96
ABC Pepsi 510
*/
August 28, 2014 at 10:09 am
JackTimber (8/28/2014)
Hi All Expert,I need help with the below.Could somebody pls help me with this.
/*
I want to traverse through the loop and find the event before HardDrink.
If no hardDrink then blank.
*/
CREATE TABLE #TEMP (UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12')
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')
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')
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')
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 DrinkValue
ABC Pepsi50
ABC xxx96
ABC Pepsi 510
*/
This looks amazingly similar to your recent thread. http://www.sqlservercentral.com/Forums/Topic1605879-392-1.aspx
The part that concerns me is that if you are unable to figure out the minor difference from the solution there and this one it is obvious you don't understand the solution to your other query. You need to read and understand that query. Once you understand it, the change to this will be really simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2014 at 10:25 am
I tried figuring out that , but 🙁
August 28, 2014 at 10:30 am
JackTimber (8/28/2014)
I tried figuring out that , but 🙁
reading the previous thread...it seems that you have 20 million rows....
what solution did you finally decide upon...pls post that here ....and as Sean says it may be easy for us to guide you on this current question
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 28, 2014 at 11:18 am
Actaully i was not able to come up with a solution for that problem, so the assignment was given to other guy. I had shown him this thread. He said that , he used ChrisM solution. It work prefectly fine for himwith some changes.
There were changes because i had not posted the correct info at first.
August 28, 2014 at 11:28 am
JackTimber (8/28/2014)
Actaully i was not able to come up with a solution for that problem, so the assignment was given to other guy. I had shown him this thread. He said that , he used ChrisM solution. It work prefectly fine for himwith some changes.There were changes because i had not posted the correct info at first.
...ok so have you posted the correct info this time ??? 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 28, 2014 at 11:42 am
Naa re, its a different asignment. I am working by keeping ChrisM solution as a base from previous example, and lets see.
If i come up with a solution would definitely post the result . 🙂
August 28, 2014 at 11:48 am
JackTimber (8/28/2014)
Naa re, its a different asignment. I am working by keeping ChrisM solution as a base from previous example, and lets see.If i come up with a solution would definitely post the result . 🙂
If you can post up a representative sample data set, your current query, and the results you are expecting, there are plenty of folks ready to help you.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 28, 2014 at 12:20 pm
JackTimber (8/28/2014)
Naa re, its a different asignment. I am working by keeping ChrisM solution as a base from previous example, and lets see.If i come up with a solution would definitely post the result . 🙂
ok..I am not really sure where you are with understanding the previous thread solutions and what you have posted as a "new" question in this thread.
for what it is worth...and with no relevance to your previous post, and based entirely on the sample data you have posted in this thread....here is a possible solution that provides what you have requested...whether this delivers effectively ...only you can tell...and then tell us if you need more help.
WITH CTE_SORT as (
SELECT
UserName
, Drink
, Value
, CreatedDate
, ROW_NUMBER() OVER (ORDER BY createddate) AS rn
FROM #TEMP)
,
CTE_HD as (
SELECT
rn - 1 AS sn
FROM cte_sort
WHERE (Drink = 'harddrink'))
SELECT
cs.UserName
, cs.Drink
, cs.Value
FROM cte_sort cs INNER JOIN
cte_hd ch ON cs.rn = ch.sn;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 28, 2014 at 12:23 pm
I tried a little bit on solution and got it right :w00t:
So here is the code
SELECT t.UserName, t.Drink, t.CreatedDate, x.Value
FROM #TEMP t
OUTER APPLY (
SELECT TOP 1 Value
FROM
(
--SELECT * FROM #TEMP
SELECT TOP 2 Value , CreatedDate
FROM #TEMP TI
WHERE TI.UserName = T.UserName
AND TI.CreatedDate < T.CreatedDate
--AND TI.Drink IN ('HardDrink')
ORDER BY TI.CreatedDate DESC
) T
ORDER BY CreatedDate DESC
) x
WHERE t.Drink IN ('HardDrink')
August 29, 2014 at 8:21 am
Hi Experts,
There would be a better solution then , the one mention above by me.
If some body has it would be great to know. Just to improve on my own. 🙂
August 30, 2014 at 10:27 am
Shadab Shah (8/29/2014)
Hi Experts,There would be a better solution then , the one mention above by me.
If some body has it would be great to know. Just to improve on my own. 🙂
Unfortunately, your code doesn't return the right result
😎
UserName Drink CreatedDate Value
-------------------- -------------------- ----------- -----------
ABC HardDrink 2014-01-18 50
ABC HardDrink 2014-02-10 96
ABC HardDrink 2014-02-25 510
The expected results
UserName Drink Value
ABC Pepsi 50
ABC xxx 96
ABC Pepsi 510
August 30, 2014 at 10:39 am
Just in case you missed this from the previous instance of the question, modified to return blank if no HardDrink
😎
USE tempdb;
GO
SET NOCOUNT ON;
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')
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')
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')
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')
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')
CREATE INDEX TMP_TBL_CREATEDATE_ASC_INCL_ALL ON #TEMP (CreatedDate ASC) INCLUDE (UserName,Drink,Value) WHERE (Drink <> 'XXX');
SET STATISTICS IO ON;
;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
WHERE T.Drink <> 'XXX'
)
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';
SET STATISTICS IO OFF;
DROP TABLE #TEMP;
Results
UserName Drink Value CreatedDate
-------------------- -------------------- ----------- -----------
ABC Pepsi 50 2014-01-14
ABC Fanta 36 2014-01-20
ABC Pepsi 510 2014-02-24
Table '#TEMP'. Scan count 2, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply