Calculated field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculated field

Author

Topic

Tech21
Starting Member

3 Posts

Posted - 2012-04-12 : 07:39:36

My current code is,

SET DATEFORMAT dmy 

SELECT     vw_Transfer_Log.Facility_Code, SUM(vw_Transfer_Log.t_Miles) AS Miles, vw_Transfer_Log.RST, 
                      REF.vw_TLC.description, vw_Transfer_Log.TRAVEL_DATE, vw_Transfer_Log.FER_SOURCE, 
                      dbo.Area.Region, dbo.Area.Locality, dbo.Area.[Facility_Name], vw_Transfer_Log.provcode, 
                      REF.vw_Providers.Organisation_Name, vw_Transfer_Log.Route_Code, CONVERT(varchar(3), 
                      vw_Transfer_Log.TRAVEL_DATE) + '-' + RIGHT(CONVERT(varchar(11), vw_Transfer_Log.TRAVEL_DATE), 2) AS [month/year], 
                      vw_Transfer_Log.Agreement_No, CASE WHEN TRAVEL_DATE BETWEEN '01 / 03 / 2009' AND 
                      '28 / 02 / 2010' THEN 'Rolling Period 1' WHEN TRAVEL_DATE BETWEEN '01 / 03 / 2010' AND 
                      '28 / 02 / 2011' THEN 'Rolling Period 2' WHEN TRAVEL_DATE BETWEEN '01 / 03 / 2011' AND 
                      '28 / 02 / 2012' THEN 'Rolling Period 3' ELSE 'Fail' END AS [Rolling Period]
FROM         vw_Transfer_Log LEFT OUTER JOIN
                      REF.vw_Providers ON vw_Transfer_Log.provcode = REF.vw_Providers.Organisation_code LEFT OUTER JOIN
                      dbo.Area ON vw_Transfer_Log.Facility_Code = dbo.Area.[Practice Code] LEFT OUTER JOIN
                      REF.vw_TLC ON vw_Transfer_Log.Route_Code = REF.vw_TLC.code
WHERE     (vw_Transfer_Log.ROUTE_IND = '5') AND (vw_Transfer_Log.ROUTE_NO = '1' OR
                      vw_Transfer_Log.ROUTE_NO = '01') OR
                      (vw_Transfer_Log.ROUTE_IND = '6') AND (vw_Transfer_Log.ROUTE_NO = '1' OR
                      vw_Transfer_Log.ROUTE_NO = '01')
GROUP BY vw_Transfer_Log.Facility_Code, vw_Transfer_Log.FER_SOURCE, vw_Transfer_Log.ROUTE_IND, 
                      vw_Transfer_Log.ROUTE_NO, REF.vw_TLC.description, vw_Transfer_Log.TRAVEL_DATE, 
                      dbo.Area.Region, dbo.Area.Locality, dbo.Area.[Facility_Name], vw_Transfer_Log.provcode, 
                      REF.vw_Providers.Organisation_Name, vw_Transfer_Log.Route_Code, vw_Transfer_Log.RST, CONVERT(varchar(3), 
                      vw_Transfer_Log.TRAVEL_DATE) + '-' + RIGHT(CONVERT(varchar(11), vw_Transfer_Log.TRAVEL_DATE), 2), 
                      vw_Transfer_Log.Agreement_No
HAVING      (vw_Transfer_Log.FER_SOURCE = '03') AND (vw_Transfer_Log.TRAVEL_DATE BETWEEN CONVERT(DATETIME, 
                      '2009-03-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-02-29 00:00:00', 102)) OR
                      (vw_Transfer_Log.FER_SOURCE = '03') AND (vw_Transfer_Log.TRAVEL_DATE BETWEEN CONVERT(DATETIME, 
                      '2009-03-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-02-29 00:00:00', 102))

I want to be able to add three extra columns 'Rolling Period 1', 'Rolling Period 2' and 'Rolling Period 3' to the result from the above query and SUM 'Miles' underneath these columns, grouped by 'Facility_Code'.

Is there a way to do all this in the same query above?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-12 : 07:55:57

Can you give
example table definition,
example data
wanted result in relation to the example data?

That would be great to make it easier to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

Tech21
Starting Member

3 Posts

Posted - 2012-04-12 : 11:40:22

quote:
Originally posted by webfred

Can you give
example table definition,
example data
wanted result in relation to the example data?

That would be great to make it easier to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi, Sorry about this formatting but didn't know how to align it as a proper table!!

However, in the desired result table, I want it to be grouped by the facility code and the sum of miles across the period 1,2 and 3. Period is a derived/calculated field based on Travel dates.

Current View Result
Facility_code Region Miles Travel_Date Period
81002 Midlands 10 29/02/2012 Period 1
81002 Midlands 24 29/02/2012 Period 2
81002 Midlands 6 29/02/2012 Period 3
81008 Yorkshire 2 29/02/2012 Period 2
81008 Yorkshire 5 29/02/2012 Period 3
81008 Yorkshire 35 29/02/2012 Period 1
81009 Bedfordshire 7 29/02/2012 Period 3
81009 Bedfordshire 56 29/02/2012 Period 3
81009 Bedfordshire 43 29/02/2012 Period 2
81009 Bedfordshire 12 29/02/2012 Period 1
81009 Bedfordshire 34 29/02/2012 Period 1

Desired View Result

Facility_code Region Period 1 Period 2 Period 3
81002 Midlands 10 24 6
81008 Yorkshire 35 2 5
81009 Bedfordshire 34 43 63

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2012-04-12 : 14:01:53

Use a stored procedure

Select Distinct, Facility_code, Region
,Cast('0' as int) as Period1
,Cast('0' as int) as Period2
,Cast('0' as int) as Period3

Into #miles
From Yourtable

Select Facility_code, Region
,Sum(Miles) as Miles,Period
Into #cons
From Yourtable
Group by Facility_code,Region,Period

Update dbo.#miles
Set Period1 = co.Miles
From dbo.#miles mi inner join dbo.#cons co on mi.Facility_code =
co.Facility_code and mi.Region = co.Region
where period = 'Period 1'

Update dbo.#miles
Set Period2 = co.Miles
From dbo.#miles mi inner join dbo.#cons co on mi.Facility_code =
co.Facility_code and mi.Region = co.Region
where period = 'Period 2'

Update dbo.#miles
Set Period3 = co.Miles
From dbo.#miles mi inner join dbo.#cons co on mi.Facility_code =
co.Facility_code and mi.Region = co.Region
where period = 'Period 3'

Select Facility_code, Region
,Period1
,Period2
,Period3
from dbo.#miles

drop table dbo.#miles

Jim
Users <> Logic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-12 : 14:22:25

Here is one way. You might also be able to use a PIVOT:

SET DATEFORMAT DMY
DECLARE @T TABLE (Facility_code INT, Region VARCHAR(100), Miles INT, Travel_Date DATE, Period VARCHAR(100))
INSERT @T
VALUES
(81002, 'Midlands', 10, '29/02/2012', 'Period 1'),
(81002, 'Midlands', 24, '29/02/2012', 'Period 2'),
(81002, 'Midlands', 6, '29/02/2012', 'Period 3'),
(81008, 'Yorkshire', 2, '29/02/2012', 'Period 2'),
(81008, 'Yorkshire', 5, '29/02/2012', 'Period 3'),
(81008, 'Yorkshire', 35, '29/02/2012', 'Period 1'),
(81009, 'Bedfordshire', 7, '29/02/2012', 'Period 3'),
(81009, 'Bedfordshire', 56, '29/02/2012', 'Period 3'),
(81009, 'Bedfordshire', 43, '29/02/2012', 'Period 2'),
(81009, 'Bedfordshire', 12, '29/02/2012', 'Period 1'),
(81009, 'Bedfordshire', 34, '29/02/2012', 'Period 1')


SELECT
	Facility_code,
	Region,
	SUM(CASE WHEN Period = 'Period 1' THEN Miles ELSE NULL END) AS [Period 1],
	SUM(CASE WHEN Period = 'Period 2' THEN Miles ELSE NULL END) AS [Period 2],
	SUM(CASE WHEN Period = 'Period 3' THEN Miles ELSE NULL END) AS [Period 3]
FROM
	@T AS T
GROUP BY
	Facility_code,
	Region

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 02:10:41

I'd go with Lamprey's Solution. It's the simplest way to accomplish what you want.

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Tech21
Starting Member

3 Posts

Posted - 2012-04-13 : 04:27:43

Thanks Lamprey for a possible solution. However, just wanted to know if I need to list/specify VALUES in the query as you have because it is about 100,000 records!! or am I missing something there?

quote:

Originally posted by Lamprey

Here is one way. You might also be able to use a PIVOT:

SET DATEFORMAT DMY
DECLARE @T TABLE (Facility_code INT, Region VARCHAR(100), Miles INT, Travel_Date DATE, Period VARCHAR(100))
INSERT @T
VALUES
(81002, 'Midlands', 10, '29/02/2012', 'Period 1'),
(81002, 'Midlands', 24, '29/02/2012', 'Period 2'),
(81002, 'Midlands', 6, '29/02/2012', 'Period 3'),
(81008, 'Yorkshire', 2, '29/02/2012', 'Period 2'),
(81008, 'Yorkshire', 5, '29/02/2012', 'Period 3'),
(81008, 'Yorkshire', 35, '29/02/2012', 'Period 1'),
(81009, 'Bedfordshire', 7, '29/02/2012', 'Period 3'),
(81009, 'Bedfordshire', 56, '29/02/2012', 'Period 3'),
(81009, 'Bedfordshire', 43, '29/02/2012', 'Period 2'),
(81009, 'Bedfordshire', 12, '29/02/2012', 'Period 1'),
(81009, 'Bedfordshire', 34, '29/02/2012', 'Period 1')


SELECT
	Facility_code,
	Region,
	SUM(CASE WHEN Period = 'Period 1' THEN Miles ELSE NULL END) AS [Period 1],
	SUM(CASE WHEN Period = 'Period 2' THEN Miles ELSE NULL END) AS [Period 2],
	SUM(CASE WHEN Period = 'Period 3' THEN Miles ELSE NULL END) AS [Period 3]
FROM
	@T AS T
GROUP BY
	Facility_code,
	Region

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-04-16 : 09:55:23

You would replace the @T table in the example with your actual table. The important part of the example is the code following the population of the table variable.

Adjust that code as needed for your actual table(s).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources