How do I created a Calculated column for Fiscal Week in SQL

Question

1.00/5 (2 votes)

See more:

CSS

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.

Posted 22-Dec-14 21:20pm

ZohaibRazaTheDProgrammer

Add a Solution

2 solutions

Solution 1

Hi,
Try this query hopw this will help you.

SQL

--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

Posted 22-Dec-14 21:32pm

syed shanu

Updated 22-Dec-14 21:33pm

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZohaibRazaTheDProgrammer · Accepted Answer · 2014-12-22T21:41:00

Solution 2

Hi community,

Below mentioned Query helped me solve the problem (hope this will help someone else here):

SQL

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

Posted 22-Dec-14 21:41pm

ZohaibRazaTheDProgrammer

Comments

Maciej Los 23-Dec-14 4:32am

Why to do you repeat solution1. It's rude!
And - by the way - you should rate solution1 and accept as an answer!