SQL Nested Select

Question

Hi, 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.

Answer 1

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

Answer 2

With column names:

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

Answer 3

Do you perhaps want a CASE statement?

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

Answer 4

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?

Answer 5

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]