Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

need help Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 7:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, October 2, 2012 10:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 1,234, Visits: 1,271
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');

#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
Post #1367399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse