Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    2

    Unanswered: SQL SELECT Query

    Hi guys,

    Looking for help with a SQL select query with several conditions, want to know if this is possible:

    I have:

    CDD
    AmendedCDD
    CompletionDate

    (All short date format)

    All in the same table "TblOrderDetails"

    I want a query that displays any CDD that are within 7 days of the today's date (or when the query is run).

    However, if CompletionDate has an entry, then the query doesn't display the result as "due".

    Also, if AmendedCDD has an entry in it, then the query ignores "CDD" and compares the AmendedCDD and CompletionDate instead.

    Any ideas?

    ____________________________________________
    -Dan Greenavis.com
    Last edited by DanFero; 06-06-13 at 08:58.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    SELECT
    [Columns]
    FROM
    TblOrderDetails
    WHERE
    CompletionDate IS NULL
    AND ABS(CASE WHEN AmendedCCD IS NULL THEN CCD ELSE AmendedCCD END - GETDATE()) < 8
    The above is aircode, but it should return columns from TblOrderDetails that don't have a completion date and have either an AmendedCCD or CCD within 7 days of the current date. As you didn't specify whether CCD or AmendedCCD would be in the past or the future relative to the current date, this should return both.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2013
    Posts
    2

    Advanced SQL SELECT

    EN: In EntityFramewok there ObjectQuery<Object>.Include(string path) to include data models relationships, my question is: How do I get data from a table relationship to which I make a query return values ?

    ES: En EntityFramewok existe ObjectQuery<Object>.Include(string path) para incluir los datos de las relaciones de los modelos, mi pregunta es: Cómo hago para que los datos de la relación de una tabla a la que le hago una consulta regrese con valores?
    Last edited by emachado9202; 06-24-13 at 02:52.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Quote Originally Posted by emachado9202 View Post
    EN: In EntityFramewok there ObjectQuery<Object>.Include(string path) to include data models relationships, my question is: How do I get data from a table relationship to which I make a query return values ?

    ES: En EntityFramewok existe ObjectQuery<Object>.Include(string path) para incluir los datos de las relaciones de los modelos, mi pregunta es: Cómo hago para que los datos de la relación de una tabla a la que le hago una consulta regrese con valores?
    Hi

    I think you need to start a new thread, as this is a new question on a new topic.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hi Dan,

    Weejas seemed to know what you were asking for. Yet it was not clear to me.

    It helps a lot if you provide us with some example data and the results you expect from that data. If you add a CREATE TABLE TempTable (.. ) and INSERT INTO TempTable (..) VALUES (...) so you get us started, you will even get an answer faster.

    Sure, Weejas made it pretty hard to beat him on speed
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Weejas seemed to know what you were asking for. Yet it was not clear to me.
    I get a lot of requests like this in my actual job. This was actually easier to process than some that revolve around Lotus Notes applications.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Can you be more vague?

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Tables have to have keys.

    This is minimal polite behavior on SQL forums. Putting that silly redundant “tbl-” affix on table names is called “tibbling” and the big kids laugh at you m See the article on it by Phil Factor over at Red Gate's newsletter.

    Please change the ?? to real DDL and we can give you real DML. Air code is not worth the paper it is written on.

    CREATE TABLE Order_Details
    (cdd ?? ,
    amended_cdd ??,
    completion_date DATE,
    PRIMARY KEY (??));

    I want a query that displays any CDD that are within 7 days of the today's date (or when the query is run).
    Which spec is it? One is a VIEW and the other is a procedure with a parameter.

    However, if completion_date has an entry, then the query doesn't display the result as "due".
    What does “has an entry mean?” Is this column NULL-able? If there is a completion_date, where is the starting date?

    Also, if amended_cdd has an entry in it, then the query ignores "cdd" and compares the amended_cdd and completion_date instead.
    Again, is the column NULL-able? Does this mean that you want COALESCE (cdd, amended_cdd) somewhere?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •