Toad World® Forums

I want the result for different years without filling in the year manually

Message from: nico_515

I’m hoping that post this message on the right forum. I’m programmer
with some SQL knoledge.

I’m doing this with TOAD!

On another forum I had a thread with a pseudo query and some people say you
don’t need a loop. Ok can be, but what is then the solution that get the
result for different years in a grid?
I have placed here the real query.
The years can every time been different but now I want the result for the years
2010 till 2014? As ‘programmer’ I think I need a loop!?
Ok I can fill in the years manualy, but that is not the way to go I think.
And now there is another request the product can be different.

I hope that somebody can help me or put me in the right direction.

--Sommatie van de volumes
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'BOTH' --'OFF_PEAK 'ON_PEAK'
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE
union
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'OFF_PEAK' --''OFF_PEAK ''ON_PEAK
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE
union
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'ON_PEAK' --''OFF_PEAK ''ON_PEAK
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE;

Historical Messages

Author: nico_515
Date: Fri Oct 21 03:07:04 PDT 2011

I’m hoping that post this message on the right forum. I’m programmer
with some SQL knoledge.

I’m doing this with TOAD!

On another forum I had a thread with a pseudo query and some people say you
don’t need a loop. Ok can be, but what is then the solution that get the
result for different years in a grid?
I have placed here the real query.
The years can every time been different but now I want the result for the years
2010 till 2014? As ‘programmer’ I think I need a loop!?
Ok I can fill in the years manualy, but that is not the way to go I think.
And now there is another request the product can be different.

I hope that somebody can help me or put me in the right direction.

--Sommatie van de volumes
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'BOTH' --'OFF_PEAK 'ON_PEAK'
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE
union
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'OFF_PEAK' --''OFF_PEAK ''ON_PEAK
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE
union
select lc.CONTRACTID
,c.CLICKTYPECODE
,count(*) NumberOfSlices
,ROUND(SUM(c.slicevolume),0) as SliceVolume
from contract lc
,click c
,(
-- De losse clickcontracten die gesommeeerd worden en dan tevens voor de
sommatie
-- o.a. het contract id mee moeten retourneren
select clc.CONTRACTID as Contract_ID
from contract clc
,click cc
where clc.CONTRACTSTATUSCODE = 'EXECUTED'
and clc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and cc.UIDCONTRACT = clc.UIDCONTRACT
and TO_NUMBER(TO_CHAR(cc.APPROVEDTIME , 'yyyy')) = 2011
group by clc.CONTRACTID
) Click_Cntr
where lc.CONTRACTSTATUSCODE = 'EXECUTED'
and lc.CONTRACTID = Click_Cntr.Contract_ID
and lc.CONTRACTTYPECODE = 'STANDARD_PRICING'
and c.UIDCONTRACT = lc.UIDCONTRACT
and c.CLICKTYPECODE = 'ON_PEAK' --''OFF_PEAK ''ON_PEAK
and TO_NUMBER(TO_CHAR(c.APPROVEDTIME , 'yyyy')) = 2011
group by lc.CONTRACTID, c.CLICKTYPECODE;

__