Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Combine 2 Queries ??

    Hello Everyone,

    I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).

    I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..

    Here is query #1:

    select investor, full_date, month_end_summary, category, loan_count
    from cust_table
    where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
    group by investor, full_date, month_end_summary, category, loan_count
    order by investor, full_date


    Here is query #2:

    select investor, full_date, month_end_summary
    from cust_table
    where datediff(month,full_date,(select max(full_date) from cust_table)) =0
    order by investor, full_date


    Can they be combined into one query to produce one reslut table??

    I really appreciate any input you may have!
    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    'm not sure if that is what you are looking for but check the page below about Union All operator:
    SQL UNION Operator

    Hope this helps.

  3. #3
    Join Date
    Aug 2008
    Posts
    55
    Thank you imex !

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    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 (you have no idea). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Did you know that your narrative was absolutely useless in Management Studio?? It does not read English!

    Oh, the lack of sample data is also how you can prevent getting help.

    Putting “_table” in a table name is a design flaw called “tibling” and we do not do it. There is no such thing as a magical, generic, universal “category” in RDBMS; it has to be “<something in particular>_category” in a valid data model. Think about how useless “investor_<unknown attribute>” is to anyone maintaining your code. Account? Show size? Then “month_end_summary” of what?? Is it a count? A dollar amount? In RDBMS, we do not store computed values, so this is another bad code smell. Summary is also vague; is it a total? Average? Other aggregate statistic?

    What is a “full_date”? In ISO-11179 rules, it is the date that a nameless something was full. Gas tank? I have the horrible feeling that you have a partial_date in this model, so this is meta-data and not a valid temporal attribute at all.

    The two confusing queries you posted have a different number of columns, so they cannot be combined by definition.

    Try again and follow the rules. You can get a lot of good free help. Here are some quick corrections to the data element names:

    SELECT investor_id, -- wild guess
    gas_tank_full_date, -- hey, why not?
    month_end_something_summary_tot, -- hey, why not?
    loan_category, -- wild guess; could be investor, customer,..
    loan_count -–the only valid data element name
    FROM Customers
    WHERE DATEDIFF(MONTH, full_date,
    (SELECT MAX(gas_tank_full_date) FROM Customers)-- expensive
    ) < 6
    GROUP BY investor_id, full_date, month_end_summary, loan_category, loan_count;

    My guess is that we can use a ROLLUP and get 1-3 order of magnitude improvement with other corrections.

Posting Permissions

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