Need query for incorrect data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need query for incorrect data

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-06-20 : 06:20:53

--I have a table named - FacilityBilling


CREATE TABLE FacilityBilling
(
	GLOBAL_FACILITY_ID INT, 
	BILLING_COMP_NAME VARCHAR(50), 
	BILLING_START_DT DATETIME,
	BILLING_END_DT DATETIME
)

INSERT INTO FacilityBilling

SELECT '10','EMBCC','2007-09-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '100','EPBS','2003-12-01 00:00:00.000','2009-03-19 00:00:00.000'
UNION ALL
SELECT '10017','EMBCC','2011-07-18 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '10190','EMBCC','2003-12-01 00:00:00.000','2004-12-01 00:00:00.000'
UNION ALL
SELECT '102','EPBS','2003-12-01 00:00:00.000','2007-06-23 00:00:00.000'
UNION ALL
SELECT '102','EPBS','2003-12-01 00:00:00.000','2007-06-23 00:00:00.000'
UNION ALL
SELECT '103','EPBS','2010-09-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '11198','EPBS','1900-01-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '11198','EPBS','2008-04-04 00:00:00.000','2008-07-31 00:00:00.000'
UNION ALL
SELECT '11989','EMBCC','1994-01-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '11989','EMBCC','2009-04-08 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '1212','EMBCC','2004-11-15 00:00:00.000','2007-12-31 00:00:00.000'
UNION ALL
SELECT '12199','EMBCC','2009-12-01 00:00:00.000','2010-10-30 00:00:00.000'
UNION ALL
SELECT '12253','LOYA','2009-06-06 00:00:00.000','2009-12-31 00:00:00.000'
UNION ALL
SELECT '12253','EMBCC','2010-01-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '9156','EMBCC','1994-01-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '9156','EMBCC','2009-02-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '9161','EMBCC','1900-01-01 00:00:00.000','2099-12-31 00:00:00.000'
UNION ALL
SELECT '9161','EPBS','2007-10-01 00:00:00.000','2008-12-04 00:00:00.000'

In the above table I have to find out incorrect record.
For GLOBAL_FACILITY_ID there should be Billing company in sequence
if for one GLOBAL_FACILITY_ID there are multiple Billing Company then
Start Date should be greater than End date of previous company.
Means dates should not be overlap.

In the above case 11198, 11989, 9156, 9161 are incorrect.

Can I write a query to get those incorrect GLOBAL_FACILITY_ID

Vaibhav T

If I cant go back, I want to go fast...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-20 : 07:53:23

select f.global_facility_id from #facilitybilling f
where exists
(select * from #facilitybilling g
where f.global_facility_id = g.global_facility_id
and f.billing_comp_name = g.billing_comp_name
and f.billing_start_dt < g.billing_end_dt
and f.billing_start_dt < g.billing_start_dt
)

Jim

Everyday I learn something that somebody else already knew

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-06-20 : 08:09:16

jimf : its not giving expected output

Vaibhav T

If I cant go back, I want to go fast...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-20 : 08:34:16

How about this. It also accomaodates the duplicate records for id 102
;with cte as

(select *
, rank() over(partition by global_facility_id order by billing_start_dt) as rown
from #facilitybilling f
)
select f.global_facility_id
from cte f
where exists
(select * from cte g
where f.global_facility_id = g.global_facility_id
and f.billing_start_dt < g.billing_end_dt
and f.rown = g.rown + 1
)
Jim

Everyday I learn something that somebody else already knew

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-06-20 : 09:15:16

Thank you very much for your reply.
I will check with complete table and let you know.

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-06-21 : 03:58:26

Hi jim,

Cant we use Row_Number() in place of rank() ?
As I am getting same result with row_number also.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources