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 : 08:57:26
|
| Hi All,I have this query below, It seems not to be calculation my MTD and YTD right.Can you assist me if you can.@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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
% of MTD and YTD Calculation