issue wih select 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

issue wih select query

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-03-16 : 10:48:44

CREATE TABLE #TEMP(DriverName varchar(50),
LicenseNo varchar(50),
PhoneNumber varchar(50),
DriverLicExpDate datetime,
MedicalCertExpDate datetime,
MVRCOVExpDate datetime)
INSERT INTO #TEMP VALUES('LUIS','LC1','9632587112',getdate()+30,getdate()-3,getdate()-4)
INSERT INTO #TEMP VALUES('JAMES','LC2','9632667112',getdate()-30,getdate()-3,getdate()-4)
INSERT INTO #TEMP VALUES('SMITH','LC3','9637787112',getdate()-22,getdate()+2,getdate()-4)
INSERT INTO #TEMP VALUES('JAME','LC4','9634487112',getdate()-12,getdate()-13,getdate()+4)
INSERT INTO #TEMP VALUES('GINS','LC5','9633587112',getdate()+3,getdate()-3,getdate()-4)

SELECT * FROM #TEMP

I want the result like the following
dispaly if the date is less than Getdate()
DNAME --LNO -- PNO ----- ColumnName ------- ExpDate
------------------------------------------------------------
LUIS ---LC1-- 9632587112-- MedicalCertExpDate ----- getdate()-3
LUIS ---LC1-- 9632587112-- MVRCOVExpDate ----- getdate()-4
JAMES --LC2-- 9632667112-- DriverLicExpDate ----- getdate()-30
JAMES --LC2-- 9632667112-- MedicalCertExpDate ----- getdate()-3
JAMES --LC2-- 9632667112-- MVRCOVExpDate ----- getdate()-4
.
.
.
.
We don't want to display LUIS DriverLicExpDate because it's greater than getdate().
Expdate is what we insert.
Please help me...........

Thanks & Regards
Binto Thomas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-16 : 11:13:47

where DriverLicExpDate < getdate()

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:16:01

where DriverLicExpDate <dateadd(dd,datediff(dd,0,getdate()),0)

if you've consider only till start of current day

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ehan
Starting Member

19 Posts

Posted - 2010-03-16 : 11:18:34

SELECT ut.DriverName,ut.LicenseNo,ut.PhoneNumber,
ut.ColumnName,
ut.ExpDate
FROM (
SELECT DriverName,LicenseNo,PhoneNumber,DriverLicExpDate,MedicalCertExpDate,MVRCOVExpDate
FROM #TEMP) t
UNPIVOT
(ExpDate FOR ColumnName IN (DriverLicExpDate,MedicalCertExpDate,MVRCOVExpDate)
) ut
WHERE ut.ExpDate < GETDATE()

binto
Yak Posting Veteran

59 Posts

Posted - 2010-03-16 : 11:41:48

Ehan......Excellent.............
Thank you very much.

Thanks & Regards
Binto Thomas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources