August 6, 2014 at 3:07 am
Hi All,
Need one help with the query. Consider the below query
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int)
--TRUNCATE TABLE #TEMP
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100)
INSERT INTO #TEMP VALUES ('ABC','Bowled',50)
INSERT INTO #TEMP VALUES ('ABC','LBW',75)
INSERT INTO #TEMP VALUES ('ABC','XXX',36)
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',45)
INSERT INTO #TEMP VALUES ('ABC','XXX',75)
INSERT INTO #TEMP VALUES ('ABC','XXX',96)
INSERT INTO #TEMP VALUES ('ABC','LBW',12)
INSERT INTO #TEMP VALUES ('ABC','XXX',93)
Now i am trying to get the result as ,
The batsmen should have got out as LBW , but his strike rate displayed should be that of either Catch Out or Hit Wicket whichever be the latest one.
PlayerName DismissFormat StrickRate
ABC LBW 100 (The strike rate of catch out)
ABC LBW 45 (The strike rate of Hit Wicket)
The total number of record return should be 2 as 2 times the batsmen is out through LBW.
August 6, 2014 at 5:55 am
;WITH SequencedData AS (SELECT Seq = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), * FROM #TEMP)
SELECT t.PlayerName, t.DismissFormat, x.StrickRate
FROM SequencedData t
OUTER APPLY (
SELECT TOP 1 StrickRate
FROM SequencedData ti
WHERE ti.PlayerName = t.PlayerName
AND ti.Seq < t.Seq
AND ti.DismissFormat IN ('Catch Out','Hit Wicket')
ORDER BY seq DESC
) x
WHERE t.DismissFormat = 'LBW'
You should create a column in your table to hold recency - ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) is a workaround.
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
August 6, 2014 at 6:03 am
There's nothing to indicate "the latest one".
The workaroud Chris had to use might not guarantee the correct output each time.
You should define for yourself what "latest one" should be based on and act accordingly (either by adding an identity column an a column holding a datetime value). This will make it a lot easier to identify the rows needed.
Keep in mind that any SELECT without ORDER BY will return the data in more or less random order. So you might not be able to return the data in the order you're looking for.
August 6, 2014 at 7:29 pm
Thanks Chris for the reply and your time.
Since yesterday i am trying to improvised on the solution given by you.
Actually there is one more condition , Consider the below
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int)
--TRUNCATE TABLE #TEMP
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100)
INSERT INTO #TEMP VALUES ('ABC','Bowled',50)
INSERT INTO #TEMP VALUES ('ABC','LBW',75)
INSERT INTO #TEMP VALUES ('ABC','XXX',36)
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',45)
INSERT INTO #TEMP VALUES ('ABC','XXX',75)
INSERT INTO #TEMP VALUES ('ABC','XXX',96)
INSERT INTO #TEMP VALUES ('ABC','LBW',12)
INSERT INTO #TEMP VALUES ('ABC','XXX',93)
INSERT INTO #TEMP VALUES ('ABC','Bowled',86)
INSERT INTO #TEMP VALUES ('ABC','XXX',13)
Now over here we have to again consider the records after 'LBW' i.e. If the last record is not LBW then consider one more row in the result set such that the last out guy should be either Bowled,Run Out or SBP.
Now in the example given above the output should be
PlayerName DismissFormat StrickRate
ABC LBW 100 (The strike rate of catch out)
ABC LBW 45 (The strike rate of Hit Wicket)
ABC Bowled 86 (The strike rate should be of Bowled only)
Not the exact solution but the ways this can be achieved would also be appreciated, so that i can try on it.
Thanks.
August 7, 2014 at 1:41 am
Should be easy enough - but as Lutz pointed out, which column will you use for the sequence? Without one, there's no concept of "earlier" or "later". Did you miss a column from your sample data set?
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
August 7, 2014 at 2:12 am
ChrisM@Work (8/7/2014)
Should be easy enough - but as Lutz pointed out, which column will you use for the sequence? Without one, there's no concept of "earlier" or "later". Did you miss a column from your sample data set?
+1, to determine which one is latest there SHOULD be a sequence column, or datetime column.
August 7, 2014 at 7:48 am
ChrisM@Work (8/7/2014)
Should be easy enough - but as Lutz pointed out, which column will you use for the sequence? Without one, there's no concept of "earlier" or "later". Did you miss a column from your sample data set?
Yes Chris i did miss that column :ermm: actually for sequencing there is a sequence column.
August 7, 2014 at 8:13 am
Shadab Shah (8/7/2014)
ChrisM@Work (8/7/2014)
Should be easy enough - but as Lutz pointed out, which column will you use for the sequence? Without one, there's no concept of "earlier" or "later". Did you miss a column from your sample data set?Yes Chris i did miss that column :ermm: actually for sequencing there is a sequence column.
Can you include it in your sample data please?
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
August 10, 2014 at 9:20 pm
ChrisM@Work (8/7/2014)
Shadab Shah (8/7/2014)
ChrisM@Work (8/7/2014)
Should be easy enough - but as Lutz pointed out, which column will you use for the sequence? Without one, there's no concept of "earlier" or "later". Did you miss a column from your sample data set?Yes Chris i did miss that column :ermm: actually for sequencing there is a sequence column.
Can you include it in your sample data please?
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int,CreatedDate date)
--TRUNCATE TABLE #TEMP
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100,'2014-01-12') --Date is in YYYY/MM/DD format
INSERT INTO #TEMP VALUES ('ABC','Bowled',50,'2014-01-14')
INSERT INTO #TEMP VALUES ('ABC','LBW',75,'2014-01-18')
INSERT INTO #TEMP VALUES ('ABC','XXX',36,'2014-01-20')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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','LBW',12,'2014-02-10')
INSERT INTO #TEMP VALUES ('ABC','XXX',93,'2014-02-22')
August 14, 2014 at 2:25 am
Hi Experts ,
I am not able to come up with the logic for making them (Catch Out/HitWicket to LBW) as one group and then perform the operation(take the strike rate).
I have also modified the test data as follow and trying
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int,CreatedDate date)
--TRUNCATE TABLE #TEMP
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100,'2014-01-12') --Date is in YYYY/MM/DD format
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',50,'2014-01-13')
INSERT INTO #TEMP VALUES ('ABC','Bowled',50,'2014-01-14')
INSERT INTO #TEMP VALUES ('ABC','LBW',75,'2014-01-18')
INSERT INTO #TEMP VALUES ('ABC','XXX',36,'2014-01-20')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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','LBW',12,'2014-02-10')
INSERT INTO #TEMP VALUES ('ABC','XXX',93,'2014-02-22')
Thanks.
August 14, 2014 at 3:06 am
Shadab Shah (8/6/2014)
Thanks Chris for the reply and your time.Since yesterday i am trying to improvised on the solution given by you.
Actually there is one more condition , Consider the below
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int)
--TRUNCATE TABLE #TEMP
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100)
INSERT INTO #TEMP VALUES ('ABC','Bowled',50)
INSERT INTO #TEMP VALUES ('ABC','LBW',75)
INSERT INTO #TEMP VALUES ('ABC','XXX',36)
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',45)
INSERT INTO #TEMP VALUES ('ABC','XXX',75)
INSERT INTO #TEMP VALUES ('ABC','XXX',96)
INSERT INTO #TEMP VALUES ('ABC','LBW',12)
INSERT INTO #TEMP VALUES ('ABC','XXX',93)
INSERT INTO #TEMP VALUES ('ABC','Bowled',86)
INSERT INTO #TEMP VALUES ('ABC','XXX',13)
Now over here we have to again consider the records after 'LBW' i.e. If the last record is not LBW then consider one more row in the result set such that the last out guy should be either Bowled,Run Out or SBP.
Now in the example given above the output should be
PlayerName DismissFormat StrickRate
ABC LBW 100 (The strike rate of catch out)
ABC LBW 45 (The strike rate of Hit Wicket)
ABC Bowled 86 (The strike rate should be of Bowled only)
Not the exact solution but the ways this can be achieved would also be appreciated, so that i can try on it.
Thanks.
Can you put CreatedDate into this dataset please?
Using your first dataset amended to include CreatedDate, the query no longer requires the new seq column:
SELECT t.PlayerName, t.DismissFormat, t.CreatedDate, x.StrickRate
FROM #TEMP t
OUTER APPLY (
SELECT TOP 1 StrickRate
FROM #TEMP ti
WHERE ti.PlayerName = t.PlayerName
AND ti.CreatedDate < t.CreatedDate
AND ti.DismissFormat IN ('Catch Out','Hit Wicket')
ORDER BY ti.CreatedDate DESC
) x
WHERE t.DismissFormat IN ('LBW')
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
August 20, 2014 at 3:26 am
Hi Chris,
I Thank you for taking time out and answering my question.
Actually i was thinking of tweaking the result set little bit, but i was not able to come up with any suitable solution.
Let us consider the below demo data
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int,CreatedDate date)
--DROP TABLE #TEMP
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100,'2014-01-12') --Date is in YYYY/MM/DD format
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',50,'2014-01-13')
INSERT INTO #TEMP VALUES ('ABC','Bowled',50,'2014-01-14')
INSERT INTO #TEMP VALUES ('ABC','LBW',75,'2014-01-18')
INSERT INTO #TEMP VALUES ('ABC','XXX',36,'2014-01-20')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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','LBW',12,'2014-02-10')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',93,'2014-02-22')
INSERT INTO #TEMP VALUES ('ABC','XXX',101,'2014-02-22')
INSERT INTO #TEMP VALUES ('ABC','XXX',150,'2014-02-23')
INSERT INTO #TEMP VALUES ('ABC','XXX',510,'2014-02-24')
INSERT INTO #TEMP VALUES ('ABC','LBW',175,'2014-02-25')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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')
With the query given by i am able to get the follow,
PlayerNameDismissFormatCreatedDateStrickRate
ABCLBW2014-01-1850
ABCLBW2014-02-1045
ABCLBW2014-02-2593
But suppose i want to tweak the resultset as
PlayerNameDismissFormatCreatedDateStrickRate
ABCLBW2014-01-1850
ABCLBW2014-02-1045
ABCLBW2014-02-2593
ABC Hit Wicket 2014-02-26 136
Where the last record is actually the record where Hit Wicket or Catch Out can be present with out LBW.
August 20, 2014 at 6:35 am
You're welcome.
Try this:
DROP TABLE #TEMP
CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int,CreatedDate date)
INSERT INTO #TEMP VALUES ('ABC','Catch Out',100,'2014-01-12') --Date is in YYYY/MM/DD format
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',50,'2014-01-13')
INSERT INTO #TEMP VALUES ('ABC','Bowled',50,'2014-01-14')
INSERT INTO #TEMP VALUES ('ABC','LBW',75,'2014-01-18')
INSERT INTO #TEMP VALUES ('ABC','XXX',36,'2014-01-20')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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','LBW',12,'2014-02-10')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',93,'2014-02-22')
INSERT INTO #TEMP VALUES ('ABC','XXX',101,'2014-02-22')
INSERT INTO #TEMP VALUES ('ABC','XXX',150,'2014-02-23')
INSERT INTO #TEMP VALUES ('ABC','XXX',510,'2014-02-24')
INSERT INTO #TEMP VALUES ('ABC','LBW',175,'2014-02-25')
INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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 ix_one ON #TEMP (PlayerName,CreatedDate,DismissFormat) INCLUDE (StrickRate)
CREATE INDEX ix_two ON #TEMP (DismissFormat) INCLUDE (CreatedDate,PlayerName,StrickRate)
CREATE INDEX ix_three ON #TEMP (PlayerName,DismissFormat,CreatedDate)
-- Grab all rows where DismissFormat = 'LBW'.
--Inline to these rows, grab the most recent row (for the same PlayerName) where DismissFormat = 'Catch Out' or 'Hit Wicket'.
-- Grab rows where DismissFormat = 'Hit Wicket' and there isn't a following row with DismissFormat = 'LBW' for the same PlayerName.
SELECT
t.PlayerName,
t.DismissFormat,
t.CreatedDate,
StrickRate = CASE WHEN t.DismissFormat = 'LBW' THEN x.StrickRate ELSE t.StrickRate END
FROM #TEMP t
OUTER APPLY (
SELECT TOP 1 StrickRate
FROM #TEMP ti
WHERE ti.PlayerName = t.PlayerName AND t.DismissFormat = 'LBW'
AND ti.CreatedDate < t.CreatedDate
AND ti.DismissFormat IN ('Catch Out','Hit Wicket')
ORDER BY ti.CreatedDate DESC
) x
WHERE
t.DismissFormat = 'LBW'
OR (
t.DismissFormat = 'Hit Wicket'
AND NOT EXISTS (
SELECT 1
FROM #TEMP ne
WHERE ne.PlayerName = t.PlayerName
AND ne.DismissFormat = 'LBW'
AND ne.CreatedDate > t.CreatedDate
)
)
--ORDER BY t.PlayerName, t.CreatedDate
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
August 20, 2014 at 7:04 am
I would once again like to thank you Chris , thanks for taking out the time and helping me.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply