none
Complex requirement in TSQL - Please help!

    Question

  • Hi,
        I have a stored procedure which returns all values (Old , New and Active) if we pass a year and Month. Eg: 2013, 10

    'Old MEMBERS COUNT IN DEPARTMENT' 

    Dept Count
    AAA  100
    BBB  200
    CCC  300


    'NEW MEMBERS COUNT IN DEPARTMENT' '

    Dept Count
    AAA 300
    BBB 500
    CCC 700


    'ACTIVE MEMBERS COUNT IN DEPARTMENT'

    Dept Count
    AAA 13500
    BBB 67400
    CCC 80063


    Now My requirement is to create a table and load the values like this below - (then i will use the same structure in ssrs to create  report)



    Similarly, if i execute the procedure for 2013 & 11 , then  that month value should also be inserted in to this table as 2013 & 10 and will go on for other Yr and Mon also..

    Can you please help me to load the data into this Table structure format - It would be really great.

    thanks in advance!
    Monday, October 28, 2013 3:25 PM

Answers

  • Create proc [Usp_Emp_Details]  
    @var_yr AS VARCHAR(4)
    ,@var_mon AS VARCHAR(2) 
    AS
    begin
    SELECT @var_yr + @var_mon AS [Year&Mon]
    ,t1.OLD_Employees as [Old]
    ,t2.OLD_Employees as [New]
    ,t3.OLD_Employees as [Active]
    FROM (SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM (
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1
    AND EmpId < 1001  
    GROUP BY Dept ) t1 INNER JOIN 
    (SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1
    AND EmpId > 1002 
    GROUP BY Dept) t2 On t1.DEPT=t2.DEPT INNER JOIN ( 
    SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1
    AND EmpId > 100 
    GROUP BY Dept) t3 
    On t1.DEPT=t3.DEPT
    end

    • Marked as answer by vskindia Monday, October 28, 2013 6:04 PM
    Monday, October 28, 2013 5:56 PM

All replies

  • Please post your stored proc code.

    Is your proc returning 3 result sets?  It would be easier to change the stored proc to return the data as columns.

    Monday, October 28, 2013 3:39 PM
  • Thanks!

    The below is the slightly simplified/modified SP as i am not supposed to share our  business logic..hope you understand..

    Can you please give me your inputs ? thanks!


    Create proc [Usp_Emp_Details] (@var_yr AS VARCHAR(4),@var_mon AS VARCHAR(2) )

    begin

    --Old

    SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(

    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR

    FROM   [DBO].[Employee]

    where Year(join_date) =@var_yr

    and month(join_date) =@var_mon

    ) A 

    WHERE ROWNBR =1

    AND EmpId < 1001  

    GROUP BY Dept


    --New

    SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(

    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR

    FROM   [DBO].[Employee]

    where Year(join_date) =@var_yr

    and month(join_date) =@var_mon

    ) A 

    WHERE ROWNBR =1

    AND EmpId > 1002 

    GROUP BY Dept

    --Active

    SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(

    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR

    FROM   [DBO].[Employee]

    where Year(join_date) =@var_yr

    and month(join_date) =@var_mon

    ) A 

    WHERE ROWNBR =1

    AND EmpId > 100 

    GROUP BY Dept

    end


    • Edited by vskindia Monday, October 28, 2013 4:03 PM edit
    Monday, October 28, 2013 4:02 PM
  • Create proc [Usp_Emp_Details]  
    @var_yr AS VARCHAR(4)
    ,@var_mon AS VARCHAR(2) 
    AS
    begin
    SELECT @var_yr + @var_mon AS [Year&Mon]
    ,t1.OLD_Employees as [Old]
    ,t2.OLD_Employees as [New]
    ,t3.OLD_Employees as [Active]
    FROM (SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM (
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1
    AND EmpId < 1001  
    GROUP BY Dept ) t1 INNER JOIN 
    (SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1
    AND EmpId > 1002 
    GROUP BY Dept) t2 On t1.DEPT=t2.DEPT INNER JOIN ( 
    SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1
    AND EmpId > 100 
    GROUP BY Dept) t3 
    On t1.DEPT=t3.DEPT
    end

    • Marked as answer by vskindia Monday, October 28, 2013 6:04 PM
    Monday, October 28, 2013 5:56 PM
  • Great!

    Thanks a lot Jingyang!!

    Monday, October 28, 2013 6:05 PM
  • You can simplify your query with SUM CASE... pattern:

    CREATE PROCEDURE [Usp_Emp_Details]  
    @var_yr AS VARCHAR(4) 
    ,@var_mon AS VARCHAR(2)  
    As
    Begin
    select  @var_yr + @var_mon AS [Year&Mon],
     SUM(CASE WHEN EmpId < 100 THEN 1 ELSE 0 ENd ) as  [Old],
     SUM(CASE WHEN  EmpId >= 100  AND EmpId <= 1001  THEN 1 ELSE 0 ENd ) as   [New],
     SUM(CASE WHEN EmpId > 1001   THEN 1 ELSE 0 ENd ) as  [Active]
     FROM 
     (
     SELECT DEPT, COUNT(DISTINCT EMPLOYEES) as OLD_Employees FROM(
    SELECT  EmpID,Emp_cd,Dept,Join_date, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Join_DATE DESC) AS ROWNBR
    FROM   [DBO].[Employee]
    where Year(join_date) =@var_yr
    and month(join_date) =@var_mon
    ) A 
    WHERE ROWNBR =1 )
     t
     End

    Monday, October 28, 2013 6:59 PM