Help on query the has to do multiple actions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help on query the has to do multiple actions

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-07 : 05:18:33

Hello all,
I have some problems to write a query that performs some actions on a table.
For example, I have this table:

CREATE TABLE [dbo].[Table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Value1] [decimal](18, 2) NOT NULL,
[UpdatingDate] [datetime] NOT NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And here some test values:

INSERT INTO [Prove].[dbo].[Table1]
([StartDate]
,[EndDate]
,[Value1]
,[UpdatingDate])
VALUES
('2011-12-02 22:00:00','2011-12-30 23:00:00',10,GETDATE()),
('2011-12-01 22:00:00','2011-12-02 21:00:00',11,GETDATE()),
('2011-12-02 01:00:00','2011-12-02 23:00:00',12,GETDATE()),
('2011-12-01 23:00:00','2011-12-31 13:00:00',13,GETDATE()),
('2011-12-02 22:00:00','2011-12-30 23:00:00',14,GETDATE()),
('2011-12-07 22:00:00','2011-12-17 23:00:00',15,GETDATE())

Now I get in input a date parameter, without hours, for example:
‘2011-12-02’
And I have to select the record that start in the past and end in the future. For this scope I can write this query:

select * from Table1
where CONVERT(date,StartDate) < '2011-12-02' and CONVERT(date,EndDate) > '2011-12-02'

and I get the record ID=4, the only that satisfy this condition:

ID StartDate EndDate Value1 UpdatingDate
4 2011-12-01 23:00:00.000 2011-12-31 13:00:00.000 13.00 2011-12-06 20:20:51.593

(1 row(s) affected)

Now the problems.
This occurrence (which can be one or more, of course) has to do:

-An Update where I set the Enddate to contain the 23:59 of the previous day that passed as a parameter
(in this example, the date ' 2011-12-01 23:59:00 ')
-Should be inserted a new occurrence with the same characteristics (in this case 1 occurrence) that I’ve got previously but with Startdate set to 00:00 of the day following the one passed as a parameter (in this case with ' 2011-12-03 00:00).

The problem I am having with my tests is that, whatever the first thing I do (step 1 or step 2) I lose the historical situation, and I find myself with more rows or misguided as values.

At the end I should get this records:

ID StartDate EndDate Value1 UpdatingDate
4 2011-12-01 23:00:00.000 2011-12-01 23:59:00.000 13.00 2011-12-06 21:20:51.593
7 2011-12-03 00:00:00.000 2011-12-31 13:00:00.000 13.00 2011-12-06 21:20:51.593

Can anyone help me?

Thanks a lot.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 05:45:43

[code]

SELECT t.id,case when t1.id=2 then datval else StartDate end as StartDate,case when t1.id=1 then datval else EndDate end as enddate,Value1,UpdatingDate
from Table1 t
cross join (select 1 as id,dateadd(ss,-1,'2011-12-02') as datval
union all
select 2,dateadd(dd,1,'2011-12-02')
)t1
where CONVERT(date,StartDate) < '2011-12-02' and CONVERT(date,EndDate) > '2011-12-02'

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-07 : 12:45:55

Ok I obtain these 2 records:

id StartDate Enddate Value1 DataAggiornamento
4 2011-12-01 23:00:00.000 2011-12-01 23:59:59.000 13.00 2011-12-06 20:20:51.593
4 2011-12-03 00:00:00.000 2011-12-31 13:00:00.000 13.00 2011-12-06 20:20:51.593

but I have to perform un Update on my Table1 to keep the first one (with ID=4) and perform an Insert of the second record (with the ID=7, the next available identity value).

So with this:

4 2011-12-01 23:00:00.000 2011-12-01 23:59:59.000 13.00 2011-12-06 20:20:51.593

I need un Update (to obtain these same values)

and with the second record:

4 2011-12-03 00:00:00.000 2011-12-31 13:00:00.000 13.00 2011-12-06 20:20:51.593

I need an Insert, so with ID=7, like this:

7 2011-12-03 00:00:00.000 2011-12-31 13:00:00.000 13.00 2011-12-06 20:20:51.593

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 13:40:42

thats easier. something like below

before you do execute this, you need to drop existing primary key on id as its not unique anymore and then create a new one with composite columns based on your rule.


SELECT t.id,case when t1.id=2 then datval else StartDate end as  StartDate,case when t1.id=1 then datval else EndDate end  as enddate,Value1,UpdatingDate
into #temp
 from Table1 t
 cross join (select 1 as id,dateadd(ss,-1,'2011-12-02') as datval
             union all 
             select 2,dateadd(dd,1,'2011-12-02')
             )t1
where CONVERT(date,StartDate) < '2011-12-02' and CONVERT(date,EndDate) > '2011-12-02'

update t
set t.EndDate= tmp.EndDate
from Table1 t
inner join #temp tmp
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate	

insert into table1
select tmp.*
from #temp tmp
left join Table1 t
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate	
where t.ID is null

drop table #temp1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-07 : 15:05:57

With this script I got this error:

Msg 8101, Level 16, State 1, Line 21
An explicit value for the identity column in table 'table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

also if I put a "set identity_insert dbo.Table1 on" at the beginning of the script itself.

Where am I wrong?

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 00:36:49

quote:
Originally posted by Ciupaz

With this script I got this error:

Msg 8101, Level 16, State 1, Line 21
An explicit value for the identity column in table 'table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

also if I put a "set identity_insert dbo.Table1 on" at the beginning of the script itself.

Where am I wrong?

Luigi

as message suggests you need put column list also like below


SELECT t.id,case when t1.id=2 then datval else StartDate end as  StartDate,case when t1.id=1 then datval else EndDate end  as enddate,Value1,UpdatingDate
into #temp
 from Table1 t
 cross join (select 1 as id,dateadd(ss,-1,'2011-12-02') as datval
             union all 
             select 2,dateadd(dd,1,'2011-12-02')
             )t1
where CONVERT(date,StartDate) < '2011-12-02' and CONVERT(date,EndDate) > '2011-12-02'

update t
set t.EndDate= tmp.EndDate
from Table1 t
inner join #temp tmp
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate	

set idenitity_insert table1 on
insert into table1
(
column1,
column2,...
columnn
)
select tmp.*
from #temp tmp
left join Table1 t
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate	
where t.ID is null


set idenitity_insert table1 off

drop table #temp1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-08 : 09:18:14

With this query:

SELECT t.id,case when t1.id=2 then datval else StartDate end as StartDate,
case when t1.id=1 then datval else EndDate end as enddate,
Value1,
UpdatingDate
into #temp
from Table1 t
cross join (select 1 as id,dateadd(ss,-1,'2011-12-02') as datval
union all
select 2,dateadd(dd,1,'2011-12-02')
)t1
where CONVERT(date,StartDate) < '2011-12-02' and CONVERT(date,EndDate) > '2011-12-02'

update t
set t.EndDate= tmp.EndDate
from Table1 t
inner join #temp tmp
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate

set identity_insert table1 on
insert into table1
(
StartDate,
EndDate,
Value1,
UpdatingDate
)
select tmp.StartDate,tmp.enddate,tmp.Value1,tmp.UpdatingDate
from #temp tmp
left join Table1 t
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate
where t.ID is null

set identity_insert table1 off

drop table #temp

I got this error:

(0 row(s) affected)

(0 row(s) affected)
Msg 545, Level 16, State 1, Line 21
Explicit value must be specified for identity column in table 'Table1' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Maybe I need to know before the new ID to insert?

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:27:28

put the identity column also in column list. otherwise remove that set identity_insert statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-08 : 09:38:40

I've changed in this way:

SELECT t.id,case when t1.id=2 then datval else StartDate end as StartDate,
case when t1.id=1 then datval else EndDate end as enddate,
Value1,
UpdatingDate
into #temp
from Table1 t
cross join (select 1 as id,dateadd(ss,-1,'2011-12-02') as datval
union all
select 2,dateadd(dd,1,'2011-12-02')
)t1
where CONVERT(date,StartDate) < '2011-12-02' and CONVERT(date,EndDate) > '2011-12-02'

update t
set t.EndDate= tmp.EndDate
from Table1 t
inner join #temp tmp
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate

set identity_insert table1 on
insert into table1
(
ID,
StartDate,
EndDate,
Value1,
UpdatingDate
)
select IDENT_CURRENT('Table1')+1,
tmp.StartDate,tmp.enddate,tmp.Value1,tmp.UpdatingDate
from #temp tmp
left join Table1 t
on tmp.ID=t.ID
and tmp.StartDate = t.StartDate
where t.ID is null

set identity_insert table1 off

drop table #temp

It makes the Update block correctly, but not the Insert.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:57:48

why are you doing this?

IDENT_CURRENT('Table1')+1
dont you have id value generated from other table (#temp)?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-08 : 12:19:49

Finally I've got it.
I've created this stored procedure, and it seem works correctly:

create procedure dbo.MyStored
@DataFile datetime
as
set nocount on

SELECT t.id,
case when t1.id=2 then datval else StartDate end as StartDate,
case when t1.id=1 then datval else EndDate end as Enddate,
Value1,
UpdatingDate
into #temp
from Table1 t
cross join (select 1 as id,dateadd(ss,-1,@DataFile) as datval
union all
select 2,dateadd(dd,1,@DataFile)
)t1
where CONVERT(date,StartDate) < @DataFile and CONVERT(date,EndDate) > @DataFile

update t
set t.EndDate = tmp.EndDate
from Table1 t
inner join #temp tmp
on tmp.ID = t.ID
and tmp.StartDate = t.StartDate

insert into table1
(
StartDate,
EndDate,
Value1,
UpdatingDate
)
select tmp.StartDate,tmp.enddate,tmp.Value1,tmp.UpdatingDate
from #temp tmp
left join Table1 t
on tmp.ID = t.ID
and tmp.StartDate = t.StartDate
where t.ID is null

drop table #temp

Thank you so much Visakh.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 12:27:40

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-09 : 15:51:44

Hi Visask,
a little add to your query.
During the Insert and Updating blocks, I have to use a user function - dbo.FromLocalToUTC - to convert the EndDate (23:59 of the previous date of reference date) to UTC, and similar the StartDate (00:00 of the following date) also to UTC.

Where and how I have to modify the query to satisfy your join conditions?

Luigi

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-09 : 16:48:39

You can make the change as below

update t
set t.EndDate = dbo.FromLocalToUTC(tmp.EndDate)
....

And similarly during your insert..

insert into table1(...)
select dbo.FromLocalToUTC(tmp.StartDate),dbo.FromLocalToUTC(tmp.enddate)

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-10 : 05:19:05

Thanks a lot again Visakh.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:43:01

i think you have to thank Vijay for this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2011-12-10 : 08:12:02

Oh sorry, Vijay, thanks again.

Luigi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources