locked
Year to Year % RRS feed

  • Question

  • My data looks like below

    ID Sales Profit Source year
    111 100 200 HR 2013
    111 Null Null IT 2013
    111 Null Null HR 2012
    111 150 250 IT 2012

    My script is working fine only problem is I would like to get data if Sales and Profit is not null for source HR give HR data.

    IF sales and profit is null for HR source give me IT sales and profit data. My report is doing year to year percentage.

    Select
    FROM
    (
    Select DISTINCT
    ISNULL(SUM(Sales),0) AS SALES,
    ISNULL(SUM(Profit),0) AS Profit,
    ID,
    SOURCE,
    YEAR
    FROM Table 1
    GROUP BY ID,SOURCE,YEAR) T1
    LEFT JOIN
    (SELECT
    ISNULL(SUM(Sales),0) AS LSALES,
    ISNULL(SUM(Profit),0) AS LProfit,
    ID,
    SOURCE,
    YEAR
    FROM Table 1
    GROUP BY ID,SOURCE,YEAR) T2
    ON T1.Year = T2.LYear + 1
    AND T1.ID = T2.ID

    I am getting

    ID Sales Profit Source year LSales LProfit Source Lyear
    111 100 200 Sales 2013 2012

    Return data should be

    ID Sales Profit Source year LSales LProfit Source Lyear
    111 100 200 Sales 2013 150 250 IT 2012


    simam

    Friday, September 9, 2016 9:38 PM

Answers

  • What do you want to show when you have both IT and HR data in your table?

    ;with cteInfo as (select ID, [year], ISNULL(sum(Sales),0) as Sales, ISNULL(sum(Profit),0) as Profit, MIN(Source) as Source
    
    from DataTable
    
    GROUP BY ID, [Year])
    
    select c1.ID, c1.Sales, c1.Profit, c1.Source, c1.[Year],
    
    LAG(c1.Sales, 1, 0) over (partition by ID order by [Year]) as LSales,
    
    LAG(c1.Profit, 1, 0) over (partition by ID order by [Year]) as LProfit,
    
    LAG(c1.Source, 1, '') over (partition by c1.ID order by c1.[Year]) as LSource,
    
    LAG(c1.Year, 1) over (partition by ID order by [Year]) as LYear
    
    from cteInfo c1
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 19, 2016 6:27 PM

All replies

  • DECLARE @tbl AS TABLE( ID INT  , Sales INT  , Profit  INT    ,[Source]   CHAR(36), [year] INT   )
    
    INSERT INTO @tbl
    SELECT 111 ,100 ,200 ,'HR' ,2013 
    UNION ALL SELECT  111, NULL, Null ,'IT', 2013 
    UNION ALL SELECT  111, Null ,NULL,'HR',  2012 
    UNION ALL SELECT  111 ,150, 250 ,'IT',  2012 
    
    
    /*
    would like to get data 
    
    IF sales and profit is NOT null for HR source then give HR data.
    
    IF sales and profit is null for HR source then give me IT sales and profit data. 
    
    */
    
    
    
    --Code from here 
    /* For Every currentYear NOT NULL Profit&Sale Value , Pick the PrevYear  NOT NULL Profit&Sale Value  ( irrespective of Any Department/Source)
      */
     ;WITH tbl AS 
     ( 
    		 SELECT  
    		 SUM(Sales)AS Sales,
    		 SUM(Profit) AS Profit,
    		 ID,
    		 [Source],
    		 [year]
    		 FROM  @tbl
    		 GROUP BY ID,[Source],[year]
     )
     SELECT * FROM 
     (
    		SELECT 
    		curr_Yr.ID , curr_Yr.Sales , curr_Yr.Profit , curr_Yr.[Source] ,  curr_Yr.[year],
    		  prev_Yr.Sales AS LSales , prev_Yr.Profit AS LProfit , prev_Yr.[Source] AS LSource ,  prev_Yr.[year] LYear , 
    		  ROW_NUMBER() OVER ( PARTITION BY curr_Yr.ID ,  curr_Yr.[Source] ,  curr_Yr.[year]   
    		  ORDER BY prev_Yr.Sales DESC ,  prev_Yr.Profit DESC  ) AS rn  
    		FROM tbl curr_Yr
    		JOIN  tbl prev_Yr ON   curr_Yr.ID=prev_Yr.ID    AND   prev_Yr.[year]  =  curr_Yr.[year] -1
    		 WHERE 
    		   (curr_Yr.Sales IS NOT NULL  AND   curr_Yr.Profit IS NOT NULL  ) AND 
    		   (prev_Yr.Sales IS NOT NULL  AND   prev_Yr.Profit IS NOT NULL  ) 
    
    )t
    WHERE rn = 1 


    • Edited by msbi_Dev Friday, September 9, 2016 11:09 PM
    Friday, September 9, 2016 10:23 PM
  • Rajiv,

    Thank you , I really appreciate if you can  modified my sql script.

    For some reason I am not able to replicate your script.

    Thanks


    simam

    Friday, September 9, 2016 11:04 PM
  • --can you try this - modified your SQL
    
    Select  t1.*, T2.SALES AS LSales, T2.Profit AS LProfit , t2.SOURCE AS LSource   , t2.LYear
     FROM
     (
    		 Select DISTINCT
    		 SUM(Sales) AS SALES,
    		 SUM(Profit) AS Profit,
    		 ID,
    		 SOURCE,
    		 YEAR
    		 FROM [Table 1]
    		 GROUP BY ID,SOURCE,[YEAR]
     
     ) T1
    
    LEFT JOIN
     (       SELECT 
    		 SUM(Sales) AS SALES,
    		 SUM(Profit) AS Profit,
    		 ID,
    		 SOURCE,
    		 YEAR AS [LYear]
    		 FROM [Table 1]
    		 GROUP BY ID,SOURCE,[YEAR] 
     ) T2
     ON T1.YEAR = T2.LYear + 1  AND T1.ID = T2.ID
     WHERE t1.SALES IS NOT NULL AND  t1.Profit IS NOT NULL  AND T2.SALES IS NOT NULL AND  T2.Profit IS NOT NULL  


    rajivkumar.bala@yahoo.co.in

    Friday, September 9, 2016 11:18 PM
  • Rajiv,

    Thank you for reason it is not working  let me research more. I will get back to you.

    Thank you, appreciate your help.


    simam

    Saturday, September 10, 2016 12:05 AM
  • Rajiv,

    Thank you for reason it is not working  let me research more. I will get back to you.

    Thank you, appreciate your help.


    simam

    What you mean its not working? What are the results you get? 


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Saturday, September 10, 2016 12:29 AM
  • I am not getting source IT only getting Source HR data

    simam

    Saturday, September 10, 2016 12:41 AM
  • I am not getting source IT only getting Source HR data

    simam

    Shiman, can you please check your data? Rajiv's script should work. Lets hope Naomi sees this thread and chimes in. She will be able to offer good insight.

    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    Saturday, September 10, 2016 1:25 AM
  • >> My data looks like below <<

    But what does your DDL look like? Did you read the basic forum rules before you posted? Also, the old Sybase COALESCE() has been replaced with the ANSI/ISO standard coalesce ().

    CREATE TABLE Sales
    (acct_nbr CHAR(3) NOT NULL, 
     sales_year CHAR(4) NOT NULL
      CHECK (sales_year LIKE '2[0-9][0-9][0-9]'), 
     sales_source CHAR(2) NOT NULL
       CHECK (sales_source IN ('IT', 'HR')), 
     PRIMARY KEY (acct_nbr, sales_year, sales_source), 
     sales_amt DECIMAL (10,2), 
     profit_amt DECIMAL (10,2));

    INSERT INTO Sales
    VALUES
    ('111', '2013', 'HR', 100.00, 200.00), 
    ('111', '2013', 'IT', NULL, NULL), 
    ('111', '2012', 'HR', NULL, NULL), 
    ('111', '2012', 'IT', 150.00, 250.00);


    >> I would like to get data if sales_source and Profit is not NULL for source HR give HR data. If sales and profit are NULL for HR source, give me IT sales and profit data. My report is doing year to year percentage. <<

    Percentages have to have a base; are you using the previous year the current year or a base year? Since we have no data to look at. We can only guess. And I do not feel like guessing.

    This query will get you a normalized result set. From this you can use the lead() or lag() functions to get your base year.

    SELECT acct_nbr, sales_year, 
           SUM (CASE WHEN sales_source = 'HR'
                     THEN sales_amt ELSE 0.00 END) AS hr_sales_amt,
           SUM (CASE WHEN sales_source = 'IT'
                     THEN sales_amt ELSE 0.00 END) AS it_sales_amt,
      FROM Sales
     GROUP BY acct_nbr, slales_year;

    In the future posting DDL instead of homemade spreadsheets, and full specs what you much better results.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Monday, September 19, 2016 6:15 PM
    • Unproposed as answer by Naomi N Monday, September 19, 2016 6:18 PM
    Saturday, September 10, 2016 5:48 AM
  • Hi shimam,

    Based on my experience, you could start with the following script.

    ;WITH CTE AS
    (
    	SELECT [ID], [Source], [year]
    		, ISNULL(SUM([Sales]), 0) AS [Sales]
    		, ISNULL(SUM([Profit]), 0) AS [Profit]
    	FROM Table1
    	GROUP BY [ID], [Source], [year]
    		
    )
    SELECT a.*
    	, b.[year] AS [Lyear]
    	, b.[Sales] AS [LSales]
    	, b.[Profit] AS [LProfit]
    FROM CTE a
    LEFT JOIN CTE b ON a.[ID] = b.[ID]
    				AND a.[Source] = b.[Source]
    				AND a.[year] = b.[year] + 1

    Sam Zha
    TechNet Community Support

    Monday, September 12, 2016 5:10 AM
  • What do you want to show when you have both IT and HR data in your table?

    ;with cteInfo as (select ID, [year], ISNULL(sum(Sales),0) as Sales, ISNULL(sum(Profit),0) as Profit, MIN(Source) as Source
    
    from DataTable
    
    GROUP BY ID, [Year])
    
    select c1.ID, c1.Sales, c1.Profit, c1.Source, c1.[Year],
    
    LAG(c1.Sales, 1, 0) over (partition by ID order by [Year]) as LSales,
    
    LAG(c1.Profit, 1, 0) over (partition by ID order by [Year]) as LProfit,
    
    LAG(c1.Source, 1, '') over (partition by c1.ID order by c1.[Year]) as LSource,
    
    LAG(c1.Year, 1) over (partition by ID order by [Year]) as LYear
    
    from cteInfo c1
    
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 19, 2016 6:27 PM