March 24, 2008 at 10:18 am
I need to use a filter on the following code but I cannot use Dynamic SQL:
DECLARE @BeginIssueDate Datetime
DECLARE @EndIssueDate DateTime
DECLARE @VoidReasonFilter VARCHAR(50)
SET @BeginIssueDate = '01/01/2008'
SET @EndIssueDate ='04/01/2008'
IF @EndIssueDate > GETDATE()
BEGIN
SET @EndIssueDate = GETDATE()
END;
IF @VoidReasonFilter = 'All Reason Codes'
BEGIN
SET @VoidREasonFilter = NULL
END
Select c.ConsultantID
,c.UserXID
,u.LastName + ', '+u.FirstName as ConsultantName
,gct.Type AS CERTTYPE
,gcg.name AS CERTGROUP
,c.XID
,gci.Amount as IssuedAmount
--,'No' as CheckCut
--,'No' As CommissionCheck
,gci.createdBY as IssuingUser
,gci.certificateID as CERTNUMBER
--,u.LastName + ', '+u.FirstName as ConsultantName
,gci.IssueDate AS ISSUEDATE
,gcv.Reason AS VOIDReason
,gcv.XID
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 gci.GCGroupXID = 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 gcv.XID = 4
If they pass in 'All Reason Codes' I need to return all Reasons.
IF @VoidReasonFilter = 'All Reason Codes'
BEGIN
SET @VoidREasonFilter = NULL
END
If they pass in 'Consultant Deactivated' I need to pass in a 1 at the end of the Where clause:
Where
gci.IssueDate >=@BeginIssueDate
AND gci.IssueDate <= @EndIssueDate
AND gcv.XID = 1
To return only those record type with gcv.XID = 1
and so forth and so on.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 24, 2008 at 10:40 am
I can go after it this way:
DECLARE @BeginIssueDate Datetime
DECLARE @EndIssueDate DateTime
DECLARE @VoidReasonFilter VARCHAR(50)
SET @BeginIssueDate = '01/01/2008'
SET @EndIssueDate ='04/01/2008'
SET @VoidReasonFilter = 'All Reason Codes'
IF @EndIssueDate > GETDATE()
BEGIN
SET @EndIssueDate = GETDATE()
END;
IF @VoidReasonFilter = 'All Reason Codes'
BEGIN
SET @VoidREasonFilter = NULL
END
IF @VoidReasonFilter = 'Issued for incorrect group/type'
BEGIN
SET @VoidREasonFilter = 3
END
IF @VoidReasonFilter = 'Issued for incorrect amount'
BEGIN
SET @VoidREasonFilter = 4
END
IF @VoidReasonFilter = 'Issued for incorrect issue date'
BEGIN
SET @VoidREasonFilter = 5
END
IF @VoidReasonFilter = 'Issued for incorrect 1099 status'
BEGIN
SET @VoidREasonFilter = 6
END
IF @VoidReasonFilter = 'Issued for incorrect expiration date'
BEGIN
SET @VoidREasonFilter = 7
END
IF @VoidReasonFilter = 'Issued for incorrect order number'
BEGIN
SET @VoidREasonFilter = 8
END
IF @VoidReasonFilter = 'Issued for incorrect certificate number'
BEGIN
SET @VoidREasonFilter = 9
END
Select c.ConsultantID
,c.UserXID
,u.LastName + ', '+u.FirstName as ConsultantName
,gct.Type AS CERTTYPE
,gcg.name AS CERTGROUP
,c.XID
,gci.Amount as IssuedAmount
--,'No' as CheckCut
--,'No' As CommissionCheck
,gci.createdBY as IssuingUser
,gci.certificateID as CERTNUMBER
--,u.LastName + ', '+u.FirstName as ConsultantName
,gci.IssueDate AS ISSUEDATE
,gcv.Reason AS VOIDReason
,gcv.XID
,gci.ModifiedBy AS VOIDUSER
,gci.ModifiedDt AS VOIDDate
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 gci.GCGroupXID = 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 gcv.XID = @VoidREasonFilter
Which works for specific XID types but I also need one that returns all XID types.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 24, 2008 at 11:09 am
How about something like this:
Select c.ConsultantID
,c.UserXID
,u.LastName + ', '+u.FirstName as ConsultantName
,gct.Type AS CERTTYPE
,gcg.name AS CERTGROUP
,c.XID
,gci.Amount as IssuedAmount
--,'No' as CheckCut
--,'No' As CommissionCheck
,gci.createdBY as IssuingUser
,gci.certificateID as CERTNUMBER
--,u.LastName + ', '+u.FirstName as ConsultantName
,gci.IssueDate AS ISSUEDATE
,gcv.Reason AS VOIDReason
,gcv.XID
,gci.ModifiedBy AS VOIDUSER
,gci.ModifiedDt AS VOIDDate
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 gci.GCGroupXID = 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 gcv.XID = @VoidREasonFilter
and 1 = case when @VoidREasonFilter is null then 1 else
case when gcv.XID = @VoidREasonFilter then 1 else 0 end
END
Just be aware that there may be performance issues with this on large sets of data, and that there are always more than one way to skin a cat.
BTW, did you ever get your other query fixed?
March 24, 2008 at 11:14 am
Also just a quick note...I noticed that you are applying a filter to a table that you are left joining to. That will effectively turn your left join into an inner join, so you need to think about whether or not you really need the left join, and if you do, then you need to think about what to do with those records that come back with a NULL, and how you want to handle them with the filter.
March 24, 2008 at 1:39 pm
That worked great! I suppressed the NULLS by the following:
Where
gci.IssueDate >=@BeginIssueDate
AND gci.IssueDate <= @EndIssueDate
--AND gcv.XID = @VoidREasonFilter
and 1 = case when @VoidREasonFilter is null then 1 else
case when gcv.XID = @VoidREasonFilter then 1 else 0 end
END
AND gcv.XID IS nOT NULL
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 24, 2008 at 3:23 pm
Oops not quite right. Do I need to set up a case statement for each of the filter options?
Where
gci.IssueDate >=@BeginIssueDate
AND gci.IssueDate <= @EndIssueDate
--AND gcv.XID = @VoidREasonFilter
and 1 = case when @VoidREasonFilter is null then 1 else
case when gcv.XID = @VoidREasonFilter then 1 else 0 end
END
Because this works fine if @VoidReasonFilter is Null it returns all the expected rows
And if I change the @VoidReasonFilter to a 1 throught a set command I get back the expected Void Reason Codes. But If I use a 2 or 3 I get a blank row even though I know there is an associated record.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 24, 2008 at 5:57 pm
Kinda hard to say since I can't see your data. you say you know that you should get results for a 2 or three, but there are other things that can limit your data like an inner join or a different where clause.
Just uncomment each portion of your where clause to pin the problem down.
March 25, 2008 at 7:54 am
I found out what was going on, it was on my sise so your code is working. But is there a way to use the same code to manipulate more than one filter? The user is also going to be able filter on a Group and Type so I need to take that into account.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 25, 2008 at 6:57 pm
Of course there is, but you'll have to either continue down the path that I've taken here, and some more case statements, or you could rewrite it with regular boolean logic. Ya know...ANDs and ORs.
March 25, 2008 at 7:13 pm
Where
gci.IssueDate >=@BeginIssueDate
AND gci.IssueDate <= @EndIssueDate
--AND gct.XID = @CertType
and 1 =
case
when @CertType is null then 1
else
case
when gct.XID = @CertType then 1
else 0
end
and 1 =
Case
When @CertGroup is null then 1
Else
CASE
When @gcg.XID = @CertGroup Then 1
ELSE 0
END
END
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply