none
SQL QUERY How to write a sql query with a complex where clause.

    Question

  • I would like to get a list of all my invoices from the past year plus any open invoices that are more than a year old.

    I don't want any overlapping rows.


    Debra has a question

    • Moved by PWMatherMVP Wednesday, December 25, 2013 2:37 PM Better suited forum
    Tuesday, December 24, 2013 5:06 PM

Answers

  • Hi Debra,

    You can do this in two ways:

    1. A single WHERE clause that contains OR and AND predicates.

    2. Two SELECT statements joined with a UNION [ALL].

    Choosing either one depends on the indexes that are defined on your table. If you could provide that information it would be helpful. In the meantime, I'll show you both ways:

    1. A single WHERE clause:

    SELECT InvoiceID
    FROM Invoices
    WHERE InvoiceDate >= DATEADD(year,-1,GETDATE())
    OR (InvoiceDate < DATEADD(year,-1,GETDATE()) AND InvoiceStatus = 'open')

    2. Two SELECT statements joined with UNION ALL:

    SELECT InvoiceID
    FROM Invoices
    WHERE InvoiceDate >= DATEADD(year,-1,GETDATE())
    UNION ALL
    SELECT InvoiceID
    FROM Invoices
    WHERE InvoiceDate < DATEADD(year,-1,GETDATE())
    AND InvoiceStatus = 'open'

    Obviously I had to guess the table and column names, but I expect you'd get the idea.


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    Wednesday, December 25, 2013 2:46 PM

All replies

  • Debra,

    Sorry but you have posted to a forum that deals exclusively with questions/issues about customizing and programming Microsoft Project, a planning and scheduling application. I suggest you delete this post and find a more appropriate forum.

    John

    Tuesday, December 24, 2013 5:26 PM
  • Group by invoice_number should eliminate duplicate rows.
    Tuesday, December 24, 2013 9:31 PM
  • Hi Debra, I have moved your query to the T-SQL forum. Paul

    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Wednesday, December 25, 2013 2:38 PM
  • Hi Debra,

    You can do this in two ways:

    1. A single WHERE clause that contains OR and AND predicates.

    2. Two SELECT statements joined with a UNION [ALL].

    Choosing either one depends on the indexes that are defined on your table. If you could provide that information it would be helpful. In the meantime, I'll show you both ways:

    1. A single WHERE clause:

    SELECT InvoiceID
    FROM Invoices
    WHERE InvoiceDate >= DATEADD(year,-1,GETDATE())
    OR (InvoiceDate < DATEADD(year,-1,GETDATE()) AND InvoiceStatus = 'open')

    2. Two SELECT statements joined with UNION ALL:

    SELECT InvoiceID
    FROM Invoices
    WHERE InvoiceDate >= DATEADD(year,-1,GETDATE())
    UNION ALL
    SELECT InvoiceID
    FROM Invoices
    WHERE InvoiceDate < DATEADD(year,-1,GETDATE())
    AND InvoiceStatus = 'open'

    Obviously I had to guess the table and column names, but I expect you'd get the idea.


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    Wednesday, December 25, 2013 2:46 PM
  • Thank you this looks like it should work. I was also wondering if I want the invoices but it also has to be according to a specific customer would I have to do something like

    select invoiceID from invoices where customer = 123 AND invoiceDate > = DATEADD(year, -1,GETDATE())

    OR customer = 123 AND InvoiceDate < DATEADD(year, -1, GETDATE())


    Debra has a question

    Wednesday, December 25, 2013 5:26 PM
  • Hi Debra,

    Yeah that'll work. But what about "open invoices"? You don't need a predicate for that?


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    Wednesday, December 25, 2013 5:28 PM
  • yes, I do need and open = true but I just wanted to make sure that this will work writing where customer = 123 and...date...

    or customer = 123 and open = true


    Debra has a question

    Wednesday, December 25, 2013 6:16 PM
  • Just make sure to use parenthesis correctly if you plan to introduce OR condition in the query. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, December 25, 2013 7:58 PM
    Moderator
  • Where would I put in parenthesis?

    Debra has a question


    Wednesday, December 25, 2013 7:59 PM
  • In your last sample it will be

    select invoiceID from invoices where (customer = 123 AND invoiceDate > = DATEADD(year, -1,GETDATE()))
    
    OR (customer = 123 AND InvoiceDate < DATEADD(year, -1, GETDATE()))

    Although this particular expression is easy to re-write this way:

    select invoiceID from invoices

    where customer = 123

    AND invoiceDate > = DATEADD(year, -1,GETDATE()) AND InvoiceDate < DATEADD(year, 1, GETDATE())



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, December 26, 2013 5:15 PM
    Moderator
  • Debra, do you not know what DDL is? Is that why you fail to post any or are you just rude?   

    CREATE TABLE Invoices
    (invoice_nbr CHAR(15) NOT NULL PRIMARY KEY,
     invoice_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     invoice_amt DECIMAL(12,2) NOT NULL
      CHECK (invoice_amt >= 0.00),
     payment_date DATE,
     ..);

    >> I would like to get a list [sic: SQL uses table, not lists] of all my invoices from the past year plus any open invoices that are more than a year old. 

    Did you mean invoices issued in 2012 or paid in 2012? Here is my guess. 

    SELECT invoice_nbr 
      FROM Invoices 
     WHERE invoice_date BETWEEN '2012-01-01' AND '2012-12-31'
        OR (payment_date IS NULL 
            AND invoice_date < '2012-01-01';


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 26, 2013 10:04 PM
  • Debra, do you not know what DDL is? Is that why you fail to post any or are you just rude?   

    Debra is not rude. But since Debra may be new to this forum, I should advice you to ignore this Celko person, who indeed is known to rude.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 26, 2013 10:55 PM