如何遍历表以查找数据集?

how to loop thru a table to find data set?(如何遍历表以查找数据集?)
本文介绍了如何遍历表以查找数据集?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我必须以分钟为单位找到订单生命周期的时间差异.即每个订单从收到订单(活动 ID 1)到键入(2)到打印(3)到交付(4)的时间

I have to find the timediff in minutes for a order lifetime. i.e time from order was received(Activity ID 1) to keyed(2) to printed(3) to delivered(4) for each order

例如

我完全迷失了应该采取哪种方法?用例或 if then 语句 ??类似于每个循环遍历每个记录?最有效的方法是什么?

I am completely lost at which approach should i take?? use case or if then statement ?? something like for each to loop thru each record? what should be the most efficient way to do it?

我知道一旦我在正确的变量中获得日期,我就可以使用 DATEDIFF.

i know once i get dates in correct variables i can use DATEDIFF.

declare @received as Datetime, @keyed as DateTime, @printed as Datetime, @Delivered as Datetime, @TurnTime1 as int
Select
IF (tblOrderActivity.ActivityID = 1) SET @received = tblOrderActivity.ActivityDate
---
----
from tblOrderActivity
where OrderID = 1 

它应该告诉我 @TurnTime1 = 48 分钟,因为 orderID 1 从收到(活动 ID 1)到键控(活动 ID 2)花了 48 分钟@TurnTime2 = 29 分钟,因为从键控(活动 ID 2)订购 1 花了 29 分钟) 为每个订单打印(活动 ID 3)等等

it should show me @TurnTime1 = 48 mins as orderID 1 took 48 mins from received(activity id 1) to keyed (activity id 2) @TurnTime2 = 29 mins as it took 29mins for order 1 from keyed(activity id 2) to printed (activity id 3) so on and so forth for each order

推荐答案

您可以通过透视数据轻松完成此操作.可以通过两种方式完成.

You can do this easily by pivoting the data.It can be done in two ways.

1.使用Conditional Aggregate来透视数据.在pivoting 之后,您可以找到不同阶段之间的datediff.试试这个.

1.Use Conditional Aggregate to pivot the data. After pivoting you can find datediff between different stages. Try this.

SELECT orderid,Received,Keyed,Printed,Delivered,
       Datediff(minute, Received, Keyed)    TurnTime1,
       Datediff(minute, Keyed, Printed)     TurnTime2,
       Datediff(minute, Printed, Delivered) TurnTime3
FROM  (SELECT OrderID,
              Max(CASE WHEN ActivityID = 1 THEN ActivityDate END) Received,
              Max(CASE WHEN ActivityID = 2 THEN ActivityDate END) Keyed,
              Max(CASE WHEN ActivityID = 3 THEN ActivityDate END) Printed,
              Max(CASE WHEN ActivityID = 4 THEN ActivityDate END) Delivered
       FROM   Yourtable
       GROUP  BY OrderID)A 

2.使用Pivot转置数据

SELECT orderid,
       [1]                        AS Received,
       [2]                        AS Keyed,
       [3]                        AS Printed,
       [4]                        AS Delivered,
       Datediff(minute, [1], [2]) TurnTime1,
       Datediff(minute, [2], [3]) TurnTime2,
       Datediff(minute, [3], [4]) TurnTime3
FROM   Yourtable
       PIVOT (Max(ActivityDate)
             FOR ActivityID IN([1],[2],[3],[4]))piv 

这篇关于如何遍历表以查找数据集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Creating table with T-SQL - can#39;t see created tables in Object explorer(使用 T-SQL 创建表 - 在对象资源管理器中看不到创建的表)
How to check if VARCHAR strings are (not) hexadecimal?(如何检查 VARCHAR 字符串是否为(非)十六进制?)
Arithmetic overflow error converting IDENTITY to data type int(将 IDENTITY 转换为数据类型 int 的算术溢出错误)
Where clause if there are multiple of the same ID(如果有多个相同的 ID,Where 子句)
Azure SQL: Invalid Object Name using Powershell#39;s quot;Invoke-sqlcmdquot; on Adventureworks(Azure SQL:使用 Powershell 的“Invoke-sqlcmd的无效对象名称在 Adventureworks 上)
How to Convert Table Data into xml format using sql with multiple sub nodes(如何使用具有多个子节点的sql将表数据转换为xml格式)