Stored Procedure - Track movements in queue


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.



ALTER procedure [dbo].[spGetFinancialMovementsBetweenDates]
	@dtStart datetime,
	@dtEnd datetime

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



Open in new window

LVL 12
James ElliottManaging DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post a sample data? And which data are returning by your SP and what should returns so we can see what's missing in your query.

Also I can't understand the use of the CASE in the subselect.
ste5anSenior DeveloperCommented:
And can you explain your data model? Cause the task sounds like it is part of an ETL process.
James ElliottManaging DirectorAuthor Commented:
Ste5an => I've googled ETL :) I don't know whether it applies or not! My setup doesn't sound like a classical ETL process. Can you explain what you want this information for, and I'll try and explain the data model in the most useful way possible. I'm not particularly well versed in this area.

Vitor => I'm fairly restricted in what I can share as regards data, but please see attached an example.

Thanks in advance.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ste5anSenior DeveloperCommented:
It's the way a data warehouse gets its data: extract, transform, load. "Snapshots", "aggregates" really sound like a DW issue. This can mean that a different model is more approriate.
Vitor MontalvãoMSSQL Senior EngineerCommented:
James, should this solve your issue?
WITH My_CTE (Uploaded_DT, same_day, DailyTotalInvoice)
AS (
	SELECT a.Uploaded_DT, a.same_day, SUM(a.App_Value) 
	FROM TableA a
		INNER JOIN QueryB b ON a.Uploaded_DT=b.Uploaded_DT
	GROUP BY a.Uploaded_DT, ROLLUP(a.same_day)
	HAVING a.same_day=1 OR a.same_day IS NULL
	(SELECT TOP 1 DailyTotalInvoice 
	WHERE same_day IS NULL
	ORDER BY Uploaded_DT ASC) bf,
	(SELECT TOP 1 DailyTotalInvoice 
	WHERE same_day = 1
	ORDER BY Uploaded_DT DESC) ReceivedInPeriod,
	(SELECT TOP 1 DailyTotalInvoice 
	WHERE same_day IS NULL
	ORDER BY Uploaded_DT DESC) cf

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James ElliottManaging DirectorAuthor Commented:
Thanks Vitor.

Nearly there I think.

I'm struggling with a.same_day though. This isn't in my source data.

Vitor MontalvãoMSSQL Senior EngineerCommented:
James, same_day it's in the Excel file you posted here. I thought those columns are fields of your table.
James ElliottManaging DirectorAuthor Commented:
ah ok, no, some of the columns were calculated on the spreadsheet itself to demonstrate what I needed.

I think you've given me enough to go on though with the rollup, and the method of selecting TOP 1 from a sorted listed. I'm going to try and work this out, so I'll post another question to take me further if I need it.

James ElliottManaging DirectorAuthor Commented:
Great direction. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.