sql help

[vizagpower]

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

 

 

[vizagpower]

help 

[Sambadu]

1st 2 select query output yem vachindi?

[Sambadu]

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

[Sambadu]

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

[vizagpower]

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

 

[SwamyRaRa]

Ok

[Leonardo_Di_Carpio]

ltt...

[Sambadu]

oka value ivu isnull function lo 

 

isnull(@ApprovedOriginalBudget,?) +ISNULL(@ApprovedBudgetIncreases,?) as [Approved Project Budget]

 

 

[vizagpower]

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 

[argadorn]

good post ...

[Sambadu]

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

[mtkr]

[loveindia]

ofcourse yes it will repeat man... because you are clearly making the variables static so they are repeating the same number...

[loveindia]

54 minutes ago, mtkr said:

y hiding under the chair man...