SQL Query not returning records for today

Question

Post reply

SQL Query not returning records for today

rhubbard-958538

SSC Enthusiast

Points: 187
More actions
December 4, 2008 at 11:33 am

#203783

I’m Stuck on this select statement and was wondering if you knew right off hand the correct function to get todays data only. I need a case statement allowing us to get this data for TODAY ONLY and not all the records in the database.
NVARCHAR1(Topic), NVARCHAR6 (Meeting Room Name), datetime1 (Meeting Start Time), datetime2 (Meeting End Time)
Select nvarchar1,nvarchar6,datetime1,datetime2 from PROWSS_Content.dbo.alluserdata Where nvarchar6 = 'Board Room'
20/20 Support Group (Bill Cooper),Board Room,2008-05-23 00:00:00.000,2008-09-05 00:55:00.000
Tina Dunn MS Offsite (Tenative),Board Room,2008-03-07 19:00:00.000,2008-03-08 01:00:00.000
Show Linda Board Room,Board Room,2008-02-22 20:30:00.000,2008-02-22 20:45:00.000
Show Linda Board Room,Board Room,2008-02-22 20:45:00.000,2008-02-22 21:00:00.000
Liz Thomfohrde MS Offsite (TENTATIVE),Board Room,2008-02-18 17:00:00.000,2008-02-19 01:00:00.000
Andrea Pyle MS Offsite (TENTATIVE),Board Room,2008-03-21 15:30:00.000,2008-03-21 19:00:00.000
Sr. Director Meeting,Board Room,2008-03-20 22:00:00.000,2008-03-21 00:00:00.000
Sr. Director Meeting,Board Room,2008-03-20 22:30:00.000,2008-03-21 00:30:00.000
Leslie Nichols,Board Room,2008-03-07 17:00:00.000,2008-03-08 01:00:00.000
Data Managment Tool: End to End Review,Board Room,2008-02-22 21:00:00.000,2008-02-22 23:00:00.000

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Meeting start date today, or meeting end date today?

Would this one fall into 18th feb or 19th feb?

Liz Thomfohrde MS Offsite (TENTATIVE),Board Room,2008-02-18 17:00:00.000,2008-02-19 01:00:00.000

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi rhubbard

No problem, should be pretty straightforward. But first, can you explain what this chunk of code does? I'm lost...

NVARCHAR1(Topic), NVARCHAR6 (Meeting Room Name), datetime1 (Meeting Start Time), datetime2 (Meeting End Time)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 3

Hi Chris,

This is a calendar out of SharePoint for several conference rooms, I will be publishing the Conference Rooms Meeting Agenda(NVARCHAR1) on a LCD Video Display board outside the selected room. I will need only today’s meetings to be displayed per room!

NVARCHAR1(Meeting Topic/Aeneda), NVARCHAR6(Meeting Room Name), Datetime1(Meeting Reservations Start), Datetime2(Meeting Reservations End)

Thanks in advance!

Roosevelt

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 4

Hello,

This Meeting reservation would start on the 18th and end the 19th. The user reserved the room from 2008-02-18 17:00:00.000 until 2008-02-19 01:00:00.000.

Thanks in advance!

Roosevelt

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 5

Chris,

I have only four rooms to display this information for, I will have one LCD outside each conference room with Its Agenda/Topic(NVARCHAR1) and time of the meeting Start (DateTime1) and end (DateTime2) to be displayed.

Cheers!

Roosevelt

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Refer to Books On Line (BOL)ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f1a5bc-4c0c-4c48-848d-8ec03473e6c1.htm

and

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7a60ba81-b2cb-4798-9de4-767247c4c39d.htm

Hopefully this should give you sufficient guidance to do what you want to do

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

rhubbard (12/4/2008)
Hello,
This Meeting reservation would start on the 18th and end the 19th. The user reserved the room from 2008-02-18 17:00:00.000 until 2008-02-19 01:00:00.000.

So you want it to appear on both the 18th and the 19th's meeting days.

SELECT NVARCHAR1, NVARCHAR6, Datetime1, Datetime2

FROM PROWSS_Content.dbo.alluserdata

WHERE (DateTime1 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime1 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0))

OR (DateTime2 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime2 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0))

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 8

Hello Chris,

Yes, schedule should resemble the outlook calendar. If a meeting wraps across 2 days then block out both days. Also I have provided the output of your provided statement bellow, Multiple Rooms (NVARCHAR6) are displaying instead of one room particular.

SELECT NVARCHAR1, NVARCHAR6, Datetime1, Datetime2

FROM PROWSS_Content.dbo.alluserdata

WHERE (DateTime1 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime1 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0))

OR (DateTime2 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime2 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0))

Rachelle Bilbruck MS Offsite (TENTATIVE),Board Room,2008-12-04 16:00:00.000,2008-12-04 18:00:00.000

Rachelle Bilbruck MS Offsite (CONFIRMED),Board Room,2008-12-04 16:00:00.000,2008-12-04 18:00:00.000

20/20 Mentorship Training,Olympic Room,2008-12-04 02:00:00.000,2008-12-04 04:00:00.000

Per Farny MS Offsite (TENTATIVE),All Star Room,2008-12-04 18:00:00.000,2008-12-05 01:00:00.000

Per Farny MS Offsite (TENTATIVE),All Star Room,2008-12-04 16:00:00.000,2008-12-05 01:00:00.000

CSCS Study Group,Executive 2,2008-12-04 22:00:00.000,2008-12-04 23:30:00.000

Please approve Auto Salon Admiration,Auto Salon Admiration,2008-12-04 19:42:51.000,2008-12-05 19:42:51.000

Please approve Auto Salon Admiration,Auto Salon Admiration,2008-12-04 19:42:52.000,2008-12-05 19:42:52.000

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

You can add a filter on the nvarchar6 for the room you want to display.

SELECT NVARCHAR1, NVARCHAR6, Datetime1, Datetime2

FROM PROWSS_Content.dbo.alluserdata

WHERE (DateTime1 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime1 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0))

OR (DateTime2 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime2 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0))

AND nvarchar6 = 'All Star Room'

If you have rows repeating, then they are repeating in the source table. That query can't duplicate rows as it's written.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 10

Hi Chris,

The Query you provided is now working and I thank you so much. My issue now is that the records dispaled for DATTIEM1 and DATETIME2 are 8 hours ahead of the actural time slotted in SharePoint can 8 hours be subtracted.

SELECT NVARCHAR1, NVARCHAR6, Datetime1, Datetime2

FROM PROWSS_Content.dbo.alluserdata

WHERE (DateTime1 >= DATEADD(dd, DATEDIFF(dd,0,getdate()),0) AND DateTime1 < DATEADD(dd, DATEDIFF(dd,0,getdate())+1,0)) and nvarchar6 = 'Board room'

RETURNED

Rachelle Bilbruck MS Offsite (TENTATIVE)2008-12-04 16:00:00.0002008-12-04 18:00:00.000

Rachelle Bilbruck MS Offsite (CONFIRMED)2008-12-04 16:00:00.0002008-12-04 18:00:00.000

Clark Nuber Lunch Meeting - DO NOT MOVE2008-12-04 20:00:00.0002008-12-04 21:00:00.000

Rachelle Bilbruck MS Offsite (CANCELLED)2008-12-04 16:00:00.0002008-12-04 18:00:00.000

Hold for Debbie Becker2008-12-04 17:00:00.0002008-12-04 17:00:00.000

Hold for Debbie Becker2008-12-04 20:00:00.0002008-12-04 21:00:00.000

CSCS Study Group2008-12-04 22:00:00.0002008-12-04 23:30:00.000

Sr. Trainers (Ame Powell)2008-12-04 18:30:00.0002008-12-04 19:30:00.000

Roosevelt

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

Use dateadd to remove 8 hours from the two datetimes. Is it because sharepoint stored times in GMT?

SELECT NVARCHAR1, NVARCHAR6, DATEADD(hh,-8,Datetime1) as StartTime, DATEADD(hh,-8,Datetime2) AS EndTime

FROM PROWSS_Content.dbo.alluserdata

WHERE DATEADD(dd, DATEDIFF(dd,0,getdate()),0) BETWEEN DATEADD(dd, DATEDIFF(dd,0,DATEADD(hh,-8,Datetime1)),0) AND DATEADD(dd, DATEDIFF(dd,0,DATEADD(hh,-8,Datetime1))+1,0)

and nvarchar6 = 'Board room'

I realised the previous where clause wouldn't have worked for a meeting over more than 2 days. This one should.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 12

Hello Gail,

First Thanks So So Much! The statement works fine but just had one last request from my BOSS.

I now need to truncate the time form 24 hour Military to regular time, also I need to remove the date displayed and only display the hour and minutes not the seconds.

Present:

2008-12-04 12:00:00.0002008-12-04 13:00:00.000

Need to be:

12:00 PM 01:00 PM

Thanks again!

Roosevelt

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 13

SELECT RIGHT(GETDATE(),7)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rhubbard-958538 SSC Enthusiast Points: 187 More actions · Answer 14

Hello Jeff,

Where would I place RIGHT(GETDATE(),7) in the statement. When I placed it at the begging it returned the present time of day. Any help would be great!

Select nvarchar1,

DATEADD(hh,-8,Datetime1)

as StartTime,

DATEADD(hh,-8,Datetime2)

AS EndTime

from PROWSS_Content.dbo.alluserdata

Where nvarchar6 = 'Board Room'

AND

CONVERT(nvarchar(8),datetime1,112) =

CONVERT(nvarchar(8),

GETDATE(),112)

and

tp_iscurrent = '1'

and

nvarchar7 = 'confirmed'

Returns:

PRO Central Development2008-12-05 10:00:00.0002047-03-08 11:00:00.000

I need the date to only show:

10:00AM

Cheers!

Roosevelt