vizagpower Posted June 20, 2016 Report Posted June 20, 2016 Im having an issue in calculating sum of two variables which capture the sum from two tables , where the issue is the same sum is being returned for every detailed row , any idea on how to fix this ? declare @prevyear int , @ytd int SELECT @prevyear= sum(a.[SalesLastYear]) FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID --where BusinessEntityID in (@id) SELECT @ytd= sum(a.SalesYTD) FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID --where BusinessEntityID in (@id) select BusinessEntityID, @ytd+@prevyear as sales FROM [AdventureWorks2012].[Sales].[SalesPerson] group by BusinessEntityID Quote
Sambadu Posted June 20, 2016 Report Posted June 20, 2016 1st 2 select query output yem vachindi? Quote
Sambadu Posted June 20, 2016 Report Posted June 20, 2016 simple ga ela rayochu kada.. SELECT BusinessEntityID, sum(a.SalesYTD) + sum(a.[SalesLastYear]) as total FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID order by BusinessEntityID Quote
Sambadu Posted June 20, 2016 Report Posted June 20, 2016 or ne style lo rayali ante.. use order by..... instead of group by.. declare @prevyear int , @ytd int SELECT @prevyear= sum(a.[SalesLastYear]) FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID --where BusinessEntityID in (@id) SELECT @ytd= sum(a.SalesYTD) FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID --where BusinessEntityID in (@id) select BusinessEntityID, @ytd+@prevyear as sales FROM [AdventureWorks2012].[Sales].[SalesPerson] order by BusinessEntityID Quote
vizagpower Posted June 20, 2016 Author Report Posted June 20, 2016 12 minutes ago, Sambadu said: or ne style lo rayali ante.. use order by..... instead of group by.. declare @prevyear int , @ytd int SELECT @prevyear= sum(a.[SalesLastYear]) FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID --where BusinessEntityID in (@id) SELECT @ytd= sum(a.SalesYTD) FROM [AdventureWorks2012].[Sales].[SalesPerson] a inner join [AdventureWorks2012].[Sales].[SalesTerritory] b on a.TerritoryID = b.TerritoryID --where BusinessEntityID in (@id) select BusinessEntityID, @ytd+@prevyear as sales FROM [AdventureWorks2012].[Sales].[SalesPerson] order by BusinessEntityID pasting the actual query GO /****** Object: StoredProcedure [dbo].[WUSTL_projectstatus] Script Date: 6/16/2016 2:37:01 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[ProjectStatus] @projectid int as begin declare @ApprovedOriginalBudget as decimal(32,2) , @ApprovedBudgetIncreases as decimal(32,2) SELECT @ApprovedOriginalBudget = sum(originalProjectBudget) from CostManagement_BudgetDetails bd inner JOIN CostManagement_Budgets b on b.id = bd.BudgetId where b.projectid = @projectid and b.docStatusId = 5 --– approved SELECT @ApprovedBudgetIncreases = sum(ProjectBudget) From CostManagement_BudgetRequestDetails brd INNER JOIN CostManagement_BudgetRequests br on br.id = brd.BudgetRequestId Where br.projectId = @projectid And br.docStatusId = 5 SELECT ISNULL([ProjectNumber],'') AS [Project #] --,ap.id ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Status Updates', N'Project Category', N'PROJECT',P.Id), '') as [Supporting Mission] ,ISNULL([ProjectName],'') AS [ProjectName] ,ISNULL(PL.Location,'') as [Building Name] ,ISNULL(ap.Name,'') as Location ,'' as [Floor] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Planning and Project Request Form', N'Department', N'PROJECT',P.Id), '') as [Department Name] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Planning and Project Request Form', N'Contact Name', N'PROJECT',P.Id), '') as [Customer] ,ISNULL([Scope],'') AS [Description of Work] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Square Footage', N'NASF', N'PROJECT',P.Id), 0) as [NASF] ,ltrim(rtrim(ISNULL([Manager],''))) AS [Project Manager] ,isnull(ps.Status,'') as [Present Phase] ,cast(dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Planning and Project Request Form', N'Date Assigned to Planner or PM', N'PROJECT',P.Id), '')as date) as [Date Assigned to Planner or PM] ,cast(dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Date Tracking', N'Programming Planning Start Date', N'PROJECT',P.Id), '')as date) as [Start Date] ,cast(dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Date Tracking', N'Substantial Completion Date', N'PROJECT',P.Id), '')as date) as [Substantial Completion Date] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Status Updates', N'Does Project Include a Move', N'PROJECT',P.Id), '') as [Does Project Involve a Move] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Status Updates', N'Weekly Status Update', N'PROJECT',P.Id), '') as [Weekly Status Update] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Status Updates', N'Plan for Next Week', N'PROJECT',P.Id), '') as [Plan for Next Week] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Status Updates', N'Issues and Risks Identified', N'PROJECT',P.Id), '') as [Issues/Risks Identified] ,isnull(@ApprovedOriginalBudget,) +ISNULL(@ApprovedBudgetIncreases,) as [Approved Project Budget] ,dbo.IsNullOrEmpty(dbo.GetRecordSpecByTemplate(N'Project Status Updates', N'Total Project Budget', N'PROJECT',P.Id), 0) AS TotalProjectBudget FROM [WashUData].[dbo].[Projects] p left join [dbo].[ProjectStatuses] ps on p.StatusId = ps.id inner join [WashUData].[dbo].[Programs] pr on p.ProgramId= pr.Id left join [WashUData].[dbo].[ProjectLocations] pl on pl.ProjectId = p.Id left join [WashUData].[dbo].[Asset_Properties] ap on ap.Id= p.PropertyId LEFT JOIN dbo.CostManagement_Budgets b ON b.projectId = p.Id where p.id in (@projectId) End Quote
Sambadu Posted June 20, 2016 Report Posted June 20, 2016 oka value ivu isnull function lo isnull(@ApprovedOriginalBudget,?) +ISNULL(@ApprovedBudgetIncreases,?) as [Approved Project Budget] Quote
vizagpower Posted June 20, 2016 Author Report Posted June 20, 2016 2 minutes ago, Sambadu said: oka value ivu isnull function lo isnull(@ApprovedOriginalBudget,?) +ISNULL(@ApprovedBudgetIncreases,?) as [Approved Project Budget] the thing is ,work outhundi for single project but incase of all projects , the same sum is repeated , say first value for isnull(@ApprovedOriginalBudget,?) +ISNULL(@ApprovedBudgetIncreases,?) is $1200 , same 1200 is repeated for all the rows Quote
Sambadu Posted June 20, 2016 Report Posted June 20, 2016 ithe 1st 2 select lo below red condition valla okate value pull chestundemo choodu....... combitination lo always okate value vastundhi yemo. where b.projectid = @projectid and b.docStatusId = 5 Quote
loveindia Posted June 20, 2016 Report Posted June 20, 2016 ofcourse yes it will repeat man... because you are clearly making the variables static so they are repeating the same number... Quote
loveindia Posted June 20, 2016 Report Posted June 20, 2016 54 minutes ago, mtkr said: y hiding under the chair man... Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.