SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ViewSell (
  id int,
  branchId int,
  totalPrice float,
  createdAt datetime
)

INSERT ViewSell (id, branchId, totalPrice, createdAt)
VALUES
(90, 7, 470.05, '2021-3-15 15:17:22.838'),
(44, 4, 9385.97, '2024-1-18 11:19:4.46'),
(0, 7, 2544.78, '2021-9-5 8:34:7.882'),
(75, 8, 5659.85, '2023-1-6 17:29:10.543'),
(97, 5, 8834.62, '2024-8-10 17:38:26.334'),
(18, 7, 8535.16, '2021-3-11 4:19:46.668'),
(14, 5, 2255.84, '2021-7-16 6:47:48.119'),
(66, 0, 277.91, '2024-8-12 11:40:37.236'),
(85, 2, 8953.03, '2021-8-15 11:16:34.627'),
(81, 7, 2054.47, '2024-1-11 16:21:4.719'),
(47, 0, 8552.61, '2020-2-18 12:27:50.538'),
(74, 6, 4958.63, '2020-1-27 20:4:36.271'),
(99, 2, 7722.43, '2023-4-14 21:22:38.160'),
(36, 8, 3520.35, '2020-3-5 16:56:7.28'),
(54, 1, 3862.86, '2022-7-22 5:18:28.552'),
(86, 3, 2249.13, '2022-3-6 19:48:6.41'),
(0, 6, 3110.43, '2023-8-12 19:26:51.588'),
(84, 3, 494.11, '2024-2-9 4:27:5.29'),
(96, 7, 3695.55, '2023-10-14 12:32:57.228'),
(98, 1, 4426.17, '2022-7-21 17:35:31.618'),
(64, 0, 553.23, '2021-9-4 7:23:10.244'),
(90, 9, 5266.13, '2024-1-16 2:10:5.518'),
(70, 1, 5668.82, '2022-7-22 6:12:33.866'),
(54, 8, 8485.07, '2021-9-14 6:5:39.673'),
(80, 1, 1523.62, '2022-7-7 4:38:29.314'),
(53, 9, 3704.05, '2020-4-6 5:32:7.587'),
(42, 9, 8103.65, '2020-7-25 2:24:53.888'),
(27, 0, 9474.71, '2024-11-7 12:18:10.182'),
(53, 7, 1035.57, '2020-11-27 2:19:9.342'),
(16, 2, 7470.38, '2023-1-22 18:33:7.164'),
(8, 9, 6470.03, '2024-11-26 20:58:57.295'),
(48, 0, 7113.78, '2021-10-12 2:12:31.884'),
(52, 5, 4022.33, '2022-11-24 15:34:51.683'),
(8, 7, 106.96, '2021-8-25 20:45:28.980'),
(31, 6, 8473.09, '2020-11-2 13:58:35.463'),
(66, 7, 604.39, '2020-3-14 19:6:16.400'),
(74, 3, 1532.30, '2021-8-14 7:8:23.303'),
(81, 2, 4517.39, '2023-4-25 11:25:17.9'),
(20, 6, 8609.50, '2022-3-2 4:3:34.740'),
(26, 8, 8721.52, '2022-2-14 17:7:5.955'),
(31, 8, 7259.79, '2024-5-15 8:40:53.259'),
(51, 6, 6607.85, '2023-11-13 16:50:58.717'),
(55, 2, 9343.17, '2023-11-17 8:56:9.87'),
(56, 6, 5805.42, '2023-2-4 4:33:2.188'),
(28, 2, 3506.16, '2022-5-20 17:48:44.330'),
(89, 0, 6700.32, '2020-4-9 4:23:31.191'),
(32, 0, 3290.93, '2023-10-5 2:37:26.18'),
(73, 0, 2367.87, '2023-10-19 12:28:48.260'),
(0, 6, 6683.25, '2021-2-10 9:31:25.641'),
(43, 7, 8545.16, '2024-1-24 21:43:50.226'),
(69, 5, 8555.00, '2023-4-1 19:11:36.704'),
(46, 2, 6103.08, '2020-8-18 3:58:14.973'),
(26, 1, 3832.69, '2022-7-6 9:8:6.881'),
(6, 6, 2622.51, '2021-1-23 13:18:12.97'),
(94, 2, 1056.38, '2024-1-3 18:36:24.743'),
(70, 8, 3267.14, '2022-8-22 10:5:42.947'),
(17, 0, 8716.07, '2023-7-21 3:8:25.811'),
(85, 9, 4213.33, '2020-9-19 10:28:3.263'),
(20, 0, 4616.86, '2024-6-19 17:24:43.632'),
(52, 4, 7502.97, '2023-11-1 17:49:51.110'),
(82, 0, 331.93, '2023-10-26 17:11:32.607'),
(47, 2, 8434.11, '2024-3-23 9:7:31.744'),
(15, 1, 2500.21, '2022-7-11 9:1:5.182'),
(99, 0, 5389.34, '2022-4-20 1:14:39.798'),
(56, 4, 7337.75, '2023-11-6 15:38:57.566'),
(4, 2, 3164.81, '2022-3-18 4:24:40.84'),
(53, 2, 1913.24, '2021-9-20 17:41:13.794'),
(45, 3, 1091.10, '2021-11-27 12:54:33.598'),
(7, 5, 3983.78, '2020-9-4 3:40:10.558'),
(78, 6, 2553.30, '2021-3-4 10:35:37.931'),
(50, 3, 6514.10, '2020-10-14 1:22:19.452'),
(18, 0, 6883.27, '2021-2-26 13:41:5.307'),
(97, 0, 6671.55, '2020-3-6 10:46:1.629'),
(27, 2, 5957.58, '2023-3-22 3:4:2.688'),
(46, 0, 1816.57, '2021-10-5 3:33:12.846'),
(70, 2, 5562.04, '2023-5-10 8:19:35.20'),
(45, 1, 9188.03, '2022-7-17 5:35:48.56'),
(2, 8, 9196.20, '2023-3-1 1:49:10.780'),
(14, 5, 9923.25, '2022-5-23 1:32:31.368'),
(65, 1, 3653.67, '2022-7-26 8:29:3.586'),
(64, 5, 6162.79, '2024-4-19 21:42:57.155'),
(55, 7, 6825.86, '2024-9-2 11:31:30.372'),
(89, 1, 9569.85, '2022-7-7 4:13:9.230'),
(37, 2, 7256.73, '2022-7-25 20:45:16.498'),
(85, 1, 2718.66, '2022-7-3 8:49:27.727'),
(6, 2, 8132.46, '2021-8-26 21:27:21.628'),
(91, 9, 2859.81, '2024-11-8 4:26:28.291'),
(22, 2, 5589.39, '2020-5-23 15:22:14.703'),
(19, 5, 6783.75, '2022-4-6 20:13:16.12'),
(48, 2, 4905.96, '2020-10-14 23:57:48.679'),
(82, 3, 4417.94, '2022-10-11 20:51:26.886'),
(26, 3, 4255.39, '2022-11-9 17:44:40.10'),
(78, 3, 8839.44, '2023-2-24 14:52:54.686'),
(92, 3, 7304.03, '2023-9-6 6:21:28.291'),
(66, 8, 8079.46, '2021-5-26 11:24:11.181'),
(21, 9, 9733.28, '2022-2-16 4:47:5.343'),
(66, 8, 2620.30, '2021-1-18 7:48:19.30'),
(84, 7, 6034.38, '2022-9-13 15:37:13.152'),
(82, 0, 7872.15, '2023-7-16 22:52:56.912'),
(5, 1, 6874.03, '2022-7-14 23:54:5.59')
100 rows affected

WITH
  timeDifference AS (
    SELECT
      branchId,
      MIN(CAST(createdAt AS DATE)) AS startdate,
      MAX(CAST(createdAt AS DATE)) AS enddate
    FROM
      viewSell
    GROUP BY
      branchid
  ),
  timeFormat AS (
    SELECT
      VS.branchId,
      totalPrice,
      CASE
        WHEN DATEDIFF(MONTH, TD.startdate, TD.enddate) < 1 THEN CAST(VS.createdAt AS DATE)
        WHEN DATEDIFF(YEAR, TD.startdate, TD.enddate) < 1
        OR (
          DATEDIFF(YEAR, TD.startdate, TD.enddate) >= 1
          AND DATEDIFF(MONTH, TD.startdate, TD.enddate) <= 12
        )
        THEN CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, VS.createdAt), 0) AS DATE)
        WHEN DATEDIFF(YEAR, TD.startdate, TD.enddate) >= 1 THEN CAST(DATEADD(yy, DATEDIFF(yy, 0, createdAt), 0) AS DATE)
      END AS timeFrame
    FROM
      ViewSell AS VS
      INNER JOIN timeDifference AS TD ON VS.branchId = TD.branchId
  )
SELECT
  branchId,
  SUM(totalPrice) AS sumTotalPrice,
  timeFrame
FROM
  timeFormat
WHERE
  branchId = 2
GROUP BY
  branchId,
  timeFrame
ORDER BY
  timeFrame ASC


branchId
sumTotalPrice
timeFrame


2
16598.43
2020-01-01

2
18998.73
2021-01-01

2
13927.7
2022-01-01

2
40572.99
2023-01-01

2
9490.49
2024-01-01


WITH
  timeDifference AS (
    SELECT
      branchId,
      MIN(CAST(createdAt AS DATE)) AS startdate,
      MAX(CAST(createdAt AS DATE)) AS enddate
    FROM
      viewSell
    GROUP BY
      branchid
  )
SELECT
  VS.branchId,
  SUM(totalPrice) AS sumTotalPrice,
  CASE
    WHEN DATEDIFF (MONTH, TD.startdate, TD.enddate) < 1 THEN CAST(VS.createdAt AS DATE)
    WHEN DATEDIFF (YEAR, TD.startdate, TD.enddate) < 1
    OR (
      DATEDIFF (YEAR, TD.startdate, TD.enddate) >= 1
      AND DATEDIFF (MONTH, TD.startdate, TD.enddate) <= 12
    ) THEN CAST(
      DATEADD (MONTH, DATEDIFF (MONTH, 0, VS.createdAt), 0) AS DATE
    )
    WHEN DATEDIFF (YEAR, TD.startdate, TD.enddate) >= 1 THEN CAST(
      DATEADD (yy, DATEDIFF (yy, 0, createdAt), 0) AS DATE
    )
  END AS timeFrame
FROM
  ViewSell AS VS
  INNER JOIN timeDifference AS TD ON VS.branchId = TD.branchId
WHERE
  VS.branchId = 2
GROUP BY
  VS.branchId,
  CASE
    WHEN DATEDIFF (MONTH, TD.startdate, TD.enddate) < 1 THEN CAST(VS.createdAt AS DATE)
    WHEN DATEDIFF (YEAR, TD.startdate, TD.enddate) < 1
    OR (
      DATEDIFF (YEAR, TD.startdate, TD.enddate) >= 1
      AND DATEDIFF (MONTH, TD.startdate, TD.enddate) <= 12
    ) THEN CAST(
      DATEADD (MONTH, DATEDIFF (MONTH, 0, VS.createdAt), 0) AS DATE
    )
    WHEN DATEDIFF (YEAR, TD.startdate, TD.enddate) >= 1 THEN CAST(
      DATEADD (yy, DATEDIFF (yy, 0, createdAt), 0) AS DATE
    )
  END
ORDER BY
  timeFrame ASC


branchId
sumTotalPrice
timeFrame


2
16598.43
2020-01-01

2
18998.73
2021-01-01

2
13927.7
2022-01-01

2
40572.99
2023-01-01

2
9490.49
2024-01-01


SELECT
  *
FROM
  ViewSell
WHERE
  branchId = 2
ORDER BY
  createdAt ASC;

SELECT
  *
FROM
  ViewSell
WHERE
  branchId = 4
ORDER BY
  createdAt ASC;

SELECT
  *
FROM
  ViewSell
WHERE
  branchId = 1
ORDER BY
  createdAt ASC


id
branchId
totalPrice
createdAt


22
2
5589.39
2020-05-23 15:22:14.703

46
2
6103.08
2020-08-18 03:58:14.973

48
2
4905.96
2020-10-14 23:57:48.680

85
2
8953.03
2021-08-15 11:16:34.627

6
2
8132.46
2021-08-26 21:27:21.627

53
2
1913.24
2021-09-20 17:41:13.793

4
2
3164.81
2022-03-18 04:24:40.840

28
2
3506.16
2022-05-20 17:48:44.330

37
2
7256.73
2022-07-25 20:45:16.497

16
2
7470.38
2023-01-22 18:33:07.163

27
2
5957.58
2023-03-22 03:04:02.687

99
2
7722.43
2023-04-14 21:22:38.160

81
2
4517.39
2023-04-25 11:25:17.900

70
2
5562.04
2023-05-10 08:19:35.200

55
2
9343.17
2023-11-17 08:56:09.870

94
2
1056.38
2024-01-03 18:36:24.743

47
2
8434.11
2024-03-23 09:07:31.743




id
branchId
totalPrice
createdAt


52
4
7502.97
2023-11-01 17:49:51.110

56
4
7337.75
2023-11-06 15:38:57.567

44
4
9385.97
2024-01-18 11:19:04.460




id
branchId
totalPrice
createdAt


85
1
2718.66
2022-07-03 08:49:27.727

26
1
3832.69
2022-07-06 09:08:06.880

89
1
9569.85
2022-07-07 04:13:09.230

80
1
1523.62
2022-07-07 04:38:29.313

15
1
2500.21
2022-07-11 09:01:05.183

5
1
6874.03
2022-07-14 23:54:05.590

45
1
9188.03
2022-07-17 05:35:48.560

98
1
4426.17
2022-07-21 17:35:31.617

54
1
3862.86
2022-07-22 05:18:28.553

70
1
5668.82
2022-07-22 06:12:33.867

65
1
3653.67
2022-07-26 08:29:03.587

branchId	sumTotalPrice	timeFrame
2	16598.43	2020-01-01
2	18998.73	2021-01-01
2	13927.7	2022-01-01
2	40572.99	2023-01-01
2	9490.49	2024-01-01

id	branchId	totalPrice	createdAt
22	2	5589.39	2020-05-23 15:22:14.703
46	2	6103.08	2020-08-18 03:58:14.973
48	2	4905.96	2020-10-14 23:57:48.680
85	2	8953.03	2021-08-15 11:16:34.627
6	2	8132.46	2021-08-26 21:27:21.627
53	2	1913.24	2021-09-20 17:41:13.793
4	2	3164.81	2022-03-18 04:24:40.840
28	2	3506.16	2022-05-20 17:48:44.330
37	2	7256.73	2022-07-25 20:45:16.497
16	2	7470.38	2023-01-22 18:33:07.163
27	2	5957.58	2023-03-22 03:04:02.687
99	2	7722.43	2023-04-14 21:22:38.160
81	2	4517.39	2023-04-25 11:25:17.900
70	2	5562.04	2023-05-10 08:19:35.200
55	2	9343.17	2023-11-17 08:56:09.870
94	2	1056.38	2024-01-03 18:36:24.743
47	2	8434.11	2024-03-23 09:07:31.743

id	branchId	totalPrice	createdAt
52	4	7502.97	2023-11-01 17:49:51.110
56	4	7337.75	2023-11-06 15:38:57.567
44	4	9385.97	2024-01-18 11:19:04.460

id	branchId	totalPrice	createdAt
85	1	2718.66	2022-07-03 08:49:27.727
26	1	3832.69	2022-07-06 09:08:06.880
89	1	9569.85	2022-07-07 04:13:09.230
80	1	1523.62	2022-07-07 04:38:29.313
15	1	2500.21	2022-07-11 09:01:05.183
5	1	6874.03	2022-07-14 23:54:05.590
45	1	9188.03	2022-07-17 05:35:48.560
98	1	4426.17	2022-07-21 17:35:31.617
54	1	3862.86	2022-07-22 05:18:28.553
70	1	5668.82	2022-07-22 06:12:33.867
65	1	3653.67	2022-07-26 08:29:03.587