|
Hi guys I have a table with list of records or cards passing a place
ID: int
CarPlate: int
PassDate: date
PassType: byte > 1 enter, 2 exit
I want to select the total amount of time each car has been in that place by date
now this is my strategy but I cant implement it 
Imaging in a particular date a cards enters and exits are as followed:
1- enter >
2- enter >
3- < exit
4- enter >
5- < exit
This particular car has entered 3 times and exited two times, I only want the enter and exit that are back to back so I need to select all the enters for all card in a particular date and then for every enter check whether there is a exit before the next enter or not and continue this to the end for each plate and then the next plate.
I have problem implementing this process of going through plates
and at the end for my end result should be something like this
CarPlate | Date | EnterExit | Duration
This EnterExit is a string of collections of enters and exits
enter at ..... exit at .....
enter at ..... exit at .....
enter at ..... exit at .....
enter at ..... exit at .....
.
.
.
Please help me out 
modified 15-Jan-14 1:45am.
|
|
|
|
|
Have a look at this query:
WITH passes AS (
SELECT platenumber
,DATE
,InOut
,RoadName
,LEAD(InOut, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_InOut
,LEAD(RoadName, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_RoadName
,LEAD(Date, 1, null) OVER (PARTITION BY platenumber ORDER BY date NULLS LAST) NEXT_Date
FROM EMSINFO e
,Devices d
,Roads r
WHERE e.deviceID = d.DeviceID
AND d.RoadID = r.RoadID
)
SELECT platenumber
,DATE entrytime
,RoadName entryroad
,NEXT_RoadName exitroad
,NEXT_Date exittime
FROM passes
WHERE InOut = 'IN'
AND NEXT_InOut = 'OUT'Here I'm assuming that you have the same table structure as in the last question.
Using the analytic function LEAD you can work on the next row, so in this query I'm making sure that the row after the inwards passage is an outwards passage.
Adjust to your needs.
Remember that analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. So if you need to do more work on the data you have to use a CTE. (Like I did)
|
|
|
|
|
Thanx, your comments are really helpful, I have another question
I have to use to functions, both return either zero or ID
function1- Finds the Next enter after a particular enter
function2- Checks if there is an exit between these two
in my query multiple times and I want to see how I can declare a variable to hold them
I wanna say
SELECT platenumber
,EnterDATE
,EnterRoad entryroad
,ExitDate exitroad: // select date from table where id = return value of function2
,ExitRoad exittime // select road from table join ... where id = return value of function2
and also in 2, 3 other places, How can I declare a Variable so I dont need to use it over and over again?
Regards,
K1
|
|
|
|
|
mrkeivan wrote: How can I declare a Variable so I dont need to use it over and over again?
You use a stored procedure.
This is the direct answer to your question.
But I don't really get what you're after, can you elaborate?
|
|
|
|
|
I have an tables as below
Team Country Gender
A US Male
B UK Female
A France Male
B Canada Female
A US Male
My Requirement: Based on the values in the table above I need a NEW Column named Selection with below criteria
If Team A + US + Male then 'SELECTED',
If Team A + France + Male then 'Re-Apply'
If Team A + France + Male then 'Re-Apply'
If Team B + UK + Female then 'Rejected'
If Team B + Canada + Female then 'SELECTED'
So I want my output to look like this.
Team Country Gender Selection
A US Male SELECTED
B UK Female Rejected
A France Male Re-Apply
B Canada Female SELECTED
A Germany Male Re-Apply
How Do I do that in MS SQL ? I tried using a function but for some reason it doesn't seems to work. Can you please help ?
Thanks in Advance.
|
|
|
|
|
Create a view and use a case statement to determine the value of Selected. However what value are you going to put when the country = Canada and the Gender is male?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi there, I was able to sort this out using an function. Funtion takes the values from each row and calculates my requirement and populated as separate column.
|
|
|
|
|
hi,
am not getting what's wrong in this procedure:
create procedure getpassword( in member varchar(20))
begin
select password from admin where member_id = member
end
am getting error at "end"
syntax error unexpected end,expecting ;
|
|
|
|
|
Try this:
create procedure getpassword(@member varchar(20))
as
begin
select password from admin where member_id = @member
end
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
am executing it in workbench, am i right.
still am getting error at @
|
|
|
|
|
How are you calling the procedure?
It should work if you call the stored procedure in one of the two following ways:
getpassword @member = 'fred smith'
getpassword 'fred smith'
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 10-Jan-14 8:21am.
|
|
|
|
|
You need to put a semi-colon at the end of your statement before the END keyword :
CREATE PROCEDURE getpassword( IN member varchar(20))
BEGIN
SELECT password FROM admin WHERE member_id = member;
END
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
How to test it in workbench, whether it's working or not.
|
|
|
|
|
thanq , how can i test in workbench, whether in query, or at stored procedures
|
|
|
|
|
how can i test whether it's working or not in workbench.
am testing in query as:
execute getpassword 'stl123';
but it's giving errors.
and in my c# code is:
MySqlCommand command = new MySqlCommand("getpassword", con);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@member", txtuser.Text);
con.Open();
using (MySqlDataReader rdr = command.ExecuteReader())
{
if (rdr.Read())
{
reader object rdr is showing no rows.
|
|
|
|
|
Change this:
Member 10263519 wrote: command.Parameters.AddWithValue("@member", txtuser.Text);
To this(remove the @ as it is not required from .net):
Member 10263519 wrote: command.Parameters.AddWithValue("member", txtuser.Text);
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
In my environment I have two servers that host several production databases each - for this example I shall call them PROD1 and PROD2
I also have two servers that host logshipped copies of the production databases - one for reporting by users, the second for report development by the IT team. These I shall call REP1 and REP2.
The setup is as follows:
PROD1 - Logships 4 databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby
PROD2 - Logships 4 other databases to REP1, logships same 4 databases to REP2 - databases on REP1 and REP2 set to Restore with Standby
Logshipping copy jobs are set to copy the transaction logs to a NAS device, to different folders for each of the reporting servers.
REP1 and REP2 therefore each host 8 logshipped copies of the production databases, set to Standby/Readonly
Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis, and the equivalent two jobs, plus two of the jobs from PROD2 to REP2 have started failing on REP2.
The job histories for the failing jobs don't show aas failed jobs, as the database connection of the jobs seems to be lost, meaning that logging of the rrors fails.
The error in the history are as follows:
Date 09/01/2014 07:30:00
Log Job History (LSRestore_PROD1_dBFirst)
Step ID 1
Server REP1
Job Name LSRestore_PROD1_dBFirst
Step Name Log shipping restore log job step.
Duration 00:01:56
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2014-01-09 07:31:55.93 *** Error: Could not apply log backup file '\\nas2\dbbackups\TransactionLog\DBFirst_20140108210000.trn' to secondary database 'ReportdbFirst'.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: An error occurred while processing the log for database 'ReportdbFirst'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
An error occurred during recovery, preventing the database 'ReportdbFirst' (13:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'ReportdbFirst', file 'livesystem_Data' on file 1.
Processed 1 pages for database 'ReportdbFirst', file 'livesystem_Log' on file 1.(.Net SqlClient Data Provider) ***
2014-01-09 07:31:55.93 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
2014-01-09 07:31:55.93 Skipping log backup file '\\nas2\dbbackups\TransactionLog\dbFirst_20140108210000.trn' for secondary database 'ReportdbFirst' because the file could not be verified.
2014-01-09 07:31:55.93 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2014-01-09 07:31:55.93 *** Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) ***
The database is left in "Restoring" mode and cannot then be used for reporting.
I have tried all sorts of things - introducing delays between the copy and restore jobs, moving the copy locatios to physical drives on the servers etc, but it still happens at least once per day, meaning I have to backup and restore the production databases manually to allow users/developers to work with the data.
Anyone any ideas? I have googled extensively and found several people reporting the same problems, but no working solutions.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: Recently, 2 of the logshipping restore jobs from PROD1 to REP1 have started to fail on a regular basis
Usually when I see this sort of thing when it appears that connections are failing in odd ways I immediately presume that it is a firewall problem. Of course unless you are firewall administrator getting someone to admit that they did in fact change something in the firewall can be problematic.
|
|
|
|
|
The servers are all behind the same firewall, so I think this is unlikely
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: The servers are all behind the same firewall
And you are positive that that statement means that the traffic in fact doesn't go through the firewall? (Versus firewall rules that 'should' allow it to behave as though the firewall wasn't impacting it.)
|
|
|
|
|
Hi Guys,
I want to write a query in Oracle/SQL Server (doesn’t make a lot of difference)
I have 3 tables:
EMSINFO (EMSINFOID, DEVICEID, DATE, PlateNumber)
Devices (DEVICEID, INOUT)
Roads (ROADID, ROADNAME)
EMSINFO Contains the enters and exits of vehicles and it’s captured by a device
We have the device id,
And roads has the lists of all the roads in the zone
based on the INOUTwe know whether its enter or exit
Now I want to select PlateNumber, total time in the zone (enter time – exit time) and
Details of when entered and when exited in a text format in one record, because each vehicle can enter and exit multiple times in a particular day
Return Fields of store procedure:
PlateNo: Plate number
text: Enter from (road name) at (time)
Exited from (road name) at ( time)
Duration: Total Duration of being in the zone
My question is generating that text or enters and exits
I appreciate the help
Mr.K
|
|
|
|
|
There is no info on which road the device controls.
Should possibly be:
Devices (DEVICEID, INOUT, ROADID)
|
|
|
|
|
Yes, that's right, neglected to mention it, there is a ROADIDin Device table, now do you have any solution for this problem?
|
|
|
|
|
There are a number of solutions to this, you can use a CTE or 2 queries.
Select all the records using an inner join on the foreign keys and order by platenumber road and date.This should give you the reads for each plate so you can get the max/min for each plate/road and do a simple timespan function on the dates.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try something like this:
WITH Entries AS (
SELECT PlateNumber
,DATE
,RoadName
FROM EMSINFO e
,Devices d
,Roads r
WHERE e.deviceID = d.DeviceID
AND d.RoadID = r.RoadID
AND d.InOut = 'IN'
)
,Exits AS (
SELECT e.PlateNumber
,e2.DATE EntryTime
,e2.RoadName EntryRoad
,Min(DATE) ExitTime
FROM Entries e2
,EMSINFO e
,Devices d
WHERE e2.platenumber = e.platenumber
AND e.deviceID = d.DeviceID
AND d.InOut = 'OUT'
AND e.DATE > e2.DATE
GROUP BY e.PlateNumber
,e2.DATE
,e2.RoadName
)
SELECT e.PlateNumber
,EntryTime
,EntryRoad
,ExitTime
,r.RoadName ExitRoad
FROM Exits e
,EMSINFO e2
,devices d
,Roads r
WHERE e.platenumber = e2.platenumber
AND e.ExitTime = e2.DATE
AND e2.DeviceID = d.DeviceID
AND d.RoadID = r.RoadID
There are probably better implementations to your problem, Search for sql+islands+and+gaps[^].
"Islands and Gaps" is the name of this classic problem.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
