Help Me Frame This Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help Me Frame This Query

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-10-17 : 03:57:28

CREATE TABLE [dbo].[ETS](
[EID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[SDateTime] [datetime] NOT NULL,
[EDateTime] [datetime] NOT NULL,
[Duration] [varchar](50) NOT NULL,
[ProjectID] [varchar](50) NOT NULL,
[ClientID] [varchar](50) NOT NULL,
)

INSERT [dbo].[ETS] ([EID], [UserName], [SDateTime], [EDateTime], [Duration], [ProjectID], [ClientID]) VALUES (12, 'abc', '10/01/2008 8:00:00 AM', '10/01/2008 11:30:00 AM', '3:30', '4234', '5443')

INSERT [dbo].[ETS] ([EID], [UserName], [SDateTime], [EDateTime], [Duration], [ProjectID], [ClientID]) VALUES (13, 'abc', '10/02/2008 11:00:00 AM', '10/02/2008 1:50:00 PM', '2:50', '4234', '5443')

INSERT [dbo].[ETS] ([EID], [UserName], [SDateTime], [EDateTime], [Duration], [ProjectID], [ClientID]) VALUES (14, 'abc', '10/03/2008 3:00:00 PM', '10/03/2008 4:00:00 PM', '1:00', '4234', '5443')

CREATE TABLE [dbo].[CTS](
[CID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [varchar](50) NOT NULL,
[ClientID] [varchar](50) NOT NULL,
[BilledDate] [datetime] NULL,
[PaidDate] [datetime] NULL
)

INSERT [dbo].[CTS] ([CID], [ProjectID], [ClientID], [BilledDate], [PaidDate]) VALUES (1, '4234', '5443', '10/14/2008 6:00:00 PM', '10/17/2008 4:00:00 PM')

The columns in the table CTS are self-explanatory. Here's an insight into the SDateTime & EDateTime columns in the table named ETS:

Suppose I start a project on 1st October at 8AM, do it till 11:30AM & go home. This means that today I have worked on the project for 3 hours & 30 minutes. I come back to the office on 2nd October, start working with the same project at 11AM, work on this project till 1:50PM & then go home. This means on 2nd October, I worked on this project for 2 hours & 50 minutes. I come back to the office on 3rd October, start working with the same project at 3PM, work on this project till 4PM & then go home. This means on 3rd October, I worked on this project for 1 hour. The project is finally over. In total, I have worked on this project for 7 hours & 20 mniutes.

What I want is a query joining the 2 tables which will give me the ProjectID (i.e. 4234), ClientID (i.e. 5443), date & time on which I started this project (i.e. 10/01/2008 8:00:00 AM), the date & time on which I finished this project (i.e. 10/03/2008 4:00:00 PM) & finally the total no. of hours I have spent on this project (i.e. 7:20 or 7 hours & 20 minutes). How do I frame this query?

Please note that I have already framed the following query to get the total no. hours & minutes I have spent on this project:

SELECT ProjectID,
(SELECT CAST(CAST((SUM(CAST(SUBSTRING(Duration, 0, CHARINDEX(':',Duration)) AS INT))*60 +
SUM(CAST(RIGHT(Duration,2) AS INT)))/60 AS VARCHAR(10)) + ':' + CAST((SUM(CAST(RIGHT(Duration,2) AS INT)))%60 AS VARCHAR(10)) AS VARCHAR(10)) WHERE ProjectID='4234') AS TotalDuration
FROM ETS1
WHERE ProjectID='4234'
GROUP BY ProjectID

Thanks,

Ron

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-17 : 07:07:23

Is this what youy need?

SELECT ProjectID,
(SELECT CAST(CAST((SUM(CAST(SUBSTRING(Duration, 0, CHARINDEX(':',Duration)) AS INT))*60 +
SUM(CAST(RIGHT(Duration,2) AS INT)))/60 AS VARCHAR(10)) + ':' + CAST((SUM(CAST(RIGHT(Duration,2) AS INT)))%60 AS VARCHAR(10)) AS VARCHAR(10)) WHERE ProjectID='4234') AS TotalDuration
,clientid,Min(SDateTime) as startT, Max(Edatetime) as EndT
FROM ETS
WHERE ProjectID='4234'
GROUP BY ProjectID,clientid