Mean calculation and compare in sql server 2019

Question

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

Accepted Answer

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

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Mean calculation and compare in sql server 2019

0 additional answers

Your answer