need help

  • CREATE TABLE product

    (

    ID INT IDENTITY(1,1)

    ,VAL VARCHAR(MAX) NOT NULL

    );

    INSERTproduct

    (

    VAL

    )

    VALUES

    (

    '10/11/2012'

    );

    INSERTproduct

    (

    VAL

    )

    VALUES

    (

    'cablegrams'

    );

    INSERTproduct

    (

    VAL

    )

    VALUES

    (

    'Set of data'

    );

    INSERTproduct

    (

    VAL

    )

    VALUES

    (

    '11/15/2012'

    );

    INSERTproduct

    (

    VAL

    )

    VALUES

    (

    '12/31/0212'

    );

    INSERTproduct

    (

    VAL

    )

    VALUES

    (

    'bar'

    );

    /*** PROBLEM #1 ***/

    --Correct the following query:

    SELECT*

    FROMDATA

    WHERECAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND '12/31/2012'

    AND ISDATE(VAL) = 1

    /*** PROBLEM #2 ***/

    --Write a set of queries that outputs all the non-date values in DATA

    --as a single pipe-delimited string

    /*** PROBLEM #3 ***/

    --Write a single query that returns one row with two columns, the

    --value of the first column being the latest date and the value of the

    --second column being the shortest string over 3 characters but

    --not counting whitespace

    thanks

  • harri.reddy (10/2/2012)


    /*** PROBLEM #1 ***/

    --Correct the following query:

    SELECT*

    FROMDATA

    WHERECAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND '12/31/2012'

    AND ISDATE(VAL) = 1

    /*** PROBLEM #2 ***/

    --Write a set of queries that outputs all the non-date values in DATA

    --as a single pipe-delimited string

    /*** PROBLEM #3 ***/

    --Write a single query that returns one row with two columns, the

    --value of the first column being the latest date and the value of the

    --second column being the shortest string over 3 characters but

    --not counting whitespace

    If you want help with your homework, put your code inside IfCode shortcuts to format it properly.

    #1 assuming you want the end date to be inclusive.

    SELECT ID, VAL

    FROM dbo.product

    WHERE ISDATE(VAL) = 1

    AND CAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND DATEADD(dd, 1, '12/31/2012');

    #2

    DECLARE @concatenated VARCHAR(1000) = '';

    SELECT @concatenated = @concatenated + [VAL] + '|' FROM dbo.product WHERE ISDATE([VAL]) = 0;

    SELECT @concatenated;

    #3

    ;WITH MaxDate (MaxDt) AS

    (

    SELECT MAX([VAL]) AS MaxDt FROM dbo.product WHERE ISDATE([VAL]) = 1

    ),

    ShortLine (SLine) AS

    (

    SELECT TOP 1 [VAL] AS SLine

    FROM dbo.product

    WHERE LEN(REPLACE([VAL], ' ', '')) > 3

    ORDER BY LEN(REPLACE([VAL], ' ', '')) ASC

    )

    SELECT MaxDt, SLine

    FROM MaxDate

    CROSS APPLY ShortLine;

    See if that gets you what you want.

    Rob

Viewing 2 posts - 1 through 1 (of 1 total)

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