Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need your expertise, to create an algorithm in Query

Status
Not open for further replies.
Valeriya

Valeriya

MIS
Jan 9, 2006
138
US
I've got the table that needs to be split up:

Item_ID MonthDiscrepant Bin Comment
030053-094-405 1 SGTC00004008120 returned
030053-094-405 2 SGTC00004008120
030053-095-405 1 SGTC00004008120 returned
030053-095-405 2 SGTC00004008120
030053-104-405 1 SGTC00286599120 returned
030053-104-405 4 SGTC00286599120
030053-104-405 13 SGTC00286599120 OnHand
040095-053-708 1 SGTC00240203659
040095-053-708 3 SGTC00240203659 implanted
050157-031-904 1 SGTC00001771142
050157-031-904 3 SGTC00001771142 implanted
1617843 1 SGTC00002041574
1617843 29 SGTC00002041574 Returned

Trying to figure out how to sort this table, according to the following logic:

If [Item_ID with comment].[MonthDiscrepant]< than [same Item_ID without comment].[MonthDiscrepant],"true returned or implanted"
If [Item_ID with comment].[MonthDiscrepant]> than [same Item_ID without comment].[MonthDiscrepant],"was counted aftre it was returned or implanted"

Actually I'm trying to split those items in two groups:

1st Group would be "Trully Returned/Implanted Item":
Compare two same Item_IDs by lenght of MonthDescrepant(if Item_ID that has a comment is less than the same Item_ID, but without comment, say " trully returned.." (that basically means if particular item was returned one month ago and never been conted after,that is a true return)

2nd Group would be "False Returned/Implanted Item":
Compare two same Item_IDs by theit lenght of Discrepancy
(if Item_ID that has a comment is greater that doesn't has a comment, say "false..") - This means if the Item_ID was appeared as "retuned" item 3 month ago, and then appeared in this table without a comment 1 and 2 month ago(which means this item was reported beeing counted on a consignment and haven't phisically come back to our warehouse)

TID MonthDiscrepant Bin Comment
030053-094-405 1 SGTC00004008120 returned
030053-094-405 2 SGTC00004008120
030053-095-405 1 SGTC00004008120 returned
030053-095-405 2 SGTC00004008120
030053-104-405 1 SGTC00286599120 returned
030053-104-405 4 SGTC00286599120
030053-104-405 13 SGTC00286599120 OnHand
040095-053-708 1 SGTC00240203659
040095-053-708 3 SGTC00240203659 implanted
050157-031-904 1 SGTC00001771142
050157-031-904 3 SGTC00001771142 implanted
1617843 1 SGTC00002041574
1617843 29 SGTC00002041574 Returned
1773119 2 SGTC00002498137
1773119 12 SGTC00002498137 Implanted
1779346 2 SGTC00001294572

Thanks a lot!

 
Status
Not open for further replies.

Similar threads

C
  • Locked
  • Question
Query expression negative to a 0
Replies
6
Views
486
dhookom
dhookom
combo
  • Locked
  • Question
SQL data types in CREATE/ALTER TABLE queries 1
Replies
2
Views
2K
combo
combo
C
  • Locked
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
422
OzFoxy
OzFoxy
breezett93
  • Question
Query takes 30 seconds - 1 minute to process. Trying to rework it to take 5 or less
Replies
8
Views
473
breezett93
breezett93
BugZap13
  • Locked
  • Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
682
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top