Calculate Target Column

  • selpoivre

    Say Hey Kid

    Points: 702

    Hi,

    I have below table

    Capture

    Sum of AP1 = 1764. I need to sql query to calculate Target column for Week  Number .Please help to calculate

    Capture2

    • This topic was modified 1 month, 1 week ago by  selpoivre.
    • This topic was modified 1 month, 1 week ago by  selpoivre.
  • Thom A

    SSC Guru

    Points: 98655

    SQL Server can't take descriptions and turn that into SQL, and if you have a valid (SQL) expression you would have to use Dynamic SQL; which is an entirely different rabbit hole you likely don't want to go down. If you're after something like this, is generally infers a larger underlying problem with the design.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • selpoivre

    Say Hey Kid

    Points: 702

    I just want Week  and Target column not formula column.Formula is just reference to calculate Target column

  • Thom A

    SSC Guru

    Points: 98655

    So what are your expected results here exactly? What is the logic and your attempts, as they will help us understand your goal. Also, please do use consumable sample data, rather than images. DDL and DML statements are the best way to supply data; otherwise we can't recreate your scenario or test.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    Does this need to be in SQL 2008?  Solutions in newer SQL versions make this a lot easier.

     

    EDIT - if this NEEDS to be in 2008, I did this in 2008 R2 that works for your sample data:

    DECLARE @tmp TABLE
    (
    [targetd] DATE
    , [ap1] INT
    , [ap2] INT
    , [diff] INT
    , [weeknum] VARCHAR(25)
    );
    INSERT INTO @tmp
    (
    [targetd]
    , [ap1]
    , [ap2]
    , [diff]
    , [weeknum]
    )
    VALUES
    (
    CAST('04-May-20' AS DATE)
    , 441
    , 89
    , 352
    , '2020-19'
    )
    , (
    CAST('05-May-20' AS DATE)
    , 441
    , 89
    , 352
    , '2020-19'
    )
    , (
    CAST('06-Jun-20' AS DATE)
    , 441
    , 89
    , 352
    , '2020-23'
    )
    , (
    CAST('07-Jun-20' AS DATE)
    , 441
    , 89
    , 353
    , '2020-23'
    );

    -- get the TOTAL AP1
    DECLARE @total INT;
    SELECT
    @total = SUM([ap1])
    FROM @tmp;

    WITH [GetWeekAsNumber]
    AS
    (
    SELECT
    [targetd]
    , [ap1]
    , [ap2]
    , [diff]
    , [weeknum]
    , CAST(REPLACE( [weeknum]
    , '-'
    , ''
    ) AS INT) AS [weekyearnumber]
    FROM @tmp
    )
    , [GetTargets]
    AS
    (
    SELECT DISTINCT
    [GetWeekAsNumber].[weeknum] AS [Week]
    --, @total - SUM([GetWeekAsNumber].[diff]) OVER (ORDER BY [GetWeekAsNumber].[weekyearnumber]) AS [Target]
    ,@total-x.s AS target
    , [GetWeekAsNumber].[weekyearnumber] -- CAST(REPLACE(weeknum,'-','') AS INT) AS weekyearnumber
    FROM [GetWeekAsNumber]
    CROSS APPLY (SELECT ISNULL(SUM(sub.diff),0)
    FROM (SELECT TOP 100 PERCENT diff
    FROM GetWeekAsNumber innerQuery
    WHERE [innerQuery].WeekYearNumber <= [GetWeekAsNumber].[weekyearnumber]
    ORDER BY [innerQuery].weekyearnumber) sub(diff)
    )x(s)
    ), [x]
    AS
    (
    SELECT
    [v].[n]
    FROM
    (
    VALUES
    (
    0
    )
    , (
    1
    )
    , (
    2
    )
    , (
    3
    )
    , (
    4
    )
    , (
    5
    )
    , (
    6
    )
    , (
    7
    )
    , (
    8
    )
    , (
    9
    )
    ) AS [v] ([n])
    )
    , [counters]
    AS
    (
    SELECT
    [ones].[n] + 10 * [tens].[n] + 100 * [hundreds].[n] + 1000 * [thousands].[n] + 10000 * [tenthousands].[n] + 100000 * [hundredthousands].[n] AS [number]
    FROM [x] AS [ones]
    , [x] AS [tens]
    , [x] AS [hundreds]
    , [x] AS [thousands]
    , [x] AS [tenthousands]
    , [x] AS [hundredthousands]
    )
    , [weekyearnumber]
    AS
    (
    SELECT
    [counters].[number]
    FROM [counters]
    WHERE [counters].[number] >=
    (
    SELECT
    MIN([GetWeekAsNumber].[weekyearnumber])
    FROM [GetWeekAsNumber]
    )
    AND [counters].[number] <=
    (
    SELECT
    MAX([GetTargets].[weekyearnumber])
    FROM [GetTargets]
    )
    )
    , [finalData]
    AS
    (
    SELECT
    LEFT(CAST([weekyearnumber].[number] AS VARCHAR(255)), 4) + '-' + RIGHT(CAST([weekyearnumber].[number] AS VARCHAR(255)), LEN([weekyearnumber].[number]) - 4) AS [WeekNumber]
    , [GetTargets].[Target]
    ,[weekyearnumber]
    FROM [GetTargets]
    FULL OUTER JOIN [weekyearnumber]
    ON [GetTargets].[weekyearnumber] = [weekyearnumber].[number]
    WHERE CAST(RIGHT(CAST([weekyearnumber].[number] AS VARCHAR(255)), LEN([weekyearnumber].[number]) - 4) AS INT)
    BETWEEN 1 AND 52
    )

    SELECT
    [finalData].[WeekNumber]
    , CASE WHEN [finalData].target IS NULL THEN
    (SELECT TOP 1 target FROM finaldata casequery WHERE [casequery].weeknumber <= [finalData].weeknumber ORDER by weeknumber) ELSE target END AS targetnew
    FROM [finalData]
    ORDER BY [finalData].[WeekNumber];

    probably not the most efficient code (multiple CTE's like that are not likely to be efficient), but for the sample data provided it seems to work pretty quick.  IF you can do this in 2012 or higher, there is a bit nicer code you can use instead of that CASE and CROSS APPLY, but this is in the 2008 section, so came up with a 2008 solution... well, 2008 R2 as I don't have a 2008 to test the above on.

     

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply