SQL View - Pass over variables

yes it can be done
just create extra text boxes on that form
and tie those variables into your View as the dates

OR
create a button to click
when that button is clicked
have a new popup form appear to exnter the dates in textboxes

Consider your view as a table where you can SELECT FROM and have a WHERE clause.

So you could have:

SELECT * FROM YourView WHERE start >= '2013-01-01' AND start <=  '2013-20-04' AND stop >= '2013-01-01' AND stop <='2013-20-04'

Select allOpen in new window

Giannis

you cannot pass parameters to a view, you would have to change it to either a stored procedure or a function, which should be relatively easy to you, despite any complexities of your view. you could do something like the following:

create procedure nameOfProc
     @startDate datetime,
     @endDate datetime
as
begin
       -- your view code
       AND (start BETWEEN @startDate AND @endDate) AND (stop BETWEEN @startDate AND @endDate)

end

a view is a stored query, these do not accept parameters

you are free to simply add a where condition e.g.

select
*
from that_view
where (start BETWEEN '2013-01-01' AND '2013-20-04') AND (stop BETWEEN '2013-01-01' AND '2013-20-04')

You could use a "table function" instead, these DO accept parameters

before I leave with that thought however please "Beware of Between"

your selection criteria probably should look more like this:

select
*
from that_view
where (start >= '2013-01-01' AND start < '2013-20-05')  -- nb < the next day
AND (stop >= '2013-01-01' AND stop < '2013-20-05') -- nb < the next day

Use the query you created in sql manager studio and place it in your asp page.

startDate=request.form("StartDate")
endDate=request.form("EndDate")
' only good dates
if not isdate(startDate) then
   startDate=dateadd("m",-1,date)
end if
if not isdate(endDate) then
   endDAte=date
end if

sql="Select * from MyTable WHERE  (start BETWEEN '"&startDate&"' AND '"&endDate&"' ) AND (stop BETWEEN '"&startDate&"'  AND '"&endDate&"' )"

' add sql to your recordset call

Select allOpen in new window

I concur with @PortletPaul to not use between and use greater then/less then instead.  Every time I run into that in ready, the warning is not using between.

seems I'm much to slow - sorry for the repetition, but please do be careful with using "between ... and" with datetime information. It can have unexpected problems.

= '2013-20-04' means equal to '2013-20-04 00:00:00:0000'

(so anything during the 24 hours up to 2013-20-05 are potentially "missed")

LOL it is why i didn't just copied and paste the between statements and changed them to >= and <=.

He is right though...

Giannis

Hi Folks.

Yes I have read there may be issues with between but didn't think it to be 100%.

However I want to try and get away from using the SQP with in the ASP page a the data is not filtered before it gets to the page and this can slow things down, where as the view has already filtered the SQL data on the SQL server and saved bandwidth....

I thought I'd Past my code here so you all get a true image of the issue:

SELECT     ID, title, firstname, surname, Department, Position, suspend
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (suspend = 0) AND (NOT EXISTS
                          (SELECT     Eng_ID, start, stop
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start BETWEEN '2013-01-01' AND '2013-20-04') AND (stop BETWEEN '2013-01-01' AND '2013-20-04')))

Select allOpen in new window

Sorry I was going to do this earlier but didn't expect such a big response.

Whats your thoughts now seeing the code...

turn it into a stored procedure

What do you mean?

see my original post on turning the view into a stored procedure

>"However I want to try and get away from using the SQP with in the ASP page a the data is not filtered before it gets to the page"

I would want to accept data to the page, then filter using vb code (not with js) before submitting.  As far as performance, I don't know you will see much difference running the sql on your asp page vs on the server.  Maybe if you have hundreds of users per minute?  

For performance, you will get much more out of using getrows() then looping through the recordset.  

For stored procedures, I will create code to access the SP as an include file and turn it into a function.  Then call the function on the page as needed.

>>.read there may be issues with between but didn't think it to be 100%.
there are issues with between and datetime ranges, and it is 100% verifiable...

the safest way to ensure you get all you want - but only what you want is to:

where ( [datetime] >= [dt-variable-low] and  [datetime] < [dt-variable-high+1] )

sorry, I'll convert you yet :)

just one observation, that NOT EXISTS subquery only needs one field, not 3 lines 14/15 below are redundant

SELECT ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (
					start BETWEEN '2013-01-01'
						AND '2013-20-04'
					)
				AND (
					stop BETWEEN '2013-01-01'
						AND '2013-20-04'
					)
			)
		)

Select allOpen in new window

So what your saying is the date filter needs to be like this:

SELECT ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (
					start >= '2013-01-01'
						AND <= '2013-20-04'
					)
				AND (
					stop >= '2013-01-01'
						AND <= '2013-20-04'
					)
			)
		)

Select allOpen in new window

   
And that the variables are:
     @startDate datetime,
     @endDate datetime

Is fo how do you pass this over from the SQL page. Is it like calling a new RS or totally different.

Sorry this is a new way forme and once I got it its in there :-)

no, you have the equivalent of between, look carefully I have removed 2 by = and moved 04 to 05

					start >= '2013-01-01'
						AND < '2013-20-05'
					)
				AND (
					stop >= '2013-01-01'
						AND < '2013-20-05'

Select allOpen in new window

Someone else may address the question of how to pass the parameters: you as using .asp ?  correct?

MMM I'm certainly not getting this as I have pasted the code in to SQL Manager and got the error on the screenshot.....
Screen-Shot-2013-04-03-at-09.34..png

my bad: my very bad, apologies

SELECT ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (
					start >= '2013-01-01'
						AND start < '2013-20-05'
					) 
				AND (
					stop >= '2013-01-01'
						AND stop < '2013-20-05'
					)
			)
		)

Select allOpen in new window

How do I pass the Variables over to the SQP Procedure from the ASP page ?

OK I now have it working to a degree but I need it to include the Stop date and not be before ...

SELECT     ID, username, password, title, firstname, surname, Department, Position, email, admin, suspend, Timestamp, last_login, confirmcode, Preptime
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (NOT EXISTS
                          (SELECT     Eng_ID
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start >= '2012-19-06') AND (start < '2013-22-06') AND (stop >= '2012-19-06') AND (stop < '2013-22-06')))

Select allOpen in new window

>>to include the Stop date and not be before

not be before.... this? (the "stop date")

language is a peculiar thing, but I'm not entirely sure what you mean - sorry.


How does this query (just above) NOT meet your expectations?
There is a filter on a field called 'stop' - is this what you mean? lines 20+21

SELECT    
  ID
, username
, password, title
, firstname
, surname
, Department
, Position
, email
, admin, suspend, Timestamp, last_login, confirmcode, Preptime
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') 
AND (NOT EXISTS
                  (
                    SELECT     Eng_ID
                    FROM          dbo.qryEng_Booked_Check
                    WHERE      (Eng_ID = dbo.tblUsers.ID) 
                    AND (start >= '2012-19-06') 
                    AND (start < '2013-22-06') 
                    AND (stop >= '2012-19-06') 
                    AND (stop  < '2013-22-06')
                  )
     )

Select allOpen in new window

Hi Sorry.

I have Start and Stop fields.

The above will work if I do < Stop but not on <= Stop.

This means it is not including the actual end data I'm passing over. Its doing before...
I'm tring to get this to work in the statement:

NOT (start >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND start <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)) AND 
                      (NOT (stop >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND stop <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)

Select allOpen in new window

>>The above will work if I do < Stop but not on <= Stop.

include the = symbol

                    AND (start >= '2012-19-06')
                    AND (start <= '2013-22-06')  -- now include 2013-22-06 00:00:00
                    AND (stop >= '2012-19-06')
                    AND (stop <= '2013-22-06') -- now include 2013-22-06 00:00:00

are you saying this does not alter the result?
I can see none of your data - so I'm flying blind

if your data contains both date and time,
then finding data that actually equals 2013-22-06 00:00:00 could be rare

Table qryEng_Booked_Check
ENG_ID    Start                                         Stop                                         Surname
31              2013-06-19 00:00:00.000      2013-06-21 23:59:59.000      Boon
25              2013-06-19 00:00:00.000      2013-06-21 23:59:59.000      Clowes
20            2013-05-21 00:00:00.000      2013-05-23 23:59:59.000      Everill

If I use this code it will remove the Engineers from the USER Table that appear in the qryRng_Booked_Check View:

SELECT     ID, username, password, title, firstname, surname, Department, Position, email, admin, suspend, Timestamp, last_login, confirmcode, Preptime
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (NOT EXISTS
                          (SELECT     Eng_ID
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID)))

Select allOpen in new window

What I would like to Add is the Data range so we can select the START and STOP Dates and add them to the filter so if engineers are booked for a job it will not show them in a drop down box. This will then only show the available engineers.

I hope this makes more sense.

Sorry for the confusion.

and stop > getdate()

i.e. as at June 20, only  the following is 'available'
20            2013-05-21 00:00:00.000      2013-05-23 23:59:59.000      Everill

SELECT ID
	, username
	, password
	, title
	, firstname
	, surname
	, Department
	, Position
	, email
	, admin
	, suspend
	, TIMESTAMP
	, last_login
	, confirmcode
	, Preptime
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer') AND (
		NOT EXISTS (
			SELECT Eng_ID
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
and stop > getdate()
			)
		)

Select allOpen in new window

note there is a NOT exists, so we need to find things we do NOT want (stop > getdate() is NOT wanted)

Sorry what do you mean not wanted ....

I need this code somehow incorporating in to the code as its a data range.

NOT (start >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND start <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)) AND 
                      (NOT (stop >= CONVERT(DATETIME, '2013-06-19 00:00:00', 102) AND stop <= CONVERT(DATETIME, '2013-06-20 00:00:00', 102)

Select allOpen in new window

So if I pass two date (PassStart and PassStop) I need to interrigate the Database's START and STOP to remove and entries that fall with in these dates as the engineer will not be available to do the job.

Thanks

WHERE (Department = N'Site Engineer') AND (
            NOT EXISTS (

the subquery that follows the above is contained within a NOT EXISTS

so IF the subquery located available people, then NOT EXISTS would reverse that, so you would end-up with UNavailable people (in the final result)

the innermost subquery needs to locate things you do NOT want (in the final result)
is that clearer now?

It's entirely up to you, you could flip the whole thing around - but I'm way too tired right now. (off to bed now)

Sorry I'm just lost on this ...

I don't not understand

that's a double negative (i.e. I do understand)

you want available people (I think/hope), so

where NOT EXISTS ( available people ) -- would be incorrect

where NOT EXISTS( unavailable people ) -- would be correct

so the logic inside the ( ... ) needs to find unavailable people

I cannot think of any way to explain this better - sorry.

OK .. I just want the available people to be shown with in the search. I know if I do the NOT() command against the table with the booked dates in it then filteres the users who are not in the booked table.

I then need to add the data search to the booked dates table so I can just search that part of the date range.

This is the SQL Code I have last.

Are you saying that the Date filter has to be before the NOT EXISTS?

FROM:

SELECT     ID, title, firstname, surname, Department, Position, suspend
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (suspend = 0) AND (NOT EXISTS
                          (SELECT     Eng_ID, start, stop
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start >= '2013-19-06') AND (start < '2013-21-06') AND (stop >= '2013-19-06') AND (stop < '2013-21-06')))

TO:

SELECT     ID, title, firstname, surname, Department, Position, suspend
FROM         dbo.tblUsers
WHERE     (Department = N'Site Engineer') AND (suspend = 0) AND (AND (start >= '2013-19-06') AND (start < '2013-21-06') AND (stop >= '2013-19-06') AND (stop < '2013-21-06')) AND (NOT EXISTS
                          (SELECT     Eng_ID, start, stop
                            FROM          dbo.qryEng_Booked_Check
                            WHERE      (Eng_ID = dbo.tblUsers.ID) ))

It is not necessary to place the "not exists subquery" in a specific location of the where clause.

Looks to me like the first of the 2 listed is more likely to be correct as the fields [start] and [stop] belong to  dbo.qryEng_Booked_Check I  expect. This looks the most correct.

the second query does not look as if the date fields in the right place

Well the first or second do not work so I'm stuck ....

can you provide some data from the tables involved? (i does not need to be a big sample)

table one just has dates in with engineer ID and active or not and the other has engineers ID and engineers names. nothing overly complicated.

It is really such a same you haven't provided data as I could test, now (again) I am left to guess :(
It truly makes a difference if data is provided.

>>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.:
                        AND (start >= '2013-19-06')
                        AND (start < '2013-21-06')
                        AND (stop >= '2013-19-06')
                        AND (stop < '2013-21-06')

You have used a YYYY-DD-MM sequence which isn't correct

the best sequence is always YYYY-MM-DD, and in fact the very best format is without delimiters YYYYMMDD

this worked for me on some dummy data (here: http://sqlfiddle.com/#!3/1195d/1 )

SELECT
       ID
     , title
     , firstname
     , surname
     , Department
     , Position
     , suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
     AND (suspend = 0)
     AND (
          NOT EXISTS (
               SELECT
                      Eng_ID
                    , start
                    , stop
               FROM dbo.qryEng_Booked_Check
               WHERE (Eng_ID = dbo.tblUsers.ID)
                    AND (start >= '2013-07-01')
                    AND (start  < '2013-07-04')
                    AND (stop  >= '2013-07-01')
                    AND (stop   < '2013-07-04')
               )
          )
;

Select allOpen in new window

by the way its not necessary for the exists subquery to return fields, this works too:

SELECT
       ID
     , title
     , firstname
     , surname
     , Department
     , Position
     , suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
     AND (suspend = 0)
     AND (
          NOT EXISTS (
               SELECT 1
               FROM dbo.qryEng_Booked_Check
               WHERE (Eng_ID = dbo.tblUsers.ID)
                    AND (start >= '2013-07-01')
                    AND (start  < '2013-07-04')
                    AND (stop  >= '2013-07-01')
                    AND (stop   < '2013-07-04')
               )
          )
;

Select allOpen in new window

Ok I will try and get data off the server.

Sorry again but is there a simple SQL command that will take the table structure and data. That was you can see the data type also.

did you try this url?
http://sqlfiddle.com/#!3/1195d/1 

all I wanted was a few records from each table, as csv or Excel
really it's because it shows how your data is stored
- without it we have to guess.

Have you tried either of the last 2 queries above?
if these are not producing any results it may be the nature of the multiple AND conditions you are using for the dates

try without conditions on the [stop] field. Does that produce results?
                    AND (start >= '2013-07-01')
                    AND (start  < '2013-07-04')
--                    AND (stop  >= '2013-07-01')
--                    AND (stop   < '2013-07-04')

still no results? try with no date conditions
--                    AND (start >= '2013-07-01')
--                    AND (start  < '2013-07-04')
--                    AND (stop  >= '2013-07-01')
--                    AND (stop   < '2013-07-04')

if there are still no results, then it probably IS a "data issue" and I really will need to see some valid data from you.

alternatively, go to that sqlfiddle url, and try to replicate a sample of your data there and let me know the url.

btw: not interested in getting private data (e.g. people's names etc.)  - they can be altered, but would like to see data that really represents what you have to deal with.

dbo.qryEng_Booked_Check

ENG_ID      Start            Stop            Username      Department            Firstname            Surname
20      2013-07-03 00:00:00.000      2013-07-05 23:00:00.000      User1      Site Engineer      User      one
31      2013-07-23 00:00:00.000      2013-07-25 23:00:00.000      User2      Site Engineer      User      Two
22      2013-08-12 00:00:00.000      2013-08-14 23:00:00.000      User3      Site Engineer      User      Three
31      2013-07-02 00:00:00.000      2013-07-04 23:00:00.000      User2      Site Engineer      User      Two
20      2013-07-02 00:00:00.000      2013-07-04 23:00:00.000      User1      Site Engineer      User      One
NULL      NULL      NULL      NULL      NULL      NULL      NULL

dbo.tblUsers

ID      Username      Password      title      firstname      surname      department      position      email      admin      suspend
20      User1      password      Mr      user      1      Site Engineer      Gas Quality Engineer      email      NULL      0
22      User2      password      Mr      user      2      Site Engineer      Gas Quality Engineer      email      NULL      0
31      User3      password      Mr      user      3      Site Engineer      Gas Quality Engineer      email      NULL      0

in addition that URL does not load for me ..

Hope the data help. I appreciate your help.

Thanks

excellent, just excellent - thank you! results from that sample data:

ID	TITLE	FIRSTNAME	SURNAME	DEPARTMENT	POSITION		SUSPEND
20	Mr	user		1	Site Engineer	Gas Quality Engineer	0
22	Mr	user		2	Site Engineer	Gas Quality Engineer	0
31	Mr	user		3	Site Engineer	Gas Quality Engineer	0

Select allOpen in new window

from this query:

SELECT
      ID
	, title
	, firstname
	, surname
	, Department
	, Position
	, suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
	AND (suspend = 0)
	AND (
		NOT EXISTS (
			SELECT Eng_ID
				, start
				, stop
			FROM dbo.qryEng_Booked_Check
			WHERE (Eng_ID = dbo.tblUsers.ID)
				AND (start >= '2013-07-01')
				AND (start < '2013-07-04')
				AND (stop >= '2013-07-01')
				AND (stop < '2013-07-04')
			)
		)
;

Select allOpen in new window

see: http://sqlfiddle.com/#!3/7c1f6/1
sqlfiddle works fro me and others, maybe it's your browser type/version? (I use Chrome / Firefox mostly), anyway by recording those URL's I can get back to latest if I need to - but do try again - its can be very helpful.

Hi,

This is great .. Fantastic...

I can see the code working on the site OK also.

However I have now found that the server is using YYYY-DD-MM formatting. This is why I was not fully seeing the right data. I changed the year on the above code to 2012 and 2014 and this then worked.

My last question to resolve this fully will be how to I change the date formatting on the server to YYYY-MM-DD.

I thought I was setup for British in both IIS and SQL.

Thanks again for this help. Learned a lot on this already.

OK I have fixed the formatting issue now.. bloody UK as YYYY-DD-MM ..

However on the code if a start date is 2013-07-02 and end sate is 2013-07-05 and I search for :

 WHERE      (Eng_ID = dbo.tblUsers.ID) AND (start >= '2013-07-02') AND (start <= '2013-07-02') AND (stop >= '2013-07-02') AND (stop <= '2013-07-02')))

Select allOpen in new window

Then this should not show the users with this date. However it is. I was told not to use between as there are issue with that way. As the above dates fill with in this the user shold not be shown.

How to I fix this.

Thanks

to change the database date format see:
http://msdn.microsoft.com/en-us/library/ms189491(v=sql.105).aspx

However the database default date format does not matter, please don't think that date information is stored in a format - they aren't (in fact date/time information is saved as integers).

YYY-DD-MM isn't a British format by the way - it is a very unusual French format I believe.
We use dd/mm/yyyy as a basic standard in 'British' areas of the world.

:) & mm/dd/yyyy is a bloody nuisance too if you ask me (Aussie's tend to place bloody in front of everything).

In SQL Server the MOST safe date format to use in queries is with NO separator, so if you wanted 2nd of July 2013 to 5th July 2031 then the safest option would be to use these in the query:

'20130702'
'20130705'

Afraid I didn't understand this:
"Then this should not show the users with this date. However it is. I was told not to use between as there are issue with that way. As the above dates fill with in this the user should not be shown."

OK.

What I'm trying to say is if an engineer is booked from 2013-01-01 to 2013-01-05 and I search to filter with 2113-01-01 to 2013-01-01 then that engineer should not be shown as it falls with in the data range.

please try this: http://sqlfiddle.com/#!3/3c749/3
 

DECLARE @from AS date, @until AS date
SET @from  = '20130701'
SET @until = '20130701'

SELECT
       ID
     , title
     , firstname
     , surname
     , Department
     , POSITION
     , suspend
FROM dbo.tblUsers
WHERE (Department = N'Site Engineer')
     AND (suspend = 0)
     AND (
          NOT EXISTS (
               SELECT Eng_ID
                    , START
                    , stop
               FROM dbo.qryEng_Booked_Check
               WHERE (Eng_ID = dbo.tblUsers.ID)
                    AND (
                          (START >= @from AND stop < @until)
                        OR
                          (@from >= START AND @until < stop )
                        )
               )
          )
;

Select allOpen in new window

note: the bracket arrangement is important here

Hi,

Thanks for this. I can see that it works in SQL view but not in ASP.

I have to change the code a little for ASP but its much the same:

strSQL = "SELECT ID, title, firstname, surname, Department, POSITION, suspend FROM dbo.tblUsers WHERE (Department = N'Site Engineer') AND (suspend = 0)AND ( NOT EXISTS ( SELECT Eng_ID, START, stop FROM dbo.qryEng_Booked_Check WHERE (Eng_ID = dbo.tblUsers.ID) AND ( ([START] >= CONVERT(DATETIME,'"&STARTa&"',103) AND [stop] < CONVERT(DATETIME,'"&FINISHEDa&"',103)) OR (CONVERT(DATETIME,'"&STARTa&"',103) >= [START] AND CONVERT(DATETIME,'"&FINISHEDa&"',103) < [stop] ))))"

Select allOpen in new window

Its not filtering the users as it is in SQL view.

Is it due to the START and FINISH SQL fields been DATETIME and not DATE ?

Thanks

Found it.
The table value was DATETIME. Then I added another two columns that were just DATES it then worked.

Thanks for the information and help.