Why have ISO when you can have Her Majesty's Revenue and Customs! 🙂 🙂 🙂

http://www.taxadvisorypartnership.com/tax-compliance/why-does-the-uk-tax-year-start-on-6-april-each-year/

From what you've said, I think its also important to determine when your fiscal "week 1" ends. Since your fiscal "Day 1" could be any day of the week, then...

Does this report have to be written in T-SQL? Using an Excel pivot table with a data connection to the tables would allow for a dynamic output (as long as...

.. so why won't a sumple SUBSTRING(@str,from,length) do the job?

(like wot Chris said! 🙂 )

You could replace the multiple spaces with a single space first:

WHILE PATINDEX('% %',@str)>0-- replace multiple spaces with single spaces

BEGIN

SET @STR=REPLACE(@str,' ',' ')

END

So the whole thing...

Assuming normalising the tables isn't an option for you, how about something like this:

WITH cte AS (SELECT * FROM (VALUES (1),(2),(3)) AS temp (no))

SELECT 'DAR-901 MDES' as STUDYID,IR.VSFORMID as SVFORMID,...

Am I missing something, or aren't all those unions to the same source tables with the same joins and where clauses? The only difference I can see is in the...

Depends on your SET DATEFIRST settings: Whilst the default (English,US) is 7, meaning weeks start on Sunday, in the UK it is quite...

Perhaps a better soultion would be to use the LEAD and LAG windowing functions to avoid the self join and thus do it in a single query without using a...

How about this group of ctes? The first cte creates a table of Dates for the current year, the second cte expands these to add the Year, Month and DayOfWeek,...

How about

WITHcte1 AS (SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total',max(b.budget) as budget

FROM #TestData a

INNER JOIN #TestBudget b on (a.account = b.account)

GROUP BY a.id,...

If there is only ever one mid-string space then the split could be done as follows:

DECLARE @STR CHAR(40)= 'something unit'

SELECTSUBSTRING(@Str,1,CHARINDEX(' ',@Str)-1) AS [LeftPart],

SUBSTRING(@Str,(CHARINDEX(' ',@Str)+1),LEN(@Str)) AS [RightPart]

Can I take it you're trying to compare a date in a table with another that you're constructing on the fly?

i.e. that r.dbAddDate >= some date?

But "some date" in your...

Nothing like as elegant as that! I didn't appreciate the power of UnionAggregate and spent my...

A breakthrough! I've managed to find a way to sort the individual line segments into order, reversing those that were "backward" with respect to the others. In the process I...