Error in Drop Create stored procedure.

  • New at SQL Server 2008 R2 ...

    The script below generates a stored procedure. Just recently I start getting an error message when running this script. The error message points to this line:

    SET [Duur]= round(([Duur (in tijd)] /100),0,1)*60 + ([Duur (in tijd)] - round(([Duur (in tijd)]/100),0,1)*100)

    And it states that

    Duur

    is an invalid column name. Earlier I never got this error message.

    Msg 207, Level 16, State 1, Procedure spIMPORTDRPDATA, Line 74

    Invalid column name 'Duur'.

    Any suggestions?

    Cheers, Julian

    USE [WZH]

    GO

    /**********************************

    DROP CREATE spIMPORTDRPDATA

    ***********************************/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIMPORTDRPDATA]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[spIMPORTDRPDATA]

    GO

    USE [WZH]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[spIMPORTDRPDATA]

    AS

    BEGIN

    /*

    JJR 20140313

    IMPORT CSV DATA

    CONVERT STRING TO DATE AND INT

    ALTER DATATYPE COLUMNS

    */

    -- DROP THE TABLE

    IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DRPDATA')

    DROP TABLE dbo.DRPDATA

    --CREATE TEST TABLE

    CREATE TABLE DRPDATA (

    Dienstcode varchar(50),

    Begindatum varchar(12),

    Sorteernaam varchar(100),

    [Org eenheid code] varchar(20),

    Begintijd int,

    Eindtijd int,

    [Duur (in tijd)] varchar(20),

    [Afwijkende tijd J/N] varchar(10),

    Roosteritemstatus varchar(50),

    [Ri Soortdienst] varchar (50),

    [Ri Mdw Externkenmerk] varchar(50),

    [Ri Functiecode] varchar(50),

    [Ri Functieoms] varchar(100))

    -- INSERT DATA FROM CSV FILE

    BULK

    INSERT DRPDATA

    FROM 'D:\xxx\drpoutput2.csv'

    WITH

    (

    FIRSTROW = 2,

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '',

    --ERRORFILE = 'D:\ErrorRows.csv',

    TABLOCK

    )

    -- CHANGE DATATYPE BEGINDATUM TO DATETIME

    ALTER TABLE WZH.DBO.DRPDATA

    ALTER COLUMN Begindatum DATETIME NULL

    -- UPDATE THE FIELD BEGINDATUM AS DATE

    UPDATE DRPDATA

    SET Begindatum = CASE

    WHEN ISDATE(Begindatum) = 0 THEN CONVERT(DATE, '30/01/2222', 103)

    ELSE CONVERT(DATE, Begindatum, 103)

    END

    -- UPDATE THE FIELD [Duur in Tijd] AS Integer

    UPDATE DRPDATA

    SET [Duur (in tijd)] = CASE

    WHEN ISNUMERIC([Duur (in tijd)]) = 1 THEN CAST([Duur (in tijd)] AS INT)

    ELSE 0

    END

    --CHANGE DATATYPE TO INT

    ALTER TABLE WZH.DBO.DRPDATA

    ALTER COLUMN [Duur (in tijd)] INT NULL

    -- ADD COLUMN DUUR(MIN)

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [Duur] INT NULL

    -- Truncate the result of the division, add minutes

    UPDATE WZH.DBO.DRPDATA

    SET [Duur]= round(([Duur (in tijd)] /100),0,1)*60 + ([Duur (in tijd)] - round(([Duur (in tijd)]/100),0,1)*100);

    -- ADD COLUMN Locatie

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [LocatieNr] INT NULL

    -- ADD COLUMN LocatieCode

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [LocatieCode] varchar(4) NULL

    -- ADD COLUMN LocatieNaam

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [LocatieNaam] varchar(50) NULL

    -- ADD COLUMN LocatieNrFeitelijk

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [LocatieNrFeitelijk] int NULL

    -- ADD COLUMN LocatieCodeFeitelijk

    ALTER TABLE WZH.DBO.DRPDATA

    ADD LocatieCodeFeitelijk varchar(4) NULL

    -- ADD COLUMN MwNr

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [MwID] INT NULL

    -- ADD COLUMN DvbNr

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [DvbNr] INT NULL

    -- ADD COLUMN NiveauZPT

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [NiveauZPT] varchar(50) NULL

    -- ADD COLUMN DienstGroep

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [DienstGroep] varchar(50) NULL

    -- ADD COLUMN OEOmschrijving

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [OEOmschrijving] varchar(100) NULL;

    -- ADD COLUMN OEGroep

    ALTER TABLE WZH.DBO.DRPDATA

    ADD OEGroep varchar(100) NULL;

    -- ADD COLUMN AfdelingZPT

    ALTER TABLE WZH.DBO.DRPDATA

    ADD AfdelingZPT varchar(4) NULL;

    -- ADD COLUMN FTEHrs

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [FTEHrs] float NULL;

    -- ADD COLUMN FlexVast

    ALTER TABLE WZH.DBO.DRPDATA

    ADD [FlexVast] varchar(10) NULL;

    -- 15) Change LocatieNr for 91,92,93 (first 2 positions of [org eenheid code] to 91

    -- Otherwise use 'normal' rule

    UPDATE DRPDATA

    SET DRPDATA.LocatieNr = CASE

    WHEN SUBSTRING([Org eenheid code],1,2) IN ('91','92','93') THEN 91

    ELSE SUBSTRING([Org eenheid code],3,2)

    END

    -- LOCATIE CODE

    -- Change Locatie CODE for 91,92,93 (first 2 positions of [org eenheid code] to BZ

    -- Otherwise use 'normal' rule: Extract from table Huizen

    UPDATE DRPDATA

    SET DRPDATA.LocatieCode = 'BZ'

    WHERE SUBSTRING([Org eenheid code],1,2) IN ('91','92','93');

    UPDATE DRPDATA

    SET DRPDATA.LocatieCode = Huizen.IDHuis

    FROM DRPDATA INNER JOIN Huizen ON DRPDATA.LocatieNr = Huizen.NrHuis

    WHERE SUBSTRING([Org eenheid code],1,2) NOT IN ('91','92','93');

    -- LOCATIE NAAM

    -- Change Locatie NAME for 91,92,93 (first 2 positions of [org eenheid code] to BEHANDELZAKEN

    UPDATE DRPDATA

    SET DRPDATA.LocatieNaam = 'Behandelzaken'

    WHERE SUBSTRING([Org eenheid code],1,2) IN ('91','92','93');

    UPDATE DRPDATA

    SET DRPDATA.LocatieNaam = Huizen.Naam

    FROM DRPDATA INNER JOIN Huizen ON DRPDATA.LocatieNr = Huizen.NrHuis

    WHERE SUBSTRING([Org eenheid code],1,2) NOT IN ('91','92','93');

    -- LOCATIE NR FEITELIJK

    -- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken

    UPDATE DRPDATA

    SET DRPDATA.LocatieNrFeitelijk = SUBSTRING(drpdata.[Org eenheid code],3,2);

    -- LOCATIE CODE FEITELIJK

    -- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken

    UPDATE DRPDATA

    SET DRPDATA.LocatieCodeFeitelijk = Huizen.IDHuis

    FROM DRPDATA INNER JOIN Huizen ON SUBSTRING(drpdata.[Org eenheid code],3,2) = Huizen.NrHuis;

    -- OEGROEP

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Overhead'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 100 and SUBSTRING([Org eenheid code],5,3) <= 399;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Facilitair'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 400 and SUBSTRING([Org eenheid code],5,3) <= 499;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Overhead'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 500 and SUBSTRING([Org eenheid code],5,3) <= 899;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Locatiefactor'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 900 and SUBSTRING([Org eenheid code],5,3) <= 911;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Zorg'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 912 and SUBSTRING([Org eenheid code],5,3) <= 969;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'ZIDB'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 970 and SUBSTRING([Org eenheid code],5,3) <= 979;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Dagactiviteiten'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 980 and SUBSTRING([Org eenheid code],5,3) <= 989;

    UPDATE DRPDATA

    SET DRPDATA.OEGroep = 'Overhead'

    WHERE SUBSTRING([Org eenheid code],5,3) >= 990 and SUBSTRING([Org eenheid code],5,3) <= 999;

    --UPDATE DRPDATA

    --SET DRPDATA.OEGroep = 'Facilitair'

    --WHERE SUBSTRING([Org eenheid code],1,2) = 80;

    -- Extract MwNr, chars Before pipe char

    UPDATE DRPDATA

    SET MwID = substring([Ri Mdw Externkenmerk],1,CHARINDEX ('|', [Ri Mdw Externkenmerk])-1)

    -- Extract Dienstverband nummer, DvbNr, chars After pipe char

    UPDATE DRPDATA

    SET DvbNr = substring([Ri Mdw Externkenmerk],CHARINDEX ('|', [Ri Mdw Externkenmerk])+1,LEN([Ri Mdw Externkenmerk]))

    -- Extract NiveauZPT from MasterTabelFuncties

    UPDATE DRPDATA

    SET DRPDATA.NiveauZPT = MasterTabelFuncties.NiveauZPT

    FROM DRPDATA INNER JOIN MasterTabelFuncties ON DRPDATA.[Ri Functiecode] = MasterTabelFuncties.IDFunctie

    -- Extract DienstGroep from MasterTabelRISoortDienst

    UPDATE DRPDATA

    SET DRPDATA.DienstGroep = MasterTabelRISoortDienst.DienstGroep

    FROM DRPDATA INNER JOIN MasterTabelRISoortDienst ON DRPDATA.[Ri Soortdienst] = MasterTabelRISoortDienst.RiSoortDienst;

    -- Extract OE Omschrijving from MasterTabelOECode

    UPDATE DRPDATA

    SET DRPDATA.OEOmschrijving = MasterTabelOECode.OEOmschrijving

    FROM DRPDATA INNER JOIN MasterTabelOECode ON DRPDATA.[Org eenheid code] = MasterTabelOECode.OECode;

    -- EXTRACT FTE HRS FROM TABLE DRPToewijzingen

    UPDATE DRPDATA

    SET DRPDATA.FTEHrs = t.[Toewijzing uren (voltijd)]

    FROM DRPDATA AS D inner JOIN

    DRPToewijzingen AS T ON D.MwID = T.[Wrkn Werknemernummer]

    AND D.[Org eenheid code] = T.[Toewijzing Orgeenheid code]

    AND D.Begindatum BETWEEN T.[Toewijzing begindatum] AND T.[Toewijzing einddatum];

    -- UPDAT VASTFLEX COLUMN

    UPDATE DRPDATA

    SET DRPDATA.FLEXVAST = CASE

    WHEN FTEHrs = 0 THEN 'FLEX'

    WHEN FTEHrs > 0 THEN 'VAST'

    ELSE 'ONB'

    END;

    -- ADD NEW ORG EENHEID CODES TO MasterTabelAfdelingenDRP

    INSERT INTO MasterTabelAfdelingenDRP ([Org eenheid code],LocatieCode,LocatieNr,Locatienaam,OEGroep,OEOmschrijving)

    SELECT DISTINCT D.[Org eenheid code], D.LocatieCode, D.LocatieNr, D.Locatienaam, D.OEGroep, D.OEOmschrijving

    FROM DRPDATA as D left outer join MasterTabelAfdelingenDRP as A on D.[Org eenheid code] = A.[Org eenheid code]

    where A.[Org eenheid code] is null;

    -- ADD Afdeling (A01 etc) to field AfdelingZPT. Get from table MasterTabelAfdelingenDRP

    UPDATE DRPDATA

    SET AfdelingZPT = MasterTabelAfdelingenDRP.ZPTIdAfd

    FROM DRPDATA AS d INNER JOIN

    MasterTabelAfdelingenDRP ON d.LocatieCode = MasterTabelAfdelingenDRP.LocatieCode AND

    d.[Org eenheid code] = MasterTabelAfdelingenDRP.[Org eenheid code];

    -- ADD IDENTITY COLUMN

    ALTER TABLE DBO.DRPDATA

    ADD IDDRPDATA BIGINT identity(1,1);

    END

    GO

  • If I understand correctly, you're loading a file into a table that gets recreated each time. You create the table with the columns from the file and add the other columns after you loaded the file.

    Why don't you create the table correctly and use a formatfile with your bulk insert? Even better, why don't you use a staging table?

    You could also change your update statements to use fewer of them.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi Luis,

    Thank you. I am new to SQL Server and am learning and developing at the same time. (Excel front end, SQL Sever back end).

    You guessed right. Every morning a csv file is generated, I need to process/manipulate that data for a number of reports.

    Did a quick search on "staging tables" and might give that a shot.

    Cheers,

    Julian

  • Just because I thought it would be fun to play around to give you an example, here's something I would propose you to try.

    Assuming that you have 2 permanent tables as follows:

    CREATE TABLE DRPDATA_STAGING (

    Dienstcode VARCHAR(50)

    ,Begindatum VARCHAR(12)

    ,Sorteernaam VARCHAR(100)

    ,[Org eenheid code] VARCHAR(20)

    ,Begintijd INT

    ,Eindtijd INT

    ,[Duur (in tijd)] VARCHAR(20)

    ,[Afwijkende tijd J/N] VARCHAR(10)

    ,Roosteritemstatus VARCHAR(50)

    ,[Ri Soortdienst] VARCHAR(50)

    ,[Ri Mdw Externkenmerk] VARCHAR(50)

    ,[Ri Functiecode] VARCHAR(50)

    ,[Ri Functieoms] VARCHAR(100)

    )

    CREATE TABLE DRPDATA (

    IDDRPDATA BIGINT identity (1,1)

    ,Dienstcode VARCHAR(50)

    ,Begindatum DATETIME

    ,Sorteernaam VARCHAR(100)

    ,[Org eenheid code] VARCHAR(20)

    ,Begintijd INT

    ,Eindtijd INT

    ,[Duur (in tijd)] INT

    ,[Afwijkende tijd J/N] VARCHAR(10)

    ,Roosteritemstatus VARCHAR(50)

    ,[Ri Soortdienst] VARCHAR(50)

    ,[Ri Mdw Externkenmerk] VARCHAR(50)

    ,[Ri Functiecode] VARCHAR(50)

    ,[Ri Functieoms] VARCHAR(100)

    ,[Duur] INT NULL

    ,[LocatieNr] INT NULL

    ,[LocatieCode] VARCHAR(4) NULL

    ,[LocatieNaam] VARCHAR(50) NULL

    ,[LocatieNrFeitelijk] INT NULL

    ,LocatieCodeFeitelijk VARCHAR(4) NULL

    ,[MwID] INT NULL

    ,[DvbNr] INT NULL

    ,[NiveauZPT] VARCHAR(50) NULL

    ,[DienstGroep] VARCHAR(50) NULL

    ,[OEOmschrijving] VARCHAR(100) NULL

    ,OEGroep VARCHAR(100) NULL

    ,AfdelingZPT VARCHAR(4) NULL

    ,[FTEHrs] FLOAT NULL

    ,[FlexVast] VARCHAR(10) NULL

    )

    You could save some steps with this:

    TRUNCATE TABLE dbo.DRPDATA

    TRUNCATE TABLE dbo.DRPDATA_STAGING

    -- INSERT DATA FROM CSV FILE

    BULK INSERT DRPDATA_STAGING

    FROM 'D:\xxx\drpoutput2.csv' WITH (

    FIRSTROW = 2

    ,FIELDTERMINATOR = ';'

    ,ROWTERMINATOR = ''

    ,

    --ERRORFILE = 'D:\ErrorRows.csv',

    TABLOCK

    )

    INSERT INTO DRPDATA(

    Dienstcode

    ,Begindatum

    ,Sorteernaam

    ,[Org eenheid code]

    ,Begintijd

    ,Eindtijd

    ,[Duur (in tijd)]

    ,[Afwijkende tijd J/N]

    ,Roosteritemstatus

    ,[Ri Soortdienst]

    ,[Ri Mdw Externkenmerk]

    ,[Ri Functiecode]

    ,[Ri Functieoms]

    ,[Duur]

    ,[LocatieNr]

    ,[LocatieCode]

    ,[LocatieNaam]

    ,[LocatieNrFeitelijk]

    ,[MwID]

    ,[DvbNr]

    ,OEGroep

    ,AfdelingZPT

    ,[FTEHrs]

    ,[FlexVast]

    )

    SELECT Dienstcode

    ,CASE

    WHEN ISDATE(Begindatum) = 0

    THEN CONVERT(DATE, '30/01/2222', 103)

    ELSE CONVERT(DATE, Begindatum, 103)

    END AS Begindatum

    ,Sorteernaam

    ,[Org eenheid code]

    ,Begintijd

    ,Eindtijd

    ,CASE

    WHEN [Duur (in tijd)] NOT LIKE '%[^0-9]%'

    THEN CAST([Duur (in tijd)] AS INT)

    ELSE 0

    END AS [Duur (in tijd)]

    ,[Afwijkende tijd J/N]

    ,Roosteritemstatus

    ,[Ri Soortdienst]

    ,[Ri Mdw Externkenmerk]

    ,[Ri Functiecode]

    ,[Ri Functieoms]

    ,CASE

    WHEN [Duur (in tijd)] NOT LIKE '%[^0-9]%'

    THEN round(([Duur (in tijd)] / 100), 0, 1) * 60 +

    ([Duur (in tijd)] - round(([Duur (in tijd)] / 100), 0, 1) * 100)

    END AS [Duur]

    ,CASE

    WHEN SUBSTRING([Org eenheid code], 1, 2) IN ('91','92','93')

    THEN 91

    ELSE SUBSTRING([Org eenheid code], 3, 2)

    END AS [LocatieNr]

    ,CASE

    WHEN SUBSTRING([Org eenheid code], 1, 2) IN ('91','92','93')

    THEN 'BZ' END AS LocatieCode

    ,CASE

    WHEN SUBSTRING([Org eenheid code], 1, 2) IN ('91','92','93')

    THEN 'Behandelzaken' END AS [LocatieNaam]

    ,SUBSTRING([Org eenheid code], 3, 2) AS LocatieNrFeitelijk

    ,substring([Ri Mdw Externkenmerk], 1, CHARINDEX('|', [Ri Mdw Externkenmerk]) - 1) AS [MwID]

    ,substring([Ri Mdw Externkenmerk], CHARINDEX('|', [Ri Mdw Externkenmerk]) + 1, LEN([Ri Mdw Externkenmerk])) AS [DvbNr]

    ,CASE WHEN SUBSTRING([Org eenheid code], 5, 3) >= 100

    AND SUBSTRING([Org eenheid code], 5, 3) <= 399

    THEN 'Overhead'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 400

    AND SUBSTRING([Org eenheid code], 5, 3) <= 499

    THEN 'Facilitair'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 500

    AND SUBSTRING([Org eenheid code], 5, 3) <= 899

    THEN 'Overhead'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 900

    AND SUBSTRING([Org eenheid code], 5, 3) <= 911

    THEN 'Locatiefactor'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 912

    AND SUBSTRING([Org eenheid code], 5, 3) <= 969

    THEN 'Zorg'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 970

    AND SUBSTRING([Org eenheid code], 5, 3) <= 979

    THEN 'ZIDB'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 980

    AND SUBSTRING([Org eenheid code], 5, 3) <= 989

    THEN 'Dagactiviteiten'

    WHEN SUBSTRING([Org eenheid code], 5, 3) >= 990

    AND SUBSTRING([Org eenheid code], 5, 3) <= 999

    THEN 'Overhead'

    END AS OEGroep

    ,NULL AS AfdelingZPT

    ,NULL AS [FTEHrs]

    ,NULL AS [FlexVast]

    FROM dbo.DRPDATA_STAGING

    -- LOCATIE CODE

    -- Change Locatie CODE for 91,92,93 (first 2 positions of [org eenheid code] to BZ

    -- Otherwise use 'normal' rule: Extract from table Huizen

    UPDATE DRPDATA SET

    LocatieCode = Huizen.IDHuis

    ,LocatieNaam = Huizen.Naam

    FROM DRPDATA

    INNER JOIN Huizen ON DRPDATA.LocatieNr = Huizen.NrHuis

    WHERE SUBSTRING([Org eenheid code], 1, 2) NOT IN ('91','92','93');

    -- LOCATIE CODE FEITELIJK

    -- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken

    UPDATE DRPDATA

    SET DRPDATA.LocatieCodeFeitelijk = Huizen.IDHuis

    FROM DRPDATA

    INNER JOIN Huizen ON SUBSTRING(drpdata.[Org eenheid code], 3, 2) = Huizen.NrHuis;

    -- Extract NiveauZPT from MasterTabelFuncties

    UPDATE DRPDATA

    SET DRPDATA.NiveauZPT = MasterTabelFuncties.NiveauZPT

    FROM DRPDATA

    INNER JOIN MasterTabelFuncties ON DRPDATA.[Ri Functiecode] = MasterTabelFuncties.IDFunctie

    -- Extract DienstGroep from MasterTabelRISoortDienst

    UPDATE DRPDATA

    SET DRPDATA.DienstGroep = MasterTabelRISoortDienst.DienstGroep

    FROM DRPDATA

    INNER JOIN MasterTabelRISoortDienst ON DRPDATA.[Ri Soortdienst] = MasterTabelRISoortDienst.RiSoortDienst;

    -- Extract OE Omschrijving from MasterTabelOECode

    UPDATE DRPDATA

    SET DRPDATA.OEOmschrijving = MasterTabelOECode.OEOmschrijving

    FROM DRPDATA

    INNER JOIN MasterTabelOECode ON DRPDATA.[Org eenheid code] = MasterTabelOECode.OECode;

    -- EXTRACT FTE HRS FROM TABLE DRPToewijzingen

    UPDATE DRPDATA

    SET DRPDATA.FTEHrs = t.[Toewijzing uren (voltijd)]

    FROM DRPDATA AS D

    INNER JOIN DRPToewijzingen AS T ON D.MwID = T.[Wrkn Werknemernummer]

    AND D.[Org eenheid code] = T.[Toewijzing Orgeenheid code]

    AND D.Begindatum BETWEEN T.[Toewijzing begindatum]

    AND T.[Toewijzing einddatum];

    -- UPDAT VASTFLEX COLUMN

    UPDATE DRPDATA

    SET DRPDATA.FLEXVAST = CASE

    WHEN FTEHrs = 0

    THEN 'FLEX'

    WHEN FTEHrs > 0

    THEN 'VAST'

    ELSE 'ONB'

    END;

    -- ADD NEW ORG EENHEID CODES TO MasterTabelAfdelingenDRP

    INSERT INTO MasterTabelAfdelingenDRP (

    [Org eenheid code]

    ,LocatieCode

    ,LocatieNr

    ,Locatienaam

    ,OEGroep

    ,OEOmschrijving

    )

    SELECT DISTINCT D.[Org eenheid code]

    ,D.LocatieCode

    ,D.LocatieNr

    ,D.Locatienaam

    ,D.OEGroep

    ,D.OEOmschrijving

    FROM DRPDATA AS D

    LEFT OUTER JOIN MasterTabelAfdelingenDRP AS A ON D.[Org eenheid code] = A.[Org eenheid code]

    WHERE A.[Org eenheid code] IS NULL;

    -- ADD Afdeling (A01 etc) to field AfdelingZPT. Get from table MasterTabelAfdelingenDRP

    UPDATE DRPDATA

    SET AfdelingZPT = MasterTabelAfdelingenDRP.ZPTIdAfd

    FROM DRPDATA AS d

    INNER JOIN MasterTabelAfdelingenDRP ON d.LocatieCode = MasterTabelAfdelingenDRP.LocatieCode

    AND d.[Org eenheid code] = MasterTabelAfdelingenDRP.[Org eenheid code];

    Note that:

    - You don't need to add columns during the procedure.

    - The insert is using less columns than the columns from the table by using a column list.

    - There can be more than one column updated in an update statement.

    - I left some columns out of the insert because I don't know your data and can't understand the column names to even guess what they could mean. You could add them if you think that is safe to do it.

    - I changed the ISNUMERIC function to a comparison to have only digits. The reason is explained here[/url].

    If you have any questions, feel free to ask.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi Luis,

    I really appreciate this, thank you. 🙂

    Tomorrow, Sunday, I will try your code.

    Thanks,

    Julian,

    Spain (no hablar Espanol)

  • Hi Luis, that was very helpful.

    Below script for a similar situation.

    -- DROP THE TABLE

    IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DRPToewijzingen_STAGING')

    DROP TABLE dbo.DRPToewijzingen_STAGING

    GO

    USE WZH

    GO

    CREATE TABLE DRPToewijzingen_STAGING(

    [Wrkn Werknemernummer] [varchar](50) NULL,

    [Wrkn Volledige naam (rooster)] [varchar](50) NULL,

    [Oe Id] [varchar](50) NULL,

    [Toewijzing begindatum] [varchar](50) NULL,

    [Toewijzing einddatum] [varchar](50) NULL,

    [Toewijzing uren (voltijd)] [varchar](50) NULL,

    [Toewijzing percentage] [varchar](50) NULL,

    [Toewijzing functiecode] [varchar](50) NULL,

    [Toewijzing status] [varchar](50) NULL,

    [Dvb Externkenmerk] [varchar](50) NULL,

    [Dvb Datumindienst] [varchar](50) NULL,

    [Dvb Datumuitdienst] [varchar](50) NULL,

    [Medewerker Id] [varchar](50) NULL,

    [Toewijzing Orgeenheid code] [varchar](50) NULL,

    [Toewijzing Orgeenheid] [varchar](50) NULL

    )

    GO

    -- DROP THE TABLE

    IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'DRPToewijzingen')

    DROP TABLE dbo.DRPToewijzingen

    GO

    CREATE TABLE DRPToewijzingen

    (

    IDDRPTOEWIJZINGEN BIGINT identity (1,1),

    [Wrkn Werknemernummer] [varchar](50) NULL,

    [Wrkn Volledige naam (rooster)] [varchar](50) NULL,

    [Oe Id] [varchar](50) NULL,

    [Toewijzing begindatum] DATETIME NULL,

    [Toewijzing einddatum] DATETIME NULL,

    [Toewijzing uren (voltijd)] DECIMAL(18,2) NULL,

    [Toewijzing percentage] DECIMAL(18,2) NULL,

    [Toewijzing functiecode] [varchar](50) NULL,

    [Toewijzing status] [varchar](50) NULL,

    [Dvb Externkenmerk] [varchar](50) NULL,

    [Dvb Datumindienst] [varchar](50) NULL,

    [Dvb Datumuitdienst] [varchar](50) NULL,

    [Medewerker Id] [varchar](50) NULL,

    [Toewijzing Orgeenheid code] [varchar](50) NULL,

    [Toewijzing Orgeenheid] [varchar](50) NULL,

    [LocatieNr] INT NULL,

    [LocatieCode] varchar(4) NULL,

    [LocatieNaam] varchar(50) NULL,

    [LocatieNrFeitelijk] int NULL,

    LocatieCodeFeitelijk varchar(4) NULL

    )

    GO

    and this

    USE [WZH]

    GO

    /**********************************

    DROP CREATE spIMPORTDRPTOEW

    ***********************************/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIMPORTDRPTOEW]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[spIMPORTDRPTOEW]

    GO

    USE [WZH]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[spIMPORTDRPTOEW]

    AS

    BEGIN

    /*

    JJR 20140409

    IMPORT DRPToewijzingen CSV File

    CONVERT STRINGS TO DATE AND INT

    TRUNCATE Removes all rows from a table without logging the individual row deletions.

    TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause,

    however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

    20140423Added columns LocatieNaam,Nr,Code etc

    20140423Added column for OEGroep similar to DRPData

    20140427Used Staging table

    */

    TRUNCATE TABLE dbo.DRPToewijzingen

    TRUNCATE TABLE dbo.DRPToewijzingen_STAGING

    -- INSERT DATA FROM CSV FILE INTO STAGING table

    BULK

    INSERT DRPToewijzingen_STAGING

    FROM 'D:\xxx\drptoewijzingen.csv'

    WITH

    (

    FIRSTROW = 2,

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '',

    --ERRORFILE = 'D:\ErrorRows.csv',

    TABLOCK

    )

    UPDATE DRPToewijzingen_STAGING

    SET [Toewijzing begindatum] = CONVERT(DATETIME,[Toewijzing begindatum],111)

    UPDATE DRPToewijzingen_STAGING

    SET [Toewijzing einddatum] = CONVERT(DATETIME,[Toewijzing einddatum],111)

    -- INSERT DATA FROM STAGING TABLE INTO DRPToewijzingen table

    INSERT INTO DRPToewijzingen

    (

    [Wrkn Werknemernummer],

    [Wrkn Volledige naam (rooster)],

    [Oe Id],

    [Toewijzing begindatum],

    [Toewijzing einddatum],

    [Toewijzing uren (voltijd)],

    [Toewijzing percentage],

    [Toewijzing functiecode],

    [Toewijzing status],

    [Dvb Externkenmerk],

    [Dvb Datumindienst],

    [Dvb Datumuitdienst],

    [Medewerker Id],

    [Toewijzing Orgeenheid code],

    [Toewijzing Orgeenheid],

    [LocatieNr],

    [LocatieCode],

    [LocatieNaam],

    [LocatieNrFeitelijk],

    LocatieCodeFeitelijk

    )

    SELECT

    [Wrkn Werknemernummer],

    [Wrkn Volledige naam (rooster)],

    [Oe Id],

    [Toewijzing begindatum],

    CASE

    WHEN ISDATE([Toewijzing einddatum]) = 0 THEN CONVERT(DATE, '22220131', 111)

    --ELSE [Toewijzing einddatum]

    ELSE CONVERT(DATE, [Toewijzing einddatum], 111)

    END AS

    [Toewijzing einddatum],

    CASE

    WHEN ISNUMERIC([Toewijzing uren (voltijd)]) = 1

    THEN CAST(replace([Toewijzing uren (voltijd)],',','.') AS DECIMAL(18,2))

    ELSE 0

    END AS

    [Toewijzing uren (voltijd)],

    CASE

    WHEN ISNUMERIC([Toewijzing percentage]) = 1

    THEN CAST(replace([Toewijzing percentage],',','.') AS DECIMAL(18,2))

    ELSE 0

    END AS

    [Toewijzing percentage],

    [Toewijzing functiecode],

    [Toewijzing status],

    [Dvb Externkenmerk],

    [Dvb Datumindienst],

    [Dvb Datumuitdienst],

    [Medewerker Id],

    [Toewijzing Orgeenheid code],

    [Toewijzing Orgeenheid],

    CASE

    WHEN SUBSTRING([Toewijzing Orgeenheid code],1,2) IN ('91','92','93') THEN 91

    ELSE SUBSTRING([Toewijzing Orgeenheid code],3,2)

    END AS

    [LocatieNr]

    ,NULL AS [LocatieCode]

    ,NULL AS [LocatieNaam]

    ,NULL AS [LocatieNrFeitelijk]

    ,NULL AS LocatieCodeFeitelijk

    FROM DRPToewijzingen_STAGING

    /*

    UPDATE VARIOUS FIELDS IN TABLE

    */

    -- LOCATIE CODE

    -- Change Locatie CODE for 91,92,93 (first 2 positions of [org eenheid code] to BZ

    -- Otherwise use 'normal' rule: Extract from table Huizen

    UPDATE DRPToewijzingen

    SET DRPToewijzingen.LocatieCode = 'BZ'

    WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) IN ('91','92','93');

    UPDATE DRPToewijzingen

    SET DRPToewijzingen.LocatieCode = Huizen.IDHuis

    FROM DRPToewijzingen INNER JOIN Huizen ON DRPToewijzingen.LocatieNr = Huizen.NrHuis

    WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) NOT IN ('91','92','93');

    -- LOCATIE NAAM

    -- Change Locatie NAME for 91,92,93 (first 2 positions of [org eenheid code] to BEHANDELZAKEN

    UPDATE DRPToewijzingen

    SET DRPToewijzingen.LocatieNaam = 'Behandelzaken'

    WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) IN ('91','92','93');

    UPDATE DRPToewijzingen

    SET DRPToewijzingen.LocatieNaam = Huizen.Naam

    FROM DRPToewijzingen INNER JOIN Huizen ON DRPToewijzingen.LocatieNr = Huizen.NrHuis

    WHERE SUBSTRING([Toewijzing Orgeenheid code],1,2) NOT IN ('91','92','93');

    -- LOCATIE NR FEITELIJK

    -- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken

    UPDATE DRPToewijzingen

    SET DRPToewijzingen.LocatieNrFeitelijk = SUBSTRING(DRPToewijzingen.[Toewijzing Orgeenheid code],3,2);

    -- LOCATIE CODE FEITELIJK

    -- Voor behandelaars van belang. Locatie = Behandelzaken, Feitelijk kunnen ze in een huis werken

    UPDATE DRPToewijzingen

    SET DRPToewijzingen.LocatieCodeFeitelijk = Huizen.IDHuis

    FROM DRPToewijzingen INNER JOIN Huizen ON SUBSTRING(DRPToewijzingen.[Toewijzing Orgeenheid code],3,2) = Huizen.NrHuis;

    END

    GO

    The CSV dates were causing me some problems: day and mont got switched.

    The text data looks like this:

    25440;P.M. vd E;378;2003-06-01;;0,00;0,00;399001;A;25440|1;2003-02-01;;4855;5353999;Vrijwilligers

    Dates are like yyyy-mm-dd. I used CONVERT(DATETIME, myDate,111) which seems to work. Still not very confident about conversion of datestrings to datetime.

    Thanks for your help,

    Cheers, Julian

  • How did you get the code 111 for CONVERT? That's completely wrong for YYYY-MM-DD. You could use code 120 or 121, however, that's an ISO stardard format so you shouldn't get those issues.

    For a better reference on CONVERT codes for datetime values, check Books On Line: http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I found it here: click

    Tried everything, but the months and days got swapped. Logins have Dutch language setting (not sure if that is of any consequence).

    Cheers,

    Julian

  • That's the same link I posted. 111 is the code referenced as Japan's standard which is defined as yyyy/mm/dd. That's not exactly yyyy-mm-dd.

    Dutch language uses the format dmy, so that shouldn't be an issue and the format you're using is the ISO standard.

    What happens when you run this code?

    SELECT CONVERT( char(20), CONVERT( datetime, '2014-04-08'))

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I get the following

    (No column name)

    aug 4 2014 12:00AM

  • Am I correct to assume that @myDate is our starting point?

    And that CONVERT only changes the display of the date string according to the added parameter (111 or 105).

    Then the calculations should give the same results...

    J.

    How do you "tell" SQL Server that @myDate is in this format: yyyy-mm-dd?

    Declare @myDate varchar(50)

    Set @myDate = '2014-04-08'

    SELECT CONVERT( char(20), CONVERT( datetime, @myDate))

    select (convert(datetime, @myDate,111))+22

    select convert(char(20), (convert(datetime, @myDate,111))+22)

    select (convert(datetime, @myDate,105))+22

    select convert(char(20), (convert(datetime, @myDate,105))+22)

    This gives identical results:

    Declare @myDate varchar(50)

    Set @myDate = '2014/dec/10' --yyyy-mm-dd

    SELECT CONVERT( char(20), CONVERT( datetime, @myDate))

    select (convert(datetime, @myDate,111))+22

    select convert(char(20), (convert(datetime, @myDate,111))+22)

    select (convert(datetime, @myDate,105))+22

    select convert(char(20), (convert(datetime, @myDate,105))+22)

  • What about using the code 120?

    CONVERT( datetime, @myDate, 120)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I might strip out the "-" to prevent issues.

    See this post, last message.

    Here I get consistant results:

    Declare @myDate varchar(50)

    Set @myDate = '20141210' --yyyymmdd

    SELECT CONVERT( char(20), CONVERT( datetime, @myDate))

    select (convert(datetime, @myDate,111))+22

    select convert(char(20), (convert(datetime, @myDate,111))+22)

    select (convert(datetime, @myDate,105))+22

    select convert(char(20), (convert(datetime, @myDate,105))+22)

    select (convert(datetime, @myDate,120))+22

    select convert(char(20), (convert(datetime, @myDate,120))+22)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply