CODEWITHSUNDEEP
sqloracle-database
jax
jax

Reputation: 840

SQL to get the sum of several items from different records in the same table within date range(ORACLE)

I have a schema as below. I would like to run the following query:

Get all the assets, description, total cost (from the asset table), its Driver, its polclass, its manager(from the assetspecs) and the (sum of all the delta) as usage from the the range of dates that can be specified. for example here we can specify from 11/2/2015 to 1/30/2016 and we should get the sum of delta as usage so the record would look like this

ASSETNUM|LOCATION|DESCRIPTION|TOTALCOST|DRIVER|POLCLASS|MANAGER|USAGE
----------------------------------------------------------------------
12345   | HERE   | SEDAN     |30000    |JOHN  | SPORT  | JOE   | 828(300+78+450) 
99999   |  .     | ......................................... 



ASSET:

ASSETNUM | LOCATION | DESCRIPTION | TOTALCOST
---------------------------------------------
12345    | HERE     | SEDAN       |30000    
99999    | THERE    | SUV         |50000
.        | .        |.            |.


ASSETSPEC:

ASSETNUM | ASSETATTRID | ALNVALUE  
---------------------------------
12345    | DRIVER      | JOHN    
12345    | POLCALSS    | SPORT           
12345    | MANAGER     | JOE           
99999    | DRIVER      | Greg  
99999    | POLCLASS    | SPORT 
99999    | .           | . 

METERREADING:

ASSETNUM | DELTA       | Reading Date  
---------------------------------
12345    | 78          | 1/30/2016      
12345    | 450         | 12/9/2015   
12345    | 300         | 11/2/2015

I figured out to join the table over and over again to get the driver, manager and polclass but I cannot figure out how to get the sum of delta within date range. This how my query looks like

SELECT V.ASSETNUM,
  drivers.ALNVALUE AS Driver,
  manager.ALNVALUE AS Manager,
  V.DESCRIPTION    AS ASSET_DESC,
  V.LOCATION,
  MAXIMO.LOCATIONS.DESCRIPTION AS Location_description,
  V.TOTALCOST,
  polclass.ALNVALUE AS POLICY_CLASS,
FROM MAXIMO.ASSET V
INNER JOIN MAXIMO.ASSETSPEC drivers
ON V.ASSETNUM           = drivers.ASSETNUM
AND drivers.ASSETATTRID = 'DRIVER'
INNER JOIN MAXIMO.ASSETSPEC polclass
ON V.ASSETNUM            = polclass.ASSETNUM
AND polclass.ASSETATTRID = 'POLCLASS'
INNER JOIN MAXIMO.ASSETSPEC manager
ON V.ASSETNUM           = manager.ASSETNUM
AND manager.ASSETATTRID = 'MANAGER'
INNER JOIN MAXIMO.LOCATIONS
ON MAXIMO.LOCATIONS.LOCATION = V.LOCATION
INNER JOIN MAXIMO.METERREADING
ON MAXIMO.METERREADING.ASSETNUM = V.ASSETNUM

Upvotes: 1

Views: 123

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

select assetnum,location,
    description,totalcost,
    manager,polcalss,driver,
    sum(delta) total_delta from
(select * from (
    select a.assetnum, 
    a.location, 
    a.description, 
    a.totalcost, 
    b.assetattrid, 
    b.alnvalue, 
    m.delta
from asset a inner join assetspec b on a.assetnum = b.assetnum
    inner join meterreading m on a.assetnum = m.assetnum
where m.reading_date between 
    to_date('1/30/2015','mm/dd/yyyy') 
    and to_date('1/30/2016','mm/dd/yyyy')
)
pivot
    (max(alnvalue) for 
    (assetattrid) in ('manager' as manager,
        'polcalss' as polcalss, 
        'driver' as driver))
)
group by assetnum,location,description,
totalcost,manager,polcalss,driver;

Upvotes: 1

Related Questions