January 29, 2008 at 3:33 pm
I have the following code:
DECLARE@ConsultantIDASnVarChar(50)
,@PeriodDateASDateTime
SET @ConsultantID = '0000112'
SET @PeriodDate = '01-29-2008'
-- Declare Local Variables
Declare @MonthStartDt As DateTime
DECLARE @MonthEndDt AS DateTime
DECLARE @QuarterStartDtASDateTime
DECLARE @QuarterEndDt AS DateTime
Declare @YearASDateTime
Declare @PeriodStartDt As DateTime
Declare @PeriodEndDt as DateTime
Declare @BumpupDate As Datetime
-- Breakdown @PeriodDate into Quarter and Months
SET @QuarterStartDt = DATEADD(quarter, DATEDIFF(quarter, 0, @periodDate), 0)
Set @QuarterEndDt = DATEADD(quarter, DATEDIFF(quarter, -1, @PeriodDate), -1)
SET @Year = DATEADD(yy, DATEDIFF(yy,0,@PeriodStartDt), 0)
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @PeriodDate), 0)
Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @PeriodDate), -1)
IF @MonthEndDt > GETDATE()
BEGIN
SET @MonthEndDt = GETDATE()
END;
-- Declare the Downline Recursive Query
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID)
AS
(
-- Anchor member defintion
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,CAST(A.SponsorID AS Nvarchar(MAX))
,'' As SponsorName
,0 as DownLineLevel
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
FROM dbo.uvwConsultantDownLine A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate
--AND A.StatusID NOT IN ('Inactive') -- 1 Record
UNION ALL
--Recursive member definition
SELECTA.ConsultantID
,A.EffectiveDate
,A.FirstName + ' ' + A.LastName as ConsultantName
,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID
,'' AS SponsorName
,DownLineLevel + 1
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
FROM dbo.uvwConsultantDownLine AS A with (nolock)
INNER JOIN DownLine AS B ON
A.SponsorID = B.ConsultantID
WHERE @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate
--AND A.StatusID NOT IN ('Inactive')
) -- Appropriate records
-- Create the Temp table #Downline that returns the CTE results
SELECT A.DownLineLevel
,A.ConsultantID
,A.EffectiveDate
,UPPER(RTRIM(A.ConsultantName)) AS ConsultantName
--,C.EmailAddress
,D.Title AS AchievedTitle
, CONVERT(NVARCHAR(MAX),A.SponsorID) AS SponsorID
,A.SponsorName
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
INTO #Downline
FROM DownLine AS A with (noLock)
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON A.CurrentLevelXID = D.XID
WHERE DownLineLevel <= 3
GROUP BY A.ConsultantID
,A.EffectiveDate
,A.ConsultantName
,A.SponsorID
,A.SponsorName
,DownLineLevel
--,C.BumpUpDate
--,C.EmailAddress
,D.Title
,A.ConsultantXID
,A.SponsorXID
,A.Active
,A.DeactivationDate
,A.BumpupDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
Select D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.EffectiveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,D.ConsultantXID
,D.SponsorXID
,D.SponsorID
,D.Active
,'StatusID' = CASE
WHEN StatusID ='Active' THEN ''
WHEN StatusID = 'Home Office' THEN ''
WHEN StatusID = 'New, Non Active Consultant' THEN ''
ELSE StatusID
END
,'ShowFlag' = CASE
WHEN @PeriodDate Between @MonthStartDt AND @MonthEndDt THEN 'X'
ELSE ' '
END
from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.EffectiveDate
,D.SponsorID
,D.SponsorXID
,D.BumpupDate
,D.ConsultantXID
,D.Active
,D.StatusID
-- Drop the temp table
DROP TABLE #Downline
The logic behind this is if the the @PeriodDate is in the Current Period (eg. @PeriodDate = 01/29/2008then it would be the currentperiod because it falls between 01/01/2008 and 01/30/2008) then I need to have the ShowFlag = 'X' otherwise if its earlier than the current Period (eg. @PeriodDate= 12/15/2007 which would be an older period) then I need to have ShowFlag set to ' '
Hope that makes sense.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 29, 2008 at 4:09 pm
Your @Period Date will always return in the current period, is this the way you want it, or are you testing the TSQL?
I response to your question, you didnt speak much about what the actual problem is. I would assume that it is an error, not sure though. Can you post what the exact problem is?
I would start by formatting the case statement.
CASE WHEN @PeriodDate BETWEEN @MonthStartDt AND @MonthEndDt THEN
'X'
ELSE
''
END AS [ShowFlag]
January 30, 2008 at 8:50 am
The @perioddate is a parameter being passed in. This version of the code is for testing but if I change the @perioddate to a date say from last month the Case statement still does not work because the @MonthEndDt and the MonthStartDt parameteres are actually derived from the @PeriodDate. It is not so much an error as it is not working right.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 30, 2008 at 9:10 am
If you want the case to work then you are going to have to derive what the current period is. You can do this by using the same logic for month start and month end but for GETDATE().
DECLARE
@PeriodDate AS DateTime
,@MonthStartDt As DateTime
,@MonthEndDt AS DateTime
SET @PeriodDate = '1-29-2008'
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
SET @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, GETDATE()), -1)
SELECT
@MonthStartDt as [CUR PER START],
@MonthEndDt AS [CUR PER END],
@PeriodDate AS [PERIOD DATE],
CASE
WHEN @PeriodDate BETWEEN @MonthStartDt AND @MonthEndDt THEN
'X'
ELSE ''
END AS [ShowFlag]
January 30, 2008 at 11:50 am
Thank you very much that worked out great!!!:w00t:
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
January 30, 2008 at 11:51 am
Cool. Thanks for the feedback. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply