|
Have another upvote for the sqlfiddle link.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
The reason Richard had so much trouble with the query is that your data structure is atrocious, you need to fix that otherwise you are going to have continuing and compounding problems in future.
Your time recording table should have 1 record per person per date. The the query would be a very simple one instead of that horror Richard had to supply! He deserves the upvote just for dealing with your structure!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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)
|
|
|
|
|