roommaster sql

gistfile1.txt

-- sample row
SELECT FIRST CONFNUM, [INTERN NUM], RESERVESTATUS, CHECKIN, CHECKOUT, NIGHTS, BEDTYPE,
                                        [RATE REQ], GROUPCODE, EMAIL, PREFIX, LASTNAME, FIRSTNAME, STREET, CITY, STATE,
                                        ZIPCODE, COUNTRY, PHONE1, ADULTS, CHILD, ROOMRATE, ENDRATE, VIPNUMBER,
                                        HOUSENOTE, NOC, MADEBY, ENTEREDBY, ENTRYDATE, ENTRYTIME,
                                        CAST(CAST(ENTRYDATE AS VARCHAR(20)) + ' ' + CAST(ENTRYTIME AS VARCHAR(20)) AS DATETIME) as ENTRYTIMESTAMP,
                                        CHANGEDATE, CHANGETIME,
                                        CAST(CAST(CHANGEDATE AS VARCHAR(20)) + ' ' + CAST(CHANGETIME AS VARCHAR(20)) AS DATETIME) as CHANGETIMESTAMP,
                                        CXLNUM,
                                        CASE
                                        WHEN datalength(CXLNUM) > 0 THEN CAST(CAST(CXLDATE AS VARCHAR(20)) + ' ' + CAST(CXLTIME AS VARCHAR(20)) AS DATETIME)
                                        ELSE NULL
                                        END AS CXLTIMESTAMP
                                FROM reserve
                                ORDER BY CONFNUM
                                
                                
-- fetch latest data
SELECT CONFNUM, [INTERN NUM], RESERVESTATUS, CHECKIN, CHECKOUT, NIGHTS, BEDTYPE,
                                        [RATE REQ], GROUPCODE, EMAIL, PREFIX, LASTNAME, FIRSTNAME, STREET, CITY, STATE,
                                        ZIPCODE, COUNTRY, PHONE1, ADULTS, CHILD, ROOMRATE, ENDRATE, VIPNUMBER,
                                        HOUSENOTE, NOC, MADEBY, ENTEREDBY, ENTRYDATE, ENTRYTIME,
                                        CAST(CAST(ENTRYDATE AS VARCHAR(20)) + ' ' + CAST(ENTRYTIME AS VARCHAR(20)) AS DATETIME) as ENTRYTIMESTAMP,
                                        CHANGEDATE, CHANGETIME,
                                        CAST(CAST(CHANGEDATE AS VARCHAR(20)) + ' ' + CAST(CHANGETIME AS VARCHAR(20)) AS DATETIME) as CHANGETIMESTAMP,
                                        CXLNUM,
                                        CASE
                                        WHEN datalength(CXLNUM) > 0 THEN CAST(CAST(CXLDATE AS VARCHAR(20)) + ' ' + CAST(CXLTIME AS VARCHAR(20)) AS DATETIME)
                                        ELSE NULL
                                        END AS CXLTIMESTAMP
                                FROM reserve
                                WHERE CHANGETIMESTAMP >= DATEADD(minute, - #{minutes_since}, GETDATE())
                                ORDER BY CHANGETIMESTAMP DESC
                                
-- history
SELECT CONFNUM, [INTERN NUM], RESERVESTATUS, CHECKIN, CHECKOUT, NIGHTS, BEDTYPE,
                                        [RATE REQ], GROUPCODE, EMAIL, PREFIX, LASTNAME, FIRSTNAME, STREET, CITY, STATE,
                                        ZIPCODE, COUNTRY, PHONE1, ADULTS, CHILD, ROOMRATE, ENDRATE, VIPNUMBER,
                                        HOUSENOTE, NOC, MADEBY, ENTEREDBY, ENTRYDATE, ENTRYTIME,
                                        CAST(CAST(ENTRYDATE AS VARCHAR(20)) + ' ' + CAST(ENTRYTIME AS VARCHAR(20)) AS DATETIME) as ENTRYTIMESTAMP,
                                        CHANGEDATE, CHANGETIME,
                                        CAST(CAST(CHANGEDATE AS VARCHAR(20)) + ' ' + CAST(CHANGETIME AS VARCHAR(20)) AS DATETIME) as CHANGETIMESTAMP,
                                        CXLNUM,
                                        CASE
                                        WHEN datalength(CXLNUM) > 0 THEN CAST(CAST(CXLDATE AS VARCHAR(20)) + ' ' + CAST(CXLTIME AS VARCHAR(20)) AS DATETIME)
                                        ELSE NULL
                                        END AS CXLTIMESTAMP
                                FROM reserve
                                WHERE CAST(CONFNUM AS bigint) > #{database.last_historical_row_number_uploaded}
                                ORDER BY CONFNUM