We help IT Professionals succeed at work.

Stored Procedure - Track movements in queue

74 Views
Last Modified: 2014-11-27
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

Open in new window

Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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 Developer
CERTIFIED EXPERT

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

Author

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.
EE.xlsb
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
James ElliottManaging Director

Author

Commented:
Thanks Vitor.

Nearly there I think.

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

Rgds
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

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.

Thanks!
James ElliottManaging Director

Author

Commented:
Great direction. Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.