Database

Eddy Vluggen wrote:

Logically, each table would have it's own layout/schema - unless all tables look the same (which would be a code-smell)

Shameel wrote:

not all production databases are normalized completely.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

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'

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 

mrkeivan wrote:

How can I declare a Variable so I dont need to use it over and over again?

create procedure getpassword(@member varchar(20))
as
begin

select password from admin where member_id = @member

end

getpassword @member = 'fred smith'

getpassword 'fred smith'

CREATE PROCEDURE getpassword( IN member varchar(20))
BEGIN
    SELECT password FROM admin WHERE member_id = member;
END