|
|
|
|
|
Monday, 19 July 2010 04:24 |
Coverts a few dates into utc format by reading the timezone from the registry as well as joining different tables depending on data
DECLARE @UTCOffset INTEGER
EXEC master..xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias',
@UTCOffset OUTPUT
SELECT ext.taskid, ext.uidl, ext.dateStart, ext.dateEnd, ext.subject, ext.detail, ext.reminder, ext.reminderDate, ext.dateModified
/*convert datefields to utc*/
,DATEADD(MINUTE, @UTCOffset, ext.datestart ) as UTCdatestart
,DATEADD(MINUTE, @UTCOffset, ext.dateend ) as UTCdateend
, CASE WHEN NOT ext.dateModified IS NULL THEN DATEADD(MINUTE, @UTCOffset, ext.datemodified )
ELSE DATEADD(MINUTE, @UTCOffset, ext.datecreated )
END AS UTCdatemodified
,DATEADD(MINUTE, @UTCOffset, ext.reminderdate ) as UTCreminderdate
,DATEADD(MINUTE, @UTCOffset, ext.recurrenceStartDate ) as UTCrecurrenceStartDate
,DATEADD(MINUTE, @UTCOffset, ext.recurrenceEndDate ) as UTCrecurrenceEndDate
FROM extask ext
/*taskType 1 */
LEFT OUTER JOIN jobSeeker_activity jsa ON jsa.act_id = ext.activityID AND ext.taskType = 1
LEFT OUTER JOIN activity_entry_type aet ON aet.typeTitle = jsa.[type] AND aet.clientID = 621
LEFT OUTER JOIN jobSeekerActivityReason jsr ON jsr.jobSeekerActivityReasonID = jsa.jobSeekerActivityReasonID AND jsr.clientID = 621
/*tasktype 2*/
LEFT OUTER JOIN companyActivities cac ON cac.ActivityID = ext.activityID AND ext.taskType = 2
LEFT OUTER JOIN companyActivityType cat ON cat.activityTypeID = cac.typeID AND cat.clientID = 621
LEFT OUTER JOIN companyActivityReason car ON car.companyActivityReasonID = cac.companyActivityReasonID AND car.clientID = 621
WHERE taskType IN (1,2) --Only tasks with a activity
AND (
aet.isOutlookAppointment = 1
OR jsr.isOutlookAppointment = 1
OR cat.isOutlookAppointment = 1
OR car.isOutlookAppointment = 1
)
AND ext.clientidfk = 621
AND ( CASE
WHEN NOT ext.dateModified IS NULL THEN DATEADD(MINUTE, @UTCOffset, ext.dateModified)
ELSE DATEADD(MINUTE, @UTCOffset, ext.dateCreated)
END) >= 621
 Read more: |
|
|
|
|
|
|
