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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to create an SQL function?

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 ----------- Field
Primary Key ------------Char(8) --------- Passport No.
1.Single --------------- SmallInt -------- Marital Status
2.Married -------------------------------------------------
3.Divorced -------------------------------------------------
4.Widow(er) -------------------------------------------------

Table 2: Changes
Comments ------------- Type --------------- Field
Primary 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 Status


Is there a way to write an SQL function that receives:
----- Passport No.
----- Date of Reference
and 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 table
as
return
select 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] desc

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder
Go to Top of Page

efas400
Starting Member

2 Posts

Posted - 2010-09-02 : 12:11:39
Thanks so so much..............
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -