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.
Author |
Topic |
efas400
Starting Member
2 Posts |
Posted - 2010-09-01 : 19:04:31
|
Hi All, I have a question regarding creating an SQL function:The following schema describes the data of marital statuses for all citizens. The initial data is recorded in the Citizen's table while all the changes in marital status are recorded in the Changes table.Table 1: Orders Comments ------------- Type ----------- FieldPrimary Key ------------Char(8) --------- Passport No. 1.Single --------------- SmallInt -------- Marital Status 2.Married -------------------------------------------------3.Divorced -------------------------------------------------4.Widow(er) -------------------------------------------------Table 2: ChangesComments ------------- Type --------------- FieldPrimary Key ------------ Char(8) ------------ Passport No. Primary Key ------------ Date --------------- Date of Change Same as in Order table - SmallInt ------------ Previous Marital Status Same as in Order table - SmallInt ------------ New Marital StatusIs there a way to write an SQL function that receives:----- Passport No.----- Date of Referenceand returns the marital status as of the date of reference.Notes:1)Assume that the table data is complete and there are no discrepancies in data.2)If there is no change in marital status, there will be no record in the Changes Table. Please help, advise. Thanks,Rebecca |
|
Devart
Posting Yak Master
102 Posts |
Posted - 2010-09-02 : 02:15:31
|
Hello,For example:create function get_marital_status (@pass char(8),@date datetime)returns tableasreturnselect top 1 o.[passport no], o.[Marital Status], c.[Date of Change]from orders o left outer join changes c on o.[passport no] = c.[passport no]where o.[passport no]=@pass and ((c.[Date of Change]<=@date and c.[Date of Change] is not null) or c.[Date of Change] is null)order by c.[Date of Change] descBest regards,Devart,SQL Server Tools:dbForge Schema ComparedbForge Data ComparedbForge Query Builder |
|
|
efas400
Starting Member
2 Posts |
Posted - 2010-09-02 : 12:11:39
|
Thanks so so much.............. |
|
|
hema30
Starting Member
2 Posts |
Posted - 2012-04-08 : 12:42:34
|
Rebecca - Does the solution enlisted by Devart worked, or do you have some other solution |
|
|
|
|
|