question

mahi222 avatar image
mahi222 asked

need help for dynamic getting data

i have a query like MAX(CASE WHEN FY = 'FY10' THEN PurAmt END) AS FY10_PurAmt, MAX(CASE WHEN FY = 'FY11' THEN PurAmt END) AS FY11_PurAmt, MAX(CASE WHEN FY = 'FY12' THEN PurAmt END) AS FY12_PurAmt, MAX(CASE WHEN FY = 'FY13' THEN PurAmt END) AS FY13_PurAmt, MAX(CASE WHEN FY = 'FY14' THEN PurAmt END) AS FY14_PurAmt, MAX(CASE WHEN FY = 'FY15' THEN PurAmt END) AS FY15_PurAmt So I need query like dynamically get the data for each FY years I needed to change FY11 as dynamic and so on
functions
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you elaborate what do you want to achieve please?
0 Likes 0 ·
actually I need to get every FiscalYear's PurchaseAmount data. For this we done in this code like static way by giving FY09,FY10..... but what I need to get PurchaseAmount data dynamically based on FiscalYear
0 Likes 0 ·
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
0 Likes 0 ·
erlokeshsharma08 avatar image
erlokeshsharma08 answered
----FIRST CREATE A FUNCTION THAT RETURNS YOU DATA Create function udf_getpamount(@inp varchar(10)) returns int begin declare @var int select @var=PurAmt from your_table where FY=@inp return @var end ---SECOND CALL THIS FUNCTION select udf_getpamount('FY10')
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image
erlokeshsharma08 answered
ADD BELOW LOGIC AS WELL:- DECLARE @FINAL_STRING VARCHAR(1000) declare @var2 VARCHAR(100)='FY10' while financial_year_count >0 begin SELECT @FINAL_STRING = @FINAL_STRING + ','+ udf_getpamount(@var2) DECLARE @YR INT =RIGHT(@VAR2,'2') SET @YR=@YR-1 SET @VAR2=LEFT(@VAR2,'2')+@YR SET financial_year_count = financial_year_count -1 end
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.