|
Hi,
I have a job_orders table in my database with cleaner_id, job_order_date, start_time and end_time.
I am inserting job orders in the database.
I would like to check before inserting for available staff
I tried the following:
SELECT cleaner_id, cleaner_name FROM cleaners WHERE cleaner_id NOT IN (SELECT cleaner_id FROM job_orders WHERE job_order_date = param_job_order_date AND start_time BETWEEN param_start_time AND param_end_time) AND is_active = TRUE ORDER BY cleaner_name;
then added a record with start_time = 10:00 and end_time = 13:00
when I tried to run the code again with start_time = 11:00 and end_time = 13:00 it works fine
but when I try start_time = 11:00 and end_time = 14:00 it will not determine that staff is already booked for 10:00 TO 13:00.
how can I fix this please?
http://www.JassimRahma.com[^]
http://www.rmc.bh[^]
http://www.ume.bh[^]
http://www.xoompage.com[^]
http://www.volow.com[^]
http://www.curesoftware.com[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Seems like you need to check the end time ...
AND end_time BETWEEN param_start_time AND param_end_time)
Maybe the check for the end time needs to be
OR end_time BETWEEN param_start_time AND param_end_time
modified 16-Sep-13 16:01pm.
|
|
|
|
|
BETWEEN always uses AND not OR
Technology News @ www.JassimRahma.com
|
|
|
|
|
What I meant to say is the following ...
OR end_time BETWEEN param_start_time AND param_end_time
Notice where I put the "OR"
|
|
|
|
|
I tried this but didn't work too!
SELECT cleaner_id, cleaner_name FROM cleaners WHERE cleaner_id NOT IN (SELECT cleaner_id FROM job_orders WHERE job_order_date = param_job_order_date AND start_time BETWEEN param_start_time AND param_end_time OR end_time BETWEEN param_start_time AND param_end_time) AND is_active = TRUE ORDER BY cleaner_name;
Please help...
Technology News @ www.JassimRahma.com
|
|
|
|
|
You've posted a list of six links in the bottom of your question, none of which relate to your question, and which don't appear to be part of your signature. I understand the desire to show off your work, but is this really the best way to do it?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi there,
I am fairly new to programming SP, I am trying to create a relationship between to tables in my database. The tables are aspnet_Membership and aspnet_Genealogy, I have setup a relation with the two tables using a PK in the table aspnet_Membership and a FK in the table aspnet_Genealogy as can be seen in the code below:
CREATE TABLE [dbo].[aspnet_Genealogy] (
[GenealogyId] INT IDENTITY (1, 1) NOT NULL,
[UserId] UNIQUEIDENTIFIER NULL,
[FamilyName] CHAR (200) NOT NULL,
[FirstName] CHAR (200) NULL,
[MiddleName1] CHAR (200) NULL,
[MiddleName2] CHAR (200) NULL,
[MiddleName3] CHAR (200) NULL,
[Gender] CHAR (10) NULL,
[DOB] VARCHAR (20) NOT NULL,
[COB] CHAR (200) NULL,
[SOB] CHAR (200) NULL,
[COOB] CHAR (200) NULL,
[Newsletter] CHAR (10) NULL,
[DateTimeGenealogy] DATETIME NOT NULL,
PRIMARY KEY CLUSTERED ([GenealogyId] ASC),
CONSTRAINT [FK_aspnet_Genealogy_aspnet_Membership] FOREIGN KEY ([UserId]) REFERENCES [dbo].[aspnet_Membership] ([UserId])
);
and
CREATE TABLE [dbo].[aspnet_Membership] (
[ApplicationId] UNIQUEIDENTIFIER NOT NULL,
[UserId] UNIQUEIDENTIFIER NOT NULL,
[Password] NVARCHAR (128) NOT NULL,
[PasswordFormat] INT DEFAULT ((0)) NOT NULL,
[PasswordSalt] NVARCHAR (128) NOT NULL,
[MobilePIN] NVARCHAR (16) NULL,
[Email] NVARCHAR (256) NULL,
[LoweredEmail] NVARCHAR (256) NULL,
[PasswordQuestion] NVARCHAR (256) NULL,
[PasswordAnswer] NVARCHAR (128) NULL,
[IsApproved] BIT NOT NULL,
[IsLockedOut] BIT NOT NULL,
[CreateDate] DATETIME NOT NULL,
[LastLoginDate] DATETIME NOT NULL,
[LastPasswordChangedDate] DATETIME NOT NULL,
[LastLockoutDate] DATETIME NOT NULL,
[FailedPasswordAttemptCount] INT NOT NULL,
[FailedPasswordAttemptWindowStart] DATETIME NOT NULL,
[FailedPasswordAnswerAttemptCount] INT NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] DATETIME NOT NULL,
[Comment] NTEXT NULL,
PRIMARY KEY NONCLUSTERED ([UserId] ASC),
FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]),
FOREIGN KEY ([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId])
);
GO
EXECUTE sp_tableoption @TableNamePattern = N'[dbo].[aspnet_Membership]', @OptionName = N'text in row', @OptionValue = N'3000';
GO
CREATE CLUSTERED INDEX [aspnet_Membership_index]
ON [dbo].[aspnet_Membership]([ApplicationId] ASC, [LoweredEmail] ASC);
The SP code is for aspnet_Add_Genealogy is as follows below:
CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
(
@FamilyName char(200),
@FirstName char(200),
@MiddleName1 char(200),
@MiddleName2 char(200),
@MiddleName3 char(200),
@Gender char(10),
@DOB varchar(20),
@COB char(200),
@SOB char(200),
@COOB char(200),
@Newsletter char(200),
@DateTimeGenealogy DateTime
)
AS
BEGIN
INSERT INTO aspnet_Genealogy (FamilyName, FirstName, MiddleName1, MiddleName2, MiddleName3, Gender, DOB, COB, SOB, COOB, Newsletter, DateTimeGenealogy)
VALUES (@FamilyName, @FirstName, @MiddleName1, @MiddleName2, @MiddleName3, @Gender, @DOB, @COB, @SOB, @COOB, @Newsletter, @DateTimeGenealogy)
DECLARE @UserId uniqueidentifier
DECLARE @GenealogyId int
IF ( @GenealogyId = +1 )
UPDATE dbo.aspnet_Genealogy
SET UserId = @UserId
FROM dbo.aspnet_Membership u, aspnet_Genealogy m
WHERE @UserId = u.UserId AND @UserId = m.UserId
IF ( @@ROWCOUNT = 0 )
RETURN -1
END
SELECT m.UserId, m.FamilyName, m.FirstName, m.MiddleName1, m.MiddleName2, m.MiddleName3, m.Gender, m.DOB, m.COB, m.SOB, m.COOB, m.Newsletter, m.DateTimeGenealogy, u.UserId
FROM dbo.aspnet_Membership u, dbo.aspnet_Genealogy m
WHERE @UserId = u.UserId AND u.UserId = m.UserId
IF ( @@ROWCOUNT = 0 )
RETURN -1
RETURN 0
I have setup a SP with INSERT and SETUP, SELECT instructions as can be seen above, I know it is probably something small I am missing. The UserId did not join with aspnet_genealogy table.
I did the inner join in a view, vw_aspnet_Genealogy, the code is below:
CREATE VIEW [dbo].[vw_aspnet_Genealogy]
AS SELECT [dbo].[aspnet_Genealogy].[UserId], [FamilyName], [dbo].[aspnet_Genealogy].[FirstName], [dbo].[aspnet_Genealogy].[MiddleName1], [dbo].[aspnet_Genealogy].[MiddleName2], [dbo].[aspnet_Genealogy].[MiddleName3], [dbo].[aspnet_Genealogy].[Gender], [dbo].[aspnet_Genealogy].[DOB], [dbo].[aspnet_Genealogy].[COB], [dbo].[aspnet_Genealogy].[SOB], [dbo].[aspnet_Genealogy].[COOB], [dbo].[aspnet_Genealogy].[Newsletter], [dbo].[aspnet_Genealogy].[DateTimeGenealogy] FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Genealogy]
ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Genealogy].[UserId]
I set a aspx webpage with a gridview and configure the gridview to use the view as its table source. I trying have the UserId data show up in the gridview from the aspnet_Member table into the aspnet_Genealogy table. Can anyone help?
Thanks
Anthony
|
|
|
|
|
Have you looked at the data (using SQL Server Management Studio) to find that the geneology table does not have the GUID in userid?
DECLARE @UserId uniqueidentifier
DECLARE @GenealogyId int
IF ( @GenealogyId = +1 )
@GenealogyId will always be 0 (or null), you have just created it, so your update statement is wrong before it starts.
I suggest you get the userid GUID before you insert the genealogy record.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi There,
Thank you for the quick response, I changed the GenealogyId to = NULL. It still doesn't work, I have never heard of guid. How does it work and how is the code implemented? Can you explain?
Look forward to hearing from you soon.
Thanks
|
|
|
|
|
edit: the EER model
link to the EER Model
Hi, good morning from Brazil. What's the right way to create the Visit table below?
CREATE TABLE IF NOT EXISTS FARM
(
FARM_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FARM_NAME VARCHAR(45) NOT NULL,
ADDRESS VARCHAR(45) NOT NULL,
OWNER VARCHAR(45) NOT NULL
)ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS PADDOCK
(
PADDOCK_NUM INT NOT NULL,
FARM_FARM_ID INT NOT NULL,
PRIMARY KEY(FARM_FARM_ID, PADDOCK_NUM),
AREA VARCHAR(45) NOT NULL,
CONSTRAINT FARM_FARM_ID_CON FOREIGN KEY(FARM_FARM_ID)
REFERENCES FARM(FARM_ID)
)ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS VISIT
(
VISIT_ID INT NOT NULL PRIMARY KEY,
GATHERING DATE,
PADDOCK_PADDOCK_NUM INT NOT NULL,
PADDOCK_FARM_ID INT NOT NULL,
CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
REFERENCES PADDOCK(PADDOCK_NUM),
CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
REFERENCES PADDOCK(FARM_FARM_ID)
)ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS VISIT
(
VISIT_ID INT NOT NULL PRIMARY KEY,
GATHERING DATE,
PADDOCK_PADDOCK_NUM INT NOT NULL,
PADDOCK_FARM_ID INT NOT NULL,
CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
REFERENCES PADDOCK(PADDOCK_NUM),
CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
REFERENCES PADDOCK(FARM_FARM_ID),
UNIQUE KEY(PADDOCK_PADDOCK_NUM, PADDOCK_FARM_ID, GATHERING)
)ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS SAMPLE
(
SAMPLE_NUM INT UNSIGNED NOT NULL AUTO_INCREMENT,
VISIT_VISIT_ID INT NOT NULL,
PRIMARY KEY(SAMPLE_NUM, VISIT_VISIT_ID),
COUNT INT,
CONSTRAINT VISIT_VISIT_ID_CON FOREIGN KEY(VISIT_VISIT_ID)
REFERENCES VISIT(VISIT_ID)
)ENGINE = INNODB;
modified 15-Sep-13 10:57am.
|
|
|
|
|
|
>I created an empty database for MySQL: paddock.
CREATE DATABASE PADDOCK;
USE PADDOCK;
|
|
|
|
|
Just a thought, I don't know MySQL, but is there a constraint where you cannot have an object the same name as the database. EF has such a constraint on field/table names.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
sorry. In fact, I created the database paddock in lowercase:
create database paddock;
use paddock;
(I think I was writing some SQL code at that moment with uppercase)
|
|
|
|
|
noislude wrote: I created the database paddock in lowercase: The server doesn't care about upper- or lowercase; the name is case-insensitive. Same goes for tables and column-names.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
The difference between your two statements is the additional key in the second statement. Keys are important for searching data/retrieving data. So it depends on how you would normally get the data from that table.
Also note that a References statement may not cause the creation of a key - I am not sure here with MySQL. So for the JOIN s with the other tables, additional Key s could be appropriate.
|
|
|
|
|
You haven't specified what error you're getting when you run the script.
For a start, you've got two CREATE TABLE IF NOT EXISTS VISIT blocks - only the first one will run.
I'm not overly familiar with MySQL, but in SQL you'd need to include both columns in a single foreign key:
CONSTRAINT PADDOCK_FARM_ID_PADDOCK_NUM_CON (PADDOCK_FARM_ID, PADDOCK_PADDOCK_NUM)
REFERENCES PADDOCK (FARM_FARM_ID, PADDOCK_NUM)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi All,
Im sure this is very simple but after googling for the past 4 hours i cannot seem to find the correct syntax.
I currenly have a SQL which amongst other tables has a DateTime Coloum in the folllowing Format:
2013-09-11 21:06:08:970
I am currently running the follow query from within my C# Application:
select * from table where errorcode = 448
What I would like to do is:
select * from table where errorcode = 448, where date = today
and also
select * from table where errorcode = 448, where date is between 2013-09-11 00:00:00:000 and 2013-09-11 23:59:59:999
I just cant figure out the correct syntax for the queries though.
Could anybody possible Help?
Many Thanks
SG
|
|
|
|
|
Ah, see this where to post it, not the C# forum, and now you've cross-posted. Very naughty.
|
|
|
|
|
Member 10266943 wrote: Im sure this is very simple but after googling for the past 4 hours i cannot seem to find the correct syntax. The syntax is defined in the manual. Google is a general search-engine, and whilst it's helpfull when researching a new topic, it's not the place to be when you simply need a manual.
Member 10266943 wrote: What I would like to do is: select * from table where errorcode = 448, where date = today You'd need to pick up a book on SQL. A statement has a single WHERE clause, and we add in more filters using the AND keyword. Something similar to below;
SELECT col1, col2
FROM sometable
WHERE errorcode = 448
AND somedate = GETDATE() There shouldn't be any columns in the table named "date" as it's a non-descriptive name. Also, "table" and "date" are reserved keywords.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm sure I don't understand the question because if you query using "WHERE [date] = '%2013-09-11%'" without any time appendix dangling from it the return will give all strings of the wildcarded nature.
Incidently, noone knows the storage datatype of [date] but the programmer who coded the procedure.
Member 10266943 wrote: the folllowing Format
Really?
Member 10266943 wrote: 2013-09-11 21:06:08:970
Check the format in Object Viewer ...
|
|
|
|
|
|
hello
Im looking for that information on the design and realization of BSC BTS site that can help me
(creates a sql server data or Oracl)
|
|
|
|
|
Member 10259811 wrote: Im looking for that information www.google.com[^].
Veni, vidi, abiit domum
|
|
|
|
|
|