SQL SERVER 2008 calculated field query

Question

I have been trying to write this complex query where I have to calculate a total FTE by prganization by profession by year . So e.g. in org1 for 2011 we have 2 DataEntryC whose total FTE = 50.95+70.95 but now i also have to add 20% of admin staff working for org1 in 2011 FTE to this . And this final figure would be the the total FTE for data entry clarks . Basically within an org in a year total staff FTE = all staff FTE +20% of admin staff FTE . So , for 2011 in Org1 data entry clark should have 141.90 FTE (50.95+70.95 +20)

enter image description here I have written the query as below but this only work when there is only one organization an has 2 professions listed . I think selfjoin might be the answer ......

update test
        set calculated_adjustment= (select calculated_adjustment from test where profession='Admin')
        where profession='DataEntryC'

Scripts:

CREATE TABLE [dbo].[test](
    [year] [varchar](5) NULL,
    [org_name] [varchar](10) NULL,
    [profession] [varchar](10) NULL,
    [fte] [float] NULL,
    [calculated_adjustment] [float] NULL
) 
GO


GO
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'DataEntryC', 50.95, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'DataEntryC', 70.95, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'Admin', 200, 20)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org2', N'Admin', 150, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org1', N'DataEntryC', 20, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'Admin', 15, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'Support', 50, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'Support', 100, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'Support', 200, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'Admin', 150, NULL)

for 2011 in Org1 data entry clark should have 141.90 FTE

If you gave a small SQL script to set up the table with the sample data you've shown, someone might be willing to try answering. — Andrew Morton, Dec 15 '14 at 20:43
Have added the script . Thanks so much for offering to help . — user3844877, Dec 15 '14 at 20:54
Ah, I didn't say *I* would be able to help, but it will make it easier if someone skilled in SQL wants to answer ;) — Andrew Morton, Dec 15 '14 at 21:35

score 0 · Accepted Answer · edited May 23 '17 at 12:20

Your sample data wasn't quite enough for me to reckon I had an answer (i.e. this could still be wrong - more extensive testing is needed), so I used this data:

INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'DataEntryC', 50.95, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'DataEntryC', 70.95, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'Admin', 200, 20)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org2', N'Admin', 150, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org1', N'DataEntryC', 20, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'Admin', 15, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'Support', 50, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2011', N'org1', N'Support', 100, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'Support', 200, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'Admin', 150, 15)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'DataEntryC', 73.27, NULL)
INSERT [dbo].[test] ([year], [org_name], [profession], [fte], [calculated_adjustment]) VALUES (N'2012', N'org2', N'DataEntryC', 20, NULL)

Then I did some research and came up with:

;WITH x (yr, prof, org, fteSum) AS
(
SELECT DISTINCT
    T.year AS yr
    , T.profession AS prof
    , T.org_name AS org
    , SUM(T.fte) OVER (PARTITION BY T.year)
        +
        (
        SELECT SUM(COALESCE(calculated_adjustment, 0))
        FROM test
        WHERE T.year = year
        AND T.org_name = org_name
        AND profession = 'Admin'
        ) AS fteSum
FROM test AS T
JOIN test AS S
ON S.year = T.year
WHERE T.profession = 'DataEntryC'
GROUP BY T.year, T.org_name, T.fte, T.profession
)

--select yr, prof, org, fteSum FROM x

UPDATE test
SET calculated_adjustment = x.fteSum
FROM x
where test.year = x.yr
AND test.org_name = x.org
AND test.profession = 'DataEntryC'

which gives

year    org_name    profession  fte calculated_adjustment
2011    org1    DataEntryC  50.95   141.9
2011    org1    DataEntryC  70.95   141.9
2011    org1    Admin   200 20
2011    org1    Support 50  NULL
2011    org1    Support 100 NULL
2011    org2    Admin   150 NULL
2012    org1    DataEntryC  20  NULL
2012    org2    Admin   15  NULL
2012    org2    Support 200 NULL
2012    org2    Admin   150 15
2012    org2    DataEntryC  73.27   128.27
2012    org2    DataEntryC  20  128.27

Which might be the result you're after.

Please note the commented-out SELECT. That is what I used to make sure the WITH thingy (a.k.a. a temporary named result set, known as a common table expression (CTE)) was returning the correct result, a technique I gleaned from an answer by Aaron Bertrand.

SQL SERVER 2008 calculated field query

1 Answers1