How to joining two different queries as one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to joining two different queries as one?

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-02 : 14:14:56

Hello,

I need some help on converting a new query into a row of another existing query. I am not sure how to explain this but this is what I'm working on:

SELECT
COALESCE(hsc_product_group_category.product_group_catagory, 'MISC') 'product category'

,ROUND(SUM([detail_price]),2) AS 'sales'
,ROUND(SUM([detail_cogs]),2) AS 'cogs'
,((SUM(detail_price))-(SUM(detail_cogs)))as 'profit'

FROM [P21].[dbo].[p21_sales_history_report_view]
LEFT OUTER JOIN hsc_p21_view_inv_mast ON [p21_sales_history_report_view].inv_mast_uid = hsc_p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN hsc_product_group_category ON [p21_sales_history_report_view].product_group_id = hsc_product_group_category.product_group_id

WHERE period = 6 AND
year_for_period = 2012 AND
parent_oe_line_uid = 0 AND
hsc_p21_view_inv_mast.class_id3 IS NULL AND
(invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )

GROUP BY
COALESCE(hsc_product_group_category.product_group_catagory, 'MISC')

As you see the query above, I execute successful. My boss also asks me to get another data that you build exactly the same query above. The only thing you need to change is changing from 'hsc_p21_view_inv_mast.class_id3 IS NULL' to 'hsc_p21_view_inv_mast.class_id3 IS NOT NULL' from the WHERE STATEMENT and name the results as 'EQUIPMENT' in the product category column as a new row in the other query.

Because it is basically the same query, I don't know how to join these 2 query as a new row for equipment with ' not null' data as a whole query. Please help me on this. I am not sure there is a way to do that or not. Please let me know if you guys need more clarification.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-02 : 19:19:55

sounds like this


SELECT
COALESCE(hsc_product_group_category.product_group_catagory,CASE WHEN 
hsc_p21_view_inv_mast.class_id3 IS NULL THEN 'MISC' ELSE 'EQUIPMENT' END) 'product category'

,ROUND(SUM([detail_price]),2) AS 'sales'
,ROUND(SUM([detail_cogs]),2) AS 'cogs'
,((SUM(detail_price))-(SUM(detail_cogs)))as 'profit'

FROM [P21].[dbo].[p21_sales_history_report_view]
LEFT OUTER JOIN hsc_p21_view_inv_mast ON [p21_sales_history_report_view].inv_mast_uid = hsc_p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN hsc_product_group_category ON [p21_sales_history_report_view].product_group_id = hsc_product_group_category.product_group_id

WHERE period = 6 AND
year_for_period = 2012 AND
parent_oe_line_uid = 0  AND
(invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0) 
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )

GROUP BY
COALESCE(hsc_product_group_category.product_group_catagory, ,CASE WHEN 
hsc_p21_view_inv_mast.class_id3 IS NULL THEN 'MISC' ELSE 'EQUIPMENT' END)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources