Convert VBA to MS SQL Stored Procedure

It really depends on how big that snippet really is...

Based on the snippet alone, it would almost appear that a VIEW or CTE over the 'Commitment' table might make it easier without necessarily having to build a new (running total) table.

I guess some of that also depends on what you do with that running total table, and if you want individual discreet dates regardless of potential missing dates in the commitment table, or, make use of a numbers (or calendar) table for aggregation.

Not too much needs to change, it could be converted as is, but there are better ways that RBAR (row by agonising row) once you bring it back into SQL Server.

How experienced are you with SQL Server, and what version are you running (e.g. SQL 2008 / SQL 2012 etc). and what datatype is recievedate

Give it some thought and let me know your thoughts. In the meantime, I will sit back and think of a couple of approaches.

Cheers,
Mark

By way of a simple example, we can use the table Commitment as a our source for Dates to then link back to that same table as a datasource for the data to be aggregated. We can do this by using table Alias's.

Of course if you need every single Date regardless of its existence in the table Commitment then we would need  different source for a list of Dates.

Also, if the reason why you are building the table PEND_SUM is because there is so much data in the commitment table that it becomes necessary to manifest that data at idle times, then we will need a different approach (like your request for a stored procedure doing incremental updates).

So, there's a couple more questions for you... Namely missing dates, and, table size (number of rows).

Anyway, a bit of code to consider and to help explain things in the world of SETS instead of RBAR :

-- do not try this - it is here to educate as a first step - it might run, but the results will have errors

SELECT convert(date, DS.recievedate) as AgeDate, 
       SUM(case when c.sampleType in ('P', 'R', 'D') then 1 else 0 end) as PSRD,
       SUM(case when c.sampleType = 'I' then 1 else 0 end) as QC,
       SUM(case when c.sampleType = 'T' then 1 else 0 end) as FFS

FROM commitment as DS
     LEFT JOIN Commitment as C on DS.recievedate >= C.recievedate and DS.sampletype = C.sampletype and DS.samplestatus = c.samplestatus 
WHERE DS.SampleType in ('P', 'R', 'D','I','T') and DS.SampleStatus <900
GROUP by convert(date, DS.recievedate)
ORDER by convert(date, DS.recievedate) desc

Select allOpen in new window

Now, if there are significant number of rows in commitments, then linking that many rows, that many times would be extremely painful and will lead to errors when aggregated.

So, to improve performance, we would ideally have a list of distinct dates. And depending on the datatype,  of recievedate we might need to cover off all milliseconds in the day. Because a DATETIME of 2017-08-20 19:20:21 will always be greater than a DATE of 2017-08-20

Which is why the above query is more than a little flawed. It is essentially showing aggregated data (multiple times) before AgeDate

What we really need is a list of DATETIMES as at end of day. To do that, we can change the above query to only get the distinct dates from the commitment table. But we have to massage a bit so it becomes a subquery

-- this one you can try - though - if there are a LOT of rows, let's discuss first

SELECT DS.AgeDate, 
       SUM(case when c.sampleType in ('P', 'R', 'D') then 1 else 0 end) as PSRD,
       SUM(case when c.sampleType = 'I' then 1 else 0 end) as QC,
       SUM(case when c.sampleType = 'T' then 1 else 0 end) as FFS

FROM (select distinct convert(char(8),recievedate,112) + ' 23:59:59.997' as agedate from commitment) as DS
     LEFT JOIN Commitment as C on C.recievedate <= DS.agedate AND C.SampleType in ('P','R','D','I','T') and C.SampleStatus <900
GROUP by DS.agedate
ORDER by DS.agedate desc

Select allOpen in new window

But there is a lot more we can do. Notice how I changed the 'where' with and 'and'. That's to allow for some index optimisation (e.g. create a covering index over recievedate+sampletype+samplestatus). We can replace the subquery with a CTE (common table expression), introduce a list of dates (without using commitment table). We can express similar to your PEND_SUM table as a View, or materialise the data.... and write a stored procedure if that is the way you want to proceed.

We can even write code to update your PEND_SUM table via a few different ways.

Lots of choices once we get inside SQL and start to use SETS.

But we need to be guided by you... Your comfort level and experience plus data volumes are all important. After all you will need to maintain it :)

Cheers,
Mark Wills

Mark's comments about DATETIME and time portions and day boundaries is correct.

BUT: I would be astonished, when a column named ReceiveDate would have any other data type than DATE..

So as Mark wrote, we need more input.

Hi All and wow great feedback and so many possibilities.   I am swamped with validation so not had time to take all the comments in - but absolutely will by end of week.  I am experienced in Oracle and SQL Sever but it has been about 10 years since I had to work with SQL.  We are working currently in an early version of SQL - will be moving to latest version at the end of the year.

The Commitment Table is significant large and continuely growing.  It also is pulling data from other outside sources and is a transactional table, so users are acting against hourly.

We need to look at the PEND_SUM table as analytics table (as much as I would like this be just a query) it is better that it becomes a collection of Data.  (We will be using Spotfire reporting against the PEND_SUM Tbl).   Even the VBA code (though not optimized) Takes over 24 hours to run if I start with first value from 1998.  After the first time populating the table it only takes a matter of seconds to run based on the last date recorded in the PEND_SUM Table - just would like this to be an automated scheduled process in SQL so I don't have to remember to run it every day.

Date field - need to look at every date going back to 1998 regardless if that date exist in Commit Table for something received, pending, etc..  As stated above "make use of a numbers (or calendar) table for aggregation"   Now I only need to consider DATE (time is not required for consideration as the measured value is only by date)

I will look in more detail at all the solution later this week and see what might work best.   Thank you everyone for all your help.

The Commitment Table is significant large

How large?  How many rows? How much columns?

You cannot compare the VBA run-time, cause it is RBAR approach. Using the correct set-based approach can reduce this to minutes, even seconds.

Take a look at Kimball. Using the correct OLAP approach, dimensional model instead of relational model and a proper ETL strategy can also reduce run-time drastically.

And last but not least:

It also is pulling data from other outside sources

Sure that not this is causing the major time of your processing time? Depending on the kind of "outside" this can have drastic impact.

Thannk you everyone.  This was great help and am pulling together the stored solution to run as a scheduled process.