Need help to write stored procedure with this scenario
-
Hello,
Let's say we have two tables A and B. Both tables have From Dates .i.e. A.FromDate and B.FromDate and both can be joined on MemberID. I need to pull a list of members from table with following condition on From Date
FromDate should be calculated:
•If A.FromDate is less than B.FromDate, use the B.FromDate
•If A.FromDate is equal to or greater than current group span B.FromDate , use the eligibility span A.FromDate
•If dates cannot be determined, member should be included on an exception list.
Please Help
-
Hello Anuj,
Please see if the following query serves your purpose.
IF OBJECT_ID('tempdb..#Output') IS NOT NULL DROP TABLE #Output
IF OBJECT_ID('tempdb..#Memeber1') IS NOT NULL DROP TABLE #Memeber1
CREATE TABLE #Memeber1
(
MemeberIDINT
, FromDateDATE
)
IF OBJECT_ID('tempdb..#Memeber2') IS NOT NULL DROP TABLE #Memeber2
CREATE TABLE #Memeber2
(
MemeberIDINT
, FromDateDATE
)
SET NOCOUNT ON
INSERT INTO #Memeber1 (MemeberID, FromDate)
VALUES (1, '2001-01-01'), (2, '2001-01-01')
INSERT INTO #Memeber2 (MemeberID, FromDate)
VALUES (1, '2001-01-01'), (2, NULL)
; WITH cte_member AS
(
SELECT A.MemeberID
,
CASE
WHEN (A.FromDate IS NULL OR B.FromDate IS NULL)
THEN NULL
ELSE
CASE
WHEN (A.FromDate < B.FromDate)
THEN B.FromDate
ELSE A.FromDate
END
END AS FromDate
FROM #Memeber1 A
JOIN #Memeber2 B
ON B.MemeberID= A.MemeberID
)
SELECT * INTO #Output FROM cte_member
-- Output 1 - List of memeber whose From Date can be determined.
SELECT * FROM #Output WHERE FromDate IS NOT NULL
-- Output 2 - List of memeber whose From Date can not be determined.
SELECT * FROM #Output WHERE FromDate IS NULL
-
pushkarna.anuj (10/5/2016)
Hello,Let's say we have two tables A and B. Both tables have From Dates .i.e. A.FromDate and B.FromDate and both can be joined on MemberID. I need to pull a list of members from table with following condition on From Date
FromDate should be calculated:
•If A.FromDate is less than B.FromDate, use the B.FromDate
•If A.FromDate is equal to or greater than current group span B.FromDate , use the eligibility span A.FromDate
•If dates cannot be determined, member should be included on an exception list.
Please Help
Two issues here.
1. The distinction between data filters and data formatting is unclear. The first two points appear to relate to formatting, the third appears to relate to filtering.
2. You introduce "current group span" and "eligibility span" with no explanation. Are these the table names, or what?
Your question cannot be answered with any degree of confidence without significant clarification, and preferably table scripts and sample date.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply