sql query

Can someone tell me what is wrong with my query.
[%0] is a variable.

I am trying to getting where the users as to enter either between dates and customer code or serial number or reference number.

When i enter only reference number it works fine, but when i enter only between dates and customer code, it is pull all the records.  Can someone help me with the query please.
Select      Distinct T0.DocNum 'SR No',
            T3.DocNum 'AR Credit No',
            T0.DocDate 'SR Date',
            T3.DocDate 'Credit Date',
            T0.DocDueDate 'SR Due Date',
            T3.DocDueDate 'AR Due Date',
            T0.CardCode 'Cust Code',
            T0.CardName 'Cust Name',
            T0.NumAtCard 'Cust Ref No',
            T5.DistNumber 'IMEI No',
            T0.DocTotal 'SR Total Amount',
            T3.DocTotal 'Total Credited',
            T0.DocTotal - T3.DocTotal 'Diff' From ORDN T0
INNER JOIN RDN1 T1 ON T1.DocEntry            = T0.DocEntry
INNER JOIN SRI1 T4 ON T1.ItemCode            = T4.ItemCode
                              AND T1.LineNum            = T4.BaseLinNum
                              AND T1.DocEntry            = T4.BaseEntry
                              AND T0.DocNum            = T4.BaseNum
INNER JOIN OSRN T5 ON T4.ItemCode            = T5.ItemCode
                              AND T4.SysSerial      = T5.SysNumber
INNER JOIN RIN1 T2 ON T2.BaseEntry            = T1.DocEntry
                              AND T2.BaseType            = T1.ObjType
                              AND T2.BaseLine            = T1.LineNum
INNER JOIN ORIN T3 ON T3.DocEntry            = T2.DocEntry
                              ( T0.DocDate >= [%0]
                              AND T0.DocDate <= [%1]
                              AND T0.CardCode LIKE '%[%2]%')
                                        OR (T5.DistNumber = [%3])
                              OR (T0.NumAtCard LIKE '%[%4]%')
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shiju SasidharanSenior IT DeveloperCommented:
                              ( T0.DocDate >= Cast([%0] as datetime)
                              AND T0.DocDate <=Cast( [%1] as DateTime)
                              AND T0.CardCode LIKE '%[%2]%')
                                        OR (T5.DistNumber = [%3])
                              OR (T0.NumAtCard LIKE '%[%4]%')
romeiovasuAuthor Commented:
No if i remove  OR (T5.DistNumber = [%3])
                              OR (T0.NumAtCard LIKE '%[%4]%')
these two line items i get the correct data, but once i enter OR, it is pulling every customer and every date records.  Something is going wrong with my OR.
Shiju SasidharanSenior IT DeveloperCommented:
What is your search requirement with DistNumber and NumAtCard if values are provided?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

romeiovasuAuthor Commented:
Yes. or they should be able to search either by only one serial number or by ref number.
first 3 or 1 part, 4 and 5 like OR.
PortletPaulEE Topic AdvisorCommented:
Please try:

WHERE (T0.DocDate >= [%0]
          AND T0.DocDate <= [%1]
          AND T0.CardCode LIKE '%[%2]%'
          AND [%2] IS NOT NULL)
      OR (T5.DistNumber = [%3]
          AND [%3] IS NOT NULL)
      OR (T0.NumAtCard LIKE '%[%4]%'
          AND [%4] IS NOT NULL)

Open in new window

If that does not work then also/instead check that %2, %3 & %4 are not empty strings e.g. AND %2 <> ''

NB: IF [DocDate] is just a date field then what you have will work as expected. If however [DocDate] has times of the day other than 00:00:00 then it would not be accurate.

also be careful with format of %0 & %1, the safest of all date literal formats to use in SQL Server is YYYYMMDD

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
romeiovasuAuthor Commented:
Only numatcard is working perfectly, if i enter only distnumber it shows all records instead of just one record. Same thing with between dates and customer code.
PortletPaulEE Topic AdvisorCommented:
What we know about your database and parameters can be seen inside the rectangle below
|                                                                           |
|                                                                           |
|                                                                           |
|                                                                           |

Please display EXACTLY what code you last tested, and provide information about the parameter values you are injecting into the code

I would further suggest you supply some sample data and the expected results for each of the 3 filters you are seeking.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.