How can I join 2 tables

Question

Post reply

How can I join 2 tables

zubamark

Right there with Babe

Points: 735
More actions
July 26, 2007 at 8:22 am

#179913

Hello,
I have SAL_WKLY table
ID SAL_EFF_DATE HRS_WKLY SAL_WKLY
-- ------------ ------- --------
1 01/01/06      35       700
1 06/01/06      30       500
1 01/01/07      25       300
1 06/01/07      35       800
and DAILY_ACTIVITY table
ID Date
-- --------
1 02/12/06
1 05/31/06
1 07/05/06
1 12/01/06
1 03/15/07
1 05/01/07
1 06/01/07
1 07/22/07
How can I join both to get salary and hours for the date in DAILY_ACTIVITY?
ID Date     HRS_WKLY SAL_WKLY
-- -------- -------- --------
1 02/12/06 35      700
1 05/31/06 35      700
1 07/05/06 30      500
1 12/01/06 30      500
1 03/15/07 25      300
1 05/01/07 25      300
1 06/01/07 35      800
1 07/22/07 35      800

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

TRy this:

SELECT [D].[ID],[D].[Date],HRS_WKLY,SAL_WKLY

FROM Sal_WKLY [W]

INNER JOIN DAily_Activity [D]

ON [D].[Date] = [W].[Sal_Eff_Date]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

SELECT [D].[ID],[D].[Date],HRS_WKLY,SAL_WKLY

FROM DAily_Activity [D]

LEFT JOIN Sal_WKLY [W]

ON [D].[Date] = [W].[Sal_Eff_Date]

If you this one then you will get all the Dates from your Activities table even if there is no Slry in Sal_WKLY table.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

zubamark Right there with Babe Points: 735 More actions · Answer 3

zubamark

Right there with Babe

Points: 735

July 26, 2007 at 9:06 am

#722602

It does not return any record.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Which Query?

Could you confirm the data in the two tables for me please?

I created the two above tables and added my own data.

and the queries seemed fine.

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

zubamark Right there with Babe Points: 735 More actions · Answer 5

you forgot to add where [D].id=[W].id

It returns only 1 row (06/01/07).

steve smith-401573 SSChampion Points: 10889 More actions · Answer 6

try this as an alternative (note, i haven't run it yet):

select da.ID, da.Date, sw.hrs_wkly, sw.sal_wkly

from daily_activity da

inner join sal_wkly sw on da.id = sw.id

inner join sal_wkly sw2 on da.id = sw2.id and sw.id = sw2.id

where da.date > sw.sal_eff_date and da.date < sw2.sal_eff_date

and sw.sal_eff_date < sw2.sal_eff_date

You cannot use equality because the dates do not necessarily match.

Note that the second condition on the second inner join is really superfluous (or just wrong?)

The point I am trying to make is that you need to bracket the daily_activity record between salary action dates, and pick up the latest VALID action date, but no action later than the labor date.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

I'm confused as to why you are expecting more records if only one of the dates match?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

zubamark Right there with Babe Points: 735 More actions · Answer 8

it returs 3 records for the same date

ID Date hrs_wkly sal_wkly

---------- ---------- ----------

1 03/15/2007 25 300.00

1 03/15/2007 30 500.00

1 03/15/2007 35 700.00

peggy pacheco SSC Veteran Points: 237 More actions · Answer 9

This produces the result requested:

select

da.ID,

da.Date,

sw.HRS_WKLY,

sw.SAL_WKLY

from DAILY_ACTIVITY da

join SAL_WKLY sw on da.ID = sw.ID

and da.Date between sw.SAL_EFF_DATE

and

isnull(((select dateadd(d,-1,min(SAL_EFF_DATE))

from SAL_WKLY

where ID = sw.ID

and SAL_EFF_DATE > sw.SAL_EFF_DATE)),da.Date)

The correlated subquery gets the end date for the salary to make it possible to return a single value for the various dates.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 10

Here's one with no subqueries and one rather straightforward derived table.

select  da.id, da.ActivityDate, der.Hours, der.Salary
from    DAILY_ACTIVITY da
join    (
    select  sw1.SAL_EFF_DATE as BeginDate, IsNull( sw2.SAL_EFF_DATE, GetDate() ) as EndDate, 
            sw1.HRS_WKLY as Hours, sw1.SAL_WKLY as Salary
    from    SAL_WKLY sw1
    left outer join    SAL_WKLY sw2
        on  DateDiff(mm, sw1.SAL_EFF_DATE, sw2.SAL_EFF_DATE) between 5 and 7
    )   der
        on  da.ActivityDate >= der.BeginDate and da.ActivityDate < der.EndDate

Highlight and execute just the select that makes up the derived table to see what you're joining to.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

zubamark Right there with Babe Points: 735 More actions · Answer 11

Thank you All, I solve the problem.

select da.ID, da.Date,

(select sw1.HRS_WKLY from SAL_WKLY sw1 where sw1.ID = da.ID and sw1.SAL_EFF_DATE = (select MAX(sw2.SAL_EFF_DATE) from SAL_WKLY sw2 where sw2.ID = da.ID and sw2.SAL_EFF_DATE <= da.Date)) as HRS_WKLY,

(select sw3.SAL_WKLY from SAL_WKLY sw3 where sw3.ID = da.ID and sw3.SAL_EFF_DATE = (select MAX(sw4.SAL_EFF_DATE) from SAL_WKLY sw4 where sw4.ID = da.ID and sw4.SAL_EFF_DATE <= da.Date)) as SAL_WKLY

from DAILY_ACTIVITY da