September 1, 2009 at 11:26 pm
I have a table with many columns. 3 columns are to be used for determining what data can be viewed in my web app.
CREATE TABLE [dbo].[LINKS] (
[LINKS_ID] int IDENTITY(1,1) NOT NULL,
[DISPLAY_STATUS]varchar(8) NULL CONSTRAINT [DF_LINKS_display_status] DEFAULT ('ACTIVE'),
[LINK_BODY] nvarchar(3000) NULL,
[LINK_HEADER] varchar(100) NULL,
[START_DATE] datetime NULL,
[END_DATE] datetime NULL,
CONSTRAINT [PK_LINKS] PRIMARY KEY([LINKS_ID])
)
GO
INSERT INTO [dbo].[LINKS]([DISPLAY_STATUS], [URL_LINK], [LINK_BODY], [LINK_HEADER],[START_DATE], [END_DATE])
VALUES(N'ACTIVE', N'www.google.com',N'Best Search Engine Ever!', N'Google', '1/1/2009', '12/31/2009')
GO
INSERT INTO [dbo].[LINKS]([DISPLAY_STATUS], [URL_LINK], [LINK_BODY], [LINK_HEADER],[START_DATE], [END_DATE])
VALUES(N'ACTIVE', N'www.bing.com',N'Worst Search Engine Ever!', N'Bing', '6/1/2009', '8/31/2009')
GO
I need a query with the following logic.
IF BOTH a START_DATE AND END_DATE are present AND DISPLAY_STATUS = 'ACTIVE' then display those records which fall between these dates according to today's date GETDATE()
ELSE
IF START_DATE OR END_DATE ARE NULL AND DISPLAY_STATUS = 'ACTIVE' Then display all ACTIVE records
September 2, 2009 at 12:47 am
Hi,
1st of, all there is a field missing in ur table definition. I have corrected here with.
CREATE TABLE [dbo].[LINKS] (
[LINKS_ID] int IDENTITY(1,1) NOT NULL,
[DISPLAY_STATUS] varchar(8) NULL CONSTRAINT [DF_LINKS_display_status] DEFAULT ('ACTIVE'),
[URL_LINK] varchar(50) NULL,
[LINK_BODY] nvarchar(3000) NULL,
[LINK_HEADER] varchar(100) NULL,
[START_DATE] datetime NULL,
[END_DATE] datetime NULL,
CONSTRAINT [PK_LINKS] PRIMARY KEY([LINKS_ID])
)
GO
Also, you have to provide all the data in your example which can fulfill all your conditions.
INSERT INTO [dbo].[LINKS]([DISPLAY_STATUS], [URL_LINK], [LINK_BODY], [LINK_HEADER],[START_DATE], [END_DATE])
VALUES(N'ACTIVE', N'www.google.com',N'Best Search Engine Ever!', N'Google', '1/1/2009', '12/31/2009')
GO
INSERT INTO [dbo].[LINKS]([DISPLAY_STATUS], [URL_LINK], [LINK_BODY], [LINK_HEADER],[START_DATE], [END_DATE])
VALUES(N'ACTIVE', N'www.bing.com',N'Worst Search Engine Ever!', N'Bing', '6/1/2009', '8/31/2009')
GO
INSERT INTO [dbo].[LINKS]([DISPLAY_STATUS], [URL_LINK], [LINK_BODY], [LINK_HEADER])
VALUES(N'ACTIVE', N'www.yahoo.com',N'Best website Ever!', N'Yahoo!')
GO
INSERT INTO [dbo].[LINKS]([DISPLAY_STATUS], [URL_LINK], [LINK_BODY], [LINK_HEADER])
VALUES(N'ACTIVE', N'www.gmail.com',N'Best mail server Ever!', N'GMail')
GO
I have tried just for a while and here are two queries, just for having some idea.. hope u can hv any..
Using 'Where':
SELECT * FROM LINKS
WHERE (
((START_DATE IS NOT NULL) AND (END_DATE IS NOT NULL) AND (DISPLAY_STATUS = 'ACTIVE')) AND
(CONVERT(DATETIME,GETDATE(),101) BETWEEN CONVERT(DATETIME, START_DATE, 101) AND CONVERT(DATETIME, END_DATE, 101))
)
OR
(
((START_DATE IS NULL) AND (END_DATE IS NULL) AND (DISPLAY_STATUS = 'ACTIVE') AND (UPPER(DISPLAY_STATUS) = 'ACTIVE'))
)
Using 'CASE':
SELECT * FROM
(
SELECT *, 'STATUS'=
CASE
WHEN (
((START_DATE IS NOT NULL) AND (END_DATE IS NOT NULL) AND (DISPLAY_STATUS = 'ACTIVE')) AND
(CONVERT(DATETIME,GETDATE(),101) BETWEEN CONVERT(DATETIME, START_DATE, 101) AND CONVERT(DATETIME, END_DATE, 101))
) THEN 'Dates are not null'
WHEN
(
((START_DATE IS NULL) AND (END_DATE IS NULL) AND (DISPLAY_STATUS = 'ACTIVE') AND (UPPER(DISPLAY_STATUS) = 'ACTIVE'))
)THEN 'Dates are null'
END
FROM LINKS
)a
WHERE STATUS IS NOT NULL
"Don't limit your challenges, challenge your limits"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply