SQL Query Oracle w dynamic dates

[Gumster]

Hi! Have an SQL query that I need to execute from excel and get the results in a sheet. I also need to be able to change the from/to date from cells in excel

Looks like this:
(p.startdate between to_date('01.10.2003','dd.mm.yyyy') and to_date('31.10.2003','dd.mm.yyyy') or
p.enddate between to_date('01.10.2003','dd.mm.yyyy') and to_date('31.10.2003','dd.mm.yyyy')
) or
(p.startdate < to_date('01.10.2003','dd.mm.yyyy') and p.enddate > to_date('31.10.2003','dd.mm.yyyy') )
)

Any help is welcome!

BR
/Gumster

 

[SkipVought]

Hi,

You need to format a string for your SQL.

sQuery = "Select * From MyTable " _ 
"Where (p.startdate between '" & FromDate &  "' and '" & ToDate &  "')  "

where FromDate and ToDate are formatted like 01.10.2003 if that's what your SQL code requires.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Gumster]

Thanks for the reply!

But how do I do it practically? I have a full queary that runs fine against the database in SQL Navigator, now I need to get the output in excel with the added funtionality that I can change the from/to dates from two cells in excel.

Not good at VB so if anyone has any code I would be very grateful

Thanks!
/Gumster

 

[Gumster]

Putting in the entire SQL Query that I need to execute. Can't get it to work from excel and I need some help with how to set up so I can change those p.startdate p.enddate. Or if anyone has a bettre solution but with the same results I would appreciate it very much


-- select trade info (standard contracts) about trades that are in delivery during selected period
select
'STD ' AS CType,
sp.tradeid,
0 as Detailsid,
'N/A' as portfolioid,
sp.accountcode,
sp.counterparty,
sp.power,
-- sp.power * p.totalhours,
sp.price,
sp.currencycode,
sp.physical,
sp.productid ,
sp.audited,
p.totalhours,
p.startdate,
p.enddate
from kw4_sptrades sp, kw4_standardprod p
where
sp.productid = p.standardproductid and
sp.physical = 0 and accountcode <>'ND' and
(
(p.startdate between to_date('01.10.2003','dd.mm.yyyy') and to_date('31.10.2003','dd.mm.yyyy') or
p.enddate between to_date('01.10.2003','dd.mm.yyyy') and to_date('31.10.2003','dd.mm.yyyy')
) or
(p.startdate < to_date('01.10.2003','dd.mm.yyyy') and p.enddate > to_date('31.10.2003','dd.mm.yyyy') )
)
UNION
select
'NONSTD' AS CType,
ch.tradeid,
cd.detailsid as Detailsid,
ch.portfolioid as portfolioid,
ch.accountcode,
ch.counterparty,
cd.power,
-- cd.power * cd.totalhours,
cd.price,
ch.currencycode,
ch.physical,
ch.productid ,
ch.audited,
cd.totalhours,
cd.startdate,
cd.enddate
from kw4_contractsheader ch, kw4_contractsdetails cd
where
ch.portfolioid = cd.portfolioid and
ch.physical = 0 and
accountcode <>'ND' and
(
(cd.startdate between to_date('01.10.2003','dd.mm.yyyy') and to_date('31.10.2003','dd.mm.yyyy') or
cd.enddate between to_date('01.10.2003','dd.mm.yyyy') and to_date('31.10.2003','dd.mm.yyyy')
) or
(cd.startdate < to_date('01.10.2003','dd.mm.yyyy') and cd.enddate > to_date('31.10.2003','dd.mm.yyyy') )
)
order by tradeid
;

 

[Gumster]

Anyone able to help me out with this?

Still haven't solved it and need it desperately by monday


Thanks!

 

[xlbo]

Not sure about connecting to oracle but in terms of dynamic dates, the SQL is just a string so

mSQL = &quot;Select x, y, z FROM a, b, c WHERE d=e AND startdate ='&quot; & format(range(&quot;A1&quot.value,&quot;dd/mm/yyyy&quot & &quot;' AND enddate ='&quot; & format(range(&quot;B1&quot.value,&quot;dd/mm/yyyy&quot & &quot;' AND f=g;&quot;

as a bit of pseudo SQL

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TheEntertainer]

Gumster,

Take a look at thread707-574324 and the original thread reference from there.

Thanks

 

[Gumster]

Thanks!
Had a look at those threads but I still couldn't solve it. Don't know enough I guess

The problem is it's an PL/SQL Statement I'm trying to run from excel and it won't execute properly in the macro. Any ideas?