Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[spGetFinancialMovementsBetweenDates] @dtStart datetime, @dtEnd datetime AS DECLARE @dOpeningBalance as DECIMAL(10,2) DECLARE @dClosingBalance as DECIMAL(10,2) DECLARE @dCreatedInPeriod as DECIMAL(10,2) SELECT c.dt, SUM(c.InvTotal) as bf, SUM(c.CreatedInDT) as created_dt INTO #tt FROM (SELECT a.Uploaded_DT, b.dt, MAX(a.[Invoice Total]) as InvTotal, CASE WHEN CONVERT(varchar(10),MAX(a.[Date Created]),103) = b.dt THEN MAX(a.[Invoice Total]) ELSE 0 END as CreatedInDT FROM TableA a INNER JOIN QueryB b ON b.uploaded_dt = a.Uploaded_DT AND a.Uploaded_DT >= @dtStart AND a.Uploaded_DT <= DATEADD(day,1,@dtEnd) WHERE a.[Date Created] IS NOT NULL GROUP BY a.uploaded_dt, b.dt, a.[Payee Number], a.[Invoice Number] ) c GROUP BY c.dt SET @dOpeningBalance = (SELECT bf FROM #tt WHERE dt = CONVERT(varchar(10),@dtStart,103)) SET @dClosingBalance = (SELECT bf FROM #tt WHERE dt = CONVERT(varchar(10),@dtEnd,103)) SET @dCreatedInPeriod = (SELECT SUM(created_dt), CASE WHEN dt > CONVERT(varchar(10),@dtStart,103) and dt <= CONVERT(varchar(10),@dtEnd,103) THEN 1 ELSE 0 END as grp FROM #tt GROUP BY CASE WHEN dt > CONVERT(varchar(10),@dtStart,103) and dt <= CONVERT(varchar(10),@dtEnd,103) THEN 1 ELSE 0 END ) SELECT @dOpeningBalance as bf, @dCreatedInPeriod as [in], (@dopeningbalance - @dClosingBalance) + @dCreatedInPeriod as [out], @dClosingBalance as cf DROP TABLE #tt GO
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.