| Author |
Topic |
|
northhunter
Starting Member
6 Posts |
 Posted - 2011-08-03 : 14:46:07
|
| Hello all,I am having difficulty when joining 2 tables in SQL. The code below is joining a calendar table with another to get the development of costs, etc. over a time period. The error being returned is "Operand data type varchar is invalid for subtract operator" and when executing the query the error is returned in Line 7 and beyond.My attempts to resolve it so far have focused on ensuring the dates are valid in the respective tables but to no success. I can not see an error in the code, but would happily welcome being told otherwise or any other suggestions as to a possible fix.Thanks in advance for any help:SELECT EXTRACTS_JULY11_3.*,OCCURRENCE_CAL.WeekKey AS OCCURRENCE_WEEKKEY, OCCURRENCE_CAL.DateFrom AS OCCURRENCE_WEEK_C, OCCURRENCE_CAL.DateTo AS OCCURRENCE_WEEK_E,OCCURRENCE_CAL.SEQ AS OCCURRENCE_WEEK_REF,OCCURRENCE_CAL.SEQ_M AS OCCURRENCE_MONTH_REF,EXTRACT_WEEK_REF - NOTIFY_WEEK_REF AS DEVELOPMENT_WEEK,EXTRACT_MONTH_REF - NOTIFY_MONTH_REF AS DEVELOPMENT_MONTH,EXTRACT_WEEK_REF - OCCURRENCE_CAL.SEQ AS DEVELOPMENT_WEEK_OCC,EXTRACT_MONTH_REF - OCCURRENCE_CAL.SEQ_M AS DEVELOPMENT_MONTH_OCC,EXTRACT_WEEK_REF - CLOSED_WEEK_REF AS DEVELOPMENT_WEEK_SETTLE,EXTRACT_MONTH_REF - CLOSED_MONTH_REF AS DEVELOPMENT_MONTH_SETTLEFROM (SELECT EXTRACTS_JULY11_2.*, CLOSED_CAL.WeekKey AS CLOSED_WEEKKEY, CLOSED_CAL.DateFrom AS CLOSED_WEEK_C, CLOSED_CAL.DateTo AS CLOSED_WEEK_E, CLOSED_CAL.SEQ AS CLOSED_WEEK_REF, CLOSED_CAL.SEQ_M AS CLOSED_MONTH_REF FROM (SELECT EXTRACTS_JULY11_1.*, EXTRACT_CAL.WeekKey AS EXTRACT_WEEKKEY, EXTRACT_CAL.DateFrom AS EXTRACT_WEEK_C, EXTRACT_CAL.DateTo AS EXTRACT_WEEK_E, EXTRACT_CAL.SEQ AS EXTRACT_WEEK_REF, EXTRACT_CAL.SEQ_M AS EXTRACT_MONTH_REF, EXTRACT_CAL.MONTH_END AS MONTH_END_EXTRACTFROM(SELECT EXTRACTS_JULY11.*, CONVERT(DATETIME, EXTRACTS_JULY11.Extract) AS EXTRACT_DATE, DATEADD(DD, -1 * DATEPART(WEEKDAY, CONVERT(DATETIME, EXTRACTS_JULY11.Extract)), CONVERT(DATETIME, EXTRACTS_JULY11.Extract)) AS EXTRACT_DATE_ADJUSTED,NOTIFY_CAL.WeekKey AS NOTIFY_WEEKKEY, NOTIFY_CAL.DateFrom AS NOTIFY_WEEK_C, NOTIFY_CAL.DateTo AS NOTIFY_WEEK_E,NOTIFY_CAL.SEQ AS NOTIFY_WEEK_REF,NOTIFY_CAL.SEQ_M AS NOTIFY_MONTH_REFFROM EXTRACTS_JULY11 LEFT JOIN CALENDAR AS NOTIFY_CALON EXTRACTS_JULY11.DATE_INFORM BETWEEN NOTIFY_CAL.DateFrom AND NOTIFY_CAL.DateTo) AS EXTRACTS_JULY11_1 INNER JOIN CALENDAR AS EXTRACT_CAL ON EXTRACTS_JULY11_1.EXTRACT_DATE_ADJUSTED BETWEEN EXTRACT_CAL.DateFrom AND EXTRACT_CAL.DateTo) AS EXTRACTS_JULY11_2 LEFT JOIN CALENDAR AS CLOSED_CAL ON EXTRACTS_JULY11_2.DATE_FINAL_CURRENT BETWEEN CLOSED_CAL.DateFrom AND CLOSED_CAL.DateTo) AS EXTRACTS_JULY11_3 LEFT JOIN CALENDAR AS OCCURRENCE_CAL ON EXTRACTS_JULY11_3.DATE_LOSS BETWEEN OCCURRENCE_CAL.DateFrom AND OCCURRENCE_CAL.DateTo |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-03 : 14:47:52
|
| What are the data types of these?EXTRACT_WEEK_REF - NOTIFY_WEEK_REF AS DEVELOPMENT_WEEK,EXTRACT_MONTH_REF - NOTIFY_MONTH_REF AS DEVELOPMENT_MONTH,EXTRACT_WEEK_REF - OCCURRENCE_CAL.SEQ AS DEVELOPMENT_WEEK_OCC,EXTRACT_MONTH_REF - OCCURRENCE_CAL.SEQ_M AS DEVELOPMENT_MONTH_OCC,EXTRACT_WEEK_REF - CLOSED_WEEK_REF AS DEVELOPMENT_WEEK_SETTLE,EXTRACT_MONTH_REF - CLOSED_MONTH_REF AS DEVELOPMENT_MONTH_SETTLETara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
northhunter
Starting Member
6 Posts |
Posted - 2011-08-03 : 15:04:34
|
| Anything deriving from DateFrom or DateTo is a date timestamp, e.g. 03/08/2011 23:59:00.That would thus be anything ending in WEEK_C or WEEK_E.As for the others, they are all integers. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
northhunter
Starting Member
6 Posts |
Posted - 2011-08-03 : 15:13:54
|
| Thanks for the help so far. In the Calendar table the dates are as detailed above, but in the data table they are slightly longer and stored in a different order, e.g. 2011-08-03 00:00:00.000 - is this a likely cause of the issue?If so, how would I change one to the other? Ideally in SSMS, but the Calendar table comes from a .csv file so I could change that directly and re-import the table.Also, when importing the table if I try to set to time then it automatically sets all dates to today's date.Thanks, again. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
northhunter
Starting Member
6 Posts |
Posted - 2011-08-03 : 15:30:07
|
| An example is, I think?, the below bit:CLOSED_CAL.SEQ_M AS CLOSED_MONTH_REFMy understanding is that it is looking up the 'Closed Date' in the data table, giving that a Month Number based on the 'SEQ_M' field in the Calendar, and then calling that the CLOSED_MONTH_REF.In Excel language it would be doing a VLOOKUP of the Closed Date month against the Calendar table.The DateFrom and DateTo come into it as they define the WEEK and MONTH references within the Calendar table.I'm not very conversive at SQL so may be (very) wrong. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
northhunter
Starting Member
6 Posts |
Posted - 2011-08-03 : 15:35:47
|
| Ah okay,the first line of code causing the error is the Subtraction on Line 7 - "EXTRACT_WEEK_REF - NOTIFY_WEEK_REF AS DEVELOPMENT_WEEK" - the derivation of which I think may lie in the way the dates are being stored in the two tables, i.e. the data types as suggested earlier. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
northhunter
Starting Member
6 Posts |
Posted - 2011-08-05 : 05:20:09
|
| Hello tkizer.I have solved the problem by doing the calculations in Access using SQL instead of persisting with the join.Just wanted to say thanks for the effort and time you put into helping.Regards, Hunter. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Error when Joining 2 tables