|
hello, thanks a lot. anyway, so it is much better to make new tables per stopovers right? for example buses have stopovers i.e PointA - Point D, which means it has stopover of Stopover B and Stopover C. In my mind is that a table for the origin and destination, reservation (id,busNum,reserveStatus,seatId,seatColuumn,fullName,date)
and for the stopovers(id,busNum,date,stopoverName,seatId,seatColumn,reserveStatus,fullName). Could you please guide me for a better way or if this would do? Thanks a lot
|
|
|
|
|
To be able to design a database correctly, one has to understand the users' requirements and their business rules. Many considerations arise, such as:
1. Can a passenger start and end his journey at any stopovers?
2. How to ensure a seat in a bus is not reserved by more than one passengers for a journey on the same date and time.
Based on the limited info that you have given and my own assumption, I have drafted a preliminary database schema for your reference and adaptation, you will have to work out your final solution yourself:
Table: reservation
reserveID (PRIMARY KEY),
busID,
reserveStatus,
custID (Foreign Key to custID in customer table),
start_stopoverID,
end_stopoverID,
[other fields]
Table: customer
custID (PRIMARY KEY),
custName,
[other fields]
Table: bus
busID (PRIMARY KEY),
busNum,
[other fields]
Table: stopovers
stopoverID (PRIMARY KEY),
stopoverName,
[other fields]
Table: bus_stopovers
stopoverID (PRIMARY KEY),
busID (PRIMARY KEY),
[other fields]
|
|
|
|
|
Thank you so much for the draft. I will work for it. i hope i could do these multiple seatmap. i need it do be done soon. Thanks a bunch.
|
|
|
|
|
Have a look at this[^] schema. It's for an Airline, but I believe you can reuse most of the info.
|
|
|
|
|
what is criteria for a SCN number change ? Means when it changes and when it not changes ?
|
|
|
|
|
|
hi...Im newly member..please help me settle this
server: Ubuntu 12.04 LTS
database use postgresql 9.1 with ebikko engine
for edms system
after transfering from the actual server with is vm worstation
to another vm worstation and change the ip..the system cannot been log in..assume some sort of hard code that need to change but does not know where.
How to solve this?
Guide me somebody
|
|
|
|
|
aswadmanap wrote: the system cannot been log in..assume some sort
It's hard to pinpoint where something goes wrong if you're using assumptions. I could assume that it's already solved.
aswadmanap wrote: some sort of hard code that need to change but does not know where.
Use the "search" option in your code-editor to locate the IP; we can't locate it for you.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
i don't know how to find the file..its is in Ubuntu 12.04 LTS server but using database postgresql version 9.1
|
|
|
|
|
Who set up the system? These things can become quite difficult without documentation.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
check that the database engine is running. If not, scrutinzie the system logs for corresponding error messages.
As already pointed out, an IP address might be hard-coded in some configuration file.
|
|
|
|
|
actually i was just transfering the system to another server by imaging procedure without making any change..but still yet cannot make authorization access..because the system is not develop by me..the vendor itself have made it.But the maintainance period is over..so i want to know exactly the problem by myself. 
|
|
|
|
|
Is there any way of creating say 100 different tables using a single SQL query/statement..Or is there another way around?![Java | [Coffee]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/coffee.gif)
|
|
|
|
|
You could use a cursor or loop, generating inline create table statements which you then execute
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
No.
A single create-statement creates a single table. You can build your own loop, but then it wouldn't be a single statement anymore. If you want it to be a single query, simply concatenate the statements.
Logically, each table would have it's own layout/schema - unless all tables look the same (which would be a code-smell), different column-specifications need to be used per table.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
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)
It's common to see more than one table with the same schema in horizontal partitioning. Eg., CustomerEast and CustomerWest. I know this violate normalization rules, but again, not all production databases are normalized completely.
|
|
|
|
|
Shameel wrote: not all production databases are normalized completely.
That's rather an understatement.
You'll also often have multiple small tables consisting of an BIGINT|UUID and a [N]VARCHAR, but even though they share their structure - you don't have a single statement.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
From your question, I'm assuming you're trying to partition a table. Creating 100 physical tables is probably not the best approach. For MS SQL, have a look at Partitioned Tables and Indexes[^] instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
you can do by using loop statement
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
