query help - 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 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-04-20 : 10:44:46

I was wondering if it was possible to do a like with an in - I tried,but failed dismally :)
I need to find all the reports but replace the reports that are similar in table b with those in table A. In the example below, the Reports in table B with the ending dates are basically the same type of reports, I need to replace those with the report in table A. I hope I'm making sense.

tableA
AID Report
1 End of Day Accounts
2 End of Month Accounts

tableB
BID Report
1 End of Day Account - 01/01/09
2 End of Day Account - 01/02/09
3 End of Day Account - 01/03/09
4 Accounts Reconcile
5 End of Month Accounts 01/31/09
6 End of Month Accounts 02/31/09
7 Group Activities

Results Table:
Accounts Reconcile
End of Day Accounts
End of Month Accounts
Group Activities

Anyone have any idea how I can do this? Thanks

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-20 : 11:36:40

Why would "End of Day Accounts" be in your output? I think you need to define similar in strict terms.

__________________________________________

An infinite universe is the ultimate cartesian product.

ann
Posting Yak Master

220 Posts

Posted - 2009-04-20 : 11:40:57

What I need is a list of reports - the problem arises from the fact that management considers all those reports that just have the dates different as 1 report. All the End Of Day Accounts are considered 1 report, irrespective of the date that is used. I cannot change the structure of the db or the data - this is existing data and what I have to work with. I could do this in code if I need to, but if there's a way to do it via sp - that would make more sense to me.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-20 : 11:50:36

I think you may have to create an xref table.

Consider the following



create table #A 
(
AID int not null,
Report Varchar(30) null
)

create table #B 
(
BID int not null,
Report Varchar(30) null
)


insert into #A

select 1, 'End of Day Accounts'
union all select 2, 'End of Month Accounts'


insert into #B

select 1, 'End of Day Account - 01/01/09'
union all
select 2, 'End of Day Account - 01/02/09'
union all
select 3, 'End of Day Account - 01/03/09'
union all
select 4, 'Accounts Reconcile'
union all
select 5, 'End of Month Accounts 01/31/09'
union all
select 6, 'End of Month Accounts 02/31/09'
union all
select 7, 'Group Activities'




select * from #a
select * from #b


select distinct A.report as report
from #A A
join #B B on A.report = Left(B.report,len(A.report))

union all

select B.report as Report
from #b B 
left join #a A on A.report = Left(B.report,len(A.report))
where A.report is null

Results:

Select of table #A
-------------------
1 End of Day Accounts
2 End of Month Accounts

Select of table #B
-------------------
1 End of Day Account - 01/01/09
2 End of Day Account - 01/02/09
3 End of Day Account - 01/03/09
4 Accounts Reconcile
5 End of Month Accounts 01/31/09
6 End of Month Accounts 02/31/09
7 Group Activities

final select
-------------------

End of Month Accounts
End of Day Account - 01/01/09
End of Day Account - 01/02/09
End of Day Account - 01/03/09
Accounts Reconcile
Group Activities

As you see in the final select you do not get "End of Day Accounts" because in table B it's "End of Day Account - Date" (no S at the end of account).

So you can't really say if A is a subset of B then use it and still get your desired results. So you need to either have more explicit selection criteria or you need a cross reference table that shows which reports should be grouped together.

I'd use a cross reference table.

Please let me know if I misunderstand something here.

____________________________________________________

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources