April 9, 2013 at 11:55 am
Hi ,
I have a table from where I want to filter out some records as given below.
CREATE TABLE #MissingTransactions (Code VARCHAR(10), Location VARCHAR(10), PrevDate DATE, PrevTime INT, NextDate DATE, NextTime INT, PrevTrans INT, NextTrans INT,NextSite VARCHAR(20),NextCompany VARCHAR(20))
INSERT INTO #MissingTransactions
SELECT 'Code1','01','1/17/2013',1711,'1/18/2013',1859,74174,74209,'Site1','Comp1'
SELECT 'Code1','26','1/14/2013',1124,'1/14/2013',1432,1,10,'Site2','Comp1'
SELECT 'Code1','26','1/14/2013',1128,'1/14/2013',1533,2,20,'Site2','Comp1'
SELECT 'Code1','26','1/14/2013',1358,'1/15/2013',722,9,22,'Site2','Comp1'
SELECT 'Code1','26','1/16/2013',1611,'1/19/2013',819,70,133,'Site2','Comp1'
SELECT 'Code1','26','2/24/2013',2055,'2/25/2013',1130,975,1000,'Site2','Comp1'
SELECT 'Code1','26','2/25/2013',1034,'2/26/2013',717,999,1006,'Site2','Comp1'
While doing a a Final Select in my report from #missingTransactions
I do not want to display PrevTrans IN (1,2,9) since
Prev Trans(2) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTarns = 2 is equal to NextDate(1/14/2013) where PrevTrans = 1
AND Prev Trans(9) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 1
OR Prev Trans(9) is in between Prev Trans (2) and Next Trans (20) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 2
Also I want to omit PrevTrans 975 and 999 with the same conditions like above.
The only ones I want to display is PrevTrans 74174 and 70.
Please help .
April 9, 2013 at 1:06 pm
Decent job posting ddl and sample data (works with a slight modification).
However when you got to the description of the desired output you totally lost me.
I do not want to display PrevTrans IN (1,2,9) since
Prev Trans(2) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTarns = 2 is equal to NextDate(1/14/2013) where PrevTrans = 1
AND Prev Trans(9) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 1
OR Prev Trans(9) is in between Prev Trans (2) and Next Trans (20) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 2
Also I want to omit PrevTrans 975 and 999 with the same conditions like above.
You need to explain that a bit more clearly at least for me.
_______________________________________________________________
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/
April 9, 2013 at 1:15 pm
i couldn't see how to get the data filtered either;
it seemed like maybe the Nextdate, but that doesn;t match your total criteria; maybe by NextCompany,Location?
i cannot see why you want to ignore 1,2,9 but not all teh others as well.
CREATE TABLE #MissingTransactions (Code VARCHAR(10), Location VARCHAR(10), PrevDate DATE, PrevTime INT, NextDate DATE, NextTime INT, PrevTrans INT, NextTrans INT,NextSite VARCHAR(20),NextCompany VARCHAR(20))
INSERT INTO #MissingTransactions
SELECT 'Code1','01','1/17/2013',1711,'1/18/2013',1859,74174,74209,'Site1','Comp1' UNION ALL
SELECT 'Code1','26','1/14/2013',1124,'1/14/2013',1432,1,10,'Site2','Comp1' UNION ALL
SELECT 'Code1','26','1/14/2013',1128,'1/14/2013',1533,2,20,'Site2','Comp1' UNION ALL
SELECT 'Code1','26','1/14/2013',1358,'1/15/2013',722,9,22,'Site2','Comp1' UNION ALL
SELECT 'Code1','26','1/16/2013',1611,'1/19/2013',819,70,133,'Site2','Comp1' UNION ALL
SELECT 'Code1','26','2/24/2013',2055,'2/25/2013',1130,975,1000,'Site2','Comp1' UNION ALL
SELECT 'Code1','26','2/25/2013',1034,'2/26/2013',717,999,1006,'Site2','Comp1'
select * from(
select ROW_NUMBER() over (partition by NextCompany,Location order by NextDate) AS RW,* from #MissingTransactions
) x
where rw = 1
Lowell
April 9, 2013 at 2:40 pm
Ye s we will need to consider location,Site and Code while eliminatong
I want to eliminate PrevTrans 1,2,9 because for the same site,location and code because PrevTrans 2 and 9 comes in the 1- 10 range .So these are not valid.
PrevTrans74174 and 70 are independent and no other Prevtrans comes between these ranges.
April 9, 2013 at 2:48 pm
PSB (4/9/2013)
Ye s we will need to consider location,Site and Code while eliminatongI want to eliminate PrevTrans 1,2,9 because for the same site,location and code because PrevTrans 2 and 9 comes in the 1- 10 range .So these are not valid.
PrevTrans74174 and 70 are independent and no other Prevtrans comes between these ranges.
We know that you want to eliminate them. The problem is we don't understand WHY you want to eliminate. You need to clarify your rules here quite a bit.
_______________________________________________________________
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/
April 9, 2013 at 2:58 pm
Sean Lange (4/9/2013)
PSB (4/9/2013)
Ye s we will need to consider location,Site and Code while eliminatongI want to eliminate PrevTrans 1,2,9 because for the same site,location and code because PrevTrans 2 and 9 comes in the 1- 10 range .So these are not valid.
PrevTrans74174 and 70 are independent and no other Prevtrans comes between these ranges.
We know that you want to eliminate them. The problem is we don't understand WHY you want to eliminate. You need to clarify your rules here quite a bit.
SELECT 'Code1','01','1/17/2013',1711,'1/18/2013',1859,74174,74209,'Site1','Comp1' UNION ALL
-->SELECT 'Code1','26','1/14/2013',1124,'1/14/2013',1432,1,10,'Site2','Comp1' UNION ALL <-- These three overlap each other, eliminate from results
-->SELECT 'Code1','26','1/14/2013',1128,'1/14/2013',1533,2,20,'Site2','Comp1' UNION ALL <-- These three overlap each other, eliminate from results
-->SELECT 'Code1','26','1/14/2013',1358,'1/15/2013',722,9,22,'Site2','Comp1' UNION ALL <-- These three overlap each other, eliminate from results
SELECT 'Code1','26','1/16/2013',1611,'1/19/2013',819,70,133,'Site2','Comp1' UNION ALL
-->SELECT 'Code1','26','2/24/2013',2055,'2/25/2013',1130,975,1000,'Site2','Comp1' UNION ALL <-- These two overlap each other, eliminate from results
-->SELECT 'Code1','26','2/25/2013',1034,'2/26/2013',717,999,1006,'Site2','Comp1' <-- These two overlap each other, eliminate from results
I'm guessing that the OP only wants independent records. Any records that overlap with others needs to be dropped from the results set.
April 9, 2013 at 3:05 pm
That is correct.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply