Combine 2 separate queries to achieve single result

[HeelNGville]

I preface this with many thanks for assistance. High level, I have a table source (sales) that houses both sale and return activity. I have 2 separate queries (a)qry_sales to retrieve sales and (b) qry_ret to retrieve returns by day and product the current year and previous yeard and aggregating results by day. As mentioned above have 2 separate queries retrieving this info, however really need to try to merge these 2 queries into a single query result if possible.

My SQLs:

Sales(summarizes product sales by day and date is driven by field sale_day)

SELECT
to_char(sh.sale_day,'DDD') as Day_No,
to_char(sh.sale_day,'MON-DD')as sales_day,
sk.prd_id,

---Get Sale Units
sum(case when sh.line_status in ('S','D') and sh.sale_day between '2018-
01-01' and current_date then sh.sale_qty else 0 end) as cy_sale_units,
sum(case when sh.line_status in ('S','D') and sh.sales_day between
'2017-01-01' and current_date-366 then sh.sale_qty else 0 end) as
py_sale_units,

--Get Sales Dollars
sum(case when sh.line_status in ('S','D') and sh.sales_day between '2018-
01-01' and current_date then sh.sale_qty*sh.unit_price else 0 end) as
cy_sale_dollars,
sum(case when sh.line_status in ('S','D') and sh.sales_day between '2017-
01-01' and current_date-366 then sh.sale_qty*sh.unit_price else 0 end) as
py_sale_dollars,

--Get Sales Cost
sum(case when sh.line_status in ('S','D') and sh.sales_day between '2018-
01-01' and current_date then sh.sale_qty*sh.unit_cost else 0 end) as
cy_sale_cost,
sum(case when sh.line_status in ('S','D') and sh.sales_day between '2017-
01-01' and current_date-366 then sh.sale_qty*sh.unit_cost else 0 end) as
py_sale_cost

FROM
sales sh

INNER JOIN prod sk
ON trim(sk.prd_id) = trim(sh.prd_id)
INNER JOIN buyer_type ba
ON trim (ba.buyer_area) = trim(sk.buyer_area)

WHERE
sh.sales_day between '2017-01-01' And current_date-1
AND sh.line_status in ('S','D')
AND sh.unit_price >0
AND ba.buyer_code_type is not null

Group By
to_char(sh.sales_day,'DDD'),
to_char(sh.sales_day,'MON-DD'),
sk.skn_nbr

Order By
to_char(sh.sales_day,'DDD')


Returns (summarizes product returns by day and date is driven by field ret_date)

SELECT
to_char(sh.ret_date,'DDD') as Day_No,
to_char(sh.ret_date,'MON-DD')as ret_day,
sk.prd_id,

--Get Return Units
sum(case when sh.line_status in ('R') and sh.ret_date between '2018-01-
01' and current_date then sh.return_qty else 0 end) as cy_ret_units,
sum(case when sh.line_status in ('R') and sh.ret_date between '2017-01-
01' and current_date-366 then sh.return_qty else 0 end) as py_ret_units,

--Get Return Sales
sum(case when sh.line_status in ('R') and sh.ret_date between '2018-01-
01' and current_date then sh.return_qty*sh.unit_price else 0 end) as
cy_ret_dollars,
sum(case when sh.line_status in ('R') and sh.ret_date between '2017-01-
01' and current_date-366 then sh.return_qty*sh.unit_price else 0 end) as
py_ret_dollars,

--Get Return Cost
sum(case when sh.line_status in ('R') and sh.ret_date between '2018-01-
01' and current_date then sh.return_qty*sh.unit_cost else 0 end) as
cy_ret_cost,
sum(case when sh.line_status in ('R') and sh.ret_date between '2017-01-
01' and current_date-366 then sh.return_qty*sh.unit_cost else 0 end) as
py_ret_cost


FROM
sales sh

INNER JOIN prod sk
ON trim(sk.prd_id) = trim(sh.prd_id)
INNER JOIN buyer_type ba
ON trim (ba.buyer_area) = trim(sk.buyer_area)

WHERE
sh.ret_date between '2017-01-01' And current_date-1
AND sh.line_status in ('R')
AND sh.unit_price >0
AND ba.buyer_code_type is not null

Group By
to_char(sh.ret_date,'DDD'),
to_char(sh.ret_date,'MON-DD'),
sk.skn_nbr

Order By
to_char(sh.ret_date,'DDD')

Basically, I am requesting assistance on how to combine the 2 SQL above to yield a single result. I have attached an image of result examples from the 2 statements above, along with the desired results I am attempting to achieve. Thanks for the time.

 

[arnelgp]

i have not used SQL, but i think you can achieved what you want by creating another query using those two queries Inner Join using DAY_NO.

 

[HeelNGville]

I do not believe another query utilizing an inner join by day would work, as I could have prd_ID with return activity in the returns query, yet no sales activity (and vice versa). There are 3 possible results:

1. prd_ID with sales activity and no return activity
2. prd_ID with sales and return activity
3. prd_ID with no sales but return activity

An inner join would work for option 2, but not option 1 or 3. It seems some form of UNION ALL needs to occur, however I am not up to speed on this. I inherited this issue and have basically self taught just enough to get by.

Any other suggestions?

 

[arnelgp]

perhaps create 3 queries.

qry_sales Left Join to qry_ret On DAY_NO
qry_ret Left Join qry_sales On DAY_NO

Union query of the the above queries.

 

[HeelNGville]

If possible, the goal is to attempt to reduce the query count from 2 to 1, in lieu of increasing from 2 to 5 and muddying the waters further.

Thanks for the suggestion though.

 

[WayneRyan]

Heel,

Hey that’s ORACLE!

What you need to do is make 1 query with CTEs (ORACLE has them)

1st part generates a date calendar with one date per row.

2nd part joins Sales to the calendar by date with your group by

3rd part joins Returns to calendar by date with your group by

Finally, join CTEs 2 & 3 by date; removing “0” days and calculating “net” per day.

Once you get the syntax you’ll be okay.

ORAFAQ is a good forum for guidance.

If you’re converting to SQL post back and I’ll help when I get on something
Besides an iPhone. This has been difficult to type.

HTH,
Wayne

 

[HeelNGville]

Thanks Wayne, I will give that a try and see how it works.

I am running the queries in MS Access via pass through against an Oracle database.

For me, this would be a non issue if the return date and ship date values were stored in a single column, but with both values stored in separate columns, that is thrown my limited coding brain into a fog.

I will try your suggestions above and see what happens. Thanks!

 

[Minty]

I haven't played with Oracle, but you can use a FULL OUTER JOIN in SQL Server to accommodate this type of scenario, it returns rows from both data sets.

I have used it to good effect to compare two different stock lists where there may be parts in one or other lists in different locations.

Basic description https://www.w3schools.com/sql/sql_join_full.asp

 

[HeelNGville]

The challenge (for me anyway) is the fact that I am having to use 2 separate date columns in my where clause and grouping by a date, which is driven by those date fields.

So, all the records are stored in a single table (sales), and within that table are 2 separate date columns:

1. sale_day (for actual sale date)
2. ret_date (actual return date)

My range for both of these columns is between '2017-01-01' And current_date-1

So, an example:

Sales:
Prd_ID-----Sale_Day-----Sale_Qty
A-----------1/1/18----------1
B-----------1/5/18----------1

Returns:
Prd_ID-----Ret_Date-----Ret_Qty
A-----------1/2/18-----------1
B-----------1/5/18-----------1
C-----------1/1/18-----------1

The results I am attempting to achieve:
Prd_ID------Day------Sale_Qty------Ret_Qty
A------------1/1/18-------1--------------0
A------------1/2/18-------0--------------1
B------------1/5/18-------1--------------1
C------------1/1/18-------0--------------1

Not sure how to force that group by day (so either sale_day or ret_date would drive this).

 

[WayneRyan]

Heel,

The group by will be in each of your data CTEs.
The calendar CTE will define the “skeleton” of your query.

Investigate CTEs, that’s where your answer lies ... and it’ll change the
Way you write your SQL for the better.

Wayne