SQL Query Manipulation

[S0lidFr0st]

Hello! I'm fairly new to using Autoit, I like the language and simplicity, however, there is a bit of a learning curve for me. I'm stuck and need some community help!

I need to manipulate a query by using GUICtrlCreateDate to select the correct date and pipe the selected date into my actual query in a specific format (yyyymmdd).

Here is an example:

_Flag_RecordsetDisplay($sConnectionString, "select * from trips_to_complete_20161122 where trip_type in ('P','C') and trip_status in ('S','PC','DC') and Flagged = 1")


Func _Flag_RecordsetDisplay($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc    ;==>_Flag_RecordsetDisplay

The part of the query that needs modified is "trips_to_complete_20161122" I need to be able to select a date (via the gui) and that selection pipe into my query.  

 

Thanks in Advanced!

Edited November 22, 2016 by S0lidFr0st

[Skysnake]

I have two helper functions:

sqldate2gui()

guidate2sql()

which convert.  Probably a design flaw on my side. Should just have used SQL friendly ISO dates in the GUI... See DTP to do that: 

 

https://www.autoitscript.com/autoit3/docs/libfunctions/_GUICtrlDTP_Create.htm

The bad news is, there is no quick fix. You will need to put in some extra lines of code to get it to work the way you want.

Sorry.

-Skysnake

[S0lidFr0st]

Just now, Skysnake said:

The bad news is, there is no quick fix. You will need to put in some extra lines of code to get it to work the way you want.

Sorry.

-Skysnake

Hey! Thanks for the reply! No need to apologize! I can work with extra code. This is a learning experience for me anyway, and if I can pull it off, will save me hours worth of time!

[jchd]

Ahem, sorry to ask, but is the date part of the table name?

 

[Skysnake]

ooh, as always you impress... good job @jchd

 

[mLipok]

From help file example for GUICtrlCreateDate:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>

Example()

Func Example()
    GUICreate("My GUI get date", 200, 200, 800, 200)
    Local $idDate = GUICtrlCreateDate("1953/04/25", 10, 10, 185, 20)
    GUISetState(@SW_SHOW)

    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop

        EndSwitch
    WEnd

    MsgBox($MB_SYSTEMMODAL, "Date", GUICtrlRead($idDate))
    GUIDelete()
EndFunc   ;==>Example

As I understand the main question is how to convert result from GUICtrlRead($idDate) to YYYYMMDD .

Remark:  GUICtrlRead for Date control as a return value give you :  The selected date in the format defined by the regional settings .

[jchd]

@S0lidFr0st

I say because it's always (like 99.999% of the times) a bad idea to mix data and schema names.

To illustrate imagine you create a daily table of <something>. You end up filling your database with a large number of distinct tables storing information having the exact same semantics. Now say your duty is to exhibit rows matching some criterion and having occured in the last 7 days. Your query will look like this:

select <list of columns> from Something_2016-11-16 where <criterion>
union all
select <list of columns> from Something_2016-11-17 where <criterion>
union all
select <list of columns> from Something_2016-11-18 where <criterion>
union all
select <list of columns> from Something_2016-11-19 where <criterion>
union all
select <list of columns> from Something_2016-11-20 where <criterion>
union all
select <list of columns> from Something_2016-11-21 where <criterion>
union all
select <list of columns> from Something_2016-11-22 where <criterion>
order by ... Geez, you're stuck here: no good criterion order! (remember SQL tables are like maths sets, orderless)

Compare with this, where the ISO date is part of the table:

select <list of columns>
from Something
where <criterion> and theDate between date('now', '-6 days') and date('now')
order by theDate

Which do you find more practical and should be more efficient?

EDIT: typing too fast, there should be double quotes or the delimiters your engine requires around the weird table names, e.g. "Something_2016-11-16" else SQL will treat - as substraction (my bad).

Edited November 22, 2016 by jchd
Delimiters omitted

[S0lidFr0st]

20 hours ago, jchd said:

Ahem, sorry to ask, but is the date part of the table name?

 

Unfortunately, yes. The way this system works is every day the previous day (table) is archived and a new table is generated for the 31st day. So on each day I have 31 tables ranging from the current date to 31 days out. I only need to work with present day data.

[jchd]

I would fire the guy who set this up.

[S0lidFr0st]

4 hours ago, jchd said:

I would fire the guy who set this up.

Oh man we are in complete agreement there. If you only knew more, you would feel so much stronger as well!