0

I have a question that I'm having trouble answering.

Find out what is the difference in number of invoices and total of invoiced products between May and June.

One way of doing it is to use sub-queries: one for June and the other one for May, and to subtract the results of the two queries. Since each of the two subqueries will return one row you can (should) use CROSS JOIN, which does not require the "on" clause since you join "all" the rows from one table (i.e. subquery) to all the rows from the other one. To find the month of a certain date, you can use MONTH function.

Here is the Erwin document

enter image description here

This is what I got so far. I have no idea how to use CROSS JOIN in this situation 

select COUNT(*) TotalInv, SUM(ILP.ProductCount) TotalInvoicedProducts
from Invoice I, (select Count(distinct ProductId) ProductCount from InvoiceLine) AS ILP 
where MONTH(inv_date) = 5

select COUNT(*) TotalInv, SUM(ILP.ProductCount) TotalInvoicedProducts
from Invoice I, (select Count(distinct ProductId) ProductCount from InvoiceLine) AS ILP 
where MONTH(inv_date) = 6

If you guys can help that would be great.

Thanks

fixnode
  • 97
  • 3
  • 8
  • 32

2 Answers2

1

The problem statement suggests you use the following steps:

  • Construct a query, with a single result row giving the values for June.
  • Construct a query, with a single result row giving the values for May.
  • Compare the results of the two queries.

The issue is that, in SQL, it's not super easy to do that third step. One way to do it is by doing a cross join, which yields a row containing all the values from both subqueries; it's then easy to use SELECT (b - a) ... to get the differences you're looking for. This isn't the only way to do the third step, but what you have definitely doesn't work.

Jose Torres
  • 347
  • 1
  • 3
  • I edited my code with the two tables that need to be cross joined. Your logic is sound and I'd like to know proper syntax for the CROSS JOIN – fixnode Nov 29 '13 at 23:28
0

can't you do something with subqueries? I haven't tested this, but something like the below should give you 4 columns, invoices and products for may and june. 

select (

select 'stuff' a, count(*) as june_invoices, sum(products) as products from invoices 
where month = 'june' 
 ) june , (

select 'stuff' a, count(*) as may_invoices, sum(products) as products from invoices 
where month = 'may' 
) may

where june.a = may.a
phatmanace
  • 4,671
  • 3
  • 24
  • 29