# Calculate Target Column

• Hi,

I have below table

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

• This topic was modified 5 months ago by  selpoivre.
• This topic was modified 5 months ago by  selpoivre.
• 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.

• 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.

• 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 AP1DECLARE @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