I'm writing a report that will show response times. Requests can come in at any time, but any request done after 5:30 PM and before 7:30 AM should default to 7:30 AM as the requesttime
SELECT
Meeting_Invites.Invite_ID, ResponseDateTime,
REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') AS Time,
CASE
WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') > 1730 OR
REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') < 0730
THEN 1
ELSE 0
END AS NextDay,
CASE
WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') > 1730
THEN 'Next Day'
WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') < 0730
THEN 'Same Day'
ELSE ''
END AS DateType
FROM
Meeting_Invites
WHERE
ResponseDateTime BETWEEN {ts '2012-11-01 00:00:00'} AND {ts '2012-12-15 23:59:59'}
ORDER BY
Time
Which produces the results below. Is there a way to create a date that would match my CreatedDate column?
ID Date Time DateType CreatedDate
--------------------------------------------------------------------------------
84613 2012-12-06 07:29:02.823 0729 1 Same Day 2012-12-06 07:30:00
84079 2012-11-11 07:47:39.947 0747 0 (Use Date column)
84048 2012-11-05 08:13:50.423 0813 0 (Use Date column)
84372 2012-11-19 08:14:23.820 0814 0 (Use Date column)
84163 2012-11-13 20:43:19.760 2043 1 Next Day 2012-11-14 07:30:00
83527 2012-11-09 21:19:52.917 2119 1 Next Day 2012-11-10 07:30:00
84374 2012-11-26 21:35:21.887 2135 1 Next Day 2012-11-17 07:30:00
82518 2012-11-08 21:59:11.413 2159 1 Next Day 2012-11-09 07:30:00
I'm using ColdFusion to create the report, so I could built in the logic when I run the report, but would prefer not to.
SELECT Meeting_Invites.Invite_ID, ResponseDateTime, REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') AS Time
, CASE WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') > 1730 OR REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') < 0730 THEN 1 ELSE 0 END AS NextDay
, CASE WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') > 1730 THEN 'Next Day' WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') < 0730 THEN 'Same Day' ELSE '' END AS DateType
, CASE WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') > 1730 THEN
DATEADD(dd, 1, CONVERT(DATETIME(CONVERT(VARCHAR(10), ResponseDateTime, 121) + ' 07:30:00', 121)))
WHEN REPLACE(CONVERT(varchar(5), ResponseDateTime, 108), ':', '') < 0730 THEN
CONVERT(DATETIME(CONVERT(VARCHAR(10), ResponseDateTime, 121) + ' 07:30:00', 121))
ELSE ResponseDateTime END AS CreatedDate
FROM Meeting_Invites
WHERE ResponseDateTime BETWEEN {ts '2012-11-01 00:00:00'} AND {ts '2012-12-15 23:59:59'}
ORDER BY Time