query again - 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

query again

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 05:58:52

Hi everyone. Hi KH. I have a complicated problem on query. I tried to solve it but i didn't get the answer.

DECLARE @date1 smalldatetime, @date2 smalldatetime
SET @date1 = '1/1/2007'
SET @date2 = '5/1/2007'

table1 - hrEmpPaySumm
empdcno otpay period1
1 100 1/1/2007
1 200 2/1/2007
1 300 2/16/2007
2 400 3/1/2007
3 500 4/1/2007
3 600 5/1/2007

table2 - hrEmpadjustments
empdcno trantype amount
1 A 1000
1 A 2000
2 B 3000
2 C 4000
2 C 5000

Expected result: if @date1 = '1/1/2007' and @date2 = '5/1/2007'
empno ot_adj amount
1 otpay 100
1 otpay 200
1 otpay 300
1 A 1000
1 A 2000
2 otpay 400
2 B 3000
2 C 4000
2 C 5000
3 otpay 500
3 otpay 600

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

pootle_flump

1064 Posts

Posted - 2007-06-29 : 06:23:00

Hi Ron

Obviously I haven't seen what you have tried. Did you try a union? Two queries each getting part of the result set you want and then union them together.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-29 : 06:28:06

DECLARE @date1 smalldatetime, @date2 smalldatetime
SET @date1 = '1/1/2007'
SET @date2 = '5/1/2007'

declare @t table(empdcno int, otpay int, period1 datetime)
insert @t
select 1, 100, '1/1/2007' union all
select 1, 200, '2/1/2007' union all
select 1, 300, '2/16/2007' union all
select 2, 400, '3/1/2007' union all
select 3, 500, '4/1/2007' union all
select 3, 600, '5/1/2007'

declare @y table(empdcno int, trantype char(1), amount int)
insert @y
select 1, 'A', 1000 union all
select 1, 'A', 2000 union all
select 2, 'B', 3000 union all
select 2, 'C', 4000 union all
select 2, 'C', 5000

Select empdcno, 'Otpay' as ot_name, otpay
from @t
where period1 between @date1 and @date2

union all

Select empdcno, trantype, amount
from @y

order by 1

--------------------------------------------------
S.Ahamed

pootle_flump

1064 Posts

Posted - 2007-06-29 : 07:43:06

Impossible to tell from the limited sample data but I suspect the second query in the uniom is meant to join to hrEmpPaySumm to filter for relevent records.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-29 : 21:39:07

thanks pbguy. i tried to use union in other way... your query is simple.
But i have problem on joining the result to other tables.
Where should i insert the join clause ?
try to look at this.

DECLARE @pDateRangeFrom smalldatetime, @pDateRangeTo smalldatetime
SET @pDateRangeFrom = '1/1/2007'
SET @pDateRangeTo = '5/1/2007'

Select empdcno, 'Otpay' as ot_name, otpay
from hrEmpPaySumm a
where (period1 between @pDateRangeFrom and @pDateRangeTo) AND
otpay > 0

union all
Select empdcno, trantype, amount
from hrEmpAdjustments
where trandate between @pDateRangeFrom and @pDateRangeTo

order by 1

inner join psdatacenter d on d.dcno = a.empdcno
inner join ...
inner join ...

what i wanted is the result should be joined to psdatacenter, etc... without using a union. should be a pure join.
showing d.fullname

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-30 : 01:02:10

ok i have solved it...

-Thanks

-Ron-

"A logician trying to explain logic to a programmer is like a cat trying to explain to a fish what it's like to get wet."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources