sql query Help

I am trying to get a list of orders for a day and it works fine. Also I wan to include orders with payment plan also works (include with a payment plan weans when the client made an order today). The issue is when I apply a date range filter to a query it exclude the orders with  payment plans.

 SELECT O.orderDate

	from trans T  
	inner join orders O on O.orderKey = T.orderKey
	
	inner join lineitems LI on LI.orderKey = O.orderKey
	
	WHERE O.void = 'N'AND T.TransDate BETWEEN @startDt AND @endDt 
							and LI.productType = 'DC' 
                            and T.validated <> 'N'
                            and LI.officeStateKey = @stateID
                            AND not EXISTS (
                                    SELECT count(1) from orders_paymentPlan OPP
                                    where opp.orderKey = o.orderKey
                                  
                                    Having(COUNT(OPP.orderKey)) > 1
                                )

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
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.

Brendt HessSenior DBACommented:
Just to be certain that I understand what you are asking, here is how I am reading your question:

I am trying to get a list of orders for a day and it works fine. Also, I want to include orders with a payment plan, and this also works, including orders with a payment plan {? weans?} when the client made an order today.

The issue is [that] when I apply a date range filter to [this] query, it excludes the orders with  payment plans.

It also appears that the sample SQL code you supplied applies that date range filter that you are talking about.

If this is correct, we (the experts) would need a couple of other bits of information to successfully help you.

We need a query that works without a date filter.
We need a sample of the data that is included in the orders_paymentPlan table. An example of an order with one row, and an order with more than one row, with an explanation why orders with more than one row are excluded.
We need to know any relationship between the trans table and the orders_paymentPlan table.
0
erikTsomikSystem Architect, CF programmer Author Commented:
Well this a dynamic query. So when I say date range I mean you can select startdate and enddate to have  the same value (for example todays date)
0
PortletPaulfreelancerCommented:
erik

I IMPLORE you to illustrate your questions with:

sample data
expected result

----------------------------------------------------------------------
just imagine a friend who is a veterinarian asked you how to fix a patients liver

have you ever seen a snakes liver?
is the patient a snake?
----------------------------------------------------------------------

we know nothing about your tables or data, just like you know nothing about that patient
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

erikTsomikSystem Architect, CF programmer Author Commented:
For example, Table Orders has the following data

OrderKey    OrderDate
1                     1/1/2015
2                      1/1/2015
3                      2/1/2015      
4                      4/1/2015

Table Trans:
transID     OrderKey   TransDate              validated
1                       1               1/1/2015                   Y
2                       2               1/1/2015                   Y
3                       3               2/1/2015                   Y
4                       4               4/1/2015                   Y


Table: orders_paymentPlan

orderKey       paymentNum         paymentDate
1                                  1                             1/1/2015
4                                   1                              4/1/2015
4                                    2                              4/1/2015

So, if the date range for example from 1/1/2015 to 2/1/2015

I should see the orderKey :1,2,and 3

If date range get increased     1/1/2015 to 4/1/2015 I should also see orderKey 4 , but I will not because it has paymentNum of 2 already, and so on

Order 2 and 3 are not the payment plan orders so they will be seen according to the date range selected.
0
PortletPaulfreelancerCommented:
I'm not certain about this suggestion, but from the sample data this query returns orderkey 1,2 & 3 when the date range is 20150101& 20150201
or the date range is  20150101& 20150401

declare @from as date = '20150101'
declare @to   as date = '20150401'

select
*
from orders o
inner join trans t on o.OrderKey = t.OrderKey
left join orders_paymentPlan pp on o.OrderKey = pp.OrderKey
where ( t.TransDate >= @from and t.TransDate < dateadd(day,1,@to) ) 
and (
      pp.paymentNum IS NULL
  OR  NOT EXISTS (
                 SELECT null from orders_paymentPlan OPP
                 where opp.orderKey = o.orderKey
                 Having(COUNT(OPP.orderKey)) > 1
                 )
    )
;

Open in new window

see: http://sqlfiddle.com/#!3/9370b/1

details
        ([OrderKey] int, [OrderDate] datetime)
    ;
        
    INSERT INTO Orders
        ([OrderKey], [OrderDate])
    VALUES
        (1, '2015-01-01 00:00:00'),
        (2, '2015-01-01 00:00:00'),
        (3, '2015-02-01 00:00:00'),
        (4, '2015-04-01 00:00:00')
    ;
    
    
    CREATE TABLE Trans
        ([transID] int, [OrderKey] int, [TransDate] datetime, [validated] varchar(1))
    ;
        
    INSERT INTO Trans
        ([transID], [OrderKey], [TransDate], [validated])
    VALUES
        (1, 1, '2015-01-01 00:00:00', 'Y'),
        (2, 2, '2015-01-01 00:00:00', 'Y'),
        (3, 3, '2015-02-01 00:00:00', 'Y'),
        (4, 4, '2015-04-01 00:00:00', 'Y')
    ;
    
    
    CREATE TABLE orders_paymentPlan 
        ([orderKey] int, [paymentNum] int, [paymentDate] datetime)
    ;
        
    INSERT INTO orders_paymentPlan  
        ([orderKey], [paymentNum], [paymentDate])
    VALUES
        (1, 1, '2015-01-01 00:00:00'),
        (4, 1, '2015-04-01 00:00:00'),
        (4, 2, '2015-04-01 00:00:00')
    ;
    
**Query 1**:

    /*
    if the date range for example from 1/1/2015 to 2/1/2015
    I should see the orderKey :1,2,and 3 
    */
    declare @from as date = '20150101'
    declare @to   as date = '20150201'
    
    select
    *
    from orders o
    inner join trans t on o.OrderKey = t.OrderKey
    left join orders_paymentPlan pp on o.OrderKey = pp.OrderKey
    where ( t.TransDate >= @from and t.TransDate < dateadd(day,1,@to) ) 
    and (
          pp.paymentNum IS NULL
      OR  NOT EXISTS (
                     SELECT null from orders_paymentPlan OPP
                     where opp.orderKey = o.orderKey
                     Having(COUNT(OPP.orderKey)) > 1
                     )
        )
    

**[Results][2]**:
    | OrderKey |                  OrderDate | transID | OrderKey |                  TransDate | validated | orderKey | paymentNum |               paymentDate |
    |----------|----------------------------|---------|----------|----------------------------|-----------|----------|------------|---------------------------|
    |        1 |  January, 01 2015 00:00:00 |       1 |        1 |  January, 01 2015 00:00:00 |         Y |        1 |          1 | January, 01 2015 00:00:00 |
    |        2 |  January, 01 2015 00:00:00 |       2 |        2 |  January, 01 2015 00:00:00 |         Y |   (null) |     (null) |                    (null) |
    |        3 | February, 01 2015 00:00:00 |       3 |        3 | February, 01 2015 00:00:00 |         Y |   (null) |     (null) |                    (null) |
**Query 2**:

    
    
    declare @from as date = '20150101'
    declare @to   as date = '20150401'
    
    select
    *
    from orders o
    inner join trans t on o.OrderKey = t.OrderKey
    left join orders_paymentPlan pp on o.OrderKey = pp.OrderKey
    where ( t.TransDate >= @from and t.TransDate < dateadd(day,1,@to) ) 
    and (
          pp.paymentNum IS NULL
      OR  NOT EXISTS (
                     SELECT null from orders_paymentPlan OPP
                     where opp.orderKey = o.orderKey
                     Having(COUNT(OPP.orderKey)) > 1
                     )
        )
    
    
    

**[Results][3]**:
    | OrderKey |                  OrderDate | transID | OrderKey |                  TransDate | validated | orderKey | paymentNum |               paymentDate |
    |----------|----------------------------|---------|----------|----------------------------|-----------|----------|------------|---------------------------|
    |        1 |  January, 01 2015 00:00:00 |       1 |        1 |  January, 01 2015 00:00:00 |         Y |        1 |          1 | January, 01 2015 00:00:00 |
    |        2 |  January, 01 2015 00:00:00 |       2 |        2 |  January, 01 2015 00:00:00 |         Y |   (null) |     (null) |                    (null) |
    |        3 | February, 01 2015 00:00:00 |       3 |        3 | February, 01 2015 00:00:00 |         Y |   (null) |     (null) |                    (null) |

  [1]: http://sqlfiddle.com/#!3/9370b/1
  [2]: http://sqlfiddle.com/#!3/9370b/1/0
  [3]: http://sqlfiddle.com/#!3/9370b/1/1

Open in new window

0

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
erikTsomikSystem Architect, CF programmer Author Commented:
Just checked the sample output  and for the 3rd output it missing the orderKey 4. Because the actual order happended on the 4/1/2015 and Want to include this order. Because the payment plan 1 happened on that day. I do not wan to include payments plan 2 ,3,4.
0
PortletPaulfreelancerCommented:
Regret I don't understand. I hope others are able to.
0
erikTsomikSystem Architect, CF programmer Author Commented:
I wan to get all orders that fit given date range . in the **[Results][3]**: I do not see the order that took place on 04/01/2015
0
PortletPaulfreelancerCommented:
instead of words, provide examples based only the sample data (do not introduce results for data we know nothing about)
select
*
from orders o
inner join trans t on o.OrderKey = t.OrderKey
left join orders_paymentPlan pp on o.OrderKey = pp.OrderKey

| OrderKey |                  OrderDate | transID | OrderKey |                  TransDate | validated | orderKey | paymentNum |               paymentDate |
|----------|----------------------------|---------|----------|----------------------------|-----------|----------|------------|---------------------------|
|        1 |  January, 01 2015 00:00:00 |       1 |        1 |  January, 01 2015 00:00:00 |         Y |        1 |          1 | January, 01 2015 00:00:00 |
|        2 |  January, 01 2015 00:00:00 |       2 |        2 |  January, 01 2015 00:00:00 |         Y |   (null) |     (null) |                    (null) |
|        3 | February, 01 2015 00:00:00 |       3 |        3 | February, 01 2015 00:00:00 |         Y |   (null) |     (null) |                    (null) |
|        4 |    April, 01 2015 00:00:00 |       4 |        4 |    April, 01 2015 00:00:00 |         Y |        4 |          1 |   April, 01 2015 00:00:00 |
|        4 |    April, 01 2015 00:00:00 |       4 |        4 |    April, 01 2015 00:00:00 |         Y |        4 |          2 |   April, 01 2015 00:00:00 |

Open in new window

for date range:
declare @from as date = '20150101'
declare @to   as date = '20150201'

what is the "expected result"?
for date range:
declare @from as date = '20150101'
declare @to   as date = '20150401'

what is the "expected result"?
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.