SQL Server中的每周项目计数 [英] Weekly Item Count in SQL Server

问题描述

尝试计算每周打开一件商品的时间。因此，如果它打开和关闭，我想知道它打开了多长时间。如果关闭日期为null我想使用GetDate（）

Trying to get a count of how long an item is open on a weekly basis. So if it opens and closes I want to know how long it was open. If close date is null I want to use GetDate()

CREATE TABLE [dbo].[TestDates](
    [ItemId] [int] IDENTITY(1,1) NOT NULL,
    [AssignmentName] [nvarchar](max) NULL,
    [AssignedDate] [date] NULL,
    [DueDate] [date] NULL,
    [PId] [int] NULL,
    [FullName] [nvarchar](max) NULL,
    [OpenDate] [date] NULL,
    [CloseDate] [date] NULL,
    [Status] [nvarchar](max) NULL,
 CONSTRAINT [PK_TestDates] PRIMARY KEY CLUSTERED
(
    [ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[TestDates](
    [ItemId] [int] IDENTITY(1,1) NOT NULL,
    [AssignmentName] [nvarchar](max) NULL,
    [AssignedDate] [date] NULL,
    [DueDate] [date] NULL,
    [PId] [int] NULL,
    [FullName] [nvarchar](max) NULL,
    [OpenDate] [date] NULL,
    [CloseDate] [date] NULL,
    [Status] [nvarchar](max) NULL,
 CONSTRAINT [PK_TestDates] PRIMARY KEY CLUSTERED
(
    [ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

推荐答案

您没有指定计数的度量单位。我在下面的例子中使用了MINUTE。使用DATEDIFF，您还可以指定其他度量单位。请参阅 DATEDIFF（Transact-SQL） [ ^ ]

You did not specify the unit of measure for the count. I used MINUTE in the example below. Using DATEDIFF, you can also specify other units of measure. See DATEDIFF (Transact-SQL)[^]

SELECT ItemId,ISNULL(AssignmentName,"") As AssignmentName,DATEDIFF(MINUTE,ISNULL(opendate,GETDATE()),ISNULL(closedate,GETDATE())) AS Minutes_Elapsed FROM testdates;

使用常数进行测试，结果 90 。

SELECT DATEDIFF(minute,ISNULL(''2012-01-01 08:00:00'',GETDATE()),ISNULL(''2012-01-01 09:30:00'',GETDATE())) As Minutes_Elapsed

这篇关于SQL Server中的每周项目计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！