CODEWITHSUNDEEP
sql
JeffreyLazo
JeffreyLazo

Reputation: 853

Comparative Query

I am trying to write a query to compare data from two date ranges.

First Date range:

select code,description, quantity*each as Total
from orderiteminfo oi, orderinfo o, invoiceinfo i
where oi.orderid = o.orderid
and o.invoiceid = i.invoiceid
and i.invdate between '2014-01-01' and '2014-02-01'
group by oi.code, description, quantity, each

Second Date range:

select code,description, quantity*each as Total
from orderiteminfo oi, orderinfo o, invoiceinfo i
where oi.orderid = o.orderid
and o.invoiceid = i.invoiceid
and i.invdate between '2015-01-01' and '2015-02-01'
group by oi.code, description, quantity, each

I would want results to be:

Code|Description|Total for First Date Range|Total for second Date Range

Upvotes: 3

Views: 79

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

Something like this

SELECT code,
       description,
       SUM(CASE
             WHEN i.invdate BETWEEN '2014-01-01' AND '2014-02-01' THEN quantity * each
           END) AS [Total for First Date Range],
       SUM(CASE
             WHEN i.invdate BETWEEN '2015-01-01' AND '2015-02-01' THEN quantity * each
           END) AS [Total for second Date Range]
FROM   orderiteminfo oi
       INNER JOIN orderinfo o
               ON oi.orderid = o.orderid
       INNER JOIN invoiceinfo i
               ON o.invoiceid = i.invoiceid
WHERE  ( i.invdate BETWEEN '2014-01-01' AND '2014-02-01'
          OR i.invdate BETWEEN '2015-01-01' AND '2015-02-01' )
GROUP  BY oi.code,
          description

As a side note always use Inner Join syntax for joining two tables instead of old style comma separated join and keep the filters alone in Where clause which is more readable

Upvotes: 1

FutbolFan
FutbolFan

Reputation: 13723

If I understand it correctly, I think you could rather do this in a single query using case when statement:

select code,
       description, 
       case 
          when i.invdate between '2014-01-01' and '2014-02-01'
            then quantity*each 
        end  Total_for_First_Date_Range,
        case 
          when i.invdate between '2015-01-01' and '2015-02-01'
            then quantity*each 
        end  Total_for_Second_Date_Range              
from orderiteminfo oi, orderinfo o, invoiceinfo i
where oi.orderid = o.orderid
and o.invoiceid = i.invoiceid 
group by oi.code, description, quantity, each; --not sure if you need this

Upvotes: 0

Related Questions