need a query for complicated logic

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 2005 Forums

Transact-SQL (2005)

need a query for complicated logic

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2009-01-03 : 23:37:05

Hi,

I have a table tbl1,structure like this with some values. I have one more table tbl2. i want to update the values into this table from tbl1.i need the data result like this.

table tbl1 containing data

ID CT type G H

1 AB son 10 20

2 AB son 10 30

3 AB son 30 30

4 AB son 40 10

5 BC kod 60 60

6 BC kod 60 60

7 BC kod 100 30

8 CD can 50 80

9 CD can 20 90

10 DE pan 70 60

I have 2 differenet percentages in G and H, i need to update into tbl2 based on the G and H combinations grouping by CT and Type. In the above table for AB or BC or any other CT the combination of the type is not only son or kod, they can have different types. for example for AB it can have kod or can also with different percentages.The result set should be like this.
So i Need a query to update the tbl2, the below one is the tbl2 data

In the columns G0, G10, G20,G30... count should be updated, if the combination is not there then it has to update 0
For Example, in tbl1 we have a comnbination of AT and type is son and Percentage for col G is 10 and col H is 20.
In the below table at the col g10 and in the column H row contains value 10 for the combination of AB and son,
'1' shoulbe updated, because with that combination only 1 value is there in the tbl1. see in tbl1 id 6 and 7 contains same percentage for both G and H with the same combination. So in tbl2 count '2' shoulb be updated at the col g60 and at the row 60 with the cmobination of BC and Kod.

Can any one help me in this, its very urgent

CT type H G0 G10 G20 G30 G40 G50 G60 G70 G80 G90 G100

AB son 10

AB son 20

AB son 30

AB son 40

AB son 50

AB son 60

AB son 70

AB son 80

AB son 90

AB son 100

BC kod 10

BC kod 20

BC kod 30

BC kod 40

BC kod 50

BC kod 60

BC kod 70

BC kod 80

BC kod 90

BC kod 100

CD can 10

CD can 20

CD can 30

CD can 40

CD can 50

CD can 60

CD can 70

CD can 80

CD can 90

CD can 100

.

.

.

dev

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-01-04 : 23:01:26

your sample data and scenario is quite unclear, although I came up with the update query below based on what I think what you want to put on your table2(the G'zzz thing based on CT,Type and H of Table1).

HTH :)

select count(ID) ID_count,CT,Type,H,G
into #temp_table1_summary
from Table1
group by CT,Type,H,G

update Table2
set G0 = (case table1.G0 = 0 then #temp_table1_summ.ID_count else '0'),
G10 = (case table1.G10 = 10 then #temp_table1_summ.ID_count else '0'),
G20 = (case table1.G20 = 20 then #temp_table1_summ.ID_count else '0'),
G30 = (case table1.G30 = 30 then #temp_table1_summ.ID_count else '0'),
G40 = (case table1.G40 = 40 then #temp_table1_summ.ID_count else '0'),
G50 = (case table1.G50 = 50 then #temp_table1_summ.ID_count else '0'),
G60 = (case table1.G60 = 60 then #temp_table1_summ.ID_count else '0'),
G70 = (case table1.G70 = 70 then #temp_table1_summ.ID_count else '0'),
G80 = (case table1.G80 = 80 then #temp_table1_summ.ID_count else '0'),
G90 = (case table1.G90 = 90 then #temp_table1_summ.ID_count else '0'),
G100 = (case table1.G100 = 100 then #temp_table1_summ.ID_count else '0')
from #temp_table1_summ
where #temp_table1_summ.CT = Table2.CT
and #temp_table1_summ.Type = Table2.Type
and #temp_table1_summ.H = Table2.H

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-01-04 : 23:06:42

sorry...

#temp_table1_summary

is same as

#temp_table1_summ

i forgot to edit it before posting....also I haven't tested the query yet because im using a pc that doesnt have sql installed on it...sorry.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources