November 1, 2013 at 11:31 pm
I have written a stored procedure like
USE [nxnv1_temp]
GO
/****** Object: StoredProcedure [dbo].[SP_CONSULTATION_DETAILS1] Script Date: 11/02/2013 10:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CONSULTATION_DETAILS1]
@AGE numeric(3, 0),
@date DATE ,
@time TIME,
@SEX varchar(1),
@AMOUNT numeric(6, 2)
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 > '18: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
i want to set the values for amount field. is it need to join the two tables? please suggest me or help me to close this issue
i have a tables like below
registration table:-
PRNOnumeric(11, 0)(primary key)
REGFROMvarchar(10)
REGDATEdatetime
FIRSTNAMEvarchar(60)
RELATIONCODEvarchar(3)
GUARDIANNAMEvarchar(50)
DOBdatetime
AGEnumeric(3, 0)
AGETYPEvarchar(6)
SEXvarchar(1)
ORGCODEvarchar(10)
BGROUPvarchar(3)
RHTYPEvarchar(3)
HNOvarchar(50)
STREETvarchar(50)
LOCvarchar(50)
AREACODEnumeric(6, 0)
PHONERvarchar(15)
PHONEOvarchar(15)
PAGERvarchar(15)
MOBILEvarchar(15)
FAXvarchar(15)
EMAILvarchar(50)
IDENTIFICATIONMARKSvarchar(200)
REFDOCTCODEvarchar(10)
PAYTYPEvarchar(11)
USERIDvarchar(50)
SENTnchar(1)
REMARKSvarchar(50)
ReligionCodenumeric(2, 0)
MIDDLENAMEvarchar(20)
LASTNAMEvarchar(30)
TITLEvarchar(10)
and
opconsamt table:-
DOCCDvarchar(6)
CONSTYPEvarchar(3)
TARIFFCDvarchar(10)
VISITNOnumeric(2, 0)
AMOUNTnumeric(6, 2)
VALIDDAYSnumeric(3, 0)
MAXVISITSnumeric(2, 0)
COMPCODEvarchar(10)
FOLLOWUPAMTnumeric(6, 2)
November 4, 2013 at 8:03 am
Duplicate post. Direct replies to the original thread. http://www.sqlservercentral.com/Forums/Topic1510846-391-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply