Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now
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.
|Format Date fields||11||56|
|How to check if SQL server database is being backed up by SQL management studio ?||13||70|
|Populate textboxes based on selected 2nd combobox from selected 1st combobox Using VB.net and SQL Server table||8||44|
|T-SQL: Number of Records is Greater Than One||7||42|
Join the community of 500,000 technology professionals and ask your questions.