# Mean calculation and compare in sql server 2019

426 Reputation points
2022-08-28T18:45:24.13+00:00

SQL SERVER 2019.

From the below data set how can i calculate :-

The Mean value of (MFR_price-DISTRIBUTOR_cost) than compare with each MFR_price-DISTRIBUTOR_cost and
if Mean value) > each MFR_price-DISTRIBUTOR_cost than 'Performing Good'
if Mean value < each MFR_price-DISTRIBUTOR_cost than 'Occur Loss'
else 'No Gain No Loss'
based on month of Created_date, basically i am trying to analyze whether profit is good or bad for specific month.

Profit = (MFR_price-DISTRIBUTOR_cost)

Data:

``````DECLARE @LOCAL_TABLEVARIABLE TABLE
(
TRACKID VARCHAR (50),
distance INT,
ChannelSource VARCHAR (50),
created_date VARCHAR (50),
delivered_date VARCHAR (50),
actual_arrival VARCHAR (50),
requested_arrival VARCHAR (50),
MFR_price FLOAT,
DISTRIBUTOR_cost FLOAT,
A1_Country  VARCHAR (50),
A2_Country VARCHAR (50)
)

INSERT @LOCAL_TABLEVARIABLE

SELECT 'Z1G510A',91,'sales', '2021-12-09 09:16:09+00','2021-12-15T02:40:39+01:00','2021-12-15 1:27:56','2021-12-15 0:45:00',286.4,320,'DE','DE' UNION ALL
SELECT 'B18EAE2',719,'website', '2022-01-21 13:26:04+00','2022-01-26T12:47:39+01:00','2022-01-26 10:00:00','2022-01-26 10:00:00',1021,1000 ,'DE','DE' UNION ALL
SELECT 'E116A40',451,'website','2022-01-07 13:33:21+00','2022-01-17T10:09:01+01:00','2022-01-14 7:00:00','2022-01-14 7:00:00',585,650,'DE','DE' UNION ALL
SELECT '91H8B6C',113,'website','2022-02-09 11:47:09+00','2022-02-11T09:30:48+01:00','2022-02-11 8:03:21','2022-02-11 17:00:00',201,204,'DE','DE' UNION ALL
SELECT 'S1Z165F',315,'sales', '2021-12-02 08:14:42+00','2021-12-10T10:28:19+01:00','2021-12-10 8:00:00','2021-12-10 13:00:00',500.8,650,'DE','DE' UNION ALL
SELECT 'F1D1308',983,'website', '2021-12-16 11:05:12+00','2021-12-23T13:55:31+01:00','2021-12-23 8:54:14','2021-12-22 16:00:00',0.8,1 ,'DE','FR'UNION ALL
SELECT '21577CA',1052,'website', '2021-12-08 12:05:18+00','2021-12-22T15:01:05+01:00','2021-12-22 8:50:00','2021-12-23 16:00:00',960,1200,'DE','IT' UNION ALL
SELECT 'X1FF3A9',904,'website','2022-01-27 13:44:48+00','2022-01-31T12:30:40+01:00','2022-01-31 12:09:20','2022-01-31 14:00:00',495,330,'DE','HU' UNION ALL
SELECT 'S1V7021',621,'website','2022-02-18 13:04:56+00','2022-02-22T12:18:28+01:00','2022-02-22 10:52:39','2022-02-23 16:04:00',730,680,'DE','DE' UNION ALL
SELECT 'P1J9B33',755,'website','2021-11-24 09:55:07+00','2021-12-07T10:33:04+01:00','2021-12-06 16:00:02','2021-12-06 16:00:00',0.8,1,'DE','CH' UNION ALL
SELECT 'U1Q436B',1139,'referral','2022-01-21 09:30:22+00','2022-01-24T16:01:33+01:00','2022-01-24 13:00:27','2022-01-24 12:00:00',2145,1626,'IT','DE' UNION ALL
SELECT 'U1R9C50',1953, 'website', '2022-01-17 09:33:53+00','2022-02-04T14:41:36+01:00','2022-02-04 11:33:29','2022-02-04 15:00:00',3300,2200,'DE','ES' UNION ALL
SELECT '61C4EC4',14,'sales', '2021-12-02 09:11:49+00','2021-12-10T02:01:06+01:00','2021-12-09 21:30:20','2021-12-10 0:15:00',254.4,310 ,'DE','DE' UNION ALL
SELECT 'N1W909D',1031,'website','2022-02-02 09:50:21+00','2022-02-21T15:03:51+01:00','2022-02-15 8:33:21','2022-02-16 16:00:00',1290,860 ,'RS','PL' UNION ALL
SELECT 'B1X5B6C',1434,'website','2021-12-09 09:46:06+00','2021-12-17T08:31:18+01:00','2021-12-17 7:14:12','2021-12-17 15:00:00',1096,1370 ,'RS','DE' UNION ALL
SELECT '61RC3D6',270,'website','2021-12-13 14:49:32+00','2021-12-22T12:07:31+01:00','2021-12-16 7:00:48','2021-12-16 21:00:00',445.6,557 ,'DE','DE' UNION ALL
SELECT '91T1FDF',618,'website','2022-02-08 08:40:12+00','2022-02-09T13:59:45+01:00','2022-02-09 13:57:13','2022-02-09 15:00:00',420,280 ,'HU','HU' UNION ALL
SELECT 'N1H4578',626,'website','2021-12-07 09:28:58+00','2022-01-03T14:41:53+01:00','2021-12-28 12:00:20','2021-12-28 16:00:00',384,480 ,'DE','PL' UNION ALL
SELECT 'P156CE5',269,'website','2022-02-16 08:38:44+00','2022-02-18T12:59:19+01:00','2022-02-18 7:30:05','2022-02-18 21:00:00',500,500 ,'DE','DE' UNION ALL
SELECT '61U06CE',230,'website','2021-12-31 09:46:21+00','2022-01-04T14:10:22+01:00','2022-01-04 11:24:53','2022-01-04 16:00:00',336,195 ,'PL','PL' UNION ALL
SELECT 'Y1PA1FC',196,'website','2021-12-01 11:54:30+00','2021-12-03T14:01:59+01:00','2021-12-03 7:01:47','2021-12-03 16:00:00',760,950 ,'DE','DE' UNION ALL
SELECT '316381B',132,'sales','2022-01-25 12:00:20+00','2022-02-01T15:41:49+01:00','2022-02-01 15:00:27','2022-02-01 14:45:00',672,300 ,'PL','PL' UNION ALL
SELECT 'H1V067F',891,'website','2022-01-19 12:22:11+00','2022-02-04T13:05:24+01:00','2022-02-03 13:11:06','2022-02-03 16:00:00',1117.5,745 ,'DE','DE' UNION ALL
SELECT 'X1LC768',538,'website','2021-12-06 14:17:16+00','2021-12-09T12:57:54+01:00','2021-12-09 11:30:53','2021-12-08 18:00:00',480,670 ,'RS','RS' UNION ALL
SELECT '81P3D0D',788,'website','2022-02-15 10:18:47+00','2022-02-17T08:43:16+01:00','2022-02-16 14:00:04','2022-02-16 15:00:00',3448.5,2299 ,'DE','DE' UNION ALL
SELECT '71J3EEA',487,'website','2022-01-03 09:04:10+00','2022-01-05T11:30:29+01:00','2022-01-05 7:45:22','2022-01-05 8:00:00',336,195 ,'DE','DE' UNION ALL
SELECT 'K1QAB19',564,'website','2022-02-04 14:58:06+00','2022-02-09T08:17:34+01:00','2022-02-08 15:32:29','2022-02-08 18:00:00',584,600 ,'DE','DE' UNION ALL
SELECT 'Y1YE019',3560,'website','2022-01-19 13:28:51+00','2022-01-22T07:35:27+01:00','2022-01-22 5:40:00','2022-01-22 6:00:00',730,800 ,'RS','PL' UNION ALL
SELECT 'A1M9DE2',599,'website','2022-01-17 12:14:12+00','2022-01-28T13:11:58+01:00','2022-01-28 12:43:37','2022-01-27 16:00:00',5535,5535,'DE','DE'
``````

Expected Result

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,689 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,625 questions

1. 26,791 Reputation points
2022-08-29T06:16:34.06+00:00

Hi @Uma
Please check this query:

``````;WITH CTE AS
(
SELECT *,MFR_price-DISTRIBUTOR_cost AS PROFIT_OR_LOSS
,ROUND(AVG(MFR_price-DISTRIBUTOR_cost)OVER(PARTITION BY 1),2) AS MEAN_VALUE
FROM @LOCAL_TABLEVARIABLE
)
SELECT *,PROFIT_OR_LOSS-MEAN_VALUE AS [PROFIT_OR_LOSS-MEAN_VALUE]
,CASE WHEN PROFIT_OR_LOSS > MEAN_VALUE THEN 'Performing Good'
WHEN PROFIT_OR_LOSS < MEAN_VALUE THEN 'Occur Loss'
ELSE 'No Gain No Loss' END AS REMARKS
FROM CTE
``````

Best regards,
LiHong