Solved

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

Posted on 2015-01-08
2
50 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
2 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 shrink a transaction log file down to a reasonable size.

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now