Solved

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Posted on 2008-10-02
263 Views
(SUM(CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours /
(case when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 2 then 180
when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 3 then 270
when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 2 then 160
when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 3 then 240 end)
ELSE
CASE WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity /
(case when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 2 then 180
when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 3 then 270
when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 2 then 160
when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 3 then 240 end)
ELSE 0 END
END
) AS [Month]

Any other way around this?
How else can I get the value to the right of the "/"
0
Question by:QPR
• 24
• 18
• 2

LVL 19

Expert Comment

You don't give enough of your query to re-write it, so here is an outline.

you need to break the equation down into its separate components, and return the sub-component solution with an inner select statement, of a group of nested selects.

case when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 2 then 180
when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 3 then 270
when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 2 then 160
when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 3 then 240 end

is a sub-component, and

count(distinct tpps.sequencenumber)
may be a sub-component of it.  (can't tell with what you posted)

so it would look something like

``````select Sum(case Hour_Qty_Code when 'Hrs' then FTEhours when 'Qty' then Quantity else 0 end / divisor) as [Month]

from

(

Select Case When ha.AllowanceCode IN

('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','T  OILT') THEN 'Hrs'

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN 'Qty'

else 'NA' end as Hour_Qty_Code,

ha.FTEhours,

ha.Quantity,

case when e.employeecode = '1011' and SeqCount = 2 then 180

when e.employeecode = '1011' and SeqCount = 3 then 270

when e.employeecode <> '1011' and SeqCount = 2 then 160

when e.employeecode <> '1011' and SeqCount = 3 then 240 end as divisor

from

ha

join e on ha.id = e.id

join

(select count(distinct tpps.sequencenumber) SeqCount, id from tpps group by id)

as sqnm on ha.id = sqnm.id

)

as subcomponent
``````
0

LVL 29

Author Comment

Heres the entire query if it helps.
``````select

es.description as 'EmployeeStatus'

, case when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end as StatusID

,

(SUM(CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours /

(case when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 2 then 180

when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 3 then 270

when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 2 then 160

when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 3 then 240 end)

ELSE

CASE WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity /

(case when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 2 then 180

when e.employeecode = '1011' and count(distinct tpps.sequencenumber) = 3 then 270

when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 2 then 160

when e.employeecode <> '1011' and count(distinct tpps.sequencenumber) = 3 then 240 end)

ELSE 0 END

END

)) AS [Month]

FROM HistoricalAllowance ha

inner join transperpaysequence tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

where ha.paysequence in (493,496)

and tpps.periodstart >= @periodstart

and tpps.periodstart <= @periodend

and ha.employeecode = e.employeecode

and tpps.description like 'Stan%'

AND Dim2.Description IN (@division)

AND d.Description IN (@department)

and (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and tpps.closed = '1'

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

group by es.description
``````
0

LVL 19

Expert Comment

I think this is pretty close, if I missed anything it's because I don't understand your desired results.  That shouldn't change the overall approach which is to nest select statements (in this case, three deep) so an aggregate in the 3rd level (count) can be used in the CASE of the second level, which is used in a SUM at the top level.  Hopefully, if my stab gives an error or the wrong results, you can figure it out, but post the error msg and I will take a look.
``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

CASE WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence from transperpaysequence group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

where ha.paysequence in (493,496)

and tpps.periodstart >= @periodstart

and tpps.periodstart <= @periodend

and ha.employeecode = e.employeecode

and tpps.description like 'Stan%'

AND Dim2.Description IN (@division)

AND d.Description IN (@department)

and (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and tpps.closed = '1'

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

)

group by EmployeeStatus

``````
0

LVL 29

Author Comment

Thanks, I get a couple of errors... I'll see if I can spot what they are and report back

Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'CASE'.
Server: Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'as'.
0

LVL 29

Author Comment

fixed, was just one 'case' keyword too many.
brb as they say
0

LVL 29

Author Comment

Server: Msg 156, Level 15, State 1, Line 58
Incorrect syntax near the keyword 'group'.

I can't see this one!
0

LVL 29

Author Comment

inner join                         -- 3RD LEVEL
Is this joining to the sub select? If so can you define a join without an "ON"?
0

LVL 29

Author Comment

it's ok I see the "ON" now.
I can't for the life of me find the syntax error tho!
0

LVL 60

Expert Comment

you left out the end on a case statement and a comma:
``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours END,

CASE WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence from transperpaysequence group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

where ha.paysequence in (493,496)

and tpps.periodstart >= @periodstart

and tpps.periodstart <= @periodend

and ha.employeecode = e.employeecode

and tpps.description like 'Stan%'

AND Dim2.Description IN (@division)

AND d.Description IN (@department)

and (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and tpps.closed = '1'

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

)

group by EmployeeStatus
``````
0

LVL 19

Expert Comment

Oops!  Nice catch chapmandew!

Actually, though, I think this gives the intended result.  I didn't mean to have two CASE statements at line 16 & 17, that was just a cut/paste error.

Man, those New Zealand schedules conflict with my sleep :)

``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours END,

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence from transperpaysequence group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

where ha.paysequence in (493,496)

and tpps.periodstart >= @periodstart

and tpps.periodstart <= @periodend

and ha.employeecode = e.employeecode

and tpps.description like 'Stan%'

AND Dim2.Description IN (@division)

AND d.Description IN (@department)

and (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and tpps.closed = '1'

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

)

group by EmployeeStatus

Open in New Window Select All
``````
0

LVL 60

Expert Comment

Thanks.  I didn't look into any of the logic at all.  Just found some syntax errors and corrected.
0

LVL 29

Author Comment

Server: Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'WHEN'.
Server: Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'as'.
0

LVL 29

Author Comment

fixed that, one of the case statements had an extra "end" but now I'm getting....
Server: Msg 156, Level 15, State 1, Line 48
Incorrect syntax near the keyword 'group'.
0

LVL 19

Expert Comment

Sorry, if your pasting my code verbatim, then line 48 is not part of the code, (in fact, I can't explain how it got pasted along with the code, looks pretty strange), so delete that.

Otherwise, in query analyzer, (just in case you're not aware of this), you can select a range of rows and press F5 and only those rows will execute.  So, if you select line 28 between the () marks, you can execute level 3, and if you select rows 7 thru 45, you can execute levels 3 & 2.
If you get an error at level 2, change line 7 to SELECT *
and comment out lines 8 thru 25 and try it again.  Once you have a select * in use, you can if needed comment out one join at a time, if it gets to that point, to find the offending line.  Some mix of tactics similar to this is faster than staring at the code, looking for commas out of place etc.
0

LVL 29

Author Comment

Yes I had pasted from the code snippet window in your post.
If I delete the offending line "group by" then the error moves up to the previous line and so on.
This occurs up until the query no longer works as referenced fields are wrong as the table/join is not part of the query
0

LVL 29

Author Comment

just to add... I saw the last line in the code window "Open in New Window Select All " and deleted it from the code.
I also added the missing "CASE" from the code in your last post "Oops!  Nice catch chapmandew!"

But I'm left with "Server: Msg 156, Level 15, State 1, Line 47 Incorrect syntax near the keyword 'group'."
If you ignore the last line in the code snippet and add the missing CASE - then that is what I'm currently using.
0

LVL 19

Expert Comment

As I suggested, each level should work individually.
So, this should return a resultset,

SELECT count(distinct sequencenumber) seqCount, paysequence from transperpaysequence group by paysequence

and so should this....

There aren't many possibilities, so approach it systematically.  Comment out the entire where clause, then comment out the entire SELECT .... From and replace with Select * from, etc etc.

``````  SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours END,

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence from transperpaysequence group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

where ha.paysequence in (493,496)

and tpps.periodstart >= @periodstart

and tpps.periodstart <= @periodend

and ha.employeecode = e.employeecode

and tpps.description like 'Stan%'

AND Dim2.Description IN (@division)

AND d.Description IN (@department)

and (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and tpps.closed = '1'

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))
``````
0

LVL 29

Author Comment

yep the first one gives me results.
the second gives me all sorts of problems...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'periodstart'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'periodstart'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'description'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'closed'.

note from your snippet you are missing a couple of things:
CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours END,
WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity
else 0 END

only works if changed to
CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours END,
CASE     WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity
else 0 END

and there is a join missing
left outer join department d
on e.departmentcode = d.departmentcode
0

LVL 29

Author Comment

I can comment out all the where clauses and it runs (giving me 250k rows instead of 6!)
It doesn't like anything in the WHERE clause that refers to the alias tpps
0

LVL 19

Expert Comment

Okay, that's a good start.
Here, you have an extra END and a comma left over from my exchange of posts with chapmandew,  this is what I should have given you the first time.....

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours
WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity
else 0 END

Next, the where clause can't contain any columns from an inner nested select if the columns aren't explicitly part of the nested SELECT.  To fix this, move the where clause phrases into level 3.  You can do this because you are using an INNER JOIN to join level 3, so the net effect will be the same.
Here is the complete level 3 now...

SELECT count(distinct sequencenumber) seqCount, paysequence
from transperpaysequence t3
where
t3.periodstart >= @periodstart and
t3.periodstart <= @periodend and
t3.description like 'Stan%' and
t3.closed = '1'
group by paysequence

Last, I can't help with department table, it has been missing from the beginning :-)
Anyway, I moved that to the end and commented it out, and put the other corrections in, so test this snippet.
And good luck, you're almost there.

``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

--      AND d.Description IN (@department)  --commented out until department join fixed

)

group by EmployeeStatus
``````
0

LVL 29

Author Comment

Have attached the snippet I'm using (join included and parameters replaced with real values - for my benefit so I don't have to keep replacing params when testing)

Still getting the error: Server: Msg 156, Level 15, State 1, Line 52
Incorrect syntax near the keyword 'group'.

Do you get the same error? I'm assuming it will check the syntax (for you) before realising the tables in question don't exist on your system?
``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= '2008-08-04' and

t3.periodstart <= '2008-08-18' and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN ('Finance & Corporate Services')

AND (e.terminationdate is null or e.terminationdate >= ('2008-08-18')) and (e.startdate <= ('2008-08-04'))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN ('Finance')

)

group by EmployeeStatus
``````
0

LVL 19

Expert Comment

No, that's not the way the query analyzer works.

But I found it.  Man, I'm feeling pretty silly, chasing this around.
Level 1 has to have an alias table name.....

) as level_one group by EmployeeStatus

Attached is the original code with the parameters, and the fix and the dept table join added.
``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)

) as level_one

group by EmployeeStatus
``````
0

LVL 29

Author Comment

Don't feel silly! I wish I had your SQL abilities so I could help look for the error rather than just blindly posting back the error messages!
Good news, no errors!
Bad news, "Month" comes back as null :(

There should be no nulls in the data but just in case I'll try isNull() ing the values becasue on the messages tab I got the message: Warning: Null value is eliminated by an aggregate or other SET operation
0

LVL 19

Expert Comment

Don't put too much faith in that Warning,
In the meantime, here is a debug script.  I declared all the parameters, so this should run as a stand-alone query.  There are 4 extra columns in the beginning, 3 columns because it seems to me the most likely explanation is the numerator of the formula for 'empmonth' is zero, which is the else part of the case statement, and 1 column because the level 3 select is the denominator, and if its not 2 or 3 then the denominator is null, because we don't have an else in there to handle that situation.

``````declare @division nvarchar(100)

declare @department nvarchar(100)

declare @periodend datetime

declare @periodstart datetime

set @division = 'Finance & Corporate Services'

set @department = 'Finance'

set @periodstart = '2008-08-04'

set @periodend = '2008-08-18'

SELECT        -- 2ND LEVEL

hha.Quantity, ha.FTEhours, ha.AllowanceCode, tpps.seqCount,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN 'HOURS'

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN 'QUANTITY'

else 'ZERO' END as debug',

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)
``````
0

LVL 19

Expert Comment

change line 14 to
else 'ZERO' END as 'debug',

(I'm not making all these typos, honestly, it's the poltergeists)
0

LVL 29

Author Comment

results

2595.60      .00      901      1      ZERO      Full-Time      1      NULL
8.00      8.00      AL      1      HOURS      Full-Time      1      NULL
-8.00      -8.00      R      1      HOURS      Full-Time      1      NULL
80.00      80.00      R      1      HOURS      Full-Time      1      NULL
2595.60      .00      901      1      ZERO      Full-Time      1      NULL
-16.00      -16.00      R      1      HOURS      Full-Time      1      NULL
80.00      80.00      R      1      HOURS      Full-Time      1      NULL
16.00      16.00      SL      1      HOURS      Full-Time      1      NULL
77.60      77.60      R      1      HOURS      Full-Time      1      NULL
1.60      1.60      R      1      HOURS      Full-Time      1      NULL
.80      .80      R      1      HOURS      Full-Time      1      NULL
77.60      77.60      R      1      HOURS      Full-Time      1      NULL
1.60      1.60      R      1      HOURS      Full-Time      1      NULL
.80      .80      R      1      HOURS      Full-Time      1      NULL
1262.02      .00      901      1      ZERO      Part-Time      2      NULL
.00      5.00      AL      1      HOURS      Part-Time      2      NULL
-5.00      -5.00      R      1      HOURS      Part-Time      2      NULL
50.00      50.00      R      1      HOURS      Part-Time      2      NULL
1262.02      .00      901      1      ZERO      Part-Time      2      NULL
-12.00      -12.00      R      1      HOURS      Part-Time      2      NULL
50.00      50.00      R      1      HOURS      Part-Time      2      NULL
2.00      2.00      SL      1      HOURS      Part-Time      2      NULL
10.00      10.00      SL      1      HOURS      Part-Time      2      NULL
80.00      80.00      R      1      HOURS      Full-Time      1      NULL
80.00      80.00      R      1      HOURS      Full-Time      1      NULL
3807.03      .00      901      1      ZERO      Full-Time      1      NULL
80.00      80.00      R      1      HOURS      Full-Time      1      NULL
3807.03      .00      901      1      ZERO      Full-Time      1      NULL
80.00      80.00      R      1      HOURS      Full-Time      1      NULL
2376.92      .00      901      1      ZERO      Full-Time      1      NULL
40.00      40.00      R      1      HOURS      Full-Time      1      NULL
40.00      40.00      R      1      HOURS      Full-Time      1      NULL
2376.92      .00      901      1      ZERO      Full-Time      1      NULL
40.00      40.00      R      1      HOURS      Full-Time      1      NULL
40.00      40.00      R      1      HOURS      Full-Time      1      NULL
.00      .00      AL8%      1      ZERO      Casual      5      NULL
.00      .00      AL8%      1      ZERO      Casual      5      NULL
0

LVL 19

Expert Comment

Run this.
``````declare @division nvarchar(100)

declare @department nvarchar(100)

declare @periodend datetime

declare @periodstart datetime

set @division = 'Finance & Corporate Services'

set @department = 'Finance'

set @periodstart = '2008-08-04'

set @periodend = '2008-08-18'

SELECT        -- 2ND LEVEL

hha.Quantity, ha.FTEhours, ha.AllowanceCode, e.employeecode, tpps.seqCount,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN 'HOURS'

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN 'QUANTITY'

else 'ZERO' END as 'debug',

es.description as 'EmployeeStatus',

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END as 'numerator',

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END as 'denominator

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)
``````
0

LVL 19

Expert Comment

they are at it again, moving stuff,
``````declare @division nvarchar(100)

declare @department nvarchar(100)

declare @periodend datetime

declare @periodstart datetime

set @division = 'Finance & Corporate Services'

set @department = 'Finance'

set @periodstart = '2008-08-04'

set @periodend = '2008-08-18'

SELECT        -- 2ND LEVEL

hha.Quantity, ha.FTEhours, ha.AllowanceCode, e.employeecode, tpps.seqCount,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN 'HOURS'

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN 'QUANTITY'

else 'ZERO' END as 'debug',

es.description as 'EmployeeStatus',

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END as 'numerator',

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END as 'denominator'

FROM HistoricalAllowance ha

inner join                         -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence) as tpps

on ha.paysequence = tpps.paysequence

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)
``````
0

LVL 29

Author Comment

2595.60      .00      901      289      1      ZERO      Full-Time      .00      NULL
8.00      8.00      AL      289      1      HOURS      Full-Time      8.00      NULL
-8.00      -8.00      R      289      1      HOURS      Full-Time      -8.00      NULL
80.00      80.00      R      289      1      HOURS      Full-Time      80.00      NULL
2595.60      .00      901      289      1      ZERO      Full-Time      .00      NULL
-16.00      -16.00      R      289      1      HOURS      Full-Time      -16.00      NULL
80.00      80.00      R      289      1      HOURS      Full-Time      80.00      NULL
16.00      16.00      SL      289      1      HOURS      Full-Time      16.00      NULL
77.60      77.60      R      336      1      HOURS      Full-Time      77.60      NULL
1.60      1.60      R      336      1      HOURS      Full-Time      1.60      NULL
.80      .80      R      336      1      HOURS      Full-Time      .80      NULL
77.60      77.60      R      336      1      HOURS      Full-Time      77.60      NULL
1.60      1.60      R      336      1      HOURS      Full-Time      1.60      NULL
.80      .80      R      336      1      HOURS      Full-Time      .80      NULL
1262.02      .00      901      511      1      ZERO      Part-Time      .00      NULL
.00      5.00      AL      511      1      HOURS      Part-Time      5.00      NULL
-5.00      -5.00      R      511      1      HOURS      Part-Time      -5.00      NULL
50.00      50.00      R      511      1      HOURS      Part-Time      50.00      NULL
1262.02      .00      901      511      1      ZERO      Part-Time      .00      NULL
-12.00      -12.00      R      511      1      HOURS      Part-Time      -12.00      NULL
50.00      50.00      R      511      1      HOURS      Part-Time      50.00      NULL
2.00      2.00      SL      511      1      HOURS      Part-Time      2.00      NULL
10.00      10.00      SL      511      1      HOURS      Part-Time      10.00      NULL
80.00      80.00      R      693      1      HOURS      Full-Time      80.00      NULL
80.00      80.00      R      693      1      HOURS      Full-Time      80.00      NULL
3807.03      .00      901      791      1      ZERO      Full-Time      .00      NULL
80.00      80.00      R      791      1      HOURS      Full-Time      80.00      NULL
3807.03      .00      901      791      1      ZERO      Full-Time      .00      NULL
80.00      80.00      R      791      1      HOURS      Full-Time      80.00      NULL
2376.92      .00      901      966      1      ZERO      Full-Time      .00      NULL
40.00      40.00      R      966      1      HOURS      Full-Time      40.00      NULL
40.00      40.00      R      966      1      HOURS      Full-Time      40.00      NULL
2376.92      .00      901      966      1      ZERO      Full-Time      .00      NULL
40.00      40.00      R      966      1      HOURS      Full-Time      40.00      NULL
40.00      40.00      R      966      1      HOURS      Full-Time      40.00      NULL
.00      .00      AL8%      1022      1      ZERO      Casual      .00      NULL
.00      .00      AL8%      1022      1      ZERO      Casual      .00      NULL
0

LVL 19

Expert Comment

Ugh!
i was reading the wrong column.  I saw 2's in the StatusID column and thought that was the seqCount column.

You are getting nulls because this snippet never returns a count() greater than 1, and your case in level 2 is
case
when e.employeecode  = '1011' and tpps.seqCount = 2 then 180
when e.employeecode  = '1011' and tpps.seqCount = 3 then 270
when e.employeecode <> '1011' and tpps.seqCount = 2 then 160
when e.employeecode <> '1011' and tpps.seqCount = 3 then 240
END as 'denominator

So seqCount must be either 2 or 3 and the case has no else clause.
``````SELECT count(distinct sequencenumber) seqCount, paysequence

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

group by paysequence
``````
0

LVL 29

Author Comment

Strange. I'm either misunderstanding, have put in wrong parameters or the data is wrong.
The count represents (fortnightly) pays within a given month. So 1 doesn't compute!
I'll have a look.
0

LVL 19

Expert Comment

The start and end dates are 14 days apart.  I would check to see if there is a time portion to the column
transperpaysequence.periodstart
so if it was '2008-08-18 14:33:00'
then it would be greater than @periodend and you wouldn't get two pay runs.
0

LVL 29

Author Comment

I wondered that (the parameters actually come from a drop down which are a list of actual pay dates) so I widened the start and end dates by 1 day either way.
Denominator is still NULL for all fields
0

LVL 29

Author Comment

But hold fire. You are right if I add an else 160 then the denomiator column is all 160.
I ran:
SELECT count(distinct sequencenumber) seqCount, paysequence
from transperpaysequence t3
where
t3.periodstart >= '2008-08-05' and
t3.periodstart <= '2008-08-19' and
t3.description like 'Stan%' and
t3.closed = '1'
group by paysequence

and only get 1.
Let me check the data and get back.
0

LVL 29

Author Comment

when I go back to the original report/query (that I inherited) and run this extratc

select count(distinct tpps2.sequencenumber) from transperpaysequence tpps2
where (tpps2.periodstart >= ('2008-08-04')) and (tpps2.periodstart <= ('2008-08-18')) and (tpps2.description like 'Stan%') and (tpps2.closed = '1')
I get 2.

0

LVL 19

Expert Comment

We certainly have ....
this code doesn't make sense to me, its from the original.

inner join transperpaysequence tpps
on ha.paysequence = tpps.paysequence

It would appear to result in a one to many link, one row in ha linking to two rows in tpps. Then, count(distinct tpps.sequencenumber) = 2.

Okay so far, then in the level 3 select, I use a
group by paysequence
statement to pre-process the count, so the same join

inner join transperpaysequence tpps
on ha.paysequence = tpps.paysequence

is now a one to one link.  What I think that tells me is in your version,

select count(distinct tpps2.sequencenumber) from transperpaysequence tpps2

there are two distinct sequencenumbers but each has a different paysequence value.  So, if they have different paysequence values, then my version will not add the counts together (because of the group by) and I return 1 as my count.  Okay, but that means

inner join transperpaysequence tpps
on ha.paysequence = tpps.paysequence

doesn't make sense.  Something's wrong with my assumptions here.

If you only want the count of pay runs, then there has to be some distinguishing mark to tell if an employee is in 2 runs or 3 runs between the dates.  Does that mean this is joining on the wrong columns?
0

LVL 29

Author Comment

In answer to all of the above.... pass!  :)
I inherited this from a consultant. When our HR dept looked at it they realised the report was not quite there. By this stage the consultant had moved on.
So I got it!
Yes only the count of pay runs as this is what the total hours are divided by. This number should be shared by all employees for a given time period unless you are employee number 1011 (no idea why).

On the report are 2 drop downs. Each drop down contains identical dates. A list of all the pay dates so far this year.
The user picks 2 dates as their criteria (@periodstart and @periodend) and we then count the distinct pay sequences enveloped between (and including) those dates.
We take the sum of quantity/ftehours and divide this by the number of paysequences (count) to give us the hours worked per pay period.

0

LVL 19

Expert Comment

Ignoring for the time being special agent 00-1011....

"We take the sum of quantity/ftehours and divide this by the number of paysequences (count) to give us the hours worked per pay period."

looks like it should say,
"We take the sum of quantity/ftehours and, depending on the paysequences (count),
divide by 160 or 240 to give us the hours worked per pay period."

That looks like someone want the equivalent full-time man-months worked, or similar statistic.
Anyway, and again setting agent 1011 on the shelf,
we can simulate this by changing the level 3 join.  By using a cross join, the single row that level 3 returns is joined to every employee, and by taking out the group by, we should get a 2 now.  The only issue, is that its indiscriminate, every employee gets the same pay run count.

I'm off for the night.....

``````declare @division nvarchar(100)

declare @department nvarchar(100)

declare @periodend datetime

declare @periodstart datetime

set @division = 'Finance & Corporate Services'

set @department = 'Finance'

set @periodstart = '2008-08-04'

set @periodend = '2008-08-18'

SELECT        -- 2ND LEVEL

hha.Quantity, ha.FTEhours, ha.AllowanceCode, e.employeecode, tpps.seqCount,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN 'HOURS'

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN 'QUANTITY'

else 'ZERO' END as 'debug',

es.description as 'EmployeeStatus',

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END as 'numerator',

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END as 'denominator'

FROM HistoricalAllowance ha

cross join                         -- 3RD LEVEL   -- changed to a cross join

(SELECT count(distinct sequencenumber) seqCount

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

) as tpps                                  -- removed the on clause

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)

``````
0

LVL 29

Author Comment

yes sorry, you are correct.. depending on the count then divide by 160 or 240.
Looks great (have pasted the results to the code snippet window). I also pushed out the periodend date to incorporate another fortnight and the results jumped from 160 to 240. One thing.... we seem to have gained some extra rows. Did we lose a group by or will this go back to the expected number when we remove debugging columns? The original report returns 7 rows. Perhaps we are seeing one row per employee each time a join is satisfied.

Have a good night. Thank you very much for sticking with this, you've been an amazing help. Over and above the call of EE duty!
``````2595.60	.00	901	289	2	ZERO	Full-Time	.00	160

8.00	8.00	AL	289	2	HOURS	Full-Time	8.00	160

-8.00	-8.00	R	289	2	HOURS	Full-Time	-8.00	160

80.00	80.00	R	289	2	HOURS	Full-Time	80.00	160

2595.60	.00	901	289	2	ZERO	Full-Time	.00	160

-16.00	-16.00	R	289	2	HOURS	Full-Time	-16.00	160

80.00	80.00	R	289	2	HOURS	Full-Time	80.00	160

16.00	16.00	SL	289	2	HOURS	Full-Time	16.00	160

77.60	77.60	R	336	2	HOURS	Full-Time	77.60	160

1.60	1.60	R	336	2	HOURS	Full-Time	1.60	160

.80	.80	R	336	2	HOURS	Full-Time	.80	160

77.60	77.60	R	336	2	HOURS	Full-Time	77.60	160

1.60	1.60	R	336	2	HOURS	Full-Time	1.60	160

.80	.80	R	336	2	HOURS	Full-Time	.80	160

1262.02	.00	901	511	2	ZERO	Part-Time	.00	160

.00	5.00	AL	511	2	HOURS	Part-Time	5.00	160

-5.00	-5.00	R	511	2	HOURS	Part-Time	-5.00	160

50.00	50.00	R	511	2	HOURS	Part-Time	50.00	160

1262.02	.00	901	511	2	ZERO	Part-Time	.00	160

-12.00	-12.00	R	511	2	HOURS	Part-Time	-12.00	160

50.00	50.00	R	511	2	HOURS	Part-Time	50.00	160

2.00	2.00	SL	511	2	HOURS	Part-Time	2.00	160

10.00	10.00	SL	511	2	HOURS	Part-Time	10.00	160

80.00	80.00	R	693	2	HOURS	Full-Time	80.00	160

80.00	80.00	R	693	2	HOURS	Full-Time	80.00	160

3807.03	.00	901	791	2	ZERO	Full-Time	.00	160

80.00	80.00	R	791	2	HOURS	Full-Time	80.00	160

3807.03	.00	901	791	2	ZERO	Full-Time	.00	160

80.00	80.00	R	791	2	HOURS	Full-Time	80.00	160

2376.92	.00	901	966	2	ZERO	Full-Time	.00	160

40.00	40.00	R	966	2	HOURS	Full-Time	40.00	160

40.00	40.00	R	966	2	HOURS	Full-Time	40.00	160

2376.92	.00	901	966	2	ZERO	Full-Time	.00	160

40.00	40.00	R	966	2	HOURS	Full-Time	40.00	160

40.00	40.00	R	966	2	HOURS	Full-Time	40.00	160

.00	.00	AL8%	1022	2	ZERO	Casual	.00	160

.00	.00	AL8%	1022	2	ZERO	Casual	.00	160
``````
0

LVL 19

Expert Comment

Here you go, caught me before I left....  The 7 rows are at level one, we been debugging level two.  We could have used a corollated sub-query, now that its all said and done, but we would have never debugged that.

Tom
``````SELECT   -- 1ST LEVEL of nested selects

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT        -- 2ND LEVEL

es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

cross join                         -- 3RD LEVEL   -- changed to a cross join

(SELECT count(distinct sequencenumber) seqCount

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

) as tpps                                  -- removed the on clause

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)

) as level_one

group by EmployeeStatus
``````
0

LVL 29

Author Comment

Great! looks like I'm just about there.
One more thing before I leave you in peace :)

I need a couple of other items in the select list but I can't seem to use the table alias' within the sub selects.

dim2.dim2code
, d.departmentcode
, d.description as Department
, e.employeecode
, e.preferredname + ' ' + e.lastname as 'Employee Name'
, es.description as 'EmployeeStatus'

Server: Msg 107, Level 16, State 3, Line 12
The column prefix 'dim2' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 12
The column prefix 'd' does not match with a table name or alias name used in the query.
etc etc

The above code returns...
Casual      5      .0000000000000
Full-Time      1      5.0000000000000
Part-Time      2      .6250000000000

I need to include the extra select itrems so i get the results in the snippet window.
Real names have been replaced with xxx

``````FIN	Finance & Corporate Services	FINANCE	Finance	289	xxx	Full-Time	1	1.000000

FIN	Finance & Corporate Services	FINANCE	Finance	336	xxx	Full-Time	1	1.000000

FIN	Finance & Corporate Services	FINANCE	Finance	693	xxx	Full-Time	1	1.000000

FIN	Finance & Corporate Services	FINANCE	Finance	791	xxx	Full-Time	1	1.000000

FIN	Finance & Corporate Services	FINANCE	Finance	966	xxx	Full-Time	1	1.000000

FIN	Finance & Corporate Services	FINANCE	Finance	511	xxx	Part-Time	2	.625000

FIN	Finance & Corporate Services	FINANCE	Finance	1022	xxx	Casual	5	NULL
``````
0

LVL 19

Accepted Solution

folderol earned 500 total points
I can't see any point in the employee name,  if I group on that then there won't be seven rows returned, right?
If you want 3 x's, then my code is perfect.  (All my code is perfect all the time, and when it's not, it's the poltergeists to blame.)
``````

SELECT                                              -- 1ST LEVEL of nested selects

max(dim2code) as dim2code,

max(departmentcode) as departmentcode,

max(Department) as Department,

employeecode,

'Employee Name' = 'xxx',

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT                                            -- 2ND LEVEL

dim2.dim2code, d.departmentcode, d.description as Department, e.employeecode, es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

cross join                                        -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

) as tpps

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)

) as level_one

group by employeecode, EmployeeStatus
``````
0

LVL 29

Author Comment

perfect! We got there.
I'm tempted to give you an A score rather than a B because..... JOKING!!!

I put the XXX in the name field in order to stop the innocent being future-Googled.
I had to include preferedname and lastname in the inner select and then group on them to get them back (final code in snippet window).
Again, many many thanks for sticking with this. I've been doing selects/joins/etc for years but when the logic gets this deep (especially when dealing with numbers!) my brain starts to smoke!
Have a good weekend.
``````SELECT                                              -- 1ST LEVEL of nested selects

max(dim2code) as dim2code,

max(departmentcode) as departmentcode,

max(Department) as Department,

employeecode,

preferredname + ' ' + lastname as 'Employee Name',

EmployeeStatus,

max(StatusID) as StatusID,

SUM(empMonth) as [Month]

from

(

SELECT                                            -- 2ND LEVEL

dim2.dim2code, d.departmentcode, d.description as Department, e.employeecode, e.preferredname, e.lastname, es.description as 'EmployeeStatus',

case

when es.description like 'Full-Time' then 1

when es.description like 'Part-Time' then 2

when es.description like 'Fixed Term Full Time' then 3

when es.description like 'Fixed Term Part Time' then 4

when es.description like 'Casual' then 5 end

as StatusID,

CASE WHEN ha.AllowanceCode IN('100','130','AL','BL','BL1','DL','JS','LD','LSL','MU','NWA1','SP','R','SL','SSL','ST','TOILT') THEN ha.FTEhours

WHEN  ha.AllowanceCode IN('120', '150','ACC','ACC1','AOT','LN','NWA')  THEN ha.Quantity

else 0 END /

case

when e.employeecode  = '1011' and tpps.seqCount = 2 then 180

when e.employeecode  = '1011' and tpps.seqCount = 3 then 270

when e.employeecode <> '1011' and tpps.seqCount = 2 then 160

when e.employeecode <> '1011' and tpps.seqCount = 3 then 240

END

AS empMonth

FROM HistoricalAllowance ha

cross join                                        -- 3RD LEVEL

(SELECT count(distinct sequencenumber) seqCount

from transperpaysequence t3

where

t3.periodstart >= @periodstart and

t3.periodstart <= @periodend and

t3.description like 'Stan%' and

t3.closed = '1'

) as tpps

inner join employee e

on e.employeecode = ha.employeecode

left outer join employeestatus es

on e.employeestatuscode = es.employeestatuscode

LEFT OUTER JOIN Dim2

ON e.Dim2Code = Dim2.Dim2Code

left outer join department d

on e.departmentcode = d.departmentcode

where ha.paysequence in (493,496)

AND ha.employeecode = e.employeecode

AND Dim2.Description IN (@division)

AND (e.terminationdate is null or e.terminationdate >= (@periodend)) and (e.startdate <= (@periodstart))

and (e.employeestatuscode not in ('nonemp', 'ztempfull', 'zutempfull'))

AND d.Description IN (@department)

) as level_one

group by employeecode, EmployeeStatus, preferredname, lastname
``````
0

LVL 19

Expert Comment

Enjoyed it.  I wonder at times how these might end, since I can't test the code.  Ice Hockey season has just begun, I'm off to the home opener so it's going to be a big weekend.

0

Featured Post

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we waâ€¦
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.