How to make Sql query for my Problem

Question

0.00/5 (No votes)

See more:

Hello Every One,

I am working in asp.net application. In which I have a requirement to show records from three tables.
following are my tables

table1: employee
column names: ID, Name, EmailID

table2: project
column names: ID, Date, EmpID, ProjectName

table3: Detail
column names: ID, Date, EmpID, SpentTime, Desc

Now my requirement is that i want following columns from three table using joining

Date, EmpID, ProjectName, Sum(SpentTime)

In these tables for a single date in project table multiple records may be exists. I am not able to getting the records with sum of time when i am retrieving records for a month then it is not returning the sum of single date time instead it returns the sum of time for a full moth.

so please give me SQL query which can help me.

Posted 24-Jul-12 2:12am

coolnavjot31

Add a Solution

Comments

E.F. Nijboer 24-Jul-12 8:27am

Someone could just give you some answer (which hopefully does the job, because you can't determine it due to lack of knowledge and/or interest) or you could go look on the internet or open a book and do some learning. Try something yourself and if there is a problem you can ask about that.

[no name] 24-Jul-12 9:10am

On top of that, your requirement does not make any sense. "the sum of single date time", think about that for a minute and figure out what that means.... what is the sum of 1 for instance.

snorkie 24-Jul-12 10:18am

Post the SQL you have tried and I'll help you get it straightened out.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

archies_gall · Answer 1 · 2012-07-24T08:13:00

Solution 1

Try this ..

SQL

SELECT SUM(SpentTime - CAST('0:0:0' as TIME))
  FROM YourTableName

Posted 24-Jul-12 8:13am

archies_gall

Comments

coolnavjot31 25-Jul-12 1:53am

Hello I am using following Sql query :

select e.ID, e.Name, e.EmailID, p.Date, p.ProjectName, d.Desc, Sum(d.SpentTime) as SpentTime from employee as e inner join project as p on e.ID = p.EmpID
Join Detail as d on e.ID = d.EmpID where e.ID = 1002 and Month(p.Date) = 7 group by p.Date, e.ID, e.Name, e.EmailID, p.ProjectName, d.Desc

archies_gall 25-Jul-12 11:29am

Did you try this?

select e.ID, e.Name, e.EmailID, p.Date, p.ProjectName, d.Desc, Sum(d.SpentTime - CAST('0:0:0' as TIME)) as SpentTime from employee as e inner join project as p on e.ID = p.EmpID
Join Detail as d on e.ID = d.EmpID where e.ID = 1002 and Month(p.Date) = 7 group by p.Date, e.ID, e.Name, e.EmailID, p.ProjectName, d.Desc

archies_gall 25-Jul-12 11:34am

What data you have in the SpentTime column?
Can you give me example data in your table?

coolnavjot31 26-Jul-12 2:31am

In My SpentTime Column i have time in minutes(Data type- integer).

archies_gall 26-Jul-12 5:43am

In minutes??
that means u have only data as
SpentTime
----------
45
30
25
59

like this only right?