Need help to give point and set the score
-
Hello,
I've table and data as following,
CREATE TABLE [dbo].[x_SCORE](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[CVID] [int] NOT NULL,
[myGender] [char](1) NULL,
[whatGender] [char](1) NULL,
[point_Gender] [tinyint] NULL,
[score_Gender] [tinyint] NULL,
[myBMI] [decimal](10, 2) NULL,
[min_BMI] [decimal](10, 2) NULL,
[max_BMI] [decimal](10, 2) NULL,
[point_BMI] [tinyint] NULL,
[score_BMI] [tinyint] NULL,
CONSTRAINT [x_SCORE_UQ1] UNIQUE NONCLUSTERED
(
[CVID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[x_SCORE] ON
INSERT [dbo].[x_SCORE] ([idx], [CVID], [myGender], [whatGender], [point_Gender], [score_Gender], [myBMI], [min_BMI], [max_BMI], [point_BMI], [score_BMI]) VALUES (-2147483648, 1449, N'M', N'M', 4, NULL, CAST(23.53 AS Decimal(10, 2)), CAST(20.22 AS Decimal(10, 2)), CAST(30.00 AS Decimal(10, 2)), 3, NULL)
INSERT [dbo].[x_SCORE] ([idx], [CVID], [myGender], [whatGender], [point_Gender], [score_Gender], [myBMI], [min_BMI], [max_BMI], [point_BMI], [score_BMI]) VALUES (-2147483647, 1925, N'F', N'M', 4, NULL, CAST(35.43 AS Decimal(10, 2)), CAST(20.22 AS Decimal(10, 2)), CAST(30.00 AS Decimal(10, 2)), 3, NULL)
SET IDENTITY_INSERT [dbo].[x_SCORE] OFF
So, my table and data must be as following,
This is the calculation for CVID=1449
1- myGender=M
2- whatGender=M
3- point_Gender=4
4- So, score_Gender=4
5- myBMI=23.53
6- min_BMI=20.22
7- max_BMI=30.00
8- point_BMI=3
9- myBMI is between 20.22 and 30.00
10- So, score_BMI=3
This is the calculation for CVID=1925
1- myGender=F
2- whatGender=M
3- point_Gender=4
4- So, score_Gender=0
5- myBMI=35.43
6- min_BMI=20.22
7- max_BMI=30.00
8- point_BMI=3
9- myBMI IS NOT between 20.22 and 30.00
10- So, score_BMI=0
After calculation, my data should be as following,
The variant for each row IS SAME. See as following,
Please help. I'm stuck
-
Unless I'm missing/misunderstanding something, I think this will do it:
UPDATE dbo.x_SCORE
SET
score_Gender = CASE WHEN myGender = whatGender THEN point_Gender ELSE 0 END,
score_BMI = CASE WHEN myBMI BETWEEN min_BMI AND max_BMI THEN point_BMI ELSE 0 END
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply