Jason-299789 (1/8/2013)

---

Final Edit,

Dwain, I figured out why yours didnt work you're addind on the Julian day which is 1-365/366

I think you need to add the Week day number...

Or, even simpler:

WITH Filenames (fn) AS (

SELECT 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'

UNION ALL SELECT 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT')

SELECT fn=LEFT(fn, PATINDEX('%[0-9]%', fn)-2)

FROM Filenames;

sqlstud (1/8/2013)

---

dwain.c (1/8/2013)

---

Here are two ways:

WITH Filenames (fn) AS (

SELECT 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'

UNION ALL SELECT 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT')

SELECT fn=SUBSTRING(fn, 1, CHARINDEX('.', fn))

FROM Filenames;

WITH Filenames (fn) AS (

SELECT 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'

UNION ALL SELECT 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT')

SELECT fn=Item

FROM Filenames

CROSS APPLY...

alex 64682 (1/7/2013)

---

Many thanks for all the responses. I haven't encountered Openrowset before but it looks like the best option. Not entirely sure where to place the remote IP Address,...

CELKO (1/7/2013)

---

I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year,...

Jason-299789 (1/8/2013)

---

Id love to see the GenerateCalendar function.

Coming from a DW background the persisted calendar table is second nature, and I'm surprised more traditional OLTP systems dont implement...

Here are two ways:

WITH Filenames (fn) AS (

SELECT 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'

UNION ALL SELECT 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT')

SELECT fn=SUBSTRING(fn, 1, CHARINDEX('.', fn))

FROM Filenames;

WITH Filenames (fn) AS (

SELECT 'XX_YYYYY_AA_BBB_SampleTransaction_120807_55322.TXT'

UNION ALL SELECT 'XX_YYYYYY_DDD_MasterTransaction_120807_00005.TXT')

SELECT fn=Item

FROM Filenames

CROSS APPLY PatternSplitCM(fn, '[0-9a-zA-Z_]')

WHERE...

Jason-299789 (1/8/2013)

---

Nice Alternative dwain, love the use of cross applys with the tally and inline month generator, as well as the normalisation of the month to always be the first....

Why not use:

TRUNCATE TABLE tblIDNames

Instead of:

DELETE FROM tblIDNames

aerojockey (1/7/2013)

---

I have an application that needs to make an atomic update to an item in a table. It has to read the value, do something complicated to it,...

If you don't want to create a calendar table, you can do this (which really just creates the calendar table on the fly).

CREATE TABLE #Contracts

(

ContractNumber INT

,ContractStartDate DATETIME

,ContractEndDate DATETIME

,ContractAmount MONEY

);

INSERT INTO...

ericwood8 (1/7/2013)

---

First off, I love this article.

On Sign Reversals section, imagine that entry 7 & 8 were $500 instead of $400. :

UNION ALL SELECT 500, '+' --...

Hi bthomson! Thanks for dropping by.

There is a link in...

Here is the FUNCTION referenced in the article. Copy/paste this code into an SSMS window on your database and execute the script.

-- PatternSplitCM will split a string based on...

Agreed that not sure why you wouldn't want to use a CTE, but here's another alternative.

DECLARE @H TABLE

(House_Acc INT, Accountid INT, repcode VARCHAR(10))

INSERT INTO @H

SELECT 123,...