计算的领域帮助 [英] Calculated feilds Help

查看：83
本文介绍了计算的领域帮助的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Hello Everyone，

Hello Everyone,

需要帮助查询来计算我们已经完成的流程的TAT（周转时间）。此查询中涉及7个表，需要帮助来计算我在下面描述的几个值

Need help with a query to calculate TAT(Turn around time) for a process we have undertaken. There are 7 tables involved within this query and need help to calculate couple of values which I described below

TableName 1 ：Customer_Inforamation - 有客户信息。每个请求有1条记录

TableName 1:Customer_Inforamation – has customer information. Has 1 record per request


  • ReqNo
  • Requestdate
  • Custname
  • requesterid
  • CustCOMMENTS
  • CustREVIEWDT
  • NCMPLTBYid
  • NAPPROVEDBY
  • n状态
  • NTEXT
  • ReqNo
  • Requestdate
  • Custname
  • requesterid
  • CustCOMMENTS
  • CustREVIEWDT
  • NCMPLTBYid
  • NAPPROVEDBY
  • NStatus
  • Ntext

示例数据

Sample Data

123456，' 1/27/2019'，'XYZ'，123，'需要数据直到本月底'，'2/25/201'，2633,2643，'执行'，'执行发布'

123456, ‘1/27/2019’, ‘XYZ’, 123, ‘Need data until end of this month’, ‘2/25/2019’, 2633, 2643, ’executed’, ’executed for release’

897654，'1/24/2019'，'YAM'，432，'数据分析'，02/27/2019'，7821,6542，'执行'，'执行释放'

897654,’1/24/2019’,’YAM’, 432,’Data For Analysis’,02/27/2019’,7821,6542,’Executed’ ,’executed for release’

表名2 ：Customer_Reports - 存储客户已请求并加入customer_information的报告。它可以包含每个客户请求超过1个报告

Table Name 2:Customer_Reports – Stores what reports customer has requested and is joined to customer_information. It can contain more than 1 report per each customer request


  • Reqno
  • Reportno
  • reportcontent
  • reportapproveddt
  • assigntoid

示例数据

Sample Data

123456,71，'A'，'02/24/201'，2643

123456, 71,’A’,’02/24/2019’, 2643

123456,72，'A'，' 02/24/2019'，2643

123456, 72,’A’,’02/24/2019’, 2643

123456,87，'A'，'02/24/201'，2643

123456, 87,’A’,’02/24/2019’, 2643

897654,121 ，'A'，'02/25 / 201'4567

897654,121, ’A’,’02/25/2019’ 4567

897654,222，'A'，'02/25 / 201'4567

897654,222, ’A’,’02/25/2019’ 4567

表名3 ：Report_Info - 存储报告名称并加入customer_reports

Table name 3: Report_Info – Stores the report name and joined to customer_reports


  • Reportno
  • ReportName

示例数据

71，'样品A'

72，'样品B'

87，'样品C'

121，'样本D'

222，'样本E'

<强> <温泉n style ="text-decoration：underline">表4： Customer_Log - 存储有关请求的所有日志记录，并加入customer_information。 


  • ReqNo
  • Event_DateTime
  • Event_type_id

示例数据

Sample Data

123456，'02 / 14 / 2019'，6

123456,’02/14/2019’,6

123456，'02 / 25/2019'，8

123456,’02/25/2019’,8

123456，'02 / 28/2019'，9

123456,’02/28/2019’,9

897654，'02 / 21 / 2019'6

897654,’02/21/2019’6

897654，'02 / 22 / 2019'7

897654,’02/22/2019’7

897654，'02 / 23 / 2019'6

897654,’02/23/2019’6

897654，'02 / 27 / 2019'8

897654,’02/27/2019’8

897654，'03 / 01 / 2019'，9

897654,’03/01/2019’,9

我们更关注事件类型ID 6,7,8和9.如果有事件7那么我们应该再次有6个进程进入启动模式

We are more concerned for events type ids 6,7, 8 and 9. If there is a event 7 then we should again have 6 for the process to be in initiated mode


  • 如果6是最后一个事件，则启动
  • 如果7则拉回
  • 如果执行8
  • 如果9则批准

根据请求的事件6和事件ID 8的客户日志表和event_ datatime字段，我们需要计算

Based on the Customer Log table and event_ datatime field of eventid 6 and event id 8 of a request we need to calculate


  • TAT - 从它的日期开始结束到完成日期

    • 注意：我们应该采用  事件ID的更长日期6或（更新日期报告已批准 表2中的
      和customer_process表中的三个日期（项目3,5,7）的更长日期，即表6
    • TAT – From the date when it was initiated to the date when it was completed
      • Note: We should take the greater date of the event id 6 or (greater date of reportapproveddt  from table 2 and greater date of three dates(Item 3,5,7) in customer_process table which is table 6

      表5 - Customer_Recipients - 存储有关报告收件人的信息，每个请求可包含多条记录。它与Customer_Information一起

      Table 5 – Customer_Recipients - Stores information on the report recipients and can contain more than 1 record per request. It is joined with Customer_Information


      • ReqNo
      • Reciploc
      • 电子邮件
      • 公司名称

      示例数据

      Sample Data

      123456 ，'C'，'Sam @ XYZ.com'，XYZ

      123456,’C’,’Sam@XYZ.com’,XYZ

      123456，'B'，'PAM @ aol.com'AOL

      123456,’B’,’PAM@aol.com’ AOL

      897654，'C'，'Sam @ Yam.com'，YAM

      897654,’C’,’Sam@Yam.com’, YAM

      897654，'B'，'Tree @Yahoo.com'，Yahoo

      897654,’B’,’Tree@Yahoo.com’, Yahoo

      897654，'V'，'Plow @ Gmai.com'Gmail

      897654,’V’,’Plow@Gmai.com’ Gmail

      表6： Customer_Process  
      。在我们发布数据之前，需要验证7个项目。此表已连接到Customer_Information

      Table 6: Customer_Process  .There are 7 items that are needed to be validated before we release data. This table is joined to the Customer_Information


      • ReqNo
      • Processid
      • Needdt
      • Cmpltdt
      • Nadt

      SampleData

      SampleData

      123456,1，'1/1 / 1900'，'02 / 18/201''，'1/1/1900'

      123456,1,’1/1/1900’,’02/18/2019’,’1/1/1900’

      123456,2，'1/1 / 1900'，'02/18/201'，'1/1/1900'

      123456,2,’1/1/1900’,’02/18/2019’,’1/1/1900’

      123456,3，'1/1 / 1900'，'02/21/201'，'1/1/1900'

      123456,3,’1/1/1900’,’02/21/2019’,’1/1/1900’

      123456 ，4，'1/1 / 1900'，'02/18/201'，'1/1/1900'

      123456,4,’1/1/1900’,’02/18/2019’,’1/1/1900’

      123456,5，'1/1 / 1900'，' 1/1/1900''02 / 23/2019'

      123456,5,’1/1/1900’,’1/1/1900’’02/23/2019’

      123456,6，'1/1 / 1900'，'02/18/201'，'1/1/1900 '

      123456,6,’1/1/1900’,’02/18/2019’,’1/1/1900’

      123456,7，'1/1 / 1900'，'02/23/201'，'1/1/1900'

      123456,7,’1/1/1900’,’02/23/2019’,’1/1/1900’

      897654,1，'1/1 / 1900'，'02/18/201'，'1/1/1900'

      897654,1,’1/1/1900’,’02/18/2019’,’1/1/1900’

      897654,2，'1/1 / 1900'， '02 / 18/2019'，'1/1/1900'

      897654,2,’1/1/1900’,’02/18/2019’,’1/1/1900’

      8976543，'1/1 / 1900'，'02/26/201'，'1/1/1900 '

      8976543,’1/1/1900’,’02/26/2019’,’1/1/1900’

      897654,4，'1/1 / 1900'，'02/18/201'，'1/1/1900'

      897654,4,’1/1/1900’,’02/18/2019’,’1/1/1900’

      897654,5'1 / 1/1900'，'02/24/201'，'1/1/1900'

      897654,5’1/1/1900’,’02/24/2019’,’1/1/1900’

      897654 ，6，'1/1 / 1900'，'02/18/201'，'1/1/1900'

      897654,6,’1/1/1900’,’02/18/2019’,’1/1/1900’

      897654,7，'1/1 / 1900'，' 1/1/1900''02 / 24/2019'

      897654,7,’1/1/1900’,’1/1/1900’’02/24/2019’

      表7 - 联系方式 - 此表已通过联系

      Table 7 – Contact – This table is joined to other tables in our db by contacted


      • Contact_id
      • FName
      • LName
      • E

      示例数据

      Sample Data

      2633，'One'，'Tre'

      2633, ‘One’, ‘Tre’

      2643，'Two'，'Hammer'

      2643,’Two’, ‘Hammer’

      7821， '三'湖'

      7821,’Three’ ‘Lake’

      6542，'四'，'黄'

      6542,’Four’,’Yellow’

      2643，'五'，'阿尔法'

      2643,’Five’,’Alpha’

      4567，'六，'Beta'

      4567,’Six,’Beta’

      查询

      Select CI.ReqNo, CI.Requestdate, CI.Custname, CI.requesterid, CI.CustCOMMENTS, CI.CustREVIEWDT, CI.NCMPLTBYid, C.FName + C. LName [Completed By] ,CI.NAPPROVEDBY, C1.FName + C1. LName [Approved By], CI.NStatus, CI.Ntext,CR.Reportno, CR.reportcontent, CR.reportapproveddt, CR.assigntoid, C2.FName + C2. LName [Analyst Name],RI.ReportName,

TAT,totalTAT 

From Customer_information CI inner join customer_reports CR On CI.Reqno = CR.Reqno

Inner join report_info RI on CR.reportno = RI.reportno

Inner join Customer_Recipients CD on CI.Reqno = CD.Reqno 

Inner join contact C on CI. NCMPLTBYid  = C.Contact_id

Inner join contact C1 on CI. NAPPROVEDBY= C1.Contact_id

Inner join contact C2 on CR. assigntoid= C2.Contact_id







      第一次请求的Reslut设置

      Reslut Set for the first request

      123456，'1/27/2019'， 'XYZ'，123，'需要数据直到本月底'，'2/25/2019'，2633，Onetre，2643，TwoHammer，'执行'，'执行释放'，71，'A'，'02 / 24/2019'，2643，OneTre，'样品A'，1,14，

      123456, ‘1/27/2019’, ‘XYZ’, 123, ‘Need data until end of this month’, ‘2/25/2019’, 2633,Onetre, 2643,TwoHammer, ’executed’, ’executed for release’,71,’A’,’02/24/2019’, 2643,OneTre,’Sample A’,1,14,

      123456，'1/27/2019'，'XYZ'，123，'需要数据，直到本月末'，'2/25/2019'，2633，Onetre，2643，TwoHammer，'执行'，'执行释放'，71，'A'，'02/24/201'，2643，OneTre， '样本B'，1,11，

      123456, ‘1/27/2019’, ‘XYZ’, 123, ‘Need data until end of this month’, ‘2/25/2019’, 2633,Onetre, 2643,TwoHammer, ’executed’, ’executed for release’,71,’A’,’02/24/2019’, 2643,OneTre,’Sample B’,1,11,

      TAT - 表4中所有日期中最大的一个，来自表2的reportapproveddt和customer_process是02/24/2019。活动8的完成日期是02/25/2019。因此TAT变为1

      TAT - The greatest of all the dates initiateddate from table 4, reportapproveddt from table 2 and customer_process is 02/24/2019 . The completed date for event 8 is 02/25/2019 . So TAT becomes 1

      Total TAT - Initiatedadte是02/14/2019并且完成日期是02/25/2019所以TotalTAT变为11

      Total TAT - Initiatedadte is 02/14/2019 and completeddate is 02/25/2019 so TotalTAT becomes 11

      Vamsi

      推荐答案

      嗨Vamsi，

      Hi Vamsi,

      感谢您的详细说明。

      根据您的描述，我已将表格结构和示例数据转换为DDL命令和DML命令。此外，我已经添加了计算字段"TAT"，"Total TAT"和"TAT"。到查询语句。请检查它，看它是否适合你。

      Based on your description, I have turned the table structures and sample data into DDL commands and DML command. Also, I have added calculated fields "TAT", "Total TAT" to the query statement. Please check it and see if it works for you.

      create table Customer_Inforamation
(
 ReqNo int,
 Requestdate date,
 Custname varchar(30),
 requesterid int,
 CustCOMMENTS varchar(64),
 CustREVIEWDT date,
 NCMPLTBYid int,
 NAPPROVEDBY int,
 NStatus varchar(64),
 [Ntext] varchar(128)
)

insert into Customer_Inforamation values
(123456, '1/27/2019', 'XYZ', 123, 'Need data until end of this month', '2/25/2019', 2633, 2643, 'executed', 'executed for release'),
(897654,'1/24/2019','YAM', 432,'Data For Analysis','02/27/2019',7821,6542,'Executed' ,'executed for release')
GO


create table Customer_Reports
(
 Reqno int,
 Reportno int,
 reportcontent varchar(20),
 reportapproveddt date,
 assigntoid int
)

insert into Customer_Reports values
(123456, 71,'A','02/24/2019', 2643),
(123456, 72,'A','02/24/2019', 2643),
(123456, 87,'A','02/24/2019', 2643),
(897654,121, 'A','02/25/2019', 4567),
(897654,222, 'A','02/25/2019', 4567)
GO


create table Report_Info
(
 Reportno int,
 ReportName varchar(64)
)

insert into Report_Info values
(71,'Sample A'),
(72,'Sample B'),
(87,'Sample C'),
(121,'Sample D'),
(222,'Sample E')
GO

create table Customer_Log
(
 ReqNo int,
 Event_DateTime date,
 Event_type_id int
)

insert into Customer_Log values
(123456,'02/14/2019',6),
(123456,'02/25/2019',8),
(123456,'02/28/2019',9),
(897654,'02/21/2019',6),
(897654,'02/22/2019',7),
(897654,'02/23/2019',6),
(897654,'02/27/2019',8),
(897654,'03/01/2019',9)
GO


create table Customer_Recipients
(
 ReqNo int,
 Reciploc varchar(30),
 Email varchar(128),
 Companyname varchar(64)
 )

 insert into Customer_Recipients values
 (123456,'C','Sam@XYZ.com','XYZ'),
 (123456,'B','PAM@aol.com', 'AOL'),
 (897654,'C','Sam@Yam.com', 'YAM'),
 (897654,'B','Tree@Yahoo.com', 'Yahoo'),
 (897654,'V','Plow@Gmai.com', 'Gmail')
GO


create table Customer_Process
(
 ReqNo int,
 Processid int,
 Needdt date,
 Cmpltdt date,
 Nadt date
 )

 insert into Customer_Process values
 (123456,1,'1/1/1900','02/18/2019','1/1/1900'),
 (123456,2,'1/1/1900','02/18/2019','1/1/1900'),
 (123456,3,'1/1/1900','02/21/2019','1/1/1900'),
 (123456,4,'1/1/1900','02/18/2019','1/1/1900'),
 (123456,5,'1/1/1900','1/1/1900','02/23/2019'),
 (123456,6,'1/1/1900','02/18/2019','1/1/1900'),
 (123456,7,'1/1/1900','02/23/2019','1/1/1900'),
 (897654,1,'1/1/1900','02/18/2019','1/1/1900'),
 (897654,2,'1/1/1900','02/18/2019','1/1/1900'),
 (897654,3,'1/1/1900','02/26/2019','1/1/1900'),
 (897654,4,'1/1/1900','02/18/2019','1/1/1900'),
 (897654,5,'1/1/1900','02/24/2019','1/1/1900'),
 (897654,6,'1/1/1900','02/18/2019','1/1/1900'),
 (897654,7,'1/1/1900','1/1/1900','02/24/2019')
 GO

create table Contact
(
 Contact_id int,
 FName varchar(64),
 LName varchar(64)
)

insert into Contact values
(2633, 'One', 'Tre'),
(2643,'Two', 'Hammer'),
(7821,'Three', 'Lake'),
(6542,'Four','Yellow'),
(2643,'Five','Alpha'),
(4567,'Six','Beta')

GO


--Query
Select 
CI.ReqNo, 
CI.Requestdate, 
CI.Custname, 
CI.requesterid, 
CI.CustCOMMENTS, 
CI.CustREVIEWDT, 
CI.NCMPLTBYid, 
C.FName + C. LName [Completed By] ,
CI.NAPPROVEDBY, 
C1.FName + C1. LName [Approved By], 
CI.NStatus, 
CI.Ntext,
CR.Reportno, 
CR.reportcontent, 
CR.reportapproveddt, 
CR.assigntoid, 
C2.FName + C2. LName [Analyst Name],
RI.ReportName
--,TAT,totalTAT 
,DATEDIFF(DAY,CR.reportapproveddt,CL.Event_DateTime) AS TAT
,DATEDIFF(DAY,CL2.max_Event_DateTime,CL.Event_DateTime) AS [Total TAT]
From Customer_Inforamation CI 
inner join customer_reports CR On CI.Reqno = CR.Reqno
Inner join report_info RI on CR.reportno = RI.reportno
Inner join Customer_Recipients CD on CI.Reqno = CD.Reqno 

Inner join contact C on CI. NCMPLTBYid  = C.Contact_id
Inner join contact C1 on CI. NAPPROVEDBY= C1.Contact_id
Inner join contact C2 on CR. assigntoid= C2.Contact_id
inner join Customer_Log CL on CI.ReqNo=CL.ReqNo AND CL.Event_type_id=8
inner join (select ReqNo,max(Event_DateTime) as max_Event_DateTime from Customer_Log where Event_type_id=6 group by ReqNo) CL2 on CI.ReqNo=CL2.ReqNo

      最好的问候，

      将


      这篇关于计算的领域帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆