Hi,
I am a novice at T-SQL and need help. The following code works fine. However, I need to remove the condition at the bottom - "where EMPLOYMENT_DATE > 0 ". When I do this, it gives me the error: Conversion failed when converting the varchar value 'PENDG' to data type int.
I noticed that 'PENDG' is a SOURCE_CODE in the OHSDPROD.SOURCE table. This table has only 2 columns. The create table script of it is
CREATE TABLE [ohsdprod].[SOURCE](
[SOURCE_CODE] [nvarchar](5) NULL,
[SOURCE_DESCRIP] [nvarchar](70) NULL
) ON [PRIMARY];
some sample rows are:
SOURCE_CODE
SOURCE_DESCRIP
N/A NOT APPLICABLE
N/C NOT CONVERTIBLE TO THIS SCHEME
PENDG
PENDING SOURCE OF INJURY
Kindly assist to resolve. This is urgent.
Thanks,
Nita
SELECT
LTRIM(FIRM_NO) wsib,
convert(date,convert(varchar(10),accident_date,110),110) accident_date,
substring((case when
(SELECT top 1 D.NATURE_CODE FROM OHSDPROD.NATURE D WHERE d.NATURE_CODE = NATURE_INJURY)= NATURE_INJURY
then (SELECT top 1 D.NATURE_DESCRIP FROM OHSDPROD.NATURE D where d.NATURE_CODE = NATURE_INJURY)
else cast(NATURE_INJURY as varchar(50)) end),1,50) NATURE_INJURY,
substring((case when (SELECT top 1 D.SOURCE_CODE FROM OHSDPROD.SOURCE D WHERE
D.SOURCE_CODE = source_INJURY ) = source_INJURY
then (SELECT top 1 D.SOURCE_DESCRIP FROM OHSDPROD.SOURCE D WHERE
D.SOURCE_CODE = source_INJURY) else cast(SOURCE_INJURY as varchar(50)) end),1,50) SOURCE_INJURY,
substring((case when (SELECT top 1 D.ACC_TYPE_CODE FROM OHSDPROD.ACC_TYPE D WHERE
D.ACC_TYPE_CODE=Accident_type) = Accident_type
then (SELECT top 1 D.ACC_TYPE_DESCRIP FROM OHSDPROD.ACC_TYPE D WHERE
D.ACC_TYPE_CODE=Accident_type) else cast(ACCIDENT_TYPE as varchar(50)) end ),1,50) ACCIDENT_TYPE,
substring((case when (SELECT top 1 D.BODY_CODE FROM OHSDPROD.BODYPART D WHERE
D.BODY_CODE=body_part) = body_part then
(SELECT top 1 D.BODY_DESCRIP FROM OHSDPROD.BODYPART D WHERE
D.BODY_CODE=body_part) else cast(BODY_PART as varchar(50)) end),1,50) BODY_PART,
substring(( case when (SELECT top 1 D.OCC_CODE FROM OHSDPROD.OCCUPATION D WHERE
D.OCC_CODE=occupation_code) = occupation_code then
(SELECT top 1 D.OCC_DESCRIP FROM OHSDPROD.OCCUPATION D WHERE
D.OCC_CODE=occupation_code) else cast(OCCUPATION_CODE as varchar(50)) end),1,50) OCCUPATION,
CUMULATIVE_TEMP_BENEFITS_COST COSTS,
ROUND(datediff(dd,convert(varchar(10),employment_DATE,110), convert(varchar(10),accident_DATE,110)) /30,2) EMPMTHS,
substring(( case when (SELECT 1 FROM ohsdprod.WCB_CLAIM W2 WHERE W1.CLAIM_ID = W2.CLAIM_ID
AND NATURE_INJURY = 03500 AND BODY_PART IN (03200, 03202, 03290)) = 1 then 'SERIOUS'
when
(SELECT 1 FROM ohsdprod.WCB_CLAIM W2 WHERE W1.CLAIM_ID = W2.CLAIM_ID
AND NATURE_INJURY IN (01000,01300,01400,01800,01900,03600,05000,05100,05101,05102,05103,05190,05200,05201,05202,05203,05290,05300,05302,05303,05800,06000,06100,06800,06900,07120,07210,07220,07310,07320,07330,08000,08500,08600,
08901,08902,09300,09400,09500,09510,09520,09710,03800,05301,07230,07240,08900,03110,03700,41110,09100,09100,09200,06200,08400,01200,08300,03100,03190)) = 1 then 'SERIOUS' else ' ' end),1,10) SERIOUS,
--Ergo mapping provided by Elaine Wong.
--substring(( case when NATURE_INJURY in (230,381,382,383,384,385,389) then 'ERGO'
-- when ACCIDENT_TYPE in (81,82,83,85,102,120,121,122,123,129,998,999) then 'ERGO' else ' ' end),1,10) ERGO,
substring((CASE
WHEN NATURE_INJURY IN
(02001, 02100, 02101, 02102, 02190, 02900, 02901, 02902, 02903, 02904, 02905, 02906, 02907, 02908, 02909, 02910, 02911, 02912, 02913, 02914, 02915, 02916, 02917, 02918, 02919, 02920, 02922, 02923, 08200, 09720, 09730, 12410, 13710, 15300, 15310, 15320, 15330, 15390, 17000, 17200, 17201, 17202, 17210, 17220, 17230, 17231, 17232, 17233, 17239, 17290, 17291, 17292, 17293, 17300, 17310, 17320, 17330, 17340, 17350, 17360, 17370, 17390, 17391, 17392, 17393, 17394, 17395, 17400, 17410, 17490, 17900, 17901, 41200, 41230, 41280, 41290, 41480, 41490)
THEN 'ERGO'
WHEN ACCIDENT_TYPE IN
('20000','21000','21100','21200','21300','21400','21500','21501','21502','21503','21509','21600','21700','21900','22000','22100','22200','22300','22400','22900','22901','23000','23100','23200','23300','23900','24000','25000','26000','27000','29000','29001')
THEN 'ERGO'
ELSE ' '
END), 1, 10) ERGO,
--(case when coalesce(BIRTH_DATE,0) <= 17530101 then 0 else
-- datediff(dd,convert(datetime,SUBSTRING(cast(birth_date as varchar(8)),1,4) + SUBSTRING(cast(birth_date as varchar(8)),5,2) +
-- SUBSTRING(cast(birth_date as varchar(8)),7,2),110),
-- convert(datetime,SUBSTRING(cast(accident_date as varchar(8)),1,4) + SUBSTRING(cast(accident_date as varchar(8)),5,2) +
-- SUBSTRING(cast(accident_date as varchar(8)),7,2),110))/365.25 end) AGE,
age,
GENDER,
--
--(case when language_code not in ('01','57','58') then 'YES' else ' ' end) FOREIGN_1,
(case when language_code not in ('EN','FR') then 'YES' else ' ' end) FOREIGN_1,
GEOGRAPHIC_LOCATION GEO_LOC,WORKSITE
FROM ohsdprod.WCB_CLAIM W1
where EMPLOYMENT_DATE > 0
AND ACCIDENT_year > 2006
--
--and coalesce(cumulative_days_lost,0) > 0
and claim_type = 'LT'
- Edited by NitaM Thursday, August 27, 2015 4:11 PM
Other recent topics
