kbnyny (5/5/2010)

---

why is this statement not working:

select t1.ACTNUMBR_1, t1.ACTNUMBR_4, t1.ACTINDX, t1.CREATDDT

from C1.dbo.AN00100 t1 union all

select t2.ACTNUMBR_1, t2.ACTNUMBR_4, t2.ACTINDX, t2.CREATDDT

from C2.dbo.AN00100 t2 union all

select t3.ACTNUMBR_1, t3.ACTNUMBR_4, t3.ACTINDX, t3.CREATDDT

from C3.dbo.AN00100 t3

WHERE...

When you do your testing, try the following:

before running your query, add

SET DATEFIRST 1 -- set the first day of a week = Monday

The result of your query might be...

keent (5/5/2010)

---

I'm very sorry about that. I was looking at the wrong month on the calendar and have since updated the expected results.

As for shops that cross over days we...

kbnyny (5/5/2010)

---

When removing the Where clause this is what I get. The column heading all on one row.

ACTNUMBR_1 ACTNUMBR_4ACTINDXCREATDDT

103 6140002909612/28/2006

103 ...

Ok, here's how I would do it (assuming it was a typo in your expected result set...)

;WITH cte AS -- transfomr the table into a more normalized structure

(

-- use...

I'm a little confused regarding your expected result:

April 3rd 2010 was a Saturday. So the Dallas shop was open 10 till 14.

Why do you expect to see data for hours...

Why do you want to do it dynamic?

Based on your sample data I would use a string split function to separate the names (the TallyTable article referenced in my signature...

You should consider normalizing your table (e.g. DayOfWeek, OpenTime (DATETIME),CloseTime (DATETIME) )

What would you do with your design if one of the shops decides to open 8:30?

Regarding the DayOfWeek value...

It is unlikely that SQL Server will "all of a sudden" display duplicate rows.

It is more likely that you have duplicate entries in one of your table leading and, based...

Even if you can't use it in production... It might help you to figure out why the data are cosidered "bad". You could use this information to modify the SSIS...

Glad I could help. 😀

But it was just the coded answer Jeff suggested first... (it's usually the other way around 😀 )

Something like this?

SELECT *

FROM

(

SELECT 'abc.de' col UNION ALL

SELECT 'abd'

) t

WHERE CHARINDEX('.',col) =0

Did you consider using BULK INSERT with an ERRORFILE specified as an option? The additional control file should help to identify the root cause.

Why do you want to use SSIS?

I would create a stored proc to send the mail and do the update and once I have that running as expected add a...

The query itself looks good, assuming you're either asking for data including '2009-12-31' (e.g. '2009-12-31 09:10:00') or your t1.CREATDDT column is already normalized (= not to include any time portion)

What...