January 20, 2008 at 4:35 am
1)
I HAVE WRITTEN THIS STORED PROC AND ADDED TWO PARAMETERS. THE REQUEST COMES WITH A DATE RANGE
I RUN IT LIKE THIS
EXEC EDI..prcCreditCardCampaign '2008-01-14 00:00:00:000','2008-01-18 23:59:59:999'
AND IT WORKS
BUT IF I AM NOT SUPPLIED WITH A DATE RANGE
I WANT TO RUN IT LIKE THIS
EXEC EDI..prcCreditCardCampaign NULL,NULL
PROBLEM IS I DONT GET ANT DATA BACK AND I THINK ITS MY IF STATEMENT. WHAT IS THE CORRECT SYNTAX ?
2)
IS THERE A BETTER WAY OF WRITING THE LEFT OUTER JOIN THAN WHAT I HAVE ?
/*
EXEC EDI..prcCreditCardCampaign '2008-01-14 00:00:00:000','2008-01-18 23:59:59:999'
EXEC EDI..prcCreditCardCampaign NULL,NULL
*/
IF OBJECT_ID('prcCreditCardCampaign') IS NOT NULL
DROP PROC prcCreditCardCampaign
GO
CREATE PROC prcCreditCardCampaign
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
AS
SET NOCOUNT ON
IF @FromDate IS NULL AND @ToDate IS NULL ???????????
BEGIN
--Create Temp Table
IF OBJECT_ID('tempdb..#CC1') IS NOT NULL
DROP TABLE #CC1
CREATE TABLE #CC1
(
Campaign VARCHAR(60)
,IDNumber VARCHAR(13)
,OriginalLoanOfficer VARCHAR(60)
,CreationDate DATETIME
,OrganogramDescription VARCHAR(100)
,CreatedBy VARCHAR(30)
,ClientNumber INT
,BranchCode CHAR(5)
,LoanID INT
,AccountNumber VARCHAR(30)
,CardNumber BIGINT
,LastUpdatedBy VARCHAR(30)
,FinalLoanOfficer VARCHAR(100)
,FinalBranch VARCHAR(100)
,straightlimit DECIMAL(13,2)
,Status CHAR(3)
,CardStatus VARCHAR(30)
,NewRepeatCat VARCHAR(20)
,ContactNumber VARCHAR(23)
,Name VARCHAR(150)
,Capital DECIMAL(13,2)
,Startdate DATETIME
)
--Insert Data
INSERT #CC1 (
Campaign
,IDNumber
,CreationDate
,CreatedBy
,ClientNumber
,LoanID
,AccountNumber
,CardNumber
,LastUpdatedBy
,straightlimit
,Status
,CardStatus
,NewRepeatCat
)
SELECT
b.Campaign
,b.IDNumber
,CASE
WHEN ISDATE(b.CreationDate) = 1
THEN CAST(CONVERT(VARCHAR(10),b.CreationDate,120) AS DATETIME)
ELSE NULL
END AS CreationDate
,b.CreatedBy
,b.ClientNumber
,a.LoanID
,a.AccountNumber
,a.CardNumber
,b.LastUpdatedBy
,a.straightlimit
,b.Status
,a.CardStatus
,NewRepeatCat
FROM EDI..SL_LoanQCardDetails a INNER JOIN EDI..LoanQuotation b ON a.LoanID = b.loanid
LEFT OUTER JOIN
(SELECT
LoanID
,MAX(NewRepeat) AS NewRepeat
,CASE
WHEN MAX(NewRepeat) = 1 THEN 'New'
ELSE 'Repeat'
END AS NewRepeatCat
FROM EDI.dbo.tbSDSales
GROUP BY LoanID) c ON a.LoanID = c.LoanID
WHERE b.Campaign LIKE '%HOME%'
AND
CASE
WHEN ISDATE(b.CreationDate) = 1
THEN CAST(CONVERT(VARCHAR(10),b.CreationDate,120) AS DATETIME)
ELSE NULL
END BETWEEN @FromDate AND @ToDate
END
January 20, 2008 at 7:12 am
Replace this part of the code...
/*
EXEC EDI..prcCreditCardCampaign '2008-01-14 00:00:00:000','2008-01-18 23:59:59:999'
EXEC EDI..prcCreditCardCampaign NULL,NULL
*/
IF OBJECT_ID('prcCreditCardCampaign') IS NOT NULL
DROP PROC prcCreditCardCampaign
GO
CREATE PROC prcCreditCardCampaign
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
AS
SET NOCOUNT ON
SELECT @FromDate = ISNULL(@FromDate,'17530101'),
@ToDate = ISNULL(@ToDate,'99991231')
That will give you the ability to leave out either or both parameters because 10/01/1753 is the first day of "SQL time" and 12/31/9999 is the last day of "SQL time".
I'll skip the lecture about using BETWEEN on dates.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2008 at 7:58 am
THANKS...IS USING BETWEEN ON DATES NOT A GOOD THING ?
January 21, 2008 at 6:35 am
Like everything else, it depends...
If you just happen to forget that a column may have times on it, BETWEEN is not a good thing because you may be missing a good portion of the EndDate. That is, of course, unless you are using the 23:59:59.997 time. If you make the mistake of using 23:59:59.999, then you are actually including whole dates (dates that have a midnight time) because it will be rounded up to the next whole day.
Rumor also has it that 2k8 will allow times to the microsecond, so all these dates that have the time of 23:59:59.997 are gonna miss data for the end date.
It's better to have something like the following because it won't miss anything nor include too much by mistake.
WHERE somedatecol >= @StartDate
AND somedatecol < @EndDate+1
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2008 at 7:03 am
Just so we are on the same page.. The < @EndDate + 1 is just to make sure that you select everything within the selected date range and the less than makes sure you get the 23:59:59:999 rows as well
so does this mean I have to change the way I run the proc
from
EXEC EDI..prcCreditCardCampaign '2008-01-14 00:00:00:000','2008-01-18 23:59:59:999'
to
EXEC EDI..prcCreditCardCampaign '2008-01-14 00:00:00:000','2008-01-18 00:00:00:000'
And I had to add this bit at the bottom
FROM EDI..SL_LoanQCardDetails a INNER JOIN
EDI..LoanQuotation b ON a.LoanID = b.loanid
LEFT OUTER JOIN
(SELECT LoanID
,MAX(NewRepeat) AS NewRepeat
,CASE
WHEN MAX(NewRepeat) = 1 THEN 'New'
ELSE 'Repeat'
END AS NewRepeatCat FROM EDI..tbSDSales
GROUP BY LoanID) c
ON a.LoanID = c.LoanID
WHERE b.Campaign LIKE '%HOME%'
--AND b.CreationDate >= @FromDate AND b.CreationDate < @ToDate + 1
AND CASE
WHEN ISDATE(b.CreationDate) = 1
THEN CAST(CONVERT(VARCHAR(10),b.CreationDate,120) AS DATETIME)
ELSE NULL
END >= @FromDate AND
CASE
WHEN ISDATE(b.CreationDate) = 1
THEN CAST(CONVERT(VARCHAR(10),b.CreationDate,120) AS DATETIME)
ELSE NULL
END < @ToDate + 1
because I was getting the following error
Msg 8115, Level 16, State 2, Procedure prcCreditCardCampaign, Line 41
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply