- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
SELECT top 20 *,
CAST(DATEPART(yy, rs_message_sendtime) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m,
rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(m,
rs_message_sendtime) AS varchar(4)) END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_sendtime) AS varchar(4))))
= 1 THEN '0' + CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_sendtime) AS varchar(4)) END)
+ 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_sendtime)
AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_sendtime) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi,
rs_message_sendtime) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(mi,
rs_message_sendtime) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)))
= 1 THEN '0' + CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_sendtime) AS varchar(4)) END)
AS sendtime,
CAST(DATEPART(yy, rs_message_valid_from) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, rs_message_valid_from)
AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(m, rs_message_valid_from)
AS varchar(4)) END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_valid_from) AS varchar(4)))) = 1 THEN '0' + CAST(DATEPART(d,
rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_valid_from) AS varchar(4)) END)
+ 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_valid_from) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_valid_from)
AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_valid_from) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi,
rs_message_valid_from) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(mi,
rs_message_valid_from) AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)))
= 1 THEN '0' + CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_valid_from) AS varchar(4)) END)
AS validfrom,
CAST(DATEPART(yy, rs_message_valid_to) AS varchar(4)) + '-' + (CASE WHEN len(CAST(DATEPART(m, rs_message_valid_to)
AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(m, rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(m, rs_message_valid_to) AS varchar(4))
END) + '-' + (CASE WHEN (len(CAST(DATEPART(d, rs_message_valid_to) AS varchar(4)))) = 1 THEN '0' + CAST(DATEPART(d, rs_message_valid_to)
AS varchar(4)) ELSE CAST(DATEPART(d, rs_message_valid_to) AS varchar(4)) END) + 'T' + (CASE WHEN len(CAST(DATEPART(hh, rs_message_valid_to)
AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(hh, rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(hh, rs_message_valid_to)
AS varchar(4)) END) + ':' + (CASE WHEN len(CAST(DATEPART(mi, rs_message_valid_to) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(mi,
rs_message_valid_to) AS varchar(4)) ELSE CAST(DATEPART(mi, rs_message_valid_to) AS varchar(4)) END)
+ ':' + (CASE WHEN len(CAST(DATEPART(ss, rs_message_valid_to) AS varchar(4))) = 1 THEN '0' + CAST(DATEPART(ss, rs_message_valid_to)
AS varchar(4)) ELSE CAST(DATEPART(ss, rs_message_valid_to) AS varchar(4)) END) AS validto
FROM rs_message_outbox_ready INNER JOIN
rs_customer_feed ON rs_customer_feed.rs_customer_feed_id = rs_message_outbox_ready.rs_customer_feed_id INNER JOIN
rs_customer ON rs_message_outbox_ready.rs_customer_id = rs_customer.rs_customer_id
WHERE
(rs_message_outbox_ready.rs_message_sendtime <= GETDATE()) AND
(rs_message_outbox_ready.rs_message_type = 2) AND
(rs_customer_feed.rs_customer_feed_status = 1) AND
(rs_customer.rs_customer_status = 1) AND
(rs_customer_feed.rs_customer_feed_category = 2) AND
(rs_message_outbox_ready.rs_message_status = 5) AND
(rs_customer_feed.rs_customer_feed_xml=1)