how to write the SQL code to add 2 collumn value

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

General SQL Server Forums

New to SQL Server Programming

how to write the SQL code to add 2 collumn value

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-07-02 : 00:09:05

hi All,
someone can advise me how to write the SQL code to add 2 column value from chart ( table1) chart1( table1) value together and update it to another table3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-02 : 00:13:17

[code]
update t3
set somecol = t1.chart + t1.chart1
from table3 t3 inner join tabble1 t1
on t3.pk = t1.pk
[/code]

KH
[spoiler]Time is always against us[/spoiler]

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-07-02 : 01:30:25

hi KHTAN,
Sorry, i am not really get the code you write here..please refer below for my 3 table. So what i wish to do is adding all the column value between chart and chart1 and update to Time.
dbo.chart
WW EE USD PM
WW01 20 22 30
WW02 20 50 60
WW03 10 20 10
dbo.chart1
WW EE USD PM
WW01 21 20 30
WW02 20 49 60
WW03 10 20 10
dbo.Time
WW EE USD PM
WW01
WW02
WW03

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-02 : 01:39:52

quote:
Originally posted by shan1430

hi KHTAN,
Sorry, i am not really get the code you write here..please refer below for my 3 table. So what i wish to do is adding all the column value between chart and chart1 and update to Time.
dbo.chart
WW EE USD PM
WW01 20 22 30
WW02 20 50 60
WW03 10 20 10
dbo.chart1
WW EE USD PM
WW01 21 20 30
WW02 20 49 60
WW03 10 20 10
dbo.Time
WW EE USD PM
WW01
WW02
WW03

Please post the table structure and which columns do you want to be added from table1 and table2 and get updated in table 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-02 : 01:47:40

[code]
update t
set ee = c.ee + c1.ee,
usd = c.usd + c1.usd,
pm = c.pm + c1.pm
from time t
inner join chart c on t.ww = c.ww
inner join chart1 c1 on t.ww = c1.ww
[/code]

KH
[spoiler]Time is always against us[/spoiler]

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-07-02 : 01:53:09

here you go..my 3 tables structure. Let take example for WW01 Row, Chart(EE)+Chart1(EE)=Time(EE),Chart(USD)+Chart1(USD)=Time(USD), Chart(PM)+Chart1(PM) = Time(PM). Thanks!!

dbo.chart
WW EE USD PM
WW01 20 22 30
WW02 20 50 60
WW03 10 20 10

dbo.chart1
WW EE USD PM
WW01 20 22 30
WW02 20 50 60
WW03 10 20 10

dbo.Time
WW EE USD PM
WW01 Sum(chart(EE)+chart1(EE)Sum(chart(USD)+char1(USD)same
WW02
WW03

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-02 : 02:22:24

ok please go through khtan's solution. it is the answer for ur question

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-07-02 : 04:47:50

hi Guys,
another help needed from you all...

How to write the SQL code for below calculation
from 7pm to 7am total is 12 hours, how i can write a code so that when I wanted to check the duration it will alway show be between 12 hours. Let take example from 2000(International) until 2300, the duration is just 2300-2100=100 ( 1 hours, so the calculation is just End Time - Start Time. How if my start time is from 2100 and End TIme is 3am => any formula can write to count our the duration for all this posibility.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 05:21:47

how will you get this?
2300-2100=100

it should be 200 not 100
also if ots format of time thats causing problem suggest you cast them to datetime before finding difference so that it calculates the difference correctly.

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-07-02 : 05:29:54

Oppss..sorry my mistake. It should 200.

Another question:
Any way to do the repeating(increase counter) calculation for below task.
update chart set
EE =(SELECT cast ((Sun_ET - Sun_ST)+( Mon_ET - Mon_ST) + (Tue_ET - Tue_ST) + (Wed_ET - Wed_ST) + (Thu_ET - Thu_ST) + (Fri_ET - Fri_ST) + (Sat_ET - Sat_ST) as varchar (8)) FROM WW01_D where WW01_D.cat = 'EE'),
USD=(SELECT cast ((Sun_ET - Sun_ST)+( Mon_ET - Mon_ST) + (Tue_ET - Tue_ST) + (Wed_ET - Wed_ST) + (Thu_ET - Thu_ST) + (Fri_ET - Fri_ST) + (Sat_ET - Sat_ST) as varchar (8)) FROM WW01_D where WW01_D.cat = 'USD'),
PM=(SELECT cast ((Sun_ET - Sun_ST)+( Mon_ET - Mon_ST) + (Tue_ET - Tue_ST) + (Wed_ET - Wed_ST) + (Thu_ET - Thu_ST) + (Fri_ET - Fri_ST) + (Sat_ET - Sat_ST) as varchar (8)) FROM WW01_D where WW01_D.cat = 'PM')
where WW = 'WW01'

Those highlited in Red is the variable that need to increase while repeatign the task
WW01_N = WW02_N-->WW52_N
WW01 = WW01-->WW52

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 05:59:57

quote:
Originally posted by shan1430

Oppss..sorry my mistake. It should 200.

Another question:
Any way to do the repeating(increase counter) calculation for below task.
update chart set
EE =(SELECT cast ((Sun_ET - Sun_ST)+( Mon_ET - Mon_ST) + (Tue_ET - Tue_ST) + (Wed_ET - Wed_ST) + (Thu_ET - Thu_ST) + (Fri_ET - Fri_ST) + (Sat_ET - Sat_ST) as varchar (8)) FROM WW01_D where WW01_D.cat = 'EE'),
USD=(SELECT cast ((Sun_ET - Sun_ST)+( Mon_ET - Mon_ST) + (Tue_ET - Tue_ST) + (Wed_ET - Wed_ST) + (Thu_ET - Thu_ST) + (Fri_ET - Fri_ST) + (Sat_ET - Sat_ST) as varchar (8)) FROM WW01_D where WW01_D.cat = 'USD'),
PM=(SELECT cast ((Sun_ET - Sun_ST)+( Mon_ET - Mon_ST) + (Tue_ET - Tue_ST) + (Wed_ET - Wed_ST) + (Thu_ET - Thu_ST) + (Fri_ET - Fri_ST) + (Sat_ET - Sat_ST) as varchar (8)) FROM WW01_D where WW01_D.cat = 'PM')
where WW = 'WW01'

Those highlited in Red is the variable that need to increase while repeatign the task
WW01_N = WW02_N-->WW52_N
WW01 = WW01-->WW52

You might need to use dynamic sql if you want to change table names dynamically

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-07-02 : 06:24:15

anyone have any idea to write the SQL dynamic? i have no idea. really need help from you guys..thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources