CODEWITHSUNDEEP
sqlsql-serverif-statementinsert
Fearghal
Fearghal

Reputation: 11407

sql server insert with if statement

I am trying to insert a row if date_start (type datetime) is in past and date_start+duration(type; real) (gets the end date.) is in future. I keep getting 'more than one result returned from sub query.

IF  (CAST(CONVERT(datetime,(SELECT date_start FROM [tableA])) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float))<0 
AND 
(24*(CAST(CONVERT(datetime, (SELECT date_start FROM [tableA])) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float)) + (SELECT duration FROM [tableA]))>0
BEGIN
        INSERT INTO [tableB](col1) 
    select 24*(CAST(CONVERT(datetime,date_start) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float))  FROM [tableA]
END

Any idea how can i do this?

Upvotes: 0

Views: 2851

Answers (3)

Fearghal
Fearghal

Reputation: 11407

Ok think i got it.... correct me if im wrong but the cursor and variable stuff is not needed. I ended up with this. I couldnt have done it with ya, cheers

INSERT INTO [TABLEB](col1,........, col6) 
         select 24*(CAST(CONVERT(datetime,date_start) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float)), ENTITY, 'PM', NULL, NULL, NULL, NULL, '0'  FROM [TABLEA] 
            where (CAST(CONVERT(datetime,date_start) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float))<0 
            AND 
            (24*(CAST(CONVERT(datetime,date_start) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float)) + duration)>0

Upvotes: 0

AgentSQL
AgentSQL

Reputation: 2940

@Fearghal you should try this - 

DECLARE @required_date DATETIME
DECLARE @duration REAL

DECLARE date_cursor CURSOR FOR
SELECT date_start, duration FROM [tableA]

OPEN date_cursor
FETCH NEXT FROM date_cursor
INTO @required_date, @duration 
WHILE @@FETCH_STATUS = 0
BEGIN
    IF  (CAST(@required_date as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as    float))<0 
AND 
(24*(CAST(@required_date as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float)) + @duration)>0
    BEGIN
         INSERT INTO [tableB](col1) 
         select 24*(CAST(CONVERT(datetime,date_start) as float)- CAST(CONVERT(datetime,CURRENT_TIMESTAMP) as float))  FROM [KAP_db_C4].[dbo].[PM]
    END
FETCH NEXT FROM date_cursor
INTO @required_date, @duration
END
CLOSE date_cursor
DEALLOCATE date_cursor

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

That would be because of this one:

SELECT duration FROM [tableA]

I'm quite sure based off your error that needs filtered so that one row gets returned.

Upvotes: 1

Related Questions