asked on # 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.

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

Microsoft SQL ServerMicrosoft SQL Server 2008

And can you explain your data model? Cause the task sounds like it is part of an ETL process.

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.

EE.xlsb

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

Thanks in advance.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

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.

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Thanks Vitor.

Nearly there I think.

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

Rgds

Nearly there I think.

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

Rgds

James, **same_day** it's in the Excel file you posted here. I thought those columns are fields of your table.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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.

Thanks!

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.

Thanks!

Great direction. Thanks

Also I can't understand the use of the CASE in the subselect.