Replace data value with reference to the row of the same table in same database

Question

Post reply

Replace data value with reference to the row of the same table in same database

SPtrt

SSChasing Mays

Points: 603
More actions
March 3, 2011 at 8:34 am

#236041

Hi all,
I'm looking for the query to replace zero value from the another row in the same table with the help of date.
eg)
I have a count in 27-FEB-2011 as 40
bit I have a count in 20-Feb-2011 as 0
so i need to replace the 20-Feb-2011 as [40(27-feb-2011) * 0.12%]
TABLE STRUCTURE
DateandTime, ID, Count,
Eg) 20Feb2011 10AM, 23 , 0
20Feb2011 11AM, 26, 0
20FEB2011 12PM, 34, 0
27FEB2011 10AM, 23, 20
27Feb2011 11AM, 26, 56
27Feb2011 12PM, 34, 34
Required Output:
================
20FEB2011 10AM, 23, 20
20FEB2011 11AM, 26, 56
20FEB2011 12PM, 34, 34
rest of all same
Any help.. Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Please post the DDL and DML.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SPtrt SSChasing Mays Points: 603 More actions · Answer 2

HI,

Thanks for reply and sorry for late reply, Here the DDL

Update tblCount SET count=(select count from tblCount where id='10' and date='20110229 10:00:00') WHERE id='10' and date='20110220 10:00:00'

the output is 20.

where my logic works.

Here the data in table are

20110220 count=0

20110229 count=20

So i need this for dynamic ie if i give the datetime it automatically search of ID between two dates and update zero value from another date. (we change get value by given date).

Do you need in more detail....

Thanks,

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

DDL = Data Definition Language, i.e. your CREATE TABLE statements.

DML = Data Manipulation Language, i.e. your INSERT statements to build the data in your tables that we will need to help you arrive at a solution

Please read this article and help us help you: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SPtrt SSChasing Mays Points: 603 More actions · Answer 4

Hi,

here i'm giving in detail

create table dbo.tblCount

(

ctID int(3) not null,

ctDate DateTime,

CtCount int

)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110210 10:00:00', 23)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110210 10:30:00', 40)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110210 11:00:00', 25)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110210 11:30:00', 30)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110210 10:00:00', 60)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110210 10:30:00', 43)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110210 11:00:00', 53)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110210 11:30:00', 33)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110218 10:00:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110218 10:30:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110218 11:00:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (1,'20110218 11:30:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110218 10:00:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110218 10:30:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110218 11:00:00', 0)

Insert into tblCount (ctId, CtDate, CtCount) Values (2,'20110218 11:30:00', 0)

--update query

update dbo.tblCount set ctValue=10*(select ctCount from tblCount where ctSourceid='1' and ctDate='20110210 10:00:00') where ctSourceId='1' and Ctdate='20110218 10:00:00'

--After update

select * from tblCount where ctSourceId=1

Kindly adivce me need to post in detail.

Thanks for your reply..

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Thanks for posting your DDL and DML...I think it might be incomplete however.

SPtiruttani (3/3/2011)
Hi all,
I'm looking for the query to replace zero value from the another row in the same table with the help of date.
eg)
I have a count in 27-FEB-2011 as 40
bit I have a count in 20-Feb-2011 as 0
so i need to replace the 20-Feb-2011 as [40(27-feb-2011) * 0.12%]
TABLE STRUCTURE
DateandTime, ID, Count,
Eg) 20Feb2011 10AM, 23 , 0
20Feb2011 11AM, 26, 0
20FEB2011 12PM, 34, 0
27FEB2011 10AM, 23, 20
27Feb2011 11AM, 26, 56
27Feb2011 12PM, 34, 34
Required Output:
================
20FEB2011 10AM, 23, 20
20FEB2011 11AM, 26, 56
20FEB2011 12PM, 34, 34
rest of all same
Any help.. Thanks in advance

None of the DML statements you provided create a row with a date greater than 2/18. Also, your table definition provides a column width for INT which is not supported in SQL Server. This is looking more and more like a homework assignment...for something other than SQL Server. Is it deliberate?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato