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.
| Author |
Topic |
|
sammy68
Starting Member
5 Posts |
 Posted - 2014-10-10 : 08:57:27
|
| I have a query I am trying to write where I need to compare one sequence to another. Here is the basic criteria.I have an ID number that is 12 bytes. The first 10 bytes are the ID and the last two bytes are a sequence number. I need to compare the same 10 byte ID number to itself where the sequence number is different (along with other criteria). So for example:Compare ID 123456789000 to 123456789001,123456789002, 123456789003 etc and not include the entire ID on the report if the 01 sequence paid amount is equal to zero OR if the 00 and 01 sequence amounts are equal.Any help is appreciated. I am a basic SQL writer and this is just beyond my skill level. Thanks! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-10 : 09:16:11
|
| If they are integers: ID12/1000000000 = ID10 |
 |
|
|
sammy68
Starting Member
5 Posts |
Posted - 2014-10-10 : 13:25:15
|
| The field is alpha-numeric |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-10 : 13:31:51
|
| You said it was a number! So:CAST(ID12 as bigint)/100 = cast(ID10 as bigint) |
|
|
|
sammy68
Starting Member
5 Posts |
Posted - 2014-10-13 : 07:34:00
|
| The issue I have is how to do the compare. How do I compare seq 00 records to the remaining seq numbers?ID 123456789000 meets the criteria for my initial query. How do I then compare that to 123456789001, 123456789002, etc. to determine if the subsequent sequences meet my remaining criteria?Here's an example of what I have so far:SELECT ID,SUBSTRING(ID,1,2) AS "CLM SEQ",FIELD3,FIELD4,FIELD5FROM TABLE1 c, TABLE2 iWHERE c.ID = i.IDAND SUBSTRING(c.ID,11,2) = SUBSTRING(i.ID,11,2)AND SUBSTRING(c.ID,1,2) = 0AND PAID >0AND STAT = '10'AND DATE BETWEEN '2014-01-01' AND '2014-12'31'This gives me my initial claims with seq 00. I then need to apply additional criteria that takes those claims and reads any additional sequences to determine if the paid on those sequences is equal to 0 or is equal to the 00 sequence. Does that make it more clear on what I am trying to do? Thanks! |
|
|
|
sammy68
Starting Member
5 Posts |
Posted - 2014-10-13 : 08:45:36
|
| Correction - this statement:AND SUBSTRING(c.ID,1,2) = 0should be:AND SUBSTRING(c.ID,11,2) = 0 |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-13 : 10:16:47
|
or:WHERE CAST(ID12 as bigint)/100 = cast(ID10 as bigint) |
|
|
|
sammy68
Starting Member
5 Posts |
Posted - 2014-10-14 : 06:44:27
|
| I don't understand your reply. How does this help with the compare I am trying to do? Thanks! |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-14 : 07:00:42
|
| It changes the comparison to that of integers, scaling the larger one to the same scale as the smaller one. Did you try it? |
|
|
|
|
|
|
|
|
Compare