CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
d90
d90

Reputation: 787

Comparing columns to check for data errors

I have a table that has 4 important columns of data in it. CusNo, Description, year, and week. All of the column data types are CHAR and cannot be changed.

The format of the data is 

CusNo    Description        Year    Week
C2134    ROY WK 41, 2016    2016    41
C1152    ROY WK 41, 2016    2016    41
C1589    ROY WK 41, 2016    2016    41
C1921    ROY WK 41, 2016    2016    40

Occasionally, the Week column's value is miskeyed causing our reports to show a missing week as shown in the last value above. In this particular case, company C1921's report would show they are missing week 41, 2016 when actually it was just entered incorrectly in column 'week'.

Is there a way to compare the data in description column to the week column to see if the listed values for that week are the same week? The issue I'm running into is that the 1st-9th weeks in the description are single digit (1,2,3...), but in the week column they are ALWAYS double digit (01, 02, 03...). Changing the data is not feasible as the accounting team uses this format for a reason.

Upvotes: 0

Views: 28

Answers (2)

Lamak
Lamak

Reputation: 70658

One way to get the year and week from the description column is:

SELECT Description,
       RIGHT(Description,4) [Year], 
       SUBSTRING(Description,8,CHARINDEX(',',Description)-8) [Week]
FROM dbo.YourTable

If you need to prefix it a zero, then:

SELECT Description, [Year], RIGHT('0'+[Week],2) [Week]
FROM (
SELECT Description,
       RIGHT(Description,4) [Year], 
       SUBSTRING(Description,8,CHARINDEX(',',Description)-8) [Week]
FROM dbo.YourTable) X;

Upvotes: 1

gh9
gh9

Reputation: 10703

Is there a way to compare the data in description column to the week column to see if the listed values for that week are the same week

SELECT t.* FROM table AS t INNER JOIN tables AS d ON t.description LIKE 'ROY WK ' + d.week +'%'

In essence this select statement is going to do a self join on itself looking for all descriptions like ROY WK 41 wild card. Since the user is fat fingering this might lead to false positives.

Upvotes: 0

Related Questions