October 19, 2015 at 9:01 am
to start with....does this work?
WITH CTE
AS (SELECT
*
FROM(
SELECT
A.ApplicantID
, A.FirstName
, M.RequestDate
FROM Applicant AS A INNER JOIN
Mission AS M ON A.ApplicantID = M.Applicant_Id
UNION ALL
SELECT
A.ApplicantID
, A.FirstName
, V.RequestDate
FROM Applicant AS A INNER JOIN
Vacation AS V ON A.ApplicantID = V.Applicant_Id) AS x)
SELECT
cte.ApplicantID
, cte.FirstName
, cte.RequestDate
, V.HourlyVacationDate
, V.HourlyVacationStartTime
, V.HourlyVacationEndTime
, V.DailyStartDate
, V.DailyEndDate
, M.StartDate
, M.EndDate
, M.StartTime
, M.EndTime
FROM cte LEFT OUTER JOIN
Vacation AS V ON cte.RequestDate = V.RequestDate
AND cte.ApplicantID = V.Applicant_Id
LEFT OUTER JOIN
Mission AS M ON cte.RequestDate = M.RequestDate
AND cte.ApplicantID = M.Applicant_Id
ORDER BY
cte.ApplicantID , cte.RequestDate;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 20, 2015 at 1:29 am
thank you so much Dear J Livingston SQL.This is my requiment.
October 26, 2015 at 5:38 am
hello J Livingston SQL
I need to get duration between starttime and endtime and then sum of this duration.
I mean I need to have sum of Vacation time and Mission time for each person.
how to improve your query for this result?
I improved your query:
WITH CTE
AS (SELECT
*
FROM(
SELECT
A.ApplicantID
, A.FirstName
, M.RequestDate
FROM Applicant AS A INNER JOIN
Mission AS M ON A.ApplicantID = M.Applicant_Id
UNION ALL
SELECT
A.ApplicantID
, A.FirstName
, V.RequestDate
FROM Applicant AS A INNER JOIN
Vacation AS V ON A.ApplicantID = V.Applicant_Id) AS x)
SELECT
cte.ApplicantID
, cte.FirstName
, cte.RequestDate
, V.HourlyVacationDate
, V.HourlyVacationStartTime
, V.HourlyVacationEndTime
, V.DailyStartDate
, V.DailyEndDate
, M.StartDate
, M.EndDate
, CONVERT(varchar, DATEADD(ms, M.StartTime * 1000, 0), 114)
, CONVERT(varchar, DATEADD(ms, M.EndTime * 1000, 0), 114)
,cast(STR(SUM(M.EndTime- M.StartTime)/3600) + RIGHT(CONVERT(char(8),DATEADD(s,SUM(M.EndTime- M.StartTime),0),108),6) as time) as HourlyDiffTime
FROM cte LEFT OUTER JOIN
Vacation AS V ON cte.RequestDate = V.RequestDate
AND cte.ApplicantID = V.Applicant_Id
LEFT OUTER JOIN
Mission AS M ON cte.RequestDate = M.RequestDate
AND cte.ApplicantID = M.Applicant_Id
group by ApplicantID,FirstName,cte.RequestDate , V.HourlyVacationDate
, V.HourlyVacationStartTime
, V.HourlyVacationEndTime
, V.DailyStartDate
, V.DailyEndDate
, M.StartDate
, M.EndDate
,M.StartTime
,M.EndTime
now I need to this result:
for ali ahmadi:sum of vacation time=4 dayes and 2 houre
I need this result to this format:HH:MM:SS(Houre:MInute:second)
and either for mission.sum of Mission time:02:30:20
October 26, 2015 at 8:05 am
elham_azizi_62 (10/26/2015)
now I need to this result:for ali ahmadi:sum of vacation time=4 dayes and 2 houre
I need this result to this format:HH:MM:SS(Houre:MInute:second)
and either for mission.sum of Mission time:02:30:20
sorry...but you are going to have to explain please how you derive these figures?
you say 4 days and 2 hours and then ask for HH:MM:SS......confused.
please use the original sample data you posted.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 27, 2015 at 12:07 am
sorry dear J Livingston SQL
in my previous post,ali ahmadi has 2 days and 1 hours vacation.now I want to have this result:17:00:00
also he has 02:00:00 mission.now I want to have this result fo mission:02:00:00
WITH CTE
AS (SELECT
*
FROM(
SELECT
A.ApplicantID
, A.FirstName
, M.RequestDate
FROM Applicant AS A INNER JOIN
Mission AS M ON A.ApplicantID = M.Applicant_Id
UNION ALL
SELECT
A.ApplicantID
, A.FirstName
, V.RequestDate
FROM Applicant AS A INNER JOIN
Vacation AS V ON A.ApplicantID = V.Applicant_Id) AS x)
SELECT
cte.ApplicantID
, cte.FirstName
, cte.RequestDate
, V.HourlyVacationDate
, CONVERT(varchar, DATEADD(ms, V.HourlyVacationStartTime * 1000, 0), 114) as HourlyVacationStartTime
, CONVERT(varchar, DATEADD(ms, V.HourlyVacationEndTime * 1000, 0), 114) as HourlyVacationEndTime
, cast(STR(SUM(V.HourlyVacationEndTime- V.HourlyVacationStartTime)/3600) + RIGHT(CONVERT(char(8),DATEADD(s,SUM(M.EndTime- M.StartTime),0),108),6) as time) as HourlyVacationDiffTime
, V.DailyStartDate
, V.DailyEndDate
, M.StartDate
, M.EndDate
, CONVERT(varchar, DATEADD(ms, M.StartTime * 1000, 0), 114) as MissionStartTime
, CONVERT(varchar, DATEADD(ms, M.EndTime * 1000, 0), 114) as MissionEndTime
, cast(STR(SUM(M.EndTime- M.StartTime)/3600) + RIGHT(CONVERT(char(8),DATEADD(s,SUM(M.EndTime- M.StartTime),0),108),6) as time) as MissionDiffTime
FROM cte LEFT OUTER JOIN
Vacation AS V ON cte.RequestDate = V.RequestDate
AND cte.ApplicantID = V.Applicant_Id
LEFT OUTER JOIN
Mission AS M ON cte.RequestDate = M.RequestDate
AND cte.ApplicantID = M.Applicant_Id
group by ApplicantID,FirstName,cte.RequestDate , V.HourlyVacationDate
, V.HourlyVacationStartTime
, V.HourlyVacationEndTime
, V.DailyStartDate
, V.DailyEndDate
, M.StartDate
, M.EndDate
,M.StartTime
,M.EndTime
October 27, 2015 at 8:51 am
maybe something along these lines will work for you.
have only looked at Vacation
WITH ctesecs as
(
SELECT
A.FirstName
, A.LastName
, SUM(CASE
WHEN DailyStartDate IS NULL THEN HourlyVacationEndTime - HourlyVacationStartTime
ELSE (DATEDIFF(d ,
dailystartdate , dailyenddate) + 1) * 8 * 60 * 60
END) AS dur_secs
FROM Applicant AS A INNER JOIN
Vacation AS V ON A.ApplicantID = V.Applicant_Id
GROUP BY
A.FirstName
, A.LastName
)
SELECT
FirstName
, LastName
, dur_secs
, LEFT(STUFF(CONVERT(varchar(20) , DATEADD(second , dur_secs , 0) - DATEADD(second , 0 , 0) , 114) , 1 , 2 , DATEDIFF(hh , 0 , DATEADD(second , dur_secs , 0) - DATEADD(second , 0 , 0))) , 8)
FROM ctesecs;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 28, 2015 at 3:23 am
thank you so much dear J Livingston SQL
🙂
October 28, 2015 at 11:01 am
elham_azizi_62 (10/28/2015)
thank you so much dear J Livingston SQL🙂
I trust that you understand how this works?
http://qa.sqlservercentral.com/articles/T-SQL/103343/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply