Help in Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help in Query

Author

Topic

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 10:34:00

Hi All:

Being myself new in SQL Programming, i will ask many questions even if they are very simple or complex. Please bear with me:

I want to write a script that does following thing:
--If the date is 11/12/2009 or 11/12/2020 or greater than today's date than display 'D'
-- If the date is valid date other than 'D' than disply 'W',
-- If the date is blank display '' ( blank'.

Please help me in this query. Thanks all.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 11:34:49

Well let's get even simpler

What version of SQL Server are you using

What is the name of the table and column you are looking at?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 11:39:35

Brett:
SQL Server 2005 Standard Edition.
The table name is Customer_Details and the column name is datDetails. Hope this helps.

Thanks for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 11:41:45

datDetails I guess is varchar?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 11:42:40

Also what does this mean?

If the date is 11/12/2009 or 11/12/2020 or greater than today's date than display 'D'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 11:51:06

OK, this is a BIZARRE reqiuirement, but....


SELECT datDetails
, CASE  WHEN datDetails = '11/20/2009'	THEN 'W'
		WHEN DATEDIFF(dd, '11/20/2020',CONVERT(datetime,datDetails)) > 0 
										THEN 'W'
		WHEN datDetails = ''			THEN ''
										ELSE 'D'
   END AS Date_Ind
  FROM Customer_Details
WHERE ISDATE(datDetails) = 1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 11:52:27

datDetais is date time.
What we have to do is, in the column, if the datDetails is 11/12/2009 or 11/12/2020 or greater than today's date than it should display 'D' in the application.

Hope this clearifies you .

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 11:58:28

Well then. First, this is probably a presentation layer issue

Second, if the column is datetime, then it can't be blank AND must be a valid date

So that get's rid of most of your "Criteria"

So, then we have



SELECT datDetails
, CASE  WHEN datDetails IN ('11/20/2009','11/20/2020')
		  OR DATEDIFF(dd, GetDate(),datDetails) > 0 
										THEN 'W'
										ELSE 'D'
   END AS Date_Ind
  FROM Customer_Details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 12:12:07

Brett:
I am going to change datDetails column to varchar(25), if this is the case, how can i achieve this? The second criteria should tell that If the date is valid date other than 'D' than disply 'W'. Here other than D means other than the dates 11/20/2009, 11/20/2020 and the date greater than today's date. As i said am new in T-SQL so can you please elaborate a little bit for the how you did it.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 12:28:12

First, did you run the code and did it work as you expected?

Second, if you are considering making a valid datetime field into a wild west varchar, I might have to just stop "helping" now. That would be a very BAD idea.

Third, isn't the case statement kind of self-explanitory? It really can't get much simpler.

What, specifically, do I need to explain to you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 12:41:23

Brett:
I did run the code am not getting expected result set.

You said that there will not be blank in the date time field so am going to change to varchar, is this a bad practice ( or bad idea).

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 12:45:25

Brett:
It is not giving the blank fields.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 13:09:37

do you mean null?

if the column is defined as datetime, it can never be space

h

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 13:11:47

how about NOT this



SELECT datDetails
, CASE  WHEN datDetails IN ('11/20/2009','11/20/2020')
		  OR DATEDIFF(dd, GetDate(),datDetails) > 0 
										THEN 'W'
		WHEN datDetails IS NULL			THEN 'blank'
										ELSE 'D'
   END AS Date_Ind
  FROM Customer_Details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 14:34:05

No i got only D as an output.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 14:51:44

ahhhh...I forgot the time portion of datetime

Hold on

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 14:56:47

OK, How aout NOT THIS



SELECT datDetails
, CASE  WHEN CONVERT(varchar(10),datDetails,101) 
			 IN ('11/20/2009','11/20/2020')
		WHEN DATEDIFF(dd, GetDate(),datDetails) > 0 
										THEN 'D'
		WHEN datDetails IS NULL			THEN 'blank'
										ELSE 'W'
   END AS Date_Ind
  FROM Customer_Details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 15:01:09

I think this matches ALL of your criteria


CREATE TABLE #myCustomer_Details (datDetails datetime)
GO

INSERT INTO #myCustomer_Details (datDetails)
SELECT '11/20/2001 12:00:00' UNION ALL
SELECT '11/20/2009 13:00:00' UNION ALL
SELECT '11/20/2020 14:00:00' UNION ALL
SELECT '11/30/2009 15:00:00' UNION ALL
SELECT '11/30/5555 16:00:00' UNION ALL
SELECT null
GO


SELECT datDetails
, CASE  WHEN CONVERT(varchar(10),datDetails,101) 
			 IN ('11/20/2009','11/20/2020')
										THEN 'D'
		WHEN DATEDIFF(dd, GetDate(),datDetails) > 0 
										THEN 'D'
		WHEN datDetails IS NULL			THEN 'blank'
										ELSE 'W'
   END AS Date_Ind
  FROM #myCustomer_Details
GO

DROP TABLE #myCustomer_Details
GO

I'll have a Guiness...from Kingston

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 15:04:39

I get
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'WHEN'. Am not able to catch the problem here.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 15:05:45

yeah my bad...try the last one...with ALL of the code

Just cut and paste to a query window

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-12 : 15:34:48

Brett:
Perfect, you are the SCRIPT KING, teach me how to become a SQL Programmer. Thanks a lot and i really appreciate your help.
Where should i start for SQL Programming!!!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources