July 26, 2007 at 8:22 am
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
July 26, 2007 at 8:26 am
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]
July 26, 2007 at 8:44 am
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]
July 26, 2007 at 9:06 am
It does not return any record.
July 26, 2007 at 9:13 am
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]
July 26, 2007 at 9:44 am
you forgot to add where [D].id=[W].id
It returns only 1 row (06/01/07).
July 26, 2007 at 9:54 am
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.
July 26, 2007 at 9:58 am
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]
July 26, 2007 at 10:36 am
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
July 26, 2007 at 11:57 pm
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.
July 27, 2007 at 10:22 am
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
July 27, 2007 at 12:33 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply