Solved

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

Posted on 2008-10-02
44
268 Views
Last Modified: 2012-05-05
(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
Comment
Question by:QPR
  • 24
  • 18
  • 2
44 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 22629565
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

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22629854
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

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22630029
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
 

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 29

Author Comment

by:QPR
ID: 22630318
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

by:QPR
ID: 22630329
fixed, was just one 'case' keyword too many.
brb as they say
0
 
LVL 29

Author Comment

by:QPR
ID: 22630359
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

by:QPR
ID: 22630373
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

by:QPR
ID: 22632266
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

by:chapmandew
ID: 22633293
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

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22634533
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 

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22634559
Thanks.  I didn't look into any of the logic at all.  Just found some syntax errors and corrected.
0
 
LVL 29

Author Comment

by:QPR
ID: 22645779
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

by:QPR
ID: 22645795
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

by:folderol
ID: 22650724
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

by:QPR
ID: 22655421
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

by:QPR
ID: 22656111
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

by:folderol
ID: 22659352
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'))

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22663505
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

by:QPR
ID: 22663530
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

by:folderol
ID: 22664062
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 

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22664674
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 

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22665190
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 

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22665250
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

by:folderol
ID: 22665376
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)

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22670970
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

by:QPR
ID: 22672095
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

by:folderol
ID: 22672329
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)

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22672361
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)

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22672807
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

by:folderol
ID: 22673138
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

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22673356
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

by:folderol
ID: 22673439
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

by:QPR
ID: 22673513
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

by:QPR
ID: 22673584
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

by:QPR
ID: 22673640
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.

 Have we inadvertantly moved some goalposts while moving this beast about?
0
 
LVL 19

Expert Comment

by:folderol
ID: 22674488
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

by:QPR
ID: 22674750
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

by:folderol
ID: 22674920
Ignoring for the time being special agent 00-1011....

Your statement
"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)
 

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22674973
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

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22675037
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.

good luck and your welcome,
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 

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22682049
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

Open in new window

0
 
LVL 19

Accepted Solution

by:
folderol earned 500 total points
ID: 22682946
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 

Open in new window

0
 
LVL 29

Author Comment

by:QPR
ID: 22683119
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

Open in new window

0
 
LVL 19

Expert Comment

by:folderol
ID: 22683634
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question