April 1, 2008 at 2:56 pm
I have the following code that is suppose to supply 3 possible filters; CertType, CertGroup and CertStatus. The CertStatus is a derived table from with in the script while CertType and CertGroup come in from an external table. Each of the filters also have a 'All' option and this is what is causing me grief. If I try 'All' in all three parameters I get nothing when I am expecting everything back. The only stipulation is that it cannot be done in Dynamic SQL: the Code:
DECLARE @BeginIssueDate Datetime
DECLARE @EndIssueDate DateTime
DECLARE @CertType VARCHAR(50)
DECLARE @CertGRoup VARCHAR(50)
DECLARE @CertStatus VARCHAR(50)
DECLARE @StatusID int
--Exec uspS_CertificateListing '01/01/2008','04/01/2008'
SET @BeginIssueDate = '01/01/2008'
SET @EndIssueDate ='04/01/2008'
SET @CertType = 'Retail'
SET @CertGroup = 'Basket'
Set @CertStatus ='All'
Create TABLE #CertStatus (
StatusID INT,
StatusDesc CHAR(20)
)
INSERT INTO #CertStatus VALUES(1, 'Voided')
INSERT INTO #CertStatus VALUES(2, 'Redeemed')
INSERT INTO #CertStatus VALUES(3, 'Expired')
INSERT INTO #CertStatus VALUES(4, 'Active')
INSERT INTO #CertStatus VALUES(5, 'All')
Create Table #Status (
StatusID INT,
StatusDesc CHAR(20)
)
INSERT INTO #Status
SELECT DISTINCT StatusID, StatusDesc
FROM #CertStatus
WHERE StatusDesc = @CertStatus
Set @CertStatus = (SELECT StatusID FROM #Status)
IF @CertStatus = 5
BEGIN
SET @CertStatus = NULL
END
CREATE Table #CertType (
Type VARCHAR(50),
XID INT
)
INSERT iNTO #CertType
SELECT DISTINCT Type, XID FROM GCType
WHERE Type = @CertType
SET @CertType = (Select XID FROM #CertType)
CREATE Table #CertGroup (
[Name] VARCHAR(50),
XID INT
)
INSERT iNTO #CertGroup
SELECT DISTINCT [Name] , XID FROM GCGroup
WHERE [NAme] = @CertGroup
SET @CertGroup = (Select XID FROM #CertGroup)
Select
gct.Type AS CERTTYPE
,gct.XID AS TypeXID
,gcg.name AS CERTGROUP
,gcg.XID AS GROUPXID
,gci.CertificateID
,c.ConsultantID
,u.LastName + ', '+u.FirstName as ConsultantName
,'1099Eligibility'=
CASE
WHEN gci.taxable = 0 THEN 'No'
ELSE 'Yes'
END
,'GCStatus' =
CASE
WHEN gcv.XID IS NOT NULL THEN 'Voided'
WHEN gci.RedeemedDate IS NOT NULL THEN 'Redeemed'
WHEN gci.ExpirationDate < GETDATE() THEN 'Expired'
ELSE 'Active'
END
,'GCStatusID' =
CASE
WHEN gcv.XID IS NOT NULL THEN 1
WHEN gci.RedeemedDate IS NOT NULL THEN 2
WHEN gci.ExpirationDate < GETDATE() THEN 3
ELSE 4
END
,gci.OrderNUmber
,c.XID AS ConsultantXID
,gci.Amount as IssuedAmount
,gci.IssueDate AS ISSUEDATE
,gci.CreatedBy AS IssuingUser
,gci.Comments
INTO #Temp2 FRom Consultant c
Inner Join GCInstance gci ON c.XID=gci.consultantxid
Left Outer Join GCGRoup gcg oN gci.GCGroupXID = gcg.XID
Left Outer Join GCType gct oN gcg.GCTypeXID = gct.XID
LEFT oUTER JOIN UserObject u ON c.UserXID = u.XID
Left Outer Join GCVoidReasonCodes gcv ON gci.GCVoidReasonXID = gcv.XID
Where
gci.IssueDate >=@BeginIssueDate
AND gci.IssueDate <= @EndIssueDate
and 1 =
case
when @CertType is null then 1
else
case
when gct.XID = @CertType then 1
else 0
End
END
Select * into #temp3 from #Temp2
WHEre GroupXID = @Certgroup
and 1 =
case
when @CertGroup is null then 1
else
case
when GroupXID = @CertGroup then 1
else 0
End
END
Select * from #Temp3
WHEre GCStatusID = @CertStatus
or 1 =
case
when @CertStatus is null then 1
else
case
when GCStatusID = @CertStatus then 1
else 0
End
END
Drop Table #Temp3
Drop table #CertType
Drop table #CertGroup
Drop table #CertStatus
DROP TABLe #Temp2
DROp Table #Status
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 1, 2008 at 3:36 pm
Since you've already hard-coded all of this part:
Create TABLE #CertStatus (
StatusID INT,
StatusDesc CHAR(20)
)
INSERT INTO #CertStatus VALUES(1, 'Voided')
INSERT INTO #CertStatus VALUES(2, 'Redeemed')
INSERT INTO #CertStatus VALUES(3, 'Expired')
INSERT INTO #CertStatus VALUES(4, 'Active')
INSERT INTO #CertStatus VALUES(5, 'All')
Create Table #Status (
StatusID INT,
StatusDesc CHAR(20)
)
INSERT INTO #Status
SELECT DISTINCT StatusID, StatusDesc
FROM #CertStatus
WHERE StatusDesc = @CertStatus
Set @CertStatus = (SELECT StatusID FROM #Status)
IF @CertStatus = 5
BEGIN
SET @CertStatus = NULL
END
Why not simplify it?
select @CertStatus =
case @CertStatus
when 'Voided' then 1
when 'Redeemed' then 2
when 'Expired' then 3
when 'Active' then 4
else null
end
That's the first thing I'd do. Next, on to the Where clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 3:42 pm
Replace:
and 1 =
case
when @CertType is null then 1
else
case
when gct.XID = @CertType then 1
else 0
End
END
Select * into #temp3 from #Temp2
WHEre GroupXID = @Certgroup
and 1 =
case
when @CertGroup is null then 1
else
case
when GroupXID = @CertGroup then 1
else 0
End
END
Select * from #Temp3
WHEre GCStatusID = @CertStatus
or 1 =
case
when @CertStatus is null then 1
else
case
when GCStatusID = @CertStatus then 1
else 0
End
END
With:
and (@CertType is null or gct.XID = @CertType)
and (@CertGroup is null or GroupXID = @CertGroup)
and (@CertStatus is null or GCStatusID = @CertStatus)
select *
from #Temp2
It's not going to take good advantage of indexes, but it should get you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 3:44 pm
And for a little more housecleaning, I'd replace:
CREATE Table #CertType (
Type VARCHAR(50),
XID INT
)
INSERT iNTO #CertType
SELECT DISTINCT Type, XID FROM GCType
WHERE Type = @CertType
SET @CertType = (Select XID FROM #CertType)
CREATE Table #CertGroup (
[Name] VARCHAR(50),
XID INT
)
INSERT iNTO #CertGroup
SELECT DISTINCT [Name] , XID FROM GCGroup
WHERE [NAme] = @CertGroup
SET @CertGroup = (Select XID FROM #CertGroup)
With:
select @CertType = XID FROM GCType
WHERE Type = @CertType
select @CertGroup = XID FROM GCGroup
WHERE [Name] = @CertGroup
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2008 at 8:30 pm
This work greats. The final code:
DECLARE @BeginIssueDate Datetime
DECLARE @EndIssueDate DateTime
DECLARE @CertType VARCHAR(50)
DECLARE @CertGRoup VARCHAR(50)
DECLARE @CertStatus VARCHAR(50)
DECLARE @StatusID int
--Exec uspS_CertificateListing '01/01/2008','04/01/2008'
SET @BeginIssueDate = '01/01/2008'
SET @EndIssueDate ='04/01/2008'
SET @CertType = 'Retail'
SET @CertGroup = 'basket'
Set @CertStatus ='Active'
IF @CertType = 'All'
BEGIN
Set @CertType = NULL
END
IF @CertGroup = 'All'
BEGIN
Set @CertGroup = NULL
END
select @CertStatus =
case @CertStatus
when 'Voided' then 1
when 'Redeemed' then 2
when 'Expired' then 3
when 'Active' then 4
else null
end
select @CertType = XID FROM GCType
WHERE Type = @CertType
select @CertGroup = XID FROM GCGroup
WHERE [Name] = @CertGroup
Select
gct.Type AS CERTTYPE
,gct.XID AS TypeXID
,gcg.name AS CERTGROUP
,gcg.XID AS GROUPXID
,gci.CertificateID
,c.ConsultantID
,u.LastName + ', '+u.FirstName as ConsultantName
,'1099Eligibility'=
CASE
WHEN gci.taxable = 0 THEN 'No'
ELSE 'Yes'
END
,'GCStatus' =
CASE
WHEN gcv.XID IS NOT NULL THEN 'Voided'
WHEN gci.RedeemedDate IS NOT NULL THEN 'Redeemed'
WHEN gci.ExpirationDate < GETDATE() THEN 'Expired'
ELSE 'Active'
END
,'GCStatusID' =
CASE
WHEN gcv.XID IS NOT NULL THEN 1
WHEN gci.RedeemedDate IS NOT NULL THEN 2
WHEN gci.ExpirationDate < GETDATE() THEN 3
ELSE 4
END
,gci.OrderNUmber
,c.XID AS ConsultantXID
,gci.Amount as IssuedAmount
,gci.IssueDate AS ISSUEDATE
,gci.CreatedBy AS IssuingUser
,gci.Comments
INTO #Temp2 FRom Consultant c
Inner Join GCInstance gci ON c.XID=gci.consultantxid
Left Outer Join GCGRoup gcg oN gci.GCGroupXID = gcg.XID
Left Outer Join GCType gct oN gcg.GCTypeXID = gct.XID
LEFT oUTER JOIN UserObject u ON c.UserXID = u.XID
Left Outer Join GCVoidReasonCodes gcv ON gci.GCVoidReasonXID = gcv.XID
Where
gci.IssueDate >=@BeginIssueDate
AND gci.IssueDate <= @EndIssueDate
and (@CertType is null or gct.XID = @CertType)
and (@CertGroup is null or gcg.XID = @CertGroup)
Select * from #Temp2
WHere (@CertStatus is null or GCStatusID = @CertStatus)
DROP TABLe #Temp2
Talk about over thinking things...
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply