Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
macekepp
Starting Member
4 Posts |
 Posted - 2009-11-10 : 09:03:36
|
| Hi All,Can you assist me with the query below.It is not calculating Perc_MTD and Perc_YTD correct.For the first few days it calculate perfectly but somewhere along the way it start to give the same figure for the Perc_MTD as well as Perc_YTD@year as varchar(16),@Month as varchar(16),@ipc_site as char(20),@PROCESS_STREAM as char(84)asSELECT C.[MONTH_ID] ,C.[MONTH] ,C.[Day]-- ,C.[AREA] ,C.[IPC_SITE] ,C.[YEAR] ,C.PROCESS_STREAM ,ROUND(CAST(SUM(C.[Escalation]) AS FLOAT)/CAST(SUM(C.[Received])AS FLOAT),2) as [Perc_Of_Escalation] ,[Perc_MTD] =( SELECT round(CAST(sum([Escalation]) AS FLOAT)/CAST(sum([Received])AS FLOAT),2) FROM ( SELECT TOP 31 A.[DATE],DDate=B.[D_Date],[Received],[Escalation] FROM [Gijima].[dbo].[GIJIMA_ESCALATION_FINAL] A INNER JOIN [Gijima].[dbo].[D_DATE] B ON A.[DATE] = B.[D_Date] WHERE A.[PROCESS_STREAM] = C.[PROCESS_STREAM] AND A.[IPC_SITE]=C.[IPC_SITE] AND A.area IN ('BFC','CARD','HL','VAF','ALL Products') AND A.[MONTH] = C.[MONTH] AND A.[DATE] <= C.[DATE] ORDER BY A.[Date] DESC ) x where [Received] <> 0 )FROM [Gijima].[dbo].[GIJIMA_ESCALATION_FINAL] CWHERE C.area IN ('BFC','CARD','HL','VAF','ALL Products') AND (C.YEAR = @year) AND (C.month = @month ) AND (C.IPC_SITE = @ipc_site) AND (C.PROCESS_STREAM = @PROCESS_STREAM)GROUP BY C.[MONTH_ID],C.[MONTH],C.[Day],C.[PROCESS_STREAM],C.[IPC_SITE],C.[YEAR],C.[DATE]ORDER BY C.[Day],C.[MONTH_ID] asc |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-10 : 12:43:33
|
| can you post some data and explain what you want with sample output. that will make much more sense than your current query |
 |
|
|
|
|
|
|
|
Calculating % of MTD and YTD