Post reply

datediff on dates contained in single column

  • 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.

  • December 2, 2009 at 9:00 am

    #1086586

    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

  • December 2, 2009 at 9:19 am

    #1086606

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

  • December 2, 2009 at 7:35 pm

    #1086851

    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)

  • December 3, 2009 at 6:51 am

    #1087040

    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

  • December 3, 2009 at 7:48 pm

    #1087456

    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)

  • December 4, 2009 at 6:46 am

    #1087657

    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

  • December 9, 2009 at 5:07 am

    #1089749

    Thanks all.

  • July 28, 2010 at 4:59 am

    #1199158

    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

  • July 28, 2010 at 6:00 am

    #1199174

    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

  • July 28, 2010 at 8:55 am

    #1199278

    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

  • July 28, 2010 at 9:01 am

    #1199284

    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

  • July 28, 2010 at 9:23 am

    #1199304

    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,>)

  • July 28, 2010 at 9:37 am

    #1199316

    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

  • July 29, 2010 at 5:50 am

    #1199723

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

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

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