WHERE clause question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE clause question

Author

Topic

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-12 : 08:15:46

I have to change my query WHERE clause, I have to remove the @customer_number ,right now it looks like this:


WHERE     (detail_record.customer_bill_to = @customer_bill_to)   
          AND( detail_record.pickup_date BETWEEN @start_date 
          AND @end_date) OR (detail_record.pickup_date  
          BETWEEN @start_date AND @end_date) 
          AND (detail_record.customer_delv_to = @customer_number)

I have to allow for these parameters.
1). Only pickup_date and deliver_date are entered as parameters, customer_bill_to nor customer_deliver_to are entered return all records
2). customer_bill_to is selected with no parameter for customer deliver_to
3). customer_deliver_to is selected with no parameter for customer_bill_to
4). Both customer_bill_to and customer_deliver_to are selected
5). All of these cases include pickup_date and deliver_date

Thanks for the help
CoachBarker

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-12 : 08:57:58

Try

WHERE (@customer_bill_to is null or detail_record.customer_bill_to = @customer_bill_to)
AND( detail_record.pickup_date >=@start_date AND detail_record.pickup_date <DATEADD(day,1,@end_date))
AND (@customer_number is null or detail_record.customer_delv_to = @customer_number)

Madhivanan

Failing to plan is Planning to fail

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-12 : 11:38:40

Work just fine just had to change customer_number to (@customer_delv_to

WHERE (@customer_bill_to is null or detail_record.customer_bill_to = @customer_bill_to)
AND( detail_record.pickup_date >=@start_date AND detail_record.pickup_date <DATEADD(day,1,@end_date))
AND (@customer_delv_to is null or detail_record.customer_delv_to = (@customer_delv_to)

One question, what does this do
<DATEADD(day,1,@end_date)
Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 13:14:10

quote:
Originally posted by CoachBarker

Work just fine just had to change customer_number to (@customer_delv_to

WHERE (@customer_bill_to is null or detail_record.customer_bill_to = @customer_bill_to)
AND( detail_record.pickup_date >=@start_date AND detail_record.pickup_date <DATEADD(day,1,@end_date))
AND (@customer_delv_to is null or detail_record.customer_delv_to = (@customer_delv_to)

One question, what does this do
<DATEADD(day,1,@end_date)
Thanks for the help
CoachBarker

<DATEADD(day,1,@end_date) chjecks if value on left side occurs before start of next day of @end_date (till midnight of @end_date 23:59:59)

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-12 : 14:21:08

Thanks for the reply, I learn something new every day.

If only Customer Bill To is entered I want to return all the records where detail_record.customer_bill_to = @CustomerBillTo, if only Customer Deliver To is enetered I want to return all records where detail_recor.customer_delv_to = @CustomerDeliverTo.

Or if only date parameters are entered return, how can I set the query to return all records in a given Start Date and End Date. Basically SELECT * if there are no parameters entered for Customer Bill To and Customer Deliver To?

Thanks for the help
CoachBarker

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-03-12 : 14:52:07

SSRS converted the where clause to this:


WHERE     (@customer_bill_to IS NULL) AND (detail_record.pickup_date >= @start_date) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date >= @start_date) AND (@customer_delv_to IS NULL) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (@customer_bill_to IS NULL) AND (detail_record.pickup_date >= @start_date) AND (detail_record.customer_delv_to = @customer_delv_to) AND 
                      (detail_record.pickup_date < DATEADD(day, 1, @end_date)) OR
                      (detail_record.customer_bill_to = @customer_bill_to) AND (detail_record.pickup_date >= @start_date) AND 
                      (detail_record.customer_delv_to = @customer_delv_to) AND (detail_record.pickup_date < DATEADD(day, 1, @end_date))

Thanks for the help
CoachBarker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources