Calculate Target Column
-
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.
Larnu.uk -
I just want Week and Target column not formula column.Formula is just reference to calculate Target column
-
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.
Larnu.uk -
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
(
SELECTDISTINCT
[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]
WHERECAST(RIGHT(CAST([weekyearnumber].[number] AS VARCHAR(255)), LEN([weekyearnumber].[number]) - 4) AS INT)
BETWEEN1 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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply