datediff on dates contained in single column

Question

Post reply

datediff on dates contained in single column

whitlander

SSChasing Mays

Points: 637
More actions
December 2, 2009 at 8:58 am

#139757

I have several columns:
ID | Source | Event | User | Time | Notes |
- where data in column 'event' can be either 'A' or 'B' but share the same ID
- where column 'time' is the time of the entry and in format '2009-08-27 10:25:52.360'
- each row being an entry at a specific point in time.
I'm trying to work out the 'number of days' between A and B entries for a particualr ID and where event 'A' usually comes before 'B'. However sometimes event 'B' comes before 'A' and so in that instance I would want the output 'number of days' to be a negative integer.
Any help will be very much appreciated.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Join the table with itself on ID.

Would look something like:

select *, datediff(day, EventA.Time, EventB.Time) as Days

from dbo.MyTable EventA

full outer join dbo.MyTable EventB

on EventA.ID = EventB.ID

and EventA.Event = 'A'

and EventB.Event = 'B';

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

Gus has a good solution. If you want absolute days, maybe enclose it in an ABS() function?

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Heh... absolutely... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

GSquared SSC Guru Points: 260824 More actions · Answer 4

Steve Jones - Editor (12/2/2009)
Gus has a good solution. If you want absolute days, maybe enclose it in an ABS() function?

However sometimes event 'B' comes before 'A' and so in that instance I would want the output 'number of days' to be a negative integer.

He wants a negative number.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

GSquared (12/3/2009)
He wants a negative number.

Heh... -42. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

GSquared SSC Guru Points: 260824 More actions · Answer 6

Jeff Moden (12/3/2009)
GSquared (12/3/2009)
He wants a negative number.
Heh... -42. 😀

i² ? 42

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

whitlander SSChasing Mays Points: 637 More actions · Answer 7

whitlander

SSChasing Mays

Points: 637

December 9, 2009 at 5:07 am

#1089749

Thanks all.

whitlander SSChasing Mays Points: 637 More actions · Answer 8

Its been a while since i posted the question above but I've recently noticed a problem with the code. Utilising the ABS() suggestion above, I came up with the following

SELECT planned.ID, planned.DataSource_ID, planned.EventType_ID, planned.User_ID, planned.Period, planned.Notes, planned.Success,

(SELECT TOP (1) - DATEDIFF(dd, Period, planned.Period) AS Expr1

FROM Event AS actual

WHERE (DataSource_ID = planned.DataSource_ID) AND (EventType_ID IN (5, 6))

ORDER BY ABS(DATEDIFF(dd, Period, planned.Period))) AS 'KPI (Days Over/Under Planned [+ / -])', DataSource.Name

FROM Event AS planned INNER JOIN

DataSource ON planned.DataSource_ID = DataSource.ID

WHERE (planned.Period < DATEADD(mm, - 1, GETDATE())) AND (planned.EventType_ID = 11)

GO

Where a date diff is calculated within the same month i.e. where the planned.period dates used in the date diff are both in June 20-07-2010 (EventType=5) and 10-07-2010 (EventType=11), the output, '-10', is accurate.

However where the planned.period dates needed are speard over two month:10-07-2010(EventType=5) and 05-06-2010(EventType=11), the output is inaccurate because instead of using 05-06-2010 in the datediff, the output considers the next nearest planned.period in July.

Hope this is understandable

Andy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Hi Andy

Can you please provide a CREATE TABLE script for table Event and a script to populate it with some dummy data which demonstrates your problem?

Is the table DataSource necessary? Does the output rowcount change if the reference to it is commented out?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

whitlander SSChasing Mays Points: 637 More actions · Answer 10

Hello there. Thanks for your help.

The Create Table:

CREATE TABLE [dbo].[Event](

[ID] [int] IDENTITY(1,1) NOT NULL,

[DataSource_ID] [int] NOT NULL,

[EventType_ID] [int] NOT NULL,

[User_ID] [int] NOT NULL,

[Period] [datetime] NULL,

[Notes] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

[Success] [int] NULL,

CONSTRAINT [PK_Event] PRIMARY KEY NONCLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

This is populated by running the following SP:

CREATE TABLE [dbo].[Event](

[ID] [int] IDENTITY(1,1) NOT NULL,

[DataSource_ID] [int] NOT NULL,

[EventType_ID] [int] NOT NULL,

[User_ID] [int] NOT NULL,

[Period] [datetime] NULL,

[Notes] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

[Success] [int] NULL,

CONSTRAINT [PK_Event] PRIMARY KEY NONCLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

An example of the table looks like:

ID, DataSource_ID, Eventtype_ID, User_ID, Period, Notes, Success

485223511326/11/2015 00:00:00Planned Availabilty in DW1

485233511303/12/2015 00:00:00Planned Availabilty in DW1

485243511311/12/2015 00:00:00Planned Availabilty in DW1

485253511317/12/2015 00:00:00Planned Availabilty in DW1

485263511324/12/2015 00:00:00Planned Availabilty in DW1

485273511331/12/2015 00:00:00Planned Availabilty in DW1

485283511307/01/2016 00:00:00Planned Availabilty in DW1

485293511314/01/2016 00:00:00Planned Availabilty in DW1

40675365116/03/2010 07:57:07System Generated1

40702365117/03/2010 07:34:50System Generated1

40735365118/03/2010 07:25:39System Generated1

40778365119/03/2010 07:34:54System Generated1

40861365122/03/2010 07:25:38System Generated1

40884365123/03/2010 07:25:19System Generated1

40915365124/03/2010 07:34:27System Generated1

40945365125/03/2010 07:25:37System Generated1

40976365126/03/2010 07:25:37System Generated1

41061365129/03/2010 07:25:32System Generated1

41087365130/03/2010 07:34:28System Generated1

41115365131/03/2010 07:25:04System Generated1

41148365101/04/2010 07:34:32System Generated1

41191365102/04/2010 07:30:09System Generated1

41291365105/04/2010 07:25:48System Generated1

41316365106/04/2010 07:34:29System Generated1

41355365107/04/2010 07:34:51System Generated1

41383365108/04/2010 07:25:05System Generated1

41416365109/04/2010 07:25:44System Generated1

41510365112/04/2010 07:35:11System Generated1

41538365113/04/2010 07:27:14System Generated1

41573365114/04/2010 07:25:57System Generated1

41727365119/04/2010 08:52:28System Generated1

41759365120/04/2010 07:27:42System Generated1

41791365121/04/2010 07:26:12System Generated1

41823365122/04/2010 07:29:38System Generated1

41855365123/04/2010 07:27:07System Generated1

41952365126/04/2010 07:36:46System Generated1

41980365127/04/2010 07:27:30System Generated1

42014365128/04/2010 07:36:39System Generated1

42049365129/04/2010 07:27:31System Generated1

42084365130/04/2010 07:27:28System Generated1

42193365103/05/2010 07:27:42System Generated1

42225365104/05/2010 07:27:30System Generated1

42259365105/05/2010 07:27:46System Generated1

42289365106/05/2010 07:37:14System Generated1

42326365107/05/2010 07:27:57System Generated1

42431365110/05/2010 11:33:13System Generated1

42484365111/05/2010 07:28:49System Generated1

42531365112/05/2010 07:26:58System Generated1

49760365113/05/2010 07:27:40System Generated1

49798365114/05/2010 07:27:41System Generated1

49908365117/05/2010 07:31:03System Generated1

49949365118/05/2010 07:28:21System Generated1

49980365119/05/2010 07:27:40System Generated1

50036365120/05/2010 07:28:03System Generated1

50072365121/05/2010 07:27:45System Generated1

50180365124/05/2010 07:28:06System Generated1

50207365125/05/2010 07:28:22System Generated1

50240365126/05/2010 07:28:01System Generated1

50272365127/05/2010 07:29:57System Generated1

50310365128/05/2010 07:28:42System Generated1

50406365131/05/2010 07:27:58System Generated1

50433365101/06/2010 07:29:35System Generated1

50476365102/06/2010 07:27:51System Generated1

50506365103/06/2010 07:27:27System Generated1

50546365104/06/2010 07:28:40System Generated1

50652365107/06/2010 07:29:20System Generated1

50686365108/06/2010 07:28:23System Generated1

50720365109/06/2010 07:38:49System Generated1

50759365110/06/2010 07:28:32System Generated1

50793365111/06/2010 07:28:49System Generated1

50894365114/06/2010 07:28:04System Generated1

50934365115/06/2010 07:28:32System Generated1

50972365116/06/2010 07:32:04System Generated1

51022365117/06/2010 07:29:21System Generated1

51059365118/06/2010 07:28:19System Generated1

51170365121/06/2010 07:29:36System Generated1

51206365122/06/2010 07:33:25System Generated1

51246365123/06/2010 07:38:08System Generated1

51288365124/06/2010 07:40:04System Generated1

51325365125/06/2010 07:29:11System Generated1

51437365128/06/2010 07:38:35System Generated1

51480365129/06/2010 07:29:57System Generated1

51518365130/06/2010 07:29:09System Generated1

51555365101/07/2010 07:29:45System Generated1

51589365102/07/2010 07:40:52System Generated1

51731365105/07/2010 09:08:03System Generated1

51770365106/07/2010 07:29:27System Generated1

51812365107/07/2010 07:29:39System Generated1

51839365108/07/2010 07:38:23System Generated1

51878365109/07/2010 07:37:51System Generated1

52008365112/07/2010 07:43:16System Generated1

52047365113/07/2010 07:28:51System Generated1

52092365114/07/2010 07:30:26System Generated1

52143365115/07/2010 07:41:31System Generated1

52178365116/07/2010 07:32:06System Generated1

52302365119/07/2010 07:38:57System Generated1

52341365120/07/2010 07:38:04System Generated1

52383365121/07/2010 07:38:39System Generated1

52420365122/07/2010 07:38:16System Generated1

52459365123/07/2010 07:28:49System Generated1

52553365126/07/2010 07:29:06System Generated1

52590365127/07/2010 07:38:45System Generated1

52628365128/07/2010 07:32:23System Generated1

535366210/06/2009 20:41:18System: Successfully Loaded 193 files for 1

536366210/06/2009 20:41:18System: Successfully Loaded 25 files for 1

539366211/06/2009 08:34:25System: Successfully Loaded 190 files for 1

540366211/06/2009 08:34:25System: Successfully Loaded 25 files for 1

551366215/06/2009 12:27:58System: Successfully Loaded 188 files for 1

552366215/06/2009 12:27:58System: Successfully Loaded 25 files for 1

556366215/06/2009 12:43:00System: Successfully Loaded 188 files for 1

557366215/06/2009 12:43:00System: Successfully Loaded 25 files for 1

583366217/06/2009 03:00:56System: Successfully Loaded 234 files for 1

584366217/06/2009 03:00:56System: Successfully Loaded 215 files for 1

585366217/06/2009 04:16:04System: Successfully Loaded 19 files for 1

587366217/06/2009 22:26:41System: Successfully Loaded 63 files for 1

588366217/06/2009 22:26:41System: Successfully Loaded 209 files for 1

590366218/06/2009 02:32:46System: Successfully Loaded 171 files for 1

591366218/06/2009 02:32:47System: Successfully Loaded 25 files for 1

597366218/06/2009 22:31:02System: Successfully Loaded 46 files for 1

598366218/06/2009 22:31:03System: Successfully Loaded 210 files for 1

603366219/06/2009 02:23:20System: Successfully Loaded 188 files for 1

604366219/06/2009 02:23:20System: Successfully Loaded 24 files for 1

607366219/06/2009 22:34:36System: Successfully Loaded 51 files for 1

608366219/06/2009 22:34:37System: Successfully Loaded 210 files for 1

610366220/06/2009 02:34:12System: Successfully Loaded 183 files for 1

611366220/06/2009 02:34:12System: Successfully Loaded 24 files for 1

619366220/06/2009 22:26:19System: Successfully Loaded 33 files for 1

620366220/06/2009 22:26:19System: Successfully Loaded 209 files for 1

621366221/06/2009 02:50:54System: Successfully Loaded 201 files for 1

622366221/06/2009 02:50:54System: Successfully Loaded 25 files for 1

625366221/06/2009 22:26:38System: Successfully Loaded 60 files for 1

626366221/06/2009 22:26:38System: Successfully Loaded 209 files for 1

627366222/06/2009 02:37:33System: Successfully Loaded 174 files for 1

628366222/06/2009 02:37:33System: Successfully Loaded 25 files for 1

631366222/06/2009 22:23:33System: Successfully Loaded 161 files for 1

633366223/06/2009 02:59:25System: Successfully Loaded 234 files for 1

634366223/06/2009 02:59:25System: Successfully Loaded 73 files for 1

637366223/06/2009 22:31:05System: Successfully Loaded 46 files for 1

638366223/06/2009 22:31:05System: Successfully Loaded 210 files for 1

640366224/06/2009 02:53:47System: Successfully Loaded 188 files for 1

641366224/06/2009 02:53:47System: Successfully Loaded 24 files for 1

645366224/06/2009 22:30:34System: Successfully Loaded 47 files for 1

646366224/06/2009 22:30:35System: Successfully Loaded 210 files for 1

40915365124/03/2010 07:34:27TIS System Generated1

40945365125/03/2010 07:25:37TIS System Generated1

40976365126/03/2010 07:25:37TIS System Generated1

41061365129/03/2010 07:25:32TIS System Generated1

41087365130/03/2010 07:34:28TIS System Generated1

41115365131/03/2010 07:25:04TIS System Generated1

41148365101/04/2010 07:34:32TIS System Generated1

41191365102/04/2010 07:30:09TIS System Generated1

41291365105/04/2010 07:25:48TIS System Generated1

41316365106/04/2010 07:34:29TIS System Generated1

41355365107/04/2010 07:34:51TIS System Generated1

41383365108/04/2010 07:25:05TIS System Generated1

41416365109/04/2010 07:25:44TIS System Generated1

41510365112/04/2010 07:35:11TIS System Generated1

41538365113/04/2010 07:27:14TIS System Generated1

41573365114/04/2010 07:25:57TIS System Generated1

41727365119/04/2010 08:52:28TIS System Generated1

41759365120/04/2010 07:27:42TIS System Generated1

41791365121/04/2010 07:26:12TIS System Generated1

41823365122/04/2010 07:29:38TIS System Generated1

41855365123/04/2010 07:27:07TIS System Generated1

41952365126/04/2010 07:36:46TIS System Generated1

41980365127/04/2010 07:27:30TIS System Generated1

42014365128/04/2010 07:36:39TIS System Generated1

42049365129/04/2010 07:27:31TIS System Generated1

42084365130/04/2010 07:27:28TIS System Generated1

42193365103/05/2010 07:27:42TIS System Generated1

42225365104/05/2010 07:27:30TIS System Generated1

42259365105/05/2010 07:27:46TIS System Generated1

42289365106/05/2010 07:37:14TIS System Generated1

42326365107/05/2010 07:27:57TIS System Generated1

42431365110/05/2010 11:33:13TIS System Generated1

42484365111/05/2010 07:28:49TIS System Generated1

42531365112/05/2010 07:26:58TIS System Generated1

49760365113/05/2010 07:27:40TIS System Generated1

49798365114/05/2010 07:27:41TIS System Generated1

49908365117/05/2010 07:31:03TIS System Generated1

49949365118/05/2010 07:28:21TIS System Generated1

49980365119/05/2010 07:27:40TIS System Generated1

50036365120/05/2010 07:28:03TIS System Generated1

50072365121/05/2010 07:27:45TIS System Generated1

50180365124/05/2010 07:28:06TIS System Generated1

50207365125/05/2010 07:28:22TIS System Generated1

50240365126/05/2010 07:28:01TIS System Generated1

50272365127/05/2010 07:29:57TIS System Generated1

50310365128/05/2010 07:28:42TIS System Generated1

50406365131/05/2010 07:27:58TIS System Generated1

50433365101/06/2010 07:29:35TIS System Generated1

50476365102/06/2010 07:27:51TIS System Generated1

50506365103/06/2010 07:27:27TIS System Generated1

50546365104/06/2010 07:28:40TIS System Generated1

50652365107/06/2010 07:29:20TIS System Generated1

50686365108/06/2010 07:28:23TIS System Generated1

50720365109/06/2010 07:38:49TIS System Generated1

50759365110/06/2010 07:28:32TIS System Generated1

50793365111/06/2010 07:28:49TIS System Generated1

50894365114/06/2010 07:28:04TIS System Generated1

50934365115/06/2010 07:28:32TIS System Generated1

50972365116/06/2010 07:32:04TIS System Generated1

51022365117/06/2010 07:29:21TIS System Generated1

51059365118/06/2010 07:28:19TIS System Generated1

51170365121/06/2010 07:29:36TIS System Generated1

51206365122/06/2010 07:33:25TIS System Generated1

51246365123/06/2010 07:38:08TIS System Generated1

51288365124/06/2010 07:40:04TIS System Generated1

51325365125/06/2010 07:29:11TIS System Generated1

51437365128/06/2010 07:38:35TIS System Generated1

51480365129/06/2010 07:29:57TIS System Generated1

51518365130/06/2010 07:29:09TIS System Generated1

51555365101/07/2010 07:29:45TIS System Generated1

51589365102/07/2010 07:40:52TIS System Generated1

51731365105/07/2010 09:08:03TIS System Generated1

51770365106/07/2010 07:29:27TIS System Generated1

51812365107/07/2010 07:29:39TIS System Generated1

51839365108/07/2010 07:38:23TIS System Generated1

51878365109/07/2010 07:37:51TIS System Generated1

52008365112/07/2010 07:43:16TIS System Generated1

52047365113/07/2010 07:28:51TIS System Generated1

52092365114/07/2010 07:30:26TIS System Generated1

52143365115/07/2010 07:41:31TIS System Generated1

52178365116/07/2010 07:32:06TIS System Generated1

52302365119/07/2010 07:38:57TIS System Generated1

52341365120/07/2010 07:38:04TIS System Generated1

52383365121/07/2010 07:38:39TIS System Generated1

52420365122/07/2010 07:38:16TIS System Generated1

52459365123/07/2010 07:28:49TIS System Generated1

52553365126/07/2010 07:29:06TIS System Generated1

52590365127/07/2010 07:38:45TIS System Generated1

52628365128/07/2010 07:32:23TIS System Generated1

535366210/06/2009 20:41:18System: Successfully Loaded 193 files for 5JX1

536366210/06/2009 20:41:18System: Successfully Loaded 25 files for 5NQ1

539366211/06/2009 08:34:25System: Successfully Loaded 190 files for 5JX1

540366211/06/2009 08:34:25System: Successfully Loaded 25 files for 5NQ1

551366215/06/2009 12:27:58System: Successfully Loaded 188 files for 5JX1

552366215/06/2009 12:27:58System: Successfully Loaded 25 files for 5NQ1

556366215/06/2009 12:43:00System: Successfully Loaded 188 files for 5JX1

557366215/06/2009 12:43:00System: Successfully Loaded 25 files for 5NQ1

583366217/06/2009 03:00:56System: Successfully Loaded 234 files for 5JX1

584366217/06/2009 03:00:56System: Successfully Loaded 215 files for 5NQ1

585366217/06/2009 04:16:04System: Successfully Loaded 19 files for 5NQ1

587366217/06/2009 22:26:41System: Successfully Loaded 63 files for 5JX1

588366217/06/2009 22:26:41System: Successfully Loaded 209 files for 5NQ1

590366218/06/2009 02:32:46System: Successfully Loaded 171 files for 5JX1

591366218/06/2009 02:32:47System: Successfully Loaded 25 files for 5NQ1

597366218/06/2009 22:31:02System: Successfully Loaded 46 files for 5JX1

598366218/06/2009 22:31:03System: Successfully Loaded 210 files for 5NQ1

603366219/06/2009 02:23:20System: Successfully Loaded 188 files for 5JX1

604366219/06/2009 02:23:20System: Successfully Loaded 24 files for 5NQ1

607366219/06/2009 22:34:36System: Successfully Loaded 51 files for 5JX1

608366219/06/2009 22:34:37System: Successfully Loaded 210 files for 5NQ1

610366220/06/2009 02:34:12System: Successfully Loaded 183 files for 5JX1

611366220/06/2009 02:34:12System: Successfully Loaded 24 files for 5NQ1

619366220/06/2009 22:26:19System: Successfully Loaded 33 files for 5JX1

620366220/06/2009 22:26:19System: Successfully Loaded 209 files for 5NQ1

621366221/06/2009 02:50:54System: Successfully Loaded 201 files for 5JX1

622366221/06/2009 02:50:54System: Successfully Loaded 25 files for 5NQ1

625366221/06/2009 22:26:38System: Successfully Loaded 60 files for 5JX1

626366221/06/2009 22:26:38System: Successfully Loaded 209 files for 5NQ1

627366222/06/2009 02:37:33System: Successfully Loaded 174 files for 5JX1

628366222/06/2009 02:37:33System: Successfully Loaded 25 files for 5NQ1

631366222/06/2009 22:23:33System: Successfully Loaded 161 files for 5NQ1

633366223/06/2009 02:59:25System: Successfully Loaded 234 files for 5JX1

634366223/06/2009 02:59:25System: Successfully Loaded 73 files for 5NQ1

637366223/06/2009 22:31:05System: Successfully Loaded 46 files for 5JX1

638366223/06/2009 22:31:05System: Successfully Loaded 210 files for 5NQ1

640366224/06/2009 02:53:47System: Successfully Loaded 188 files for 5JX1

641366224/06/2009 02:53:47System: Successfully Loaded 24 files for 5NQ1

645366224/06/2009 22:30:34System: Successfully Loaded 47 files for 5JX1

646366224/06/2009 22:30:35System: Successfully Loaded 210 files for 5NQ1

Cheers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Hi Andy, I'm sorry but I don't have time to convert your post into a set of statements which will populate the sample table.

It should look something like this:

INSERT INTO <Tablename> (<columnlist>)

SELECT <list of values> UNION ALL

.

SELECT <list of values>

Please read the link in my sig if you are unsure.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

whitlander SSChasing Mays Points: 637 More actions · Answer 12

Sorry about that. How's this?

INSERT INTO [PELVIS].[dbo].[Event]

([DataSource_ID]

,[EventType_ID]

,[User_ID]

,[Period]

,[Notes]

,[Success])

VALUES

(<DataSource_ID, int,>

,<EventType_ID, int,>

,<User_ID, int,>

,<Period, datetime,>

,<Notes, varchar(255),>

,<Success, int,>)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

whitlander (7/28/2010)
Sorry about that. How's this?
INSERT INTO [PELVIS].[dbo].[Event]
([DataSource_ID]
,[EventType_ID]
,[User_ID]
,[Period]
,[Notes]
,[Success])
VALUES
(<DataSource_ID, int,>
,<EventType_ID, int,>
,<User_ID, int,>
,<Period, datetime,>
,<Notes, varchar(255),>
,<Success, int,>)

If I copy this, paste it into a SSMS window and run it, will it create a table and populate it with sample data?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

whitlander SSChasing Mays Points: 637 More actions · Answer 14

thanks for being patient with me Chris. I'll get you some better raw material post haste...