Query to calculate difference between time only for that particular Date

Question

following are the sample data from table Station

([ID], [Area], [Time_Stamp], [Error], [E_Value])

VALUES

(201, 'hlpw1', '2019-10-02 06:10:00', 'Error 1 Value 1', 186),
(202, 'hlpw1', '2019-10-02 06:30:00', 'Error 1 Value 2', 78),
(203, 'hlpw1', '2019-10-02 06:55:00', 'Error 1 Value 3', 229),
(204, 'hlpw1', '2019-10-02 07:10:00', 'Error 1 Value 4', 175),
(205, 'hlpw1', '2019-10-02 07:40:00', 'Error 1 Value 5', 286),
(222, 'hlpw1', '2019-10-02 16:10:00', 'Error 1 Value 22', 81),
(223, 'hlpw1', '2019-10-02 16:42:00', 'Error 1 Value 23', 259),
(224, 'hlpw1', '2019-10-02 17:10:00', 'Error 1 Value 24', 77),
(233, 'hlpw1', '2019-10-02 21:25:00', 'Error 1 Value 33', 65),
(234, 'hlpw1', '2019-10-02 21:40:00', 'Error 1 Value 34', 113),
(235, 'hlpw1', '2019-10-02 22:50:00', 'Error 1 Value 35', 294),
(236, 'hlpw1', '2019-10-02 23:23:00', 'Error 1 Value 36', 276),
(237, 'hlpw1', '2019-10-02 23:40:00', 'Error 1 Value 37', 190),
(238, 'hlpw1', '2019-10-03 05:15:00', 'Error 1 Value 38', 241),
(239, 'hlpw1', '2019-10-03 06:10:00', 'Error 1 Value 39', 262),
(240, 'hlpw1', '2019-10-03 06:37:00', 'Error 1 Value 40', 80),
(241, 'hlpw1', '2019-10-03 07:10:00', 'Error 1 Value 41', 267),
(256, 'hlpw1', '2019-10-03 16:10:00', 'Error 1 Value 56', 70),
(257, 'hlpw1', '2019-10-03 16:42:00', 'Error 1 Value 57', 94),
(258, 'hlpw1', '2019-10-03 17:10:00', 'Error 1 Value 58', 282),
(267, 'hlpw1', '2019-10-03 21:25:00', 'Error 1 Value 67', 168),
(268, 'hlpw1', '2019-10-03 21:40:00', 'Error 1 Value 68', 258),
(269, 'hlpw1', '2019-10-03 22:50:00', 'Error 1 Value 69', 262),
(270, 'hlpw1', '2019-10-03 23:23:00', 'Error 1 Value 70', 166),
(306, 'hlpw1', '2019-10-04 05:15:00', 'Error 1 Value 106', 115),
(307, 'hlpw1', '2019-10-04 06:10:00', 'Error 1 Value 107', 242),
(308, 'hlpw1', '2019-10-04 06:37:00', 'Error 1 Value 108', 240),
(309, 'hlpw1', '2019-10-04 07:10:00', 'Error 1 Value 109', 94),
(324, 'hlpw1', '2019-10-04 16:10:00', 'Error 1 Value 124', 296),
(325, 'hlpw1', '2019-10-04 16:42:00', 'Error 1 Value 125', 207),
(326, 'hlpw1', '2019-10-04 17:10:00', 'Error 1 Value 126', 153),
(335, 'hlpw1', '2019-10-04 21:25:00', 'Error 1 Value 135', 201),
(336, 'hlpw1', '2019-10-04 21:40:00', 'Error 1 Value 136', 65),
(337, 'hlpw1', '2019-10-04 22:50:00', 'Error 1 Value 137', 70),
(338, 'hlpw1', '2019-10-04 23:23:00', 'Error 1 Value 138', 273),
(339, 'hlpw1', '2019-10-04 23:40:00', 'Error 1 Value 139', 202),
(340, 'hlpw1', '2019-10-05 05:15:00', 'Error 1 Value 140', 60),
(341, 'hlpw1', '2019-10-05 06:10:00', 'Error 1 Value 141', 193),
(342, 'hlpw1', '2019-10-05 06:37:00', 'Error 1 Value 142', 120),
(343, 'hlpw1', '2019-10-05 07:10:00', 'Error 1 Value 143', 149),
(358, 'hlpw1', '2019-10-05 16:10:00', 'Error 1 Value 158', 73),
(359, 'hlpw1', '2019-10-05 16:42:00', 'Error 1 Value 159', 279),
(360, 'hlpw1', '2019-10-05 17:10:00', 'Error 1 Value 160', 279),
(369, 'hlpw1', '2019-10-05 21:25:00', 'Error 1 Value 169', 282),
(370, 'hlpw1', '2019-10-05 21:40:00', 'Error 1 Value 170', 292),
(371, 'hlpw1', '2019-10-05 22:50:00', 'Error 1 Value 171', 139),
(372, 'hlpw1', '2019-10-05 23:23:00', 'Error 1 Value 172', 176),
(373, 'hlpw1', '2019-10-05 23:40:00', 'Error 1 Value 173', 237),
(374, 'hlpw1', '2019-10-06 05:15:00', 'Error 1 Value 174', 146),
(375, 'hlpw1', '2019-10-06 06:10:00', 'Error 1 Value 175', 140),
(376, 'hlpw1', '2019-10-06 06:37:00', 'Error 1 Value 176', 208),
(377, 'hlpw1', '2019-10-06 07:10:00', 'Error 1 Value 177', 261),
(392, 'hlpw1', '2019-10-06 16:10:00', 'Error 1 Value 192', 102),
(393, 'hlpw1', '2019-10-06 16:42:00', 'Error 1 Value 193', 196),
(394, 'hlpw1', '2019-10-06 17:10:00', 'Error 1 Value 194', 165),
(403, 'hlpw1', '2019-10-06 21:25:00', 'Error 1 Value 203', 226),
(404, 'hlpw1', '2019-10-06 21:40:00', 'Error 1 Value 204', 75),
(405, 'hlpw1', '2019-10-06 22:50:00', 'Error 1 Value 205', 195),
(406, 'hlpw1', '2019-10-06 23:23:00', 'Error 1 Value 206', 258),
(407, 'hlpw1', '2019-10-06 23:40:00', 'Error 1 Value 207', 129),
(408, 'hlpw1', '2019-10-07 05:15:00', 'Error 1 Value 208', 279),
(409, 'hlpw1', '2019-10-07 06:10:00', 'Error 1 Value 209', 235),
(410, 'hlpw1', '2019-10-07 06:37:00', 'Error 1 Value 210', 261),
(411, 'hlpw1', '2019-10-07 07:10:00', 'Error 1 Value 211', 279),
(426, 'hlpw1', '2019-10-07 16:10:00', 'Error 1 Value 226', 108),
(427, 'hlpw1', '2019-10-07 16:42:00', 'Error 1 Value 227', 291),
(428, 'hlpw1', '2019-10-07 17:10:00', 'Error 1 Value 228', 196),
(437, 'hlpw1', '2019-10-07 21:25:00', 'Error 1 Value 237', 141),
(438, 'hlpw1', '2019-10-07 21:40:00', 'Error 1 Value 238', 287),
(439, 'hlpw1', '2019-10-07 22:50:00', 'Error 1 Value 239', 227),
(440, 'hlpw1', '2019-10-07 23:23:00', 'Error 1 Value 240', 286),
(441, 'hlpw1', '2019-10-07 23:40:00', 'Error 1 Value 241', 160),
(442, 'hlpw1', '2019-10-08 05:15:00', 'Error 1 Value 242', 147),
(443, 'hlpw1', '2019-10-08 06:10:00', 'Error 1 Value 243', 223),
(444, 'hlpw1', '2019-10-08 06:37:00', 'Error 1 Value 244', 260),
(445, 'hlpw1', '2019-10-08 07:10:00', 'Error 1 Value 245', 279),
(460, 'hlpw1', '2019-10-08 16:10:00', 'Error 1 Value 260', 205),
(461, 'hlpw1', '2019-10-08 16:42:00', 'Error 1 Value 261', 96),
(462, 'hlpw1', '2019-10-08 17:10:00', 'Error 1 Value 262', 275),
(471, 'hlpw1', '2019-10-08 21:25:00', 'Error 1 Value 271', 251),
(472, 'hlpw1', '2019-10-08 21:40:00', 'Error 1 Value 272', 217),
(473, 'hlpw1', '2019-10-08 22:50:00', 'Error 1 Value 273', 234),
(474, 'hlpw1', '2019-10-08 23:23:00', 'Error 1 Value 274', 191),
(475, 'hlpw1', '2019-10-08 23:40:00', 'Error 1 Value 275', 15)

where Time_Stamp is in YYY-MM-dd hh:mm:ss.

I want to calculate the time difference between the first value and second value of time_stamp . so for first instance 2019-10-02 06:30:00 - 2019-10-02 06:10:00= 1200 sec and so on.

1.

I don't want to calculate the difference between the date 2019-10-03 05:15:00 - 2019-10-02 23:40:00. I want to calculate the difference between the time only for that particular date.

2.

I don't want to calculate difference on weekends so in this case 2019-10-04 and 2019-10-05

second part I have achieved with below query but the first part where i am getting difficulties.

SELECT *
  , (case When (((DATEPART(dw, Time_Stamp) + @@DATEFIRST) % 7) NOT IN (0, 1))
      then DATEDIFF( SECOND, Time_Stamp, LEAD(Time_Stamp) OVER (ORDER BY [ID])) 
      else null 
   end) AS [diff] 
FROM [Station] 
WHERE [Time_Stamp] between '20191002 00:00:00.000' and '20191008 23:59:00.000'
ORDER BY [ID] 
OFFSET 0 ROWS

Below is the link for http://sqlfiddle.com/#!18/9b0c2/1

Answer 1

Try this.. I added condition AND CAST(Time_Stamp AS DATE)=CAST(LEAD(Time_Stamp) OVER (ORDER BY [ID]) AS DATE)

SELECT *
  , (case When (((DATEPART(dw, Time_Stamp) + @@DATEFIRST) % 7) NOT IN (0, 1)) AND CAST(Time_Stamp AS DATE)=CAST(LEAD(Time_Stamp) OVER (ORDER BY [ID]) AS DATE)
      then DATEDIFF( SECOND, Time_Stamp, LEAD(Time_Stamp) OVER (ORDER BY [ID])) 
      else null 
   end) AS [diff] 
FROM [Station] 
WHERE [Time_Stamp] between '20191002 00:00:00.000' and '20191008 23:59:00.000'
ORDER BY [ID] 
OFFSET 0 ROWS

Answer 2

Try this,

case When (((DATEPART(dw, Time_Stamp) + @@DATEFIRST) % 7) NOT IN (0, 1) AND DATEDIFF( DAY, Time_Stamp, LEAD(Time_Stamp) OVER (ORDER BY [ID]))=0)
      then DATEDIFF( SECOND, Time_Stamp, LEAD(Time_Stamp) OVER (ORDER BY [ID])) 
      else null 
   end