如何在sql server中获取gp和totalgp [英] how to get gp and totalgp in sql server

问题描述

您好我对sql server有疑问，

如何根据sql server中的两个表获得例外输出 

CREATE表[dbo]。[目标]（

[Location] [varchar]（50）NULL，

[目标] [int] NULL，

[月] [日期] NULL

）

CREATE TABLE [dbo]。[Master]（

[Date] [date] NULL，

[Employee] [varchar]（50）NULL ，

[GP] [int] NULL，

[Location] [varchar]（50）NULL

）

INSERT [dbo]。[目标]（[位置]，[目标] ，[月]）价值（N'MG Road'，50000，CAST（0xB93D0B00 AS日期））

GO

INSERT [dbo]。[目标]（[地点] ]，[目标]，[月]）价值（N'Madiwala'，60000，CAST（0xB93D0B00 AS日期））

GO

INSER T [dbo]。[目标]（[位置]，[目标]，[月]）价值（N'Silk Board'，30000，CAST（0xB93D0B00 AS日期））

GO

INSERT [dbo]。[目标]（[位置]，[目标]，[月]）价值（N'BTM'，35000，CAST（0xB93D0B00 AS日期））

GO

INSERT [dbo]。[目标]（[位置]，[目标]，[月]）价值（N'MG Road'，55000，CAST（0xBA3D0B00 AS日期））

GO

INSERT [dbo]。[目标]（[位置]，[目标]，[月]）价值（N'Madiwala'，65000，CAST（0xBA3D0B00 AS日期） ）

GO

INSERT [dbo]。[目标]（[位置]，[目标]，[月]）价值（N'Silk Board'，35000，CAST （0xBA3D0B00 AS日期））

GO

INSERT [dbo]。[目标]（[位置]，[目标]，[月]）价值（N'BTM' ，35000，CAST（0xBA3D0B00 AS日期））

GO

INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location] ]）VALUES（CAST（0x283E0B00 AS Date），N'Ram'，2000，N'MG Road'）

GO

INSERT [dbo]。[Master]（ [日期]，[员工]，[GP]，[位置]）价值（CAST（ 0x283E0B00 AS Date），N'Ram'，1800，N'Silk Board'）

GO

INSERT [dbo]。[Master]（[Date]，[Employee] ]，[GP]，[位置]）价值（CAST（0x293E0B00 AS日期），N'Sami'，15000，N'BTM'）

GO

INSERT [ dbo]。[Master]（[Date]，[Employee]，[GP]，[Location]）VALUES（CAST（0x2A3E0B00 AS Date），N'Ram'，2500，N'Silk Board'）

GO

INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location]）VALUES（CAST（0x2A3E0B00 AS Date），N'Ram' ，2500，N'MG Road'）

GO

INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location] ）VALUES（CAST（0x293E0B00 AS Date），N'Sami'，2000，N'BTM'）

GO

INSERT [dbo]。[Master]（[Date] ]，[员工]，[GP]，[位置]）价值（CAST（0x2A3E0B00 AS日期），N'Sami'，19000，N'Madiwala'）

GO

INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location]）VALUES（CAST（0x2E3E0B00 AS Date），N'Ram'，30000，N'MG Road'）

GO

INSER T [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location]）VALUES（CAST（0x2E3E0B00 AS Date），N'Ram'，30000，N'Madiwala'）

GO

INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location]）VALUES（CAST（0x2F3E0B00 AS Date），N'Ram '，25000，N'Madiwala'）

GO

INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location] ）VALUES（CAST（0x2E3E0B00 AS Date），N'Sami'，20000，N'BTM'）

GO

INSERT [dbo]。[Master]（[Date] ]，[员工]，[GP]，[位置]）价值（CAST（0x2E3E0B00 AS日期），N'Sami'，15000，N'Silk Board'）

GO¥ b $ b INSERT [dbo]。[Master]（[Date]，[Employee]，[GP]，[Location]）VALUES（CAST（0x2F3E0B00 AS Date），N'Sami'，15000，N'Silk Board'）




基于以上数据我希望输出如下： 

员工 |位置
| TotalGP |月目标 |目标％

Ram | MG Road
| 3800 | 50000
        | 7.6

Ram | MG Road
| 5000 | 50000
        | 10
$
Ram | Madiwala
| 60000 | 65000
        | 92.30769231

萨米 | Madiwala
| 17000 | 60000
        | 28.33333333

萨米 | Madiwala
| 19000 | 60000
        | 31.66666667

萨米 |丝绸板
| 35000 | 35000
        | 100

sami |丝绸板
| 15000 | 35000
        | 42.85714286

Ram | Madiwala
| 25000 | 65000
        | 38.46153846



$
我尝试过如下：
$


选择员工， date，sum（gp）totalgp from master 



group by 员工，日期 





选择*来自&bbsp
（选择  ;日期，员工，地点，gp，ROW_NUMBER（）结束（分区 员工，日期



bp by gp desc）as rn 

来自主人）a b
其中rn = 1

以上查询未给出预期结果。



目标％：totalgp / monthgoal * 100

月目标：应从月目标表中选择月目标，并且应显示员工产生最大GP的位置的目标（总和GP）在
例如：Ram在MG Road和madiwala工作，但他的GP总和更多在MG Road，所以MG road'Goal在4月份对Ram进行了抨击，但对于5月月Ram的目标&bbsp
总GP定义：

例如Ram 在25-04-18只在MG路上获得了2000，但是在同一天的其他地方，他已经获得了1800额外的奖励，所以我们正在为Ram而不管位置是否为3800，并且
位置列定义

位置 - 员工可能在一个或两个地点工作，但在这里您应该显示他已经获得最大Gp的位置（每月的总Gp，以及
例如：Ram在MG Road和madiwala工作，但他的GP总和更多在MG Road，所以MG路在4月份被显示，但是对于5月月，Ram位置是madiwala  b
你能不能请告诉我如何编写查询以在sql server中执行此任务 

Hi I have one doubt in sql server ,
how to get excepted output based on two tables in sql server 
CREATE TABLE [dbo].[Goal](
[Location] [varchar](50) NULL,
[Goal] [int] NULL,
[Month] [date] NULL
)
CREATE TABLE [dbo].[Master](
[Date] [date] NULL,
[Employee] [varchar](50) NULL,
[GP] [int] NULL,
[Location] [varchar](50) NULL
)
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'MG Road', 50000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Madiwala', 60000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Silk Board', 30000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'BTM', 35000, CAST(0xB93D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'MG Road', 55000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Madiwala', 65000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'Silk Board', 35000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Goal] ([Location], [Goal], [Month]) VALUES (N'BTM', 35000, CAST(0xBA3D0B00 AS Date))
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x283E0B00 AS Date), N'Ram', 2000, N'MG Road')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x283E0B00 AS Date), N'Ram', 1800, N'Silk Board')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x293E0B00 AS Date), N'Sami', 15000, N'BTM')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2A3E0B00 AS Date), N'Ram', 2500, N'Silk Board')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2A3E0B00 AS Date), N'Ram', 2500, N'MG Road')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x293E0B00 AS Date), N'Sami', 2000, N'BTM')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2A3E0B00 AS Date), N'Sami', 19000, N'Madiwala')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Ram', 30000, N'MG Road')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Ram', 30000, N'Madiwala')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2F3E0B00 AS Date), N'Ram', 25000, N'Madiwala')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Sami', 20000, N'BTM')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2E3E0B00 AS Date), N'Sami', 15000, N'Silk Board')
GO
INSERT [dbo].[Master] ([Date], [Employee], [GP], [Location]) VALUES (CAST(0x2F3E0B00 AS Date), N'Sami', 15000, N'Silk Board')


based on above data I want output like below : 
Employe |Location |TotalGP|Month Goal |Goal %
Ram |MG Road |3800 |50000         |7.6
Ram |MG Road |5000 |50000         |10
Ram |Madiwala |60000 |65000         |92.30769231
Sami |Madiwala |17000 |60000         |28.33333333
Sami |Madiwala |19000 |60000         |31.66666667
Sami |Silk board |35000 |35000         |100
sami |Silk board |15000 |35000         |42.85714286
Ram |Madiwala |25000 |65000         |38.46153846


I tried like below :

select employee,date,sum(gp)totalgp from master 

group by  employee,date 



select * from 
(select  date,employee,location,gp,ROW_NUMBER()over(partition  by  employee,date

order by gp desc ) as rn 
from master )a
where rn=1
above query not given expected result.

Goal %: totalgp/monthgoal * 100
Month Goal:Month goal should be selected from the month goal table, and it should displaying the goal of the location in which the employee has yielded maximum GP (sum of GP) in a 
eg : Ram worked in both MG Road and madiwala, but his sum of GP is more in MG Road so MG road'Goal is diplayed against Ram for April month , but for May month Ram's Goal 
Total GP definition:
Eg Ram  on 25-04-18 has gained 2000 only in MG road , but in other location on same day he has gained 1800 extra , so we are diplaying 3800 for Ram irrespective to the location 
Location column definition
Location - The employee might work in one or two locations , but here you should display the location in which he has scored maximum Gp (total Gp of the month in a 
eg : Ram worked in both MG Road and madiwala, but his sum of GP is more in MG Road so MG road is diplayed for April month , but for May month Ram locaion is madiwala 
can you please tell me how to write query to achive this task in sql server 

推荐答案

您的示例数据和输出不匹配。无论如何，试试这个：

Your sample data and outputs do not match. Anyway, try this:

;WITH CTE_TotalGP AS (
	SELECT [Employee], [Location], YEAR([Date]) AS [Year], MONTH([Date]) AS [Month], SUM([GP]) AS [TotalGP]
	FROM [dbo].[Master]
	GROUP BY [Employee], [Location], YEAR([Date]), MONTH([Date])
)

SELECT t.[Employee], t.[Location], t.[Year], t.[Month], t.[TotalGP], g.[Goal] AS [MonthGoal], CAST(t.[TotalGP] * 100 AS decimal(10, 2)) / g.[Goal] 
FROM [dbo].[Goal] AS g
INNER JOIN CTE_TotalGP AS t ON t.[Location] = g.[Location] AND t.[Year] = YEAR(g.[Month]) AND t.[Month] = MONTH(g.[Month])

这篇关于如何在sql server中获取gp和totalgp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！