I'm making a complete meal out of a stored procedure that I think should be very simple. Please help.
I have Table A which holds financial information in queue, which I snapshot throughout the day on different days (Uploaded_DT).
I have a Query B which gives me a list of the days that the snapshots were taken, and gives me the last snapshot time of the day.
I join this to Table A to give me 1 lot of data for each day, taken from the last snapshot of the day.
In Table A, I have [Date Created] and [Invoice Total] from which I want the stored procedure to aggregate and return the SUM(Invoice Total) for the first day, less the SUM(Invoice Total) for the final day, plus any SUM(Invoice Values) with a [Date Created] in the period.
Here's where I've got to.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER procedure [dbo].[spGetFinancialMovementsBetweenDates]
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
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
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]
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