need help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » need help

need help

Rate Topic

Display Mode

Topic Options

Author

Message

harri.reddy

Posted Tuesday, October 02, 2012 7:31 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346

CREATE TABLE product
(
ID INT IDENTITY(1,1)
,VAL VARCHAR(MAX) NOT NULL
);

INSERT product
(
VAL
)
VALUES
(
'10/11/2012'
);

INSERT product
(
VAL
)
VALUES
(
'cablegrams'
);

INSERT product
(
VAL
)
VALUES
(
'Set of data'
);

INSERT product
(
VAL
)
VALUES
(
'11/15/2012'
);

INSERT product
(
VAL
)
VALUES
(
'12/31/0212'
);

INSERT product
(
VAL
)
VALUES
(
'bar'
);

/*** PROBLEM #1 ***/
--Correct the following query:
SELECT *
FROM DATA
WHERE CAST(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

Post #1367371

robert.gerald.taylor

Posted Tuesday, October 02, 2012 10:50 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 733, Visits: 782

harri.reddy (10/2/2012)

/*** PROBLEM #1 ***/
--Correct the following query:
SELECT *
FROM DATA
WHERE CAST(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');

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

;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

Post #1367399

« Prev Topic | Next Topic »

Permissions