Stored Procedure - Track movements in queue

Hi,

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
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

Select allOpen in new window