| Author |
Topic |
|
sapator
Constraint Violating Yak Guru
462 Posts |
 Posted - 2008-03-24 : 07:53:45
|
| Hi.I have some contracts and i want to find for every specific contract (every contract can be repeated 2-3 or more times (same policynumber) the contracts that have the same date. That's the contracts INSIDE the contract that have the same data.I was trying something like select distinct m.masterpolicyno from multicontract m where m.category=3 and m.issuedate = m.issuedatebut it think i must use "having" and "in" but i can't figure it out.Any help?Thanks. |
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 07:55:07
|
| Please post some sample data & the desired output. |
 |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 08:10:37
|
| Ok..for example. MasterPolicyno Category issuedate decription1 1910088973 1 01-01-2008 Master 2 1910088973 99 01-21-2008 Advanced3 1910088973 3 02-01-2008 Renewal 2 1910088973 99 02-03-2008 Advanced3 1910088973 3 01-04-2008 Renewal 4 1910088973 3 01-04-2008 Renewalso the problem is that i cannot have 2 Renewals with the same date (perfect would be to check for a given period, 1-2 months).it must hit the category 3 (renewal) and check if the period is the same. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 08:11:29
|
| sorry they came out close to each other. It's category 1 or 99 or 3 and the date is after. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 08:35:14
|
| if any help. There is also an id on contract that is different in every contract inside this contract.I'm trying this but i get zero columns.select m.masterpolicyno from multicontract m where masterpolicyno = 1910088973and m.category=3 and m.issuedate = m.issuedateand m.masterpolicyno in ( select mc.id from multicontract mc where mc.masterpolicyno = m.masterpolicyno and mc.id <> m.id and m.issuedate = mc.issuedate) |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 08:38:23
|
| But what should be your output exactly?It should be policies that are repeated on the same date? |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 08:40:49
|
| Well i should probably have used a distinct masterpolicyno also.The output will be the masterpolicyno.If it has renewals with the same date then it should output the masterpolicyno |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 08:49:06
|
| Is this what you want?Declare @tbl as table(id int ,MasterPolicyno int,Category int,issuedate datetime,decription varchar(40))Insert into @tbl select 1 ,1910088973, 1 ,'01-01-2008', 'Master' union allselect 2 ,1910088973, 99, '01-21-2008', 'Advanced' union allselect 3, 1910088973, 3 ,'02-01-2008', 'Renewal' union allselect 2 ,1910088973, 99, '02-03-2008', 'Advanced' union allselect 3, 1910088973, 3, '01-04-2008' ,'Renewal' union allselect 4, 1910088973, 3, '01-04-2008', 'Renewal'select id,MasterPolicyno,Category,issuedate from(select id,MasterPolicyno,Category,issuedate,row_number()over(partition by category order by category)as rowid from @tbl)t where rowid=1 |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 08:57:34
|
| Can you help me to test it?It gives 'row_number' is not a recognized function name.the table i want to use is multicontract.Thanks.(i use as rowid from multicontract) |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 08:58:46
|
| Also i tried the below and i get some results but i don't know if i'm doing it correctly.. select m.masterpolicyno from multicontract m -- where masterpolicyno = 1910088973where m.category=3 --and m.issuedate = m.issuedateand exists ( select mc.id from multicontract mc where mc.id<> m.id and m.issuedate = mc.issuedate and mc.category=3 and m.masterpolicyno=mc.masterpolicyno)group by m.masterpolicynohaving count(m.id) >1 |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 08:59:52
|
| Are you using SQL 2000 or SQL 2005? |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 09:02:07
|
| 2005 |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 09:07:33
|
| Then I think your database compatibility level is 80.You need to make it 90.Execute this command.EXEC sp_dbcmptlevel YouDatabaseName, 90; |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 09:16:00
|
| The database was originally 2000. I use 2005 to view it. Also i don't know if i have the permissions to do it.Can it be done by another way?What will it change to my database? |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 09:20:20
|
| The row_number() function is a new function in SQL 2005.Because the compatibilty level of your database is 80 the row_number() function wont work.I wanted to make it to 90 for the function to work. |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 09:25:30
|
| Try thisselect m.masterpolicyno from multicontract m where exists ( select mc.id from multicontract mc where mc.id<> m.id and m.issuedate = mc.issuedate and m.masterpolicyno=mc.masterpolicyno)group by m.masterpolicynohaving count(m.id) >1 |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 09:26:22
|
| I can't mess around with the database cuz there are people here that still use 2000.Thanks btw. Also will my Tsql work so i don't have to bother you :) |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-24 : 09:29:52
|
| Yes ur query will work without changing the compatibility level. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 09:33:09
|
| Hi.So my Tsql is correct?I think i must also use where m.category=3 outside the exist statement?Is that correct?Thanks for your time. |
|
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-03-24 : 09:35:20
|
| Ow you answer. Sorry.Ok thank you very much!!(i'll keep the ,row_number() in mind for another time).THANKS!! |
|
|
|
|
Find exact same dates