Skip to content

Instantly share code, notes, and snippets.

@mikegreen
Last active September 29, 2021 01:48
Show Gist options
  • Save mikegreen/475d04448e0fd9f53a77 to your computer and use it in GitHub Desktop.
Save mikegreen/475d04448e0fd9f53a77 to your computer and use it in GitHub Desktop.
roommaster sql
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment