Solved

Merge fields from two tables in a view based on a span of days

Posted on 2015-01-08
2
54 Views
Last Modified: 2015-01-08
I have two tables;  PayData  with 5 fields:  PRDate, Dept, Amount, Hours, EarnCode and PaySched with 5 fields; PRNum, FromDate, ThroughDate, RptDate, Year.  


I want to merge the field values within the PayData file with the correct PRNum when the PayData.PRDate falls within the PaySched.FromDate and PaySched.ThroughDate.


How can I define the view to get what I need?

Thanks

Glen
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40539123
--CREATE VIEW [view_name] AS
SELECT pd.*, ps.* --whatever columns you need from each table
FROM PayData pd
LEFT OUTER JOIN PaySched ps ON
    pd.PRDate >= ps.FromDate AND
    pd.PRDate <= ps.ThroughDate
0
 

Author Closing Comment

by:GPSPOW
ID: 40539178
Thank you
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question