|
The purpose of the schema is security, how you use it is up to you.
It's not an overly big deal to give the user associated with one schema rights on another schema.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
After you CREATE SCHEMA you'll notice that the object, if I could call it that for a moment, is found in the Security folder of the Object Browser tree. But as far as using SCHEMA as one would an "idea" in the "scheme of things" ... it's nature is perfect for that.
I use it to isolate dev stages as I work on scripts. Testing procedures, isolating a vast array of data tables by filtering them via SCHEMA, in Object Viewer, is very easy. In fact one of the only downsides to using SQL Server Management Studio happens to be that only one SCHEMA at a time is filterable.
That and a very aggrevating lack of code editor collapsablilty (-/+) as in VS. But that's rant for another holiday. ARRRRGHHHH me buckoe.
|
|
|
|
|
Super Lloyd wrote: am about to create 18 brand new table ...I don't want to make thing more complicated
Then I suggest you might want to take another look at the first part.
|
|
|
|
|
I want to get data on the bases of selected dates i.e start date and endate it might be any dates between the week but the dates stored in the table are start date of the week and end date of the week and days of the week mon,tue,wed,thur,fri,sat,sun.
|
|
|
|
|
what database are you using? SQL Server, Oracle, MySQL or Access.
Also edit your question and expand on what you are after also include sample table stucture and/or even sample date so that people can give you a full answer.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
i'm using sql server 2008
i'm having table where timesheet is filled weekely i'e from monday to sunday and i'm saving start and end date of the week but when retrieving the data dates selected may be any day of the week.
example if selected date is 15/8/2013 and 30/8/2013
and dates saved in table is from
2013-08-12(mon) 2013-08-18(sun)
2013-08-19 2013-08-25
2013-08-26 2013-09-01
2013-09-02 2013-09-08
2013-07-08 2013-07-14
2013-06-03 2013-06-09
2013-07-22 2013-07-28
MON TUE WED THUR FRI SAT SUN
8 8 7 7 7 7 7
8 8 8 8 8 8 8
0 8 8 8 8 8 8
0 8 8 8 8 8 0
8 8 8 8 8 8 8
8 8 8 8 8 8 8
8 8 8 8 8 8 8
i need data between 15/8/2013 to 30/8/2013
|
|
|
|
|
You can get the data for any weeks which overlap the selected date range with:
WHERE
WeekStartDate <= @SelectedEndDate
And
WeekEndDate >= @SelectedStartDate
For your example, that would give you the data between 2013-08-12 and 2013-09-01.
Since the data for a week appears to be on a single row, it's not obvious how you intend to return only part of a week.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
from this query we are getting data for whole week but selected startdate may be thur or any day i need data from that particular day to the particular enddate selected
|
|
|
|
|
As I said, since the data for the entire week appears to be on a single row, it's not obvious how you intend to return only part of a week.
Once you explain that, then we might be able to help you. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
any alternate solution to go with suggest please.
|
|
|
|
|
Since you haven't explained how you want the data to be returned, how is anyone supposed to suggest a solution?!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
from selected date i need date,day of the week and no of hrs he worked on that day and same till the end date selected
|
|
|
|
|
So you essentially want one row per day?
DECLARE @SelectedStartDate date = '20130815';
DECLARE @SelectedEndDate date = '20130830';
WITH cteOverlappingWeeks As
(
SELECT
StartDate,
MON,
TUE,
WED,
THUR,
FRI,
SAT,
SUN
FROM
dbo.Timesheet
WHERE
StartDate <= @SelectedEndDate
And
EndDate >= @SelectedStartDate
),
ctePivotedWeeks (WorkDay, Hours) As
(
SELECT
StartDate,
MON
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 1, StartDate),
TUE
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 2, StartDate),
WED
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 3, StartDate),
THUR
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 4, StartDate),
FRI
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 5, StartDate),
SAT
FROM
cteOverlappingWeeks
UNION ALL
SELECT
DateAdd(day, 6, StartDate),
SUN
FROM
cteOverlappingWeeks
)
SELECT
WorkDay,
Hours
FROM
ctePivotedWeeks
WHERE
WorkDay Between @SelectedStartDate And @SelectedEndDate
ORDER BY
WorkDay
;
Example: http://www.sqlfiddle.com/#!3/b8a7a/1[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
 Nice tool never knew about SQLFiddle Thanks 
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
