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
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
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!
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
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.