如何在SQL中为Fiscal Week创建计算列

本文介绍了如何在SQL中为Fiscal Week创建计算列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Hi,

Below mentioned is my Query:

SELECT DPSNumber, StatusDateTime FROM DispatchTool

The Resulst is:

DPSNumber   StatusDateTime
123123526   8/4/14 12:00 AM
123123527   8/5/14 12:00 AM
123123528   8/6/14 12:00 AM
123123529   8/7/14 12:00 AM

I want to add an additional column to this result as shown below:

DPSNumber   StatusDateTime  FiscalWeek
123123526   8/4/14 12:00 AM 27
123123527   8/5/14 12:00 AM 27
123123528   8/6/14 12:00 AM 27
123123529   8/7/14 12:00 AM 27

Fiscal Week for my Organization started on January 31, 2014. 'FiscalWeek' Column should display the Fiscal Week Number calculated using the Date-Time mentioned in 'StatusDateTime' column.

Thanks in Advance.

推荐答案

--create sample table 
CREATE TABLE [dbo].[DispatchTool](
	[DPSNumber] [int] NOT NULL,
	StatusDateTime Datetime
) ON [PRIMARY]


-- Insert Sample Data 

INSERT INTO [TestTB].[dbo].[DispatchTool]
           ([DPSNumber],[StatusDateTime])
     VALUES
           (123123526     ,     '2014-08-4 12:00:00.000')
INSERT INTO [TestTB].[dbo].[DispatchTool]
           ([DPSNumber],[StatusDateTime])
     VALUES
           (123123527     ,     '2014-08-5 12:00:00.000')
 
 INSERT INTO [TestTB].[dbo].[DispatchTool]
           ([DPSNumber],[StatusDateTime])
     VALUES
           (123123528     ,     '2014-08-6 12:00:00.000')
           
INSERT INTO [TestTB].[dbo].[DispatchTool]
           ([DPSNumber],[StatusDateTime])
     VALUES
           (123123529    ,     '2014-08-7 12:00:00.000')  

-- Your FiscalWeek select query.

Declare @officialFiscalWeek datetime='2014-01-31 12:00:00.000'

select  DPSNumber,
		StatusDateTime,
		DATEDIFF(week,@officialFiscalWeek,StatusDateTime) as FiscalWeek 
		FROM
		DispatchTool

SELECT DPSNumber, StatusDateTime
, Datediff(wk, CONVERT(DATE, 'January 31, 2014'), CONVERT(DATE, StatusDateTime)) FiscalWeek

这篇关于如何在SQL中为Fiscal Week创建计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！