?
Solved

Stored Procedure - Track movements in queue

Posted on 2014-11-25
9
Medium Priority
?
52 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

0
Comment
Question by:James Elliott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40464018
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.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40464073
And can you explain your data model? Cause the task sounds like it is part of an ETL process.
0
 
LVL 12

Author Comment

by:James Elliott
ID: 40464212
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
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 35

Expert Comment

by:ste5an
ID: 40464336
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.
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40464795
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 
	(SELECT TOP 1 DailyTotalInvoice 
	FROM My_CTE
	WHERE same_day IS NULL
	ORDER BY Uploaded_DT ASC) bf,
	(SELECT TOP 1 DailyTotalInvoice 
	FROM My_CTE
	WHERE same_day = 1
	ORDER BY Uploaded_DT DESC) ReceivedInPeriod,
	(SELECT TOP 1 DailyTotalInvoice 
	FROM My_CTE
	WHERE same_day IS NULL
	ORDER BY Uploaded_DT DESC) cf

Open in new window

0
 
LVL 12

Author Comment

by:James Elliott
ID: 40468580
Thanks Vitor.

Nearly there I think.

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

Rgds
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40468584
James, same_day it's in the Excel file you posted here. I thought those columns are fields of your table.
0
 
LVL 12

Author Comment

by:James Elliott
ID: 40468592
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!
0
 
LVL 12

Author Closing Comment

by:James Elliott
ID: 40468594
Great direction. Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question