Select the results of a calculated column in same sql query

April 12, 2012 at 04:31:17
Specs: Windows XP
Hi,

I have a sql query which has a field named 'Miles' and a calculated field named 'Period'. the results in the 'Period' column are 'Period 1', 'Period 2' and 'Period 3'. Is there a way that I can make three new columns Period 1', 'Period 2' and 'Period 3 and get the sum of 'Miles' for each period in it.

Is it possible to do this in the same query instead of creating views and then querying on it. I am a novice at SQL and any help will be greatly appreciated.

Thanks


See More: Select the results of a calculated column in same sql query

Report •


#1
April 13, 2012 at 06:34:36
Would you post your current code please? Be easier to "get" what you mean...

Thanks a bunch

Elinor Hurst
http://elinorhurst.blogspot.com


Report •

#2
April 16, 2012 at 03:58:59
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 again.


Report •

Related Solutions


Ask Question