SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design a table to hold filters for selection criteria


Design a table to hold filters for selection criteria

Author
Message
SQLCurious
SQLCurious
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 452
I have an ordering database with several tables that store data of orders belonging to a wide variety of clients. There is a generic report that I need to run which outputs the same data elements. However the criteria to select these orders will vary widely between each client. For e.g.,

i) for client# 1 it could be all orders that are still open after 30 days of placing an order
(
OrderStatus = 'Open'
AND
GetDate() - OrderCreationDate >= 30
)

ii) for client# 2 it could be all orders that have been completed 60 days or earlier
(
OrderStatus = 'Completed'
AND
GetDate() - OrderCompletedDate >= 60
)

iii) for client# 3 it could be a combination of different things (all orders in West Region that are in hold status for more than 10 days + all orders in Eastern Region that are in shipping and are expected to be delivered in the next 2 days + all completed orders for the rest of the regions).
(
OrderRegion = 'West'
AND
OrderStatus = 'Hold'
AND
GetDate() - OrderHoldDate >= 10
)
OR
(
OrderRegion = 'East'
AND
OrderStatus = 'Shipping'
AND
ExpectedDeliveryDate - GetDate() BETWEEN 0 AND 2
)
OR
(
OrderRegion NOT IN ('East', 'West')
AND
OrderStatus = 'Completed'
)

I want to have a stored procedure that selects all data and dynamically attach the where condition at the end for filtering. This way I wouldn't have to worry about any additions/changes that are made to the selection criteria. I can build an interface for admins who can use the UI to maintain the selection criteria and not worry about any code changes to accommodate it. I would like to design a table that holds this criteria. At this point in time, I am thinking of using key value pairs (Column Name, Column Value) but I am not sure how to implement multiple logical operators. I am pretty sure this is not a unique requirement, so hoping some of you would have already implemented such cases. Can you please throw in your ideas for an efficient and felixible implementation of this requirement?

This is more of a design questions, so I did not have any code or sample data set to share.

Thanks in advance.
SQLCurious
SQLCurious
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 452
No replies? Sad

I would appreciate any kind of help on this!
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12045 Visits: 37520
though this doesn't directly answer your method.....I think that the following

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

may provide some alternative ideas......

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7157 Visits: 2033
My advise here is to build a more general stored procedure that brings in all the columns you need at a granular (lowest common factor) level. I assume you mean to create a SSRS report. When client one gets selected in the report parameter control for the client... you could assign it default values for the start day and other parameters. Although I've never done this it seems that it would use the functionality of cascading parameters.

----------------------------------------------------
How to post forum questions to get the best help
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7157 Visits: 2033
You would need a helper dataset query that went something like :IF (@client=1)
begin
select @datefrom = dateadd(m, -6, getdate() )

You pass the results to the master dataset which would do something like
EXEC myProc @client @fromdate.This way you can use the procedure for ad-hoc purposes outside of the ssrs report. I would advise against dynamice SQL. Someone could add a command to drop a database at the end of a text field where you would type in the client. No need to have that risk.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search