SQL Nested Select

Question

I am trying to construct a sql select statement that does the following:

SELECT GetDate() Mon, GetDate() Tues

But I also want to be able to put WHERE restrictions on each column (Mon, Tues). So I want something like this, but it is not correct:

SELECT (GetDate() Mon WHERE (1=1)), (GetDate() TuesWHERE (1=1))

Please point me in the right direction, thanks.

Oliver S: Your question makes no sense, because the example query doesn't return anything useful. Describe what you are really trying to do and folks may give you more relevant answers. — Bill Karwin
– Bill Karwin, Commented Jan 29, 2009 at 20:23
I'm gonna guess that the GETDATE() function and "WHERE 1=1" are just examples, and that in reality his query is much more complex. In which case, Terrapin's answer is probably what he's looking for, to maintain the column names. Just substitute the real functions and where clauses. — BradC
– BradC, Commented Jan 30, 2009 at 14:37

Seibar · Accepted Answer · 2009-01-29 19:53:11Z

2

With column names:

select
    (select getDate() where (1=1)) Mon,
    (select getDate() where (2=2)) Tues

answered Jan 29, 2009 at 19:53

Seibar

70.6k38 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BradC Over a year ago

I'm gonna go out on a limb and guess that the GETDATE function and WHERE 1=1 are just examples, and that in reality his query is much more complex. In which case, Terrapin's answer is probably what he's looking for, to maintain the column names. Just substitute the real functions and where clauses.

BradC · Accepted Answer · 2009-01-29 20:01:06Z

2

GetDate() just retrieves the current date/time.

So all your original query (and most of the "answers" so far) does is retrieving the current date and time twice, then putting a "Mon" and "Tue" column label on it.

Is this really what you want?

answered Jan 29, 2009 at 20:01

BradC

40.3k14 gold badges77 silver badges91 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2009-01-29 19:54:03Z

1

Do you perhaps want a CASE statement?

Or are you trying to actually get the most recent monday/tuesday/wednesday/etc?

answered Jan 29, 2009 at 19:54

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Comments

cjk · Accepted Answer · 2009-01-29 19:48:24Z

0

SELECT (select GetDate() Mon WHERE (1=1)), (select GetDate() Tues WHERE (1=1))

answered Jan 29, 2009 at 19:48

cjk

46.6k9 gold badges83 silver badges113 bronze badges

1 Comment

Spencer Ruport Over a year ago

SELECT (select GetDate() WHERE (1=1)) Mon, (select GetDate() WHERE (1=1)) Tues

John · Accepted Answer · 2009-01-30 10:20:20Z

Here is a function that takes a datetime and a day of the week as an input. It returns the most recent date with that day of the week.

It assumes a DATEFIRST of 7 (Making sunday day 1).

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_getDateFromDayName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_getDateFromDayName]
GO

--Set Sunday to be day one
SET DATEFIRST 7
GO

CREATE FUNCTION [dbo].[udf_getDateFromDayName] 
(
    @dtsMaxDate DATETIME,
    @sDayName VARCHAR(8)
)
RETURNS DATETIME
AS
BEGIN

    DECLARE @iDayNumber INT, @dtsTemp DATETIME

    SET @sDayName = LEFT(UPPER(@sDayName),3)

    SET @iDayNumber =
        ( CASE
             WHEN @sDayName = 'SUN' THEN 1
             WHEN @sDayName = 'MON' THEN 2
             WHEN @sDayName = 'TUE' THEN 3
             WHEN @sDayName = 'WED' THEN 4
             WHEN @sDayName = 'THU' THEN 5
             WHEN @sDayName = 'FRI' THEN 6
             WHEN @sDayName = 'SAT' THEN 7
             ELSE 0 --Invalid day
           END
        )

    IF @iDayNumber = 0 RETURN NULL

    SET @dtsTemp = CAST(FLOOR(CAST(@dtsMaxDate AS FLOAT)) AS DATETIME)

    WHILE DATEPART(DW,@dtsTemp) <> @iDayNumber
        SET @dtsTemp = DATEADD (dd, -1, @dtsTemp)

        RETURN @dtsTemp
END

GO

Once you have the function, your query is simple.

You can do something like this:

SELECT  dbo.udf_getDateFromDayName(GETDATE(),'mon') [Mon],
        dbo.udf_getDateFromDayName(GETDATE(),'tue') [Tue],
        dbo.udf_getDateFromDayName(GETDATE(),'wed') [Wed],
        dbo.udf_getDateFromDayName(GETDATE(),'thu') [Thu],
        dbo.udf_getDateFromDayName(GETDATE(),'fri') [Fri],
        dbo.udf_getDateFromDayName(GETDATE(),'sat') [Sat],
        dbo.udf_getDateFromDayName(GETDATE(),'sun') [Sun]

Or ideally, put the max date in a variable first:

DECLARE @dtsNow DATETIME
SET @dtsNow = GETDATE()
SELECT  dbo.udf_getDateFromDayName(@dtsNow,'mon') [Mon],
        dbo.udf_getDateFromDayName(@dtsNow,'tue') [Tue],
        dbo.udf_getDateFromDayName(@dtsNow,'wed') [Wed],
        dbo.udf_getDateFromDayName(@dtsNow,'thu') [Thu],
        dbo.udf_getDateFromDayName(@dtsNow,'fri') [Fri],
        dbo.udf_getDateFromDayName(@dtsNow,'sat') [Sat],
        dbo.udf_getDateFromDayName(@dtsNow,'sun') [Sun]

Collectives™ on Stack Overflow

SQL Nested Select

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related