CODEWITHSUNDEEP
vbaexcelloops
SMORF
SMORF

Reputation: 499

Loop sql code using next date from range

I am using the following VBA script to generate some system data based on the date from the cell value of sheets("SOURCE").range("C2").

Is there a way to loop the sql script and repeat the code using the next date in the range (dates will be listed in sheet "SOURCE", column E). Loop should end when a blank cell is reached?

Each set of data should populate beneath the last set of results in sheet "DATA".

Sub DATA()

Sheets("DATA").Select

    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select

Dim SQL As String
Dim orasession As Object
Dim oradatabase As Object
Dim dyprod As Object
Dim Row As Long

Application.ScreenUpdating = False

Set orasession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraSession Object. ( Oracle )
Set oradatabase = orasession.DbOpenDatabase("thal_cnded.world", "bde_rep/report", 0&) 'Create the OraDatabase Object by opening a connection to Oracle.

SQL = SQL & "select (select min(trunc(cb.act_date)) from com_bde_ahp_log cb "
SQL = SQL & "where (cb.prod_mach like 'M%' or cb.prod_mach like 'B%') and m.wcenter = cb.wcenter (+) and cb.prod_plant = 'W' and cb.diff_ok_disc_qty > 0 "
SQL = SQL & "and cb.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:10:00 ', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and cb.act_date <= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:09:59', 'DD/MM/YYYY HH24:MI:SS')) as one, "
SQL = SQL & "m.machine as two, "
SQL = SQL & "(select nvl(sum(cb.diff_ok_disc_qty),0) from com_bde_ahp_log cb "
SQL = SQL & "where m.machine = cb.prod_mach And m.wcenter = cb.wcenter And m.prod_plant = cb.prod_plant And cb.diff_ok_disc_qty > 0 "
SQL = SQL & "and cb.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and cb.act_date <= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:09:59', 'DD/MM/YYYY HH24:MI:SS')) as three, "
SQL = SQL & "(select nvl(sum(c.change_m),0) "
SQL = SQL & "from (select distinct mach, count((previous_order)) change_m "
SQL = SQL & "from (select c.prod_mach mach, c.act_date, c.part_no, c.wcenter wcenter, m.machgrp machgrp, "
SQL = SQL & "    NVL((select distinct c1.part_no from com_bde_ahp_log c1 "
SQL = SQL & "        where c1.wcenter not like 'D%' and c1.prod_plant = 'W' and c1.ast = 51 and c1.prod_mach||'-'||c1.cavity = c.prod_mach||'-'||c.cavity and rownum = 1 "
SQL = SQL & "        and c1.act_date = "
SQL = SQL & "            (select max(c2.act_date) from com_bde_ahp_log c2 "
SQL = SQL & "                where c2.wcenter not like 'D%' and c2.prod_plant = 'W' and c2.ast = 51 and c2.prod_mach||'-'||c2.cavity = c.prod_mach||'-'||c.cavity "
SQL = SQL & "                and c2.act_date < c.act_date and c2.act_date between c.act_date-0.5 and c.act_date)),'NA') previous_order, p.grpname format "
SQL = SQL & "from machine_master_data m, com_bde_ahp_log c "
SQL = SQL & "left join (select grpname,prodtyp, plant, packtyp from RLS_PROD_GROUP where grpname in ('BD25','BD50','DVD_5','DVD_9','DVD_10','UMD_2','UMD_1')) p on p.prodtyp = c.prodtyp and c.prod_plant = p.plant and substr(c.packtyp,2,1) = substr(p.packtyp,2,1) "
SQL = SQL & "where c.wcenter not like 'D%' "
SQL = SQL & "and c.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and c.act_date <= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:09:59', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and to_char(c.act_date,'hh24:mi:ss') between (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 1) and (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 2) "
SQL = SQL & "and c.prod_plant = 'W' and c.ast = 51 and m.machine = c.prod_mach and m.wcenter = c.wcenter and m.prod_plant = c.prod_plant "
SQL = SQL & "group by c.prod_mach, c.cavity, c.part_no, c.wcenter, c.act_date, m.machgrp, c.prod_mach, p.grpname order by 1,2) where previous_order != part_no "
SQL = SQL & "group by mach, wcenter, machgrp, format order by 1) c where c.mach = m.machine) as four, "
SQL = SQL & "(select nvl(sum(cb.diff_ok_disc_qty),0) from com_bde_ahp_log cb "
SQL = SQL & "where m.machine = cb.prod_mach And m.wcenter = cb.wcenter And m.prod_plant = cb.prod_plant And cb.diff_ok_disc_qty > 0 "
SQL = SQL & "and cb.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and cb.act_date <= (to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:09:59', 'DD/MM/YYYY HH24:MI:SS'))+1) as five, "
SQL = SQL & "(select nvl(sum(c.change_m),0) from (select distinct mach, count((previous_order)) change_m "
SQL = SQL & "from (select c.prod_mach mach, c.act_date, c.part_no, c.wcenter wcenter, m.machgrp machgrp, "
SQL = SQL & "    NVL((select distinct c1.part_no from com_bde_ahp_log c1 "
SQL = SQL & "        where c1.wcenter not like 'D%' and c1.prod_plant = 'W' and c1.ast = 51 and c1.prod_mach||'-'||c1.cavity = c.prod_mach||'-'||c.cavity and rownum = 1 "
SQL = SQL & "        and c1.act_date = (select max(c2.act_date) "
SQL = SQL & "                from com_bde_ahp_log c2 "
SQL = SQL & "                where c2.wcenter not like 'D%' and c2.prod_plant = 'W' and c2.ast = 51 and c2.prod_mach||'-'||c2.cavity = c.prod_mach||'-'||c.cavity "
SQL = SQL & "                and c2.act_date < c.act_date and c2.act_date between c.act_date-0.5 and c.act_date)),'NA') previous_order, p.grpname format "
SQL = SQL & "from machine_master_data m, com_bde_ahp_log c "
SQL = SQL & "left join (select grpname, prodtyp, plant, packtyp from RLS_PROD_GROUP where grpname in ('BD25','BD50','DVD_5','DVD_9','DVD_10','UMD_2','UMD_1')) p on p.prodtyp = c.prodtyp and c.prod_plant = p.plant and substr(c.packtyp,2,1) = substr(p.packtyp,2,1) "
SQL = SQL & "where c.wcenter not like 'D%' "
SQL = SQL & "and c.act_date >= to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 18:10:00', 'DD/MM/YYYY HH24:MI:SS') "
SQL = SQL & "and c.act_date <= (to_date(' " & Sheets("SOURCE").Range("E2") & " '||' 06:09:59', 'DD/MM/YYYY HH24:MI:SS'))+1 "
SQL = SQL & "and ((to_char(c.act_date,'hh24:mi:ss') between (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 5) and '23:59:59') or (to_char(c.act_date,'hh24:mi:ss') between '00:00:00' and (select substr(t.time,2) from bde_report_times_v t where plant = 'W' and seq_nr = 6))) "
SQL = SQL & "and c.prod_plant = 'W' and c.ast = 51 and m.machine = c.prod_mach and m.wcenter = c.wcenter and m.prod_plant = c.prod_plant "
SQL = SQL & "group by c.prod_mach, c.cavity, c.part_no, c.wcenter, c.act_date, m.machgrp, c.prod_mach, p.grpname order by 1,2) where previous_order != part_no "
SQL = SQL & "group by mach, wcenter, machgrp, format order by 1) c where c.mach = m.machine) as six "
SQL = SQL & "from machine_master_data m "
SQL = SQL & "where (m.machine like 'M%' or m.machine like 'B%') "
SQL = SQL & "and m.prod_plant = 'W' "
SQL = SQL & "order by length(m.machine), m.machine "

Set dyprod = oradatabase.CreateDynaset(SQL, 0&)

Sheets("DATA").Select
Row = 2
        If Not dyprod.EOF And Not dyprod.bof Then
                dyprod.movefirst
                       Do Until dyprod.EOF
                            Sheets("DATA").Cells(Row, 1).Select
                            ActiveCell.Value = dyprod.Fields("one").Value
                            Sheets("DATA").Cells(Row, 2).Select
                            ActiveCell.Value = dyprod.Fields("two").Value
                            Sheets("DATA").Cells(Row, 3).Select
                            ActiveCell.Value = dyprod.Fields("three").Value
                            Sheets("DATA").Cells(Row, 4).Select
                            ActiveCell.Value = dyprod.Fields("four").Value
                            Sheets("DATA").Cells(Row, 5).Select
                            ActiveCell.Value = dyprod.Fields("five").Value
                            Sheets("DATA").Cells(Row, 6).Select
                            ActiveCell.Value = dyprod.Fields("six").Value
                            dyprod.movenext

                            Row = Row + 1
                        Loop
                End If

End Sub

I did think I could use the following to loop through the date range, but I cannot work out how to link this and the SQL and make the data appear under the last set of results each time?

Sub DateLoop()

    Dim x As Integer

    NumRows = Range("E2", Range("E2").End(xlDown)).Rows.Count

    Range("E2").Select

        For x = 1 To NumRows

        ActiveCell.Offset(1, 0).Select

    Next

End Sub

Upvotes: 0

Views: 148

Answers (1)

Zac
Zac

Reputation: 1944

You could get the row count for column "E"

Dim oWS as Worksheet : Set oWS = Worksheets("SOURCE")
Dim intRowCount as Integer : intRowCount = oWS.Cells(Rows.Count, "E").End(xlUp).Row

Now create a For loop just before you assign the value to SQL

For intRow = 1 To intRowCount

Now that you have your loop, change your Sheets("SOURCE").Range("E2") to Sheets("SOURCE").Range("E" & intRow). In your Do Until dyprod.EOF loop, you can now assign the value as Sheets("DATA").Cells(intRow, 1).Value = ...

Upvotes: 3

Related Questions