To find perfect expense?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » To find perfect expense?

To find perfect expense?

Rate Topic

Display Mode

Topic Options

Author

Message

raghuldrag

Posted Tuesday, March 05, 2013 9:18 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 3:53 AM
Points: 94, Visits: 235

Hai friends,

create table user
(
user_id varchar(100),
name varchar(100),
designation_id varchar(100),
grade_id varchar(100)
)

insert into user values('0012','abc',13,8)
insert into user values('0010','bc',5,3)
insert into user values('0011','bjc',2,3)
insert into user values('0013','bct',6,3)
insert into user values('0016','bci',59,35)
insert into user values('0019','bcp',9,11)
create table designation
(
desigantaion_id varchar(100),
name varchar(100),
grade_id varchar(100)
)

insert into designation values('13','progrmmer',8)
insert into designation values('5','GM','3')
insert into designation values('2','regional manager','3')
insert into designation values('6',' accounts manager','3')
insert into designation values('59','worker','35')
insert into designation values('9','trainee','11')

create table sal
(
effective_date date,
sala varchar(100),
designation_id varchar(100)
grade varchar(100)
)

insert into sal values('2010-01-01','5000','13','b2')
insert into sal values('2010-01-01','10000','5','a1')
insert into sal values('2010-01-01','10000','2','a1')
insert into sal values('2010-01-01','10000','6','a1')
insert into sal values('2010-01-01','2000','59','e')
insert into sal values('2010-01-01','3000','9','c')

insert into sal values('2011-01-01','5500','13','b2')
insert into sal values('2011-01-01','11000','5','a1')
insert into sal values('2011-01-01','11000','2','a1')
insert into sal values('2011-01-01','11000','6','a1')

insert into sal values('2012-03-01','2500','59','e')
insert into sal values('2012-02-01','3600','9','c')
insert into sal values('2012-01-01','15000','6','a1')

now i wanna make a join all the table,if i ' m pass the effective_date depends on the salry ll display all the employes....
such designations are missed on the '2012-01-01' in that... if i m pass the all effective _dates the outputs of sal shows '2010' and '2012','2011' also that each employess sal display three times
i waana display the occurate sal of all employees depends on effective_dates.

Post #1427155

Bhuvnesh

Posted Wednesday, March 06, 2013 1:01 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Please provide expected output too here with respect to the any effective date (from you sample data)

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1427219

marcel.eppel

Posted Wednesday, March 06, 2013 7:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:02 AM
Points: 25, Visits: 37

Your table definitions contain serious errors. Only way to join users (give the correct datatype and references are used) is by joining sals with users on designation_id.
This happens to be a cross join (Example for user '0012'):

user_id, effective_date, sala, user.designation_id, sal.designation_id, user.grade_id, sal.grade
'0012', '2012-01-01', '5000', '13', '13', '8', 'b2'
'0012', '2011-01-01', '5500', '13', '13', '8', 'b2'
'0012', '2012-01-01', '5000', '13', '13', '8', 'b2'

You also might duplicate values when using designation table to join user and sal.

Work on your table design!

Post #1427371

« Prev Topic | Next Topic »

Permissions