T-SQL Query Help

Transact-SQL
1

Hi Guys,
I need urgent help. I am working on a SQL query. Below is the sample data.

id B_id Station Client B_Date B_time B_rate
1 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:14:00.000 1150.00
2 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:41:00.000 1150.00
3 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:55:30.000 20.00
4 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:28:06.000 1150.00
5 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:38:06.000 20.00
6 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:48:06.000 20.00
7 2030823 BRAVO CASL 2019-05-28 1900-01-01 08:58:06.000 60.00
8 2030823 BRAVO CASL 2019-05-28 1900-01-01 09:15:30.000 1150.00
9 2030823 BRAVO CASL 2019-06-02 1900-01-01 08:14:00.000 2600.00
10 2030823 BRAVO CASL 2019-06-02 1900-01-01 08:44:00.000 20.00

First step:- I want to group by B_id/Station/Client and B_date
Second Step:- Then I want to compare each B_time if It is 30m different then Sum(b_rate) and total count.
Here is the first example
1 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:14:00.000 1150.00
2 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:41:00.000 1150.00
3 2030823 BRAVO CASL 2019-05-27 1900-01-01 08:55:30.000 20.00
Compare Time:- 08:14 == 08:41 = Yes
08:14 == 08:55 = No
08:41 == 08:14 = Yes
08:41 == 08:55 = Yes
08:55 == 08:14 = No
08:55 == 08:41 = NO (BECAUSE WE already compare this one so it should be NO)

Please help me out to how I accomplished this one.

Blockquote

Create table #tbl_Main
(
id int identity(1,1)
,B_id int
,Station varchar(20)
,Client varchar(20)
,B_Date Date
,B_time Datetime
,B_rate Decimal(10,2)

)

Insert into #tbl_Main (B_id,Station,Client,B_Date,B_time,B_rate)
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:55:30.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:41:00.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-05-27 00:00:00.000','1900-01-01 08:14:00.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:58:06.000','60.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:48:06.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:38:06.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 08:28:06.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-05-28 00:00:00.000','1900-01-01 09:15:30.000','1150.00'
Union
Select '2030823','BRAVO','CASL','2019-06-02 00:00:00.000','1900-01-01 08:44:00.000','20.00'
Union
Select '2030823','BRAVO','CASL','2019-06-02 00:00:00.000','1900-01-01 08:14:00.000','2600.00'

Select * from #tbl_Main

Blockquote

2

No sure exactly what you looking for but here is something you could possibly start with
WITH CTE AS (
SELECT *,DATEADD(MINUTE,DATEDIFF(MINUTE,0,CAST(B_Date AS DATETIME)) + DATEDIFF(MINUTE,0,CAST(B_time AS DATETIME)),0) AS FullTime
FROM #tbl_Main)
SELECT Z.B_id,Z.Station,Z.Client,Z.FullTime,SUM(ASum)
FROM (
Select A.B_id,A.Station,A.Client,A.FullTime,(A.B_rate + B.B_rate) AS ASum from CTE A JOIN CTE B ON A.id <> B.id
AND A.B_id = B.B_id
AND A.Station = B.Station
AND A.Client = B.Client
AND DATEDIFF(MINUTE,A.FullTime,B.FullTime) <= 30
AND A.FullTime < B.FullTime) Z
GROUP BY Z.B_id,Z.Station,Z.Client,Z.FullTime;

3

Thank you Lewie for your effort. I will run in to my environment and let you know. Once again thank you for your help.