Thanks for the sample data - what a treat 🙂

Have a play with this. To "ice the cake", use DENSE_RANK() to renumber the groups. Ask if you're unsure but you...

Can't you tell by looking at the data? Can you post a few random samples here?

Tac11 (9/3/2015)

---

Hi all,

I am able to create the table below but trying to execute 'Insert' query below I get this :

Msg 8152, Level 16, State 10, Line 2

String or binary...

For your amusement, here's...

tt-615680 (9/3/2015)

---

CONVERT(date, col1) AS 'DateTimeStamp', is the line I've chabged unless I need to change it to something else?

and I get the following error:

Conversion failed when converting date and/or time...

-- sample data:

DROP TABLE #source_table_1

CREATE TABLE #source_table_1 ([contract] VARCHAR(2), [date] DATE, amount INT)

INSERT INTO #source_table_1 ([contract], [date], amount) VALUES

('XX','20160101',100),

('XX','20160104',200),

('YY','20160101',300),

('YY','20160104',400),

('ZZ','20160101',500),

('ZZ','20160104',600)

DROP TABLE #source_table_2

CREATE TABLE #source_table_2 ([date] DATE)

INSERT INTO #source_table_2 ([date]) VALUES...

tt-615680 (9/3/2015)

---

I have now changed the date time to be converted to date but I still get the following message:

Conversion failed when converting date and/or time from character string.

-- 9...

tt-615680 (9/3/2015)

---

Thank you for your reply!

I commented out the insert into statement but all I get in return is the details about the files, e.g. file name file extension. LSN...

in section 9

-- 9 - Populate the #ParsedFileList table with the final data

INSERT INTO #ParsedFileList

(DateTimeStamp,

LSN,

FileSize,

FileName1)

SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS...

-- Your query looks like this...

DELETE FROM CSV_Details

WHERE CSVID NOT IN

(

-- level 2

SELECT CSVID

FROM CSV_Details d2

INNER JOIN Transmittal_Planning_MarkNo m2

ON m2.Mark_No = d2.Mark_Rev_No_fab

WHERE d2.DeleteFlag IS NOT...

yb751 (9/1/2015)

---

You shouldn't have multiple CompanyID's stored this way. You'll want to normalize your data. You can't do joins that way unless the actual ID in tableB was...

WHERE [DETERMINATION] <> 'Denied'

AND [Number] ='A150731000039'

---- Removes incorrect records where these names match----

AND (

[Name] IS NULL

OR [PName] IS NULL

OR ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')

)

Avoid using...

TSQL Tryer (9/1/2015)

---

So, I have a table "table a"

This has two columns. "username" and "companyid"

A sample of data for "companyid" is - "1,2,3,4".

How would I go about join...

Jeff Moden (8/25/2015)

---

No need to Google. Here's a chart...

ScottPletcher (8/25/2015)

---

ChrisM@Work (8/25/2015)

---

ScottPletcher (8/25/2015)

---

ChrisM@Work (8/25/2015)

---

ScottPletcher (8/25/2015)

---

ChrisM@Work (8/25/2015)

---

ScottPletcher (8/21/2015)

---

ChrisM@Work (8/21/2015)

---

ScottPletcher (8/21/2015)

---

ChrisM@Work (8/21/2015)

The inner select can be accelerated with this index:

CREATE INDEX ix_Helper ON [cts].[exception_Main]

(productArea, reportable, reportYear, reportMonth, queueID)

INCLUDE (volume,...