• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

SQL CTE results

I am using a parameter CTE in a Stored procedure and would like to know how to get the results for all employee. This CTE is currently give me the results for one employee.
Time-CTE.docx
0
YVR33
Asked:
YVR33
  • 4
  • 4
  • 3
  • +1
1 Solution
 
LowfatspreadCommented:
this

commenting out the selection on the employeeid would work for all employees in the specified projects/departments


;WITH time_cte as 
(       
        SELECT temployee,tmainprojectid, tMainProject
         , SUM(tUnits) units , tProject ,tIsCurrent
         ,tActivityType
         from Trans t
         WHERE /*tEmployee=@temployee and */
             tmainprojectid = @tmainprojectid  
        Group by tEmployee, tProject, tMainProjectID, tMainProject
             ,tIsCurrent, tActivityType)
--Select * from time_cte
,prj_cte (prjID,prjKey ,prjProject    
           ,prjDescription,prjlaborcontractamount
           , budgethrs, percentemp, costratio
          
           ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost)
        AS 
        ( 
         SELECT 
                     prjID  
                    ,prjKey 
                    , prjProject
                    ,prjDescription,prjlaborcontractamount 
                    ,prjlaborcontractamount/170 as budgethrs
                    ,prjlaborcontractamount/170/prjTotalHours as percentemp
                    ,prjLaborBilled/prjLaborCost as costratio
                    ,prjTotalHours ,prjTotalCost,prjLaborBilled,prjLaborCost
              FROM Project p
              WHERE prjProject = @prjProject and prjID = @prjID   )  --
,emp_cte (empkey, empname)
             AS 
             (SELECT empKey, empname
             FROM Employee
             WHERE empKey= @empKey 
           
             )
      SELECT   e.empKey, e.empName 
               ,t.temployee 
               ,t.tmainprojectid --
               ,prjID
                     --,cast(p.prjID as int)prjID   --
               ,tMainProject 
               ,sum(t.units) as units
               ,t.tProject
               ,p.prjKey,p.prjProject, p.prjDescription,p.prjlaborcontractamount
               ,prjlaborcontractamount/170  as budgethrs, budgethrs/prjTotalHours as percentemp
               ,p.prjTotalHours,p.prjTotalCost,p.prjLaborBilled,p.prjLaborCost
               ,prjLaborBilled/prjLaborCost as costratio
      SELECT *
       From prj_cte p 
              LEFT OUTER JOIN  time_cte t 
              ON t.tmainproject = prjKey and t.tMainProjectID= prjID
              LEFT OUTER JOIN emp_cte e
              ON tEmployee = empKey
        Group by 
                t.temployee ,t.tmainprojectid, tMainProject
               ,t.tProject ,p.prjID,p.prjKey,p.prjProject
               ,p.prjDescription,p.prjlaborcontractamount
               ,p.prjTotalHours ,p.prjTotalCost
               ,p.prjLaborBilled,p.prjLaborCost
               ,budgethrs
               ,percentemp
               ,costratio
               ,e.empKey, e.empName
     ORDER by empname

Open in new window

0
 
LowfatspreadCommented:
sorry forgot the @empkey in the emp_cte

this is for all employee's in the specified projects...
;WITH time_cte as 
(       
        SELECT temployee,tmainprojectid, tMainProject
         , SUM(tUnits) units , tProject ,tIsCurrent
         ,tActivityType
         from Trans t
         WHERE /*tEmployee=@temployee and */
             tmainprojectid = @tmainprojectid  
        Group by tEmployee, tProject, tMainProjectID, tMainProject
             ,tIsCurrent, tActivityType)
--Select * from time_cte
,prj_cte (prjID,prjKey ,prjProject    
           ,prjDescription,prjlaborcontractamount
           , budgethrs, percentemp, costratio
          
           ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost)
        AS 
        ( 
         SELECT 
                     prjID  
                    ,prjKey 
                    , prjProject
                    ,prjDescription,prjlaborcontractamount 
                    ,prjlaborcontractamount/170 as budgethrs
                    ,prjlaborcontractamount/170/prjTotalHours as percentemp
                    ,prjLaborBilled/prjLaborCost as costratio
                    ,prjTotalHours ,prjTotalCost,prjLaborBilled,prjLaborCost
              FROM Project p
              WHERE prjProject = @prjProject and prjID = @prjID   )  --
,emp_cte (empkey, empname)
             AS 
             (SELECT empKey, empname
             FROM Employee
          /*   WHERE empKey= @empKey */
           
             )
      SELECT   e.empKey, e.empName 
               ,t.temployee 
               ,t.tmainprojectid --
               ,prjID
                     --,cast(p.prjID as int)prjID   --
               ,tMainProject 
               ,sum(t.units) as units
               ,t.tProject
               ,p.prjKey,p.prjProject, p.prjDescription,p.prjlaborcontractamount
               ,prjlaborcontractamount/170  as budgethrs, budgethrs/prjTotalHours as percentemp
               ,p.prjTotalHours,p.prjTotalCost,p.prjLaborBilled,p.prjLaborCost
               ,prjLaborBilled/prjLaborCost as costratio
      SELECT *
       From prj_cte p 
              LEFT OUTER JOIN  time_cte t 
              ON t.tmainproject = prjKey and t.tMainProjectID= prjID
              LEFT OUTER JOIN emp_cte e
              ON tEmployee = empKey
        Group by 
                t.temployee ,t.tmainprojectid, tMainProject
               ,t.tProject ,p.prjID,p.prjKey,p.prjProject
               ,p.prjDescription,p.prjlaborcontractamount
               ,p.prjTotalHours ,p.prjTotalCost
               ,p.prjLaborBilled,p.prjLaborCost
               ,budgethrs
               ,percentemp
               ,costratio
               ,e.empKey, e.empName
     ORDER by empname

Open in new window

0
 
LowfatspreadCommented:
and this is for all employee's with out regard to projects etc...
;WITH time_cte as 
(       
        SELECT temployee,tmainprojectid, tMainProject
         , SUM(tUnits) units , tProject ,tIsCurrent
         ,tActivityType
         from Trans t
           
        Group by tEmployee, tProject, tMainProjectID, tMainProject
             ,tIsCurrent, tActivityType)
--Select * from time_cte
,prj_cte (prjID,prjKey ,prjProject    
           ,prjDescription,prjlaborcontractamount
           , budgethrs, percentemp, costratio
          
           ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost)
        AS 
        ( 
         SELECT 
                     prjID  
                    ,prjKey 
                    , prjProject
                    ,prjDescription,prjlaborcontractamount 
                    ,prjlaborcontractamount/170 as budgethrs
                    ,prjlaborcontractamount/170/prjTotalHours as percentemp
                    ,prjLaborBilled/prjLaborCost as costratio
                    ,prjTotalHours ,prjTotalCost,prjLaborBilled,prjLaborCost
              FROM Project p
               )  --
,emp_cte (empkey, empname)
             AS 
             (SELECT empKey, empname
             FROM Employee 
           
             )
      SELECT   e.empKey, e.empName 
               ,t.temployee 
               ,t.tmainprojectid --
               ,prjID
                     --,cast(p.prjID as int)prjID   --
               ,tMainProject 
               ,sum(t.units) as units
               ,t.tProject
               ,p.prjKey,p.prjProject, p.prjDescription,p.prjlaborcontractamount
               ,prjlaborcontractamount/170  as budgethrs, budgethrs/prjTotalHours as percentemp
               ,p.prjTotalHours,p.prjTotalCost,p.prjLaborBilled,p.prjLaborCost
               ,prjLaborBilled/prjLaborCost as costratio
      SELECT *
       From prj_cte p 
              LEFT OUTER JOIN  time_cte t 
              ON t.tmainproject = prjKey and t.tMainProjectID= prjID
              LEFT OUTER JOIN emp_cte e
              ON tEmployee = empKey
        Group by 
                t.temployee ,t.tmainprojectid, tMainProject
               ,t.tProject ,p.prjID,p.prjKey,p.prjProject
               ,p.prjDescription,p.prjlaborcontractamount
               ,p.prjTotalHours ,p.prjTotalCost
               ,p.prjLaborBilled,p.prjLaborCost
               ,budgethrs
               ,percentemp
               ,costratio
               ,e.empKey, e.empName
     ORDER by empname

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
YVR33Author Commented:
i got multiple duplicate records, using the last suggestion.  Just to convey what I am actually tryig to do: I would like  to have parameters setup to have the employee selected for a date range.  The report should show all projects that they work for the date range; summing the hours that they worked on that project.  
ALTER  PROCEDURE       [dbo].[TimeHoursByProject_DW2]
  ( @empname         varchar (225)
   , @StartDate      datetime
   , @EndDate        datetime)
   --, @tmainprojectid varchar (30), @temployee  int ,@prjProject int
   --, @prjID varchar (30))  -- ,  @empKey int)


        AS
   SET @EndDate =2011-12-31
   SET @StartDate = 2011-01-01
     
SET NOCOUNT ON
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

declare @tmainprojectid varchar (30), @temployee  int ,@prjProject int
       ,@prjID varchar (30)   ,  @empKey int
       
;WITH time_cte as
(      
        SELECT temployee,tmainprojectid, tMainProject
         , SUM(tUnits) units , tProject ,tIsCurrent
         ,tActivityType
         from Trans t
           
        Group by tEmployee, tProject, tMainProjectID, tMainProject
             ,tIsCurrent, tActivityType,tDate)
--Select * from time_cte
,prj_cte (prjID,prjKey ,prjProject    
           ,prjDescription,prjlaborcontractamount
           , budgethrs, percentemp, costratio
         
           ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost)
        AS
        (
         SELECT
                     prjID  
                    ,prjKey
                    , prjProject
                    ,prjDescription,prjlaborcontractamount
                    ,prjlaborcontractamount/170 as budgethrs
                    ,prjlaborcontractamount/170/prjTotalHours as percentemp
                    ,prjLaborBilled/prjLaborCost as costratio
                    ,prjTotalHours ,prjTotalCost,prjLaborBilled,prjLaborCost
              FROM Project p
               )  --
,emp_cte (empkey, empname)
             AS
             (SELECT empKey, empname
             FROM Employee
           
             )
      SELECT   e.empKey, e.empName
               ,t.temployee
               ,t.tmainprojectid --
               ,prjID
                     --,cast(p.prjID as int)prjID   --
               ,tMainProject
               ,sum(t.units) as units
               ,t.tProject
               ,p.prjKey,p.prjProject, p.prjDescription,p.prjlaborcontractamount
               ,prjlaborcontractamount/170  as budgethrs, budgethrs/prjTotalHours as percentemp
               ,p.prjTotalHours,p.prjTotalCost,p.prjLaborBilled,p.prjLaborCost
               ,prjLaborBilled/prjLaborCost as costratio
 
       From prj_cte p
              LEFT OUTER JOIN  time_cte t
              ON t.tmainproject = prjKey and t.tMainProjectID= prjID
              LEFT OUTER JOIN emp_cte e
              ON tEmployee = empKey
        Group by
                t.temployee ,t.tmainprojectid, tMainProject
               ,t.tProject ,p.prjID,p.prjKey,p.prjProject
               ,p.prjDescription,p.prjlaborcontractamount
               ,p.prjTotalHours ,p.prjTotalCost
               ,p.prjLaborBilled,p.prjLaborCost
               ,budgethrs
               ,percentemp
               ,costratio
               ,e.empKey, e.empName
     ORDER by empname
0
 
YVR33Author Commented:
i thought I was on the right track, but maybe there is a better way.
0
 
LowfatspreadCommented:
why does time_cte have this date in the group by?

  Group by tEmployee, tProject, tMainProjectID, tMainProject
             ,tIsCurrent, tActivityType,tDate)

where is your test for the start/end of the period...

please restate your problem, as am i no longer sure what it is..
0
 
YVR33Author Commented:
i was able to figure out my inital problem by correcting my where statement, I now have the results that I need, howver, I am trying to emliminate the duplicate values for the empname. I think that a row_number over partition by would be able to help with this, just not sure how to add it in the CTE.
Example of current dataset:
EmployeeName    empkey   Temployee  Prjid         prjdescp
Sara P                 568          175409       352          Jones L Turly
Sara P                 568          175289       685          Royal Sunrise
John M                857          168598       859          SL Star
John M                857          158269       998          1895 Heritage
John M                857          187598       925          Terrace Property

Example of expected dataset:
Sara P                 568          175409       352          Jones L Turly
                                            175289       685          Royal Sunrise
John M                857           168598       859          SL Star
                                            158269       998          1895 Heritage
                                            187598       925          Terrace Property





ALTER  PROCEDURE       [dbo].[TimeHoursByProject_DW2]
    (  @empname      varchar (225)= NULL
      --,@empkey     int = NULL
      ,@StartDate    datetime = NULL
      ,@EndDate      datetime = NULL)

  AS
     
SET NOCOUNT ON
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

       
;WITH time_cte as  
(      
        SELECT SUM(tUnits) units , tEmployee,tmainproject, tMainProjectID--, tDate
        FROM Trans t
        WHERE tdate BETWEEN @startDate AND @endDate
        GROUP BY tEmployee,tmainproject,tMainProjectID,tDate)

,prj_cte (prjID,prjKey ,prjProject    
           ,prjDescription
           ,prjlaborcontractamount
           ,budgethrs
           ,percentemp
           ,costratio
           ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost)
        AS
        (
            SELECT prjID  
            ,prjKey
            ,prjProject
            ,prjDescription
            ,ISNULL(prjlaborcontractamount ,0)
            ,ISNULL(prjlaborcontractamount/nullif(170,0),0) as budgethrs
            ,ISNULL(prjlaborcontractamount/nullif(170/prjTotalHours,0),0) as percentemp
            ,ISNULL(prjLaborBilled/nullif(prjLaborCost,0),0) as costratio
            ,ISNULL(prjTotalHours,0)
            ,ISNULL(prjTotalCost,0)
            ,ISNULL(prjLaborBilled,0)
            ,ISNULL(prjLaborCost,0)
              FROM Project p
              where prjPhaseLevel =0
         
       )
       ,emp_cte (empkey, empname)
             AS
             ( SELECT  DISTINCT empKey, empname
             FROM Employee
             --WHERE empKey= @empKey
           
            )
         
/*      
,emp_cte (Employeename, empID )
             AS
             (SELECT e.empname
             ,e.empKey
             ,ROW_NUMBER() OVER (Partition BY e.empname, e.empkey ORDER by e.empname) as empID
              From Employee e
             )
             SELECT case when c.empname = 1
                    then c.empname else '' end as Ename
                    FROM emp_cte c
               ORDER by empName, empKey
                      )
      */

      SELECT  e.empName as EmployeeName ,e.empKey
             ,t.temployee
             ,t.tMainProject
             ,t.tmainprojectid ---,t.tdate
             ,prjID
             ,sum(t.units) as units
             ,p.prjKey,p.prjProject, p.prjDescription ,p.prjlaborcontractamount
             ,prjlaborcontractamount/170  as budgethrs
             ,budgethrs/prjTotalHours as percentemp
             ,p.prjTotalHours,p.prjTotalCost,p.prjLaborBilled,p.prjLaborCost
             ,prjLaborBilled/prjLaborCost as costratio
      From prj_cte p
             LEFT OUTER JOIN  time_cte t
             ON t.tmainproject = prjKey and t.tMainProjectID= prjID
             LEFT OUTER JOIN emp_cte e
             ON tEmployee = empkey
      WHERE tMainProjectID is not null and empkey is not null
            and tEmployee is not null
            --
     Group by  tEmployee,tmainproject, tMainProjectID--, tDate
      ,prjID,prjKey ,prjProject  
      ,prjDescription
      ,prjlaborcontractamount
      ,budgethrs
      ,percentemp
      ,costratio
      ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost
                     ,e.empName  ,e.empKey
     ORDER by  empname, tMainProjectID, prjDescription
0
 
SharathData EngineerCommented:
Can you check this?
ALTER  PROCEDURE       [dbo].[TimeHoursByProject_DW2]
    (  @empname      varchar (225)= NULL
      --,@empkey     int = NULL
      ,@StartDate    datetime = NULL
      ,@EndDate      datetime = NULL)

  AS
     
SET NOCOUNT ON
SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF 

       
;WITH time_cte as  
(       
        SELECT SUM(tUnits) units , tEmployee,tmainproject, tMainProjectID--, tDate
        FROM Trans t
        WHERE tdate BETWEEN @startDate AND @endDate
        GROUP BY tEmployee,tmainproject,tMainProjectID,tDate)

,prj_cte (prjID,prjKey ,prjProject    
           ,prjDescription 
           ,prjlaborcontractamount
           ,budgethrs
           ,percentemp
           ,costratio
           ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost)
        AS 
        ( 
            SELECT prjID  
            ,prjKey 
            ,prjProject
            ,prjDescription 
            ,ISNULL(prjlaborcontractamount ,0)
            ,ISNULL(prjlaborcontractamount/nullif(170,0),0) as budgethrs
            ,ISNULL(prjlaborcontractamount/nullif(170/prjTotalHours,0),0) as percentemp
            ,ISNULL(prjLaborBilled/nullif(prjLaborCost,0),0) as costratio
            ,ISNULL(prjTotalHours,0) 
            ,ISNULL(prjTotalCost,0)
            ,ISNULL(prjLaborBilled,0)
            ,ISNULL(prjLaborCost,0)
              FROM Project p
              where prjPhaseLevel =0 
          
       )
       ,emp_cte (empkey, empname)
             AS 
             ( SELECT  DISTINCT empKey, empname
             FROM Employee
             --WHERE empKey= @empKey 
            
            )
         
/*       
,emp_cte (Employeename, empID ) 
             AS 
             (SELECT e.empname 
             ,e.empKey 
             ,ROW_NUMBER() OVER (Partition BY e.empname, e.empkey ORDER by e.empname) as empID
              From Employee e
             ) 
             SELECT case when c.empname = 1 
                    then c.empname else '' end as Ename
                    FROM emp_cte c
               ORDER by empName, empKey
                      )
      */
,cte1 as (
      SELECT  e.empName as EmployeeName ,e.empKey 
             ,t.temployee
             ,t.tMainProject
             ,t.tmainprojectid ---,t.tdate
             ,prjID
             ,sum(t.units) as units 
             ,p.prjKey,p.prjProject, p.prjDescription ,p.prjlaborcontractamount
             ,prjlaborcontractamount/170  as budgethrs 
             ,budgethrs/prjTotalHours as percentemp
             ,p.prjTotalHours,p.prjTotalCost,p.prjLaborBilled,p.prjLaborCost
             ,prjLaborBilled/prjLaborCost as costratio
             
      From prj_cte p 
             LEFT OUTER JOIN  time_cte t 
             ON t.tmainproject = prjKey and t.tMainProjectID= prjID
             LEFT OUTER JOIN emp_cte e
             ON tEmployee = empkey
      WHERE tMainProjectID is not null and empkey is not null 
            and tEmployee is not null
            --
     Group by  tEmployee,tmainproject, tMainProjectID--, tDate
      ,prjID,prjKey ,prjProject  
      ,prjDescription
      ,prjlaborcontractamount
      ,budgethrs
      ,percentemp
      ,costratio
      ,prjTotalHours ,prjTotalCost ,prjLaborBilled,prjLaborCost
                     ,e.empName  ,e.empKey),
     cte2 as (
     select *,ROW_NUMBER() over (partition by e.empName order by e.empname, t.tMainProjectID, p.prjDescription) rn 
       from cte1)
    SELECT case when rn = 1 then EmployeeName else NULL end EmployeeName,
       empKey,temployee,tMainProject,tmainprojectid,prjID,units,prjKey,prjProject,prjDescription,prjlaborcontractamount,
       budgethrs,percentemp,prjTotalHours,prjTotalCost,prjLaborBilled,prjLaborCost,costratio 
      from cte2
     ORDER by  empname, tMainProjectID, prjDescription

Open in new window

0
 
vette333Commented:
The column in this select statement is giving me errors that colunm could not be bound.
select *,ROW_NUMBER() over (partition by e.empName order by e.empname, t.tMainProjectID, p.prjDescription) rn
       from cte1)

I change the column name to the following and the results were 0 records found.
select *,ROW_NUMBER() over (partition by EmployeeName order by EmployeeName, tMainProjectID, prjDescription) rn
       from cte1)
0
 
vette333Commented:
I reveiwed the code given to me and made changes.  Works as expected! Thanks Sharath_123!
Now I am off to design the report in SSRS.
0
 
vette333Commented:
Solution Sharath_123 provided worked.
0
 
vette333Commented:
Well actually I got another inqury on the above code: The date range that I am providing is now showing the current units within that parameters of a @start and @end date.  I would like to know how it would be possible to add something like a date with the current time as I have above as well as a ptd date starting from the beginning of time (1900) to today's date.  So I would need two columns one  that will give me the sum of the units using the parameters and another column giving me the sum of the units using the date range of  1900- present.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now