October 12, 2015 at 3:57 am
hello all.
I have 3 tables :
Vacation:DailyStartDate smalldatetime,DailyEndDate smalldatetime,
HourlyDate smalldatetime,HourlyStartTime time,HourlyEndTime time,ApplicantId int(fk)
Mission:StartDate smalldatetime,EndDate smalldatetime,StartTime time,EndTime time,ApplicantId int(fk)
Applicant:firstname nvarchar(500),lastname nvarchar(500)
I need to write a query for this result: from specified date to specified date and for one person:
date HourlyVacationStartTime HourlyVacationEndTime DailyVacation MissionStartTime MissionEndTime
please guide me how can I Write this query?
thanks
October 12, 2015 at 4:12 am
for example:
from 2015-10-04 to 2015-10-11 for john I need to have this result:
date HourlyVacationStartTime HourlyVacationEndTime DailyVacation MissionStartTime MissionEndTime
2015-10-04,46800,50400,Null,Null,Null
2015-10-06,null,null,2015-10-06,null,null
2015-10-07,null,null,2015-10-07,null,null
2015-10-10,null,null,null,28800,79200
2015-10-11,null,null,null,46800,50400
thank you
October 12, 2015 at 4:16 am
Table definitions as CREATE TABLE statements please, and some sample data in the form of INSERT statements. Please ensure that the sample data is correct for the expected results you've posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2015 at 4:41 am
havent you asked this type of question before?
http://qa.sqlservercentral.com/Forums/FindPost1719078.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 12, 2015 at 6:05 am
hello dear GilaMonster.
CREATE TABLE [dbo].[Vacation](
[VacationID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_Id] [int] NULL,
[HourlyVacationStartTime] [Time](0) NULL,
[HourlyVacationEndTime] [Time](0) NULL,
[DailyStartDate] [smalldatetime] NULL,
[DailyEndDate] [smalldatetime] NULL,
[DailyVacationDate] [smalldatetime] NULL,
PRIMARY KEY CLUSTERED
(
[VacationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Mission](
[MissionID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_Id] [int] NULL,
[StartDate] [smalldatetime] NULL,
[EndDate] [smalldatetime] NULL,
[StartTime] [Time](0) NULL,
[EndTime] [Time](0) NULL,
PRIMARY KEY CLUSTERED
(
[MissionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Applicant](
[ApplicantID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](255) NOT NULL,
[LastName] [nvarchar](255) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ApplicantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[Applicant] VALUES('ali','ahmadi')
INSERT INTO [dbo].[Applicant] VALUES('amir','ebadi')
INSERT INTO [dbo].[Applicant] VALUES('bahare','rezaei')
INSERT INTO [dbo].[Applicant] VALUES('nooshin','sarami')
INSERT INTO [Mali].[dbo].[Vacation] VALUES (1,'2015-10-04 00:00:00','2015-10-04 00:00:00',46800,50400,null)
INSERT INTO [Mali].[dbo].[Vacation] VALUES (1,null,null,null,null,'2015-10-06')
INSERT INTO [Mali].[dbo].[Vacation] VALUES (1,null,null,null,null,'2015-10-07')
INSERT INTO [Mali].[dbo].[Vacation] VALUES (2,null,null,null,null,'2015-09-18')
INSERT INTO [Mali].[dbo].[Vacation] VALUES (2,null,null,null,null,'2015-09-19')
INSERT INTO [Mali].[dbo].[Vacation] VALUES (2,'2015-09-22 00:00:00','2015-09-22 00:00:00',46800,50400,null)
INSERT INTO [Mali].[dbo].[Mission] VALUES (1,'2015-10-10','2015-10-10',28800,79200)
INSERT INTO [Mali].[dbo].[Mission] VALUES (1,'2015-09-11','2015-09-11',46800,50400)
INSERT INTO [Mali].[dbo].[Mission] VALUES (2,'2015-09-10','2015-09-10',46800,50400)
INSERT INTO [Mali].[dbo].[Mission] VALUES (2,'2015-10-11','2015-10-11',46800,50400)
October 12, 2015 at 6:20 am
Ok, but your required results you say
from 2015-10-04 to 2015-10-11 for john
There's no John in your sample data.
Please ensure that the sample data is correct for the expected results you've posted.
And it's not. So, please either post sample data that can be used to produce the results you asked for earlier, or post the expected results from that set of sample data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2015 at 6:24 am
ok You please consider Ali Ahmadi for example.
October 12, 2015 at 6:34 am
Where does this row come from?
2015-10-11,null,null,null,46800,50400
The only mission which Ali has with those times has a date of '2015-09-11', so why does it end up in the expected results with a date of '2015-10-11'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 12, 2015 at 6:54 am
Dear GilaMonster I only take an example. please You Consider this data and guide me for my result not exactly my data.
thank you
October 12, 2015 at 7:03 am
How am I supposed to give you the results you want when the data you provide couldn't possibly generate those results? I can't read your mind, I can't see your screen. I don't know your requirements.
So, should that row be in the output, and if so how does a row with a date in September appear in a resultset filtered from 4 October to 11 October? Is there some date addition requirement that you haven't mentioned, or was that row in the results incorrectly?
Also, which of the dates in the Mission table are used to check the date range? If there's a mission with a start date of '2015-10-02' and an end date of '2015-10-15' and the filter is for the dates '2015-10-10' to '2015-10-20', should that mission show appear in the results or not? Should it show up for a filter of '2015-10-01' to '2015-10-05'
What about when the filter range is between the mission's start and end dates? eg a mission with a start date of '2015-10-02' and an end date of '2015-10-15' and the filter is for the dates '2015-10-05' to '2015-10-10', should that mission be returned?
Same questions for vacations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2015 at 12:37 am
Also, which of the dates in the Mission table are used to check the date range? If there's a mission with a start date of '2015-10-02' and an end date of '2015-10-15' and the filter is for the dates '2015-10-10' to '2015-10-20', should that mission show appear in the results or not? Should it show up for a filter of '2015-10-01' to '2015-10-05'
What about when the filter range is between the mission's start and end dates? eg a mission with a start date of '2015-10-02' and an end date of '2015-10-15' and the filter is for the dates '2015-10-05' to '2015-10-10', should that mission be returned?
Same questions for vacations.
hello.
all of filter which you mentioned is required.also I have a RequestDate filed in Vacation And Mission Table that I want to use for filtering.
October 15, 2015 at 2:07 am
hello GilaMonster
I have a Filed in Mission and Vacation which is RequestDate with smalltime type.I want to filter on this field.
CREATE TABLE [dbo].[Mission](
[MissionID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_Id] [int] NULL,
[StartDate] [smalldatetime] NULL,
[EndDate] [smalldatetime] NULL,
[StartTime] [int] NULL,
[EndTime] [int] NULL,
[RequestDate] [smalldatetime] NULL,
CONSTRAINT [PK__Mission__66DFB8542D80AE19] PRIMARY KEY CLUSTERED
(
[MissionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Vacation](
[VacationID] [int] IDENTITY(1,1) NOT NULL,
[Applicant_Id] [int] NULL,
[DailyStartDate] [smalldatetime] NULL,
[DailyEndDate] [smalldatetime] NULL,
[HourlyVacationStartTime] [int] NULL,
[HourlyVacationEndTime] [int] NULL,
[HourlyVacationDate] [smalldatetime] NULL,
[RequestDate] [smalldatetime] NULL,
CONSTRAINT [PK__Vacation__E420DF8429B01D35] PRIMARY KEY CLUSTERED
(
[VacationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Mission] VALUES (1,'2015-10-10','2015-10-10',46800,50400,'2015-10-10')
INSERT INTO [dbo].[Mission] VALUES (2,'2015-10-13','2015-10-13',46800,50400,'2015-10-13')
INSERT INTO [dbo].[Mission] VALUES (1,'2015-10-15','2015-10-15',46800,50400,'2015-10-15')
GO
INSERT INTO [dbo].[Vacation] VALUES (1,'2015-10-04','2015-10-05',null,null,null,'2015-10-03')
INSERT INTO [dbo].[Vacation] VALUES (2,'2015-10-02','2015-10-03',null,null,null,'2015-10-01')
INSERT INTO [dbo].[Vacation] VALUES (2,null,null,28800,79200,'2015-10-10','2015-10-11')
INSERT INTO [dbo].[Vacation] VALUES (1,null,null,46800,50400,'2015-10-20','2015-10-21')
GO
I need to have all dailyvacation or Hourlyvacation or mission with their date and time for all person between two date for example '2015-10-01' and '2015-10-29'
I hope that I get what I mean.
thank you
October 16, 2015 at 11:41 pm
hello all.
please help me about my query.
October 17, 2015 at 4:13 am
elham_azizi_62 (10/16/2015)
hello all.please help me about my query.
based on the latest sample data....what results are you expecting?
please provide details
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 19, 2015 at 12:10 am
hello J Livingston SQL
thank you for your reply.
I need this output:
Name,RequestDate,HourlyVacationDate,HourlyVacationStartTime,HourlyVacationEndTime,DailyStartDate,DailyEndDate,StartDate,EndDate,StartTime,EndTime
AliAhmadi,'2015-10-03',null,null,null,'2015-10-04','2015-10-05',null,null,null,null
AliAhmadi,'2015-10-10',null,null,null,null,null,null,'2015-10-10','2015-10-10',46800,50400
AliAhmadi,'2015-10-15',null,null,null,null,null,null,'2015-10-15','2015-10-15',46800,50400
AliAhmadi,'2015-10-21','2015-10-20',46800,50400,null,null,null,null,null,null
AmirEbadi,'2015-10-13',null,null,null,null,null,'2015-10-13','2015-10-13',46800,50400
AmirEbadi,'2015-10-01',null,null,null,'2015-10-02','2015-10-03',null,null,null,null
AmirEbadi,'2015-10-11','2015-10-10',28800,79200,null,null,null,null,null,null
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply