Hello Techie,
may anyone please help me how to get this.
There are three tables
Table 1: Main
Table 2: BoltReference
table 3: LugReference
Each column is having rule, if any data violate the rule, it should be appear in result column
Rules
Diameter Looks For Blanks OR number values greater than 28 or less then 6 in this column
Width Looks For Blanks OR number values greater than 16 or less then 3 in this column
Bolt1 Looks For Blanks or items that do not match the supplied Bolt Reference Table
Bolt2 Looks For items that do not match the supplied Bolt Reference Table (blanks '' are OK)
Bolt3 Looks For items that do not match the supplied Bolt Reference Table (blanks '' are OK)
Core Looks For Blanks or items that do not have two decimal places
Offset Looks for blanks or Plus Sign (+) or numeric values greater then 125
Lug Looks For Blanks or items that do not match the supplied Lug Reference Table
Outputs
Alias column Result 1 has value whenever there is invalid data in Diameter column else blank
Alias column Result 2 has value whenever there is invalid data in width column else blank
Alias column Result 3 has value whenever there is invalid data in Bolt1 column else blank
Alias column Result 4 has value whenever there is invalid data in Bolt2 column else blank
Alias column Result 5 has value whenever there is invalid data in Bolt3 column else blank
Alias column Result 6 has value whenever there is invalid data in Core column else blank
Alias column Result 7 has value whenever there is invalid data in Offset column else blank
Alias column Result 8 has value whenever there is invalid data in Lug column else blank
*/
DECLARE @MAIN TABLE ( SKU VARCHAR (50), Diameter INT, Width FLOAT, Bolt1 VARCHAR (20), Bolt2 VARCHAR (20), Bolt3 VARCHAR (20), Core FLOAT, Offset CHAR (10), Lug VARCHAR (30) ) INSERT @MAIN SELECT 'R105C', '18','', '121X','','','70.70','+58','Acorn' UNION ALL SELECT 'R121C', '2','8.5','125X','','', '70.7','49','Duplex' UNION ALL SELECT 'RAR10', '20','8.5','135X','555XX','','87.10','','Check me' SELECT * FROM @MAIN DECLARE @BoltReference TABLE ( Bolt1 VARCHAR (20), Bolt2 VARCHAR (20), Bolt3 VARCHAR (20) ) Insert @BoltReference select '184','100','84' UNION ALL select '121','129','213' UNION ALL select '135','131','410' SELECT * FROM @BoltReference DEClare @LugReference table ( lug varchar (30) ) insert @LugReference SELECT 'Acorn' UNION ALL SELECT 'Duplex' UNION ALL SELECT 'OpenEnd'
Output look like this.
SKU | Diameter | Width | Bolt1 | Bolt2 | Bolt3 | Core | Offset | Lug | Result1 | Result2 | Result3 | Result4 | Result5 | Result6 | Result7 | Result8 |
R105C | 18 | 121X | 70.70 | +58 | Acorn | Width | Offset | |||||||||
R121C | 2 | 8.5 | 125X | 70.7 | 49 | Duplex | Diameter | Bolt1 | Core | |||||||
RAR10 | 20 | 8.5 | 135X | 555XX | 87.10 | Check me | Bolt2 | Offset | Lug |
if column contain correct value then its respective result column should be blank ''.
Thanks a lot
- Edited by myGodsql 17 hours 45 minutes ago