Forum Replies Created

Viewing 15 posts - 286 through 300 (of 355 total)

  • RE: In way over my head

    The following query will return a resultset with 64 rows for each day that lies within the specified date range (@StartDate to @EndDate).

    The columns in the resultset are:

    SlotNumber intInteger between...

  • RE: In way over my head

    SELECT

    START.Value AS StartTime,

    STOP.Value AS StopTime,

    ...

  • RE: In way over my head

    Are you sure you want 64 columns in the resultset?

    I think it might be simpler to return 1 row per 15 minute slot, i.e. 64 rows per day.

    Also, do you...

  • RE: Conversion failed when converting datetime from character string

    If all you need are dates and can do with out time of day and assuming that all rows have date first with similar format, You can try this:

    datediff(d,convert(datetime,left(start,10)),convert(datetime,left(stop,10)))

    This...

  • RE: Conversion failed when converting datetime from character string

    What are the different date/time formats you've already got in your table?

    Is there any sort of control (e.g. front-end validation) over what strings can be inserted?

    How is data inserted...

  • RE: Time Duration Addition

    Either format the total hours worked in front end, or if this is not applicable something like:

    SELECT CONVERT(varchar(10), CONVERT(int, SUM(HoursWorked))) + ':'

    + RIGHT('0' + CONVERT(varchar(2), CONVERT(int, FLOOR(60.0 * SUM(HoursWorked)...

  • RE: Conversion failed when converting datetime from character string

    Storing date/times in SQL server as a varchar (or nvarchar) data type is usually a very bad idea.

    The following TSQL shows how you can convert your date/time string with time...

  • RE: count of consecutive value

    Jeff,

    You got me worried with your doubts about this ROW_NUMBER() difference technique.

    I used this technique in a proposed solution to this topic:

    http://www.sqlservercentral.com/Forums/Topic656492-338-1.aspx

    However, I think the proposed solution is valid,...

  • RE: Amalgamating Dates

    The query below produces a resultset that matches the data in tblExpectedResults provided by the OP. It requires a Tally table containing consecutive integers starting at 0 and containing at...

  • RE: 1-M join, concat all records

    Just thought of another way of dealing with the XML encoding, which is probably better than my previous offering. This method uses the XQuery value method to decode the generated...

  • RE: Can this be done without temporary tables

    Or this...

    SELECT DISTINCT S.Computername

    FROM dbo.tblSoftware S

    LEFT JOIN dbo.tblRegistry R

    ON (S.Computername = R.Computername AND R.RegKey IN ('Regkey1', 'Regkey2'))

    WHERE (S.SoftwareName = 'App1')

    AND (R.Computername IS NULL)

  • RE: 1-M join, concat all records

    I think these are the XML characters that will most likely give you trouble (and it is hard work to get the entity encodings to display properly in this forum...

  • RE: 1-M join, concat all records

    I adapted the following solution from this article by Jeff Moden:

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    INSERT #product (ProductId, Categories)

    SELECT

    C1.ProductID,

    Categories = (

    SELECT LTRIM(C.CategoryText + ' ')

    FROM #category C

    WHERE (C.ProductId = C1.ProductId)

    FOR XML PATH('')

    )

    FROM...

  • RE: Group by Query Error

    These error messages are pretty clear.

    Read the error messages then look at your query.

  • RE: Amalgamating Dates

    This should be doable by joining table dbo.tblSupplierSeasons with a Tally table or a Calendar table to produce a resultset where each row corresponds to a single day, then joining...

Viewing 15 posts - 286 through 300 (of 355 total)