Tally Date Change Problem
Ceola Roefaro
Ha, maybe you'll even get a subsidized license for that version from Dell, NetApp, or whoever your SAN vendor is if you system version everything. Add in Ledger tables and they'll give you a few free drives.
This has caught me out a few times where the same code in YYYY-MM-DD format ends up being interpreted as either DD/MM/YYYY or MM/DD/YYYY depending on the server so it would be good if they fixed that as entering dates without the - just doesn't look nice.
I always add SET DATEFORMAT ymd at the top of scripts just to make sure it works as expected. Often in the UK servers end up being installed in USA language where IT teams just press Next, Next Next... on the installers.
I realise it is historical so may never be "fixed" and we have DATETIME2 but mistakes should not need to remain for eternity and isn't that why we have compatibility levels which often people forget to change and still have it set to 2008.
I have a table for events, with a field that specifies how frequently the event occurs (in days). I'd like to select all occurrences of the event within a given date range, including the calculated occurrences (e.g. if the first event date is Jan 6 2011 and it occurs every 7 days, you'd see Jan 13 and Jan 20 in the results).
Here are what the first 10 rows will look like with the understanding that the values for first_even_datet and occurs_every will be quite different because of the methods I used to generate constrained random data.
Just to be sure, you're going to need a Tally Table to duplicate the OPs experiment. Here's the code for that. If you already have one, make sure that it had the required unique clustered index (usually in the form of a PK) for performance reasons. I have modernized the row-source tables in the "pseudo-cursor" part of the code to NOT use the deprecated "syscolumns" view.
We're ready to rock. A part of the OP's code got swallowed by the forum but I was able to recover it using an edit of his original post. It actually looks like this except that I changed the "end date" to match the data that I just generated (and that's the only change I made). Since the code contains no scalar or multi-statement UDFs, I also turned on statistics to try to explain what's going on.
If you check out the highlighted arrow in the execution plan below, you find that it contains 11 MILLION actual rows because of the non-SARGable (SARG = "Search ARGument" and non-SARGable means it can't use an index properly) criteria that caused a full CROSS JOIN between the 11,000 row Tally Table and the 1,000 row #Events table. And those are ACTUAL rows, not ESTIMATED rows, folks.
The reason is because the "N" column of the Tally Table is used in a formula and the entire Tally Table must be scanned as a result for every row in the #Events table. This is a common error that makes people thing that Tally Tables produce slow code.
So, how do we fix it? Rather than using t.N to calculate a date for each row, let's take the difference of dates and divide by the number of days to figure out the number of occurrences needed to equate t.N to and see what happens. Note that the only thing I changed in the code below was the criteria in the WHERE clause to make the lookup on t.N SARGable (able to use an index to start and stop a seek followed by a range scan).
The bottom line is that while the use of a Tally Table is nearly a panacea of performance, you do have to use it correctly, just like anything else. You have to use "Best Practices", such as writing a SARGable WHERE clause to get it to us the index correctly, just like anything else.
Again, my most sincere apologies, especially to the OP, for being so late with this. I hope it will help someone in the future. I also apologize for not having the time to rewrite the rCTE example on this thread to show how bad it can be. If you're interested in why rCTEs are so bad and you don't mind SQLServerCentral.com membership, then here's an article on the subject. I'd post all that here but it's too long to do so.
Here is one method using Oracle (you can switch this to other engines by modifying the sub-query that generates consecutive numbers, see below). The idea behind this query is to generate a consecutive list of multipliers (e.g. 0, 1, 2, 3..., n) up to the window size (days between dates). This is what the sub-query returns. We use this to cross join with the event table and then limit the results to the requested date range.
I have now had two phone calls from HMRC regarding companies for which I process payroll stating that my month 6 payments do not tally with what is due according to the FPS returns. They have accepted that, looking back at prior months, it is clearly the 'amount due' which is incorrect, rather than the payment. Having looked at the HMRC online records for all five companies for which I process the payroll, they all have discrepancies in month 6. Some almost look as though the year to date figures have been used, although they don't quite tally - one is 70k out on a normal monthly amount due of around 20k. Has anyone else encountered this issue? I am using Miracle Lite to process the payroll and every month up to now has been fine.
HMRC tell me that the onus is on me to prove that it is not an issue with my software before they will investigate from their end. I have manually checked the FPS submission reports produced during the submission process and they all tally with the P32 and the amount that we paid.
It was only after we pressed them to explain how it was possible that they held an FPS report apparently submitted by us, which was totally at odds with the FPS report we held that they admitted there was a technical issue at their end. Apparently, this has resulted in the generation of duplicate FPS reports and their system is not picking up the correct FPS figures that have been submitted. I was informed that this technical issue has affected a number of employers.
We received a letter yesterday stating they had not received payment for Q2, however on the online portal our balance was nil? Again, the figure they quoted we owe is one they have seemingly pulled from thin air as doesn't at all tally with the submissions made by me, or with what is online! Aargh!
HMRC have since issued a report denying responsibility for online liability errors inferring that third party payroll software is to blame. I do not believe this and I suspect that the report was written not by independent IT experts (= really needed) reviewing HMRC's systems for recording FPS liabilities but rather by HMRC's own IT partners.
Sadly there are by HMRC's own admission 1% of employers affected by the duplicate records issue where HMRC corrupt the FPS based on the flawed design of RTI. Even 1% is 2000 schemes and they only admit to 1% at present as these are the self-selected 'underpayments' they have followed up so far. Now that the Generic Notifications Service is in place (that agents can access unlike the liabilities and payments viewer that only employers can see), I hope that the true scale of the issue with the flawed design of RTI will become clear that is leading to major reconciliation problems and worry for agents and employers alike. We know so far that duplicates (where the YTD figures are added in twice are caused by amending a start or leave date, payments after leaving and amending a DOB or gender - all commonplace payroll scenarios. On no account pay any monies over if you haves the FPS figures and they tally with the remittance paid. As for the matter to be referred to the disputes team at DMB, it usually then goes into a black hole for most of my clients for at least six months!
I have had discussion with HMRC recently as our online account was showing a balance owing, which I knew to be incorrect. When I spoke to them, they confirmed that the account on their records was showing up to date and clear. They referred me to their technical team, who deal with the online account. They checked the FPS and EPS, all confirmed as received OK, and our account clear. They had no explanation why the online account was showing a balance due (Which, incidentally, I knew to refer to an EPS)
I think where no agreement can be reached, inviting HMRC to audit the payroll records must be considered. If enough employers (and pension payers) were to do this, HMRC would quickly find another solution. And were HMRC to visit and discover the cause of discrepancies lay within the payroll software being used, this would surely enable the employer to seek compensation from the software provider?
Client 1 : received at Debt Management letter on 17th September requesting payment of 307.02. They forwarded it, from Hong Kong, to us as they were concerned, being a Queens Council this could be seriously harmful to his reputation ! We process the payroll and make payments to employees and make the necessary HMRC payment and thus, knew this could not be correct. On contacting HMRC the confirmed that they had a credit of this amount "sat there" and they would "re-jig" the payments to ensure this is allocated. (Words in "" are HMRC's actual words used).
Client 2 : received an overpayment notice dated 24th October advising they should reduce their next payment by this amount. However, the RTI submission for month 7, submitted on 10th October, was not showing on the clients "At a Glance". We contacted the Employer Section of HMRC and was advised that their system updated on the 6th and the 22nd of each month. How then, we asked can our submission of 10th October not have been updated prior to sending the overpayment notice of 24th October ? No answer was available and we were advised to check again on the 7th December. We imagine that there could be several employers out there that would see an overpayment notice, think yippee and reduce their next payment to HMRC without any further checking. At the year end an underpayment will be demanded - those employers are not going to be very happy then..............
Mike Nicholas, you may have hit the nail on the head. Last month, or maybe the end of month 5, I had an issue because I changed the start dates (because they'd been set up wrong) for all of our employees who had joined the companies at formation (under TUPE). Their start dates had incorrectly been set up as the original start dates of the old company, thus implying that they had had multiple employments in prior tax years, which I feared could lead to underpayment notices getting issued all over the place. I contacted HMRC and asked them what I should do when I discovered the error. The reply was 'just file the FPS next period with the corrected dates and there should be no problems'.
ff7609af8f