difficul case.... - SQL Server Forums

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

difficul case....

Author

Topic

hacke_02
Starting Member

8 Posts

Posted - 2009-05-25 : 12:02:12

table A

------------------------------------------
ID | Name | email | S_ID |
------------------------------------------
D-01 | XXXX | XXXXXXXXX | S-01 |
D-02 | XXXX | XXXXXXXXX | S-02 |
D-03 | XXXX | XXXXXXXXX | S-03 |
D-04 | XXXX | XXXXXXXXX | S-03 |
D-05 | XXXX | XXXXXXXXX | S-04 |
D-06 | XXXX | XXXXXXXXX | S-05 |
D-07 | XXXX | XXXXXXXXX | S-05 |
-------------------------------------------

table B

------------------------------
S-ID | status | salary |
------------------------------
D-01 | XXXX | 6000 |
D-02 | XXXX | 4000 |
D-03 | XXXX | 3500 |
D-04 | XXXX | 2500 |
D-05 | XXXX | 1500 |

table C

------------------------------
ID | date | overtimehour |
------------------------------
D-02 | 24-oct | 3 |
D-03 | 26-oct | 4 |
D-03 | 23-oct | 3 |
D-04 | 25-oct | 5 |
D-04 | 24-oct | 4 |
D-05 | 20-oct | 3 |
D-05 | 24-oct | 9 |
D-05 | 28-oct | 10 |
---------------------------

Question Is:
display A.name, A.email, B.status ,B.sallary

note:
ID which has overtime must be overpay
and 1 hour multiplied 3%

Example:
ID = D-04 have double overtime,
which has a 9 hour in table C
so his overtime wages is 9 * 3 * 2500 / 100
and his sallary in oct month ic2500 + 675

some one HELP ME PLEASE, It's So Important, it's the task of school

by jj

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 12:08:36

What have you tried so far?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hacke_02
Starting Member

8 Posts

Posted - 2009-05-25 : 12:15:20

i has try with join table
and another solve, but which ID= D-01 Always NULL
cant your show the code ????

by jj

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-25 : 12:30:06

quote:
Originally posted by hacke_02

some one HELP ME PLEASE, It's So Important, it's the task of school

So you want us to do your homework for you?

Show us what you're done, explain what you're struggling with and show that you've made some effort and someone will probably help you.

If we just give you the answer, then you've learnt nothing.

--
Gail Shaw
SQL Server MVP

hacke_02
Starting Member

8 Posts

Posted - 2009-05-25 : 12:40:01

sorry for a cross posting...

I have to try it,
books, e-book, website, to my despair.
therefore, I try to ask in this forum.
i know thats logic
but the view that I can always null in D-01.
because the salaries in D-01 multiplied by the null or something that does not exist.
how to avoid this problem

thank's

by jj

hacke_02
Starting Member

8 Posts

Posted - 2009-05-25 : 13:08:52

select A.ID,A.Email,B.status,B.sallary + sum(C.overtime)*0.03*B.sallry
from A
left outer join B on A.s-id = b.s-id
inner join C on C.ID = A.ID
group by A.ID,A.Email,B.status

by jj

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-25 : 15:27:42

Ok, and what's the problem you're having? What doesn't that query do that it should?
If it's nulls that are the problem, lookup ISNULL in the SQL books online.

Oh, and you're missing a column from your group by clause. Any columns in the select that are not aggregated must be in the group by clause.

--
Gail Shaw
SQL Server MVP

hacke_02
Starting Member

8 Posts

Posted - 2009-05-26 : 02:38:56

thank's, i'll try it first.
if i met anymore problems, i'll ask u guys again.
Thanks a lot for helping me out

by jj

hacke_02
Starting Member

8 Posts

Posted - 2009-05-28 : 03:16:47

I had try wiht the code, and it's work..
SELECT karyawan.nokar,karyawan.namakar,jabatan.namajab,
jabatan.gaji + sum(isnull(lembur.jml_jam,'0')* 0.03 * gaji )as 'total'
FROM karyawan
left outer join lembur on karyawan.nokar = lembur.nokar
INNER join jabatan on karyawan.nojab = jabatan.nojab
GROUP BY karyawan.nokar,karyawan.namakar,jabatan.namajab,jabatan.gaji

and i want to ask some thing..how to remove zore behind koma (exp; 100.00)

and have any syntax from what i done??to slove that case??? thank's a lote

by jj

prav3575
Starting Member

5 Posts

Posted - 2009-05-28 : 04:28:07

you can also try with one of inline query

select a.name,a.email,b.status,
(select isnull(sum(overtime)* 3* b.salary/100,0)+ b.salary total_sal from table_c
where id=a.id) tot
from table_a a inner join table_b b
on a.id=b.s_id

prav3575
Starting Member

5 Posts

Posted - 2009-05-28 : 04:43:01

this query is using joins

select a.name,a.email,b.status,isnull(sum(c.overtime)* 3* b.salary/100,0)+ b.salary
from table_a a left outer join table_c c on a.id=c.id
inner join table_b b on a.id=b.s_id
group by a.name,a.email,b.status,b.salary

hacke_02
Starting Member

8 Posts

Posted - 2009-05-28 : 06:50:06

hiuhh,, i think i must learn more..
but thank for all

by jj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources