October 29, 2013 at 12:46 am
Hi all,
1.I have input parameters like @age,@date, @time And @gender and output parameter was @paid amt.
2. I have a conditions like
a)between monday to friday(upto 9 pm) the doctor consultation fee was like
Above 10 years -- 200 rs
between 5-10 years -- 100 r.s
below 5 years-- 0 r.s
b) On saturdays the doctor consultation fee was like
between 0-10 years -- 300 r.s
above 10 years-- 500 r.s
c) On sundays the doctor consultation fee was like
between 0-10 years -- 300 r.s
above 10 years-- 700 r.s
d) on nights the consultation fee was like
between 0-10 years -- 200 r.s
above 10 years-- 500 r.s
so i want to write validation in a storedprocedure to met the above conditions.
please help me out from this task.
Thanks in Advance,
Avinash P
October 29, 2013 at 2:03 am
Multiple post - see http://qa.sqlservercentral.com/Forums/Topic1509192-391-1.aspx
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
October 29, 2013 at 7:35 am
Have you tried anything so far? My assumption we all want to see that one has put an effort to solve the problem, before posting the
questions here. Have you looked into CASE for example?
October 29, 2013 at 11:59 pm
I tried like below
USE [NXNV1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONSULTATION_DETAILS1]
@age SMALLINT,
@date DATE ,
@time TIME,
@gender CHAR(1),
@amount varchar(20)
AS
BEGIN
SELECT @amount=
(CASE
WHEN DATENAME(DW, @date) = 'Sunday' THEN
CASE
WHEN @Age <= 10 THEN 300
ELSE 700
END
WHEN DATENAME(DW, @date) = 'Saturday' THEN
CASE
WHEN @Age <= 10 THEN 300
ELSE 500
END
ELSE
CASE
WHEN @time < '06:00' OR @time > '21:00' THEN
CASE
WHEN @age <= 10 THEN 200
ELSE 500
END
ELSE
CASE
WHEN @age < 5 THEN 0
WHEN @age >= 5 AND @age <= 10 THEN 100
ELSE 200
END
END
END) FROM MASTEROPCONSAMT
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply