"dotnet dude" <arpit***@gmail.com> wrote in message
news:1158239247.971715.308370@m73g2000cwd.googlegroups.com...
> Here is an interesting problem:
> Lets say I own some imaginary company. Like for all companies, my
> employees report their time to some time reporting application.
> Following table stores these time recording activities:
>
> EmpId    Date    Project    No_of_hours
> 1            08/01     P1              4
> 1            08/01     P2              3
> 1            08/01     P3              1
> 1            08/02     P3              7
> 1            08/02     P1              1
> 1            08/03     P1              4
> 1            08/03     P1              4
>
>
> - In given month, employees spend hours on multiple projects
>
> I want to write a query which entering the begin and end dates and the
> emp id will return the projects the employee worked on during that
> period and the no_of_hours he worked on each
> Output I am looking should be something similar to this:
>
> EmpId  Project   No_of_hours
> 1            P1              30
> 1            P2              15
> 1            P3              20
>
> Any  help is appreciated ...
> Thanks in advance
>

> Just a basic group by and sum will do this.  If your date columns have a
> time component rather than being set to midnight (the default if no time is
> specified), you will need to do the range as shown below.
>
> select empid
> , project
> , sum(no_of_hours) as TotalHours
> where date >= '2006-08-01'
> and date<'2006-09-01'
> group by empid
> , project
>
> "dotnet dude" <arpit***@gmail.com> wrote in message
> news:1158239247.971715.308370@m73g2000cwd.googlegroups.com...
> > Here is an interesting problem:
> > Lets say I own some imaginary company. Like for all companies, my
> > employees report their time to some time reporting application.
> > Following table stores these time recording activities:
> >
> > EmpId    Date    Project    No_of_hours
> > 1            08/01     P1              4
> > 1            08/01     P2              3
> > 1            08/01     P3              1
> > 1            08/02     P3              7
> > 1            08/02     P1              1
> > 1            08/03     P1              4
> > 1            08/03     P1              4
> >
> >
> > - In given month, employees spend hours on multiple projects
> >
> > I want to write a query which entering the begin and end dates and the
> > emp id will return the projects the employee worked on during that
> > period and the no_of_hours he worked on each
> > Output I am looking should be something similar to this:
> >
> > EmpId  Project   No_of_hours
> > 1            P1              30
> > 1            P2              15
> > 1            P3              20
> >
> > Any  help is appreciated ...
> > Thanks in advance
> >

"dotnet dude" <arpit***@gmail.com> wrote in message
news:1158245040.232708.20750@b28g2000cwb.googlegroups.com...
> Thanks guys, looks goood :)
> Another question, is it possible to also return the actual start and
> end dates for each project that employee worked on during the date
> range entered.
> e.g. if the date entered is 08/01 to 08/31 and during this period
> employee worked on two projects P1 and P2. I want to display the date
> when the employee first start working on each project and the last day
> that he worked on it
> Something similar to this:
>
> EmpId Project  Emp_started_working_on   Last_day_worked_on_this_proj
> No_of_hours
> 1         P1          05/15             08/15       30
> 1         P2          07/01             08/05       15
>
>
> Jim Underwood wrote:
> > Just a basic group by and sum will do this.  If your date columns have a
> > time component rather than being set to midnight (the default if no time
is
> > specified), you will need to do the range as shown below.
> >
> > select empid
> > , project
> > , sum(no_of_hours) as TotalHours
> > where date >= '2006-08-01'
> > and date<'2006-09-01'
> > group by empid
> > , project
> >
> > "dotnet dude" <arpit***@gmail.com> wrote in message
> > news:1158239247.971715.308370@m73g2000cwd.googlegroups.com...
> > > Here is an interesting problem:
> > > Lets say I own some imaginary company. Like for all companies, my
> > > employees report their time to some time reporting application.
> > > Following table stores these time recording activities:
> > >
> > > EmpId    Date    Project    No_of_hours
> > > 1            08/01     P1              4
> > > 1            08/01     P2              3
> > > 1            08/01     P3              1
> > > 1            08/02     P3              7
> > > 1            08/02     P1              1
> > > 1            08/03     P1              4
> > > 1            08/03     P1              4
> > >
> > >
> > > - In given month, employees spend hours on multiple projects
> > >
> > > I want to write a query which entering the begin and end dates and the
> > > emp id will return the projects the employee worked on during that
> > > period and the no_of_hours he worked on each
> > > Output I am looking should be something similar to this:
> > >
> > > EmpId  Project   No_of_hours
> > > 1            P1              30
> > > 1            P2              15
> > > 1            P3              20
> > >
> > > Any  help is appreciated ...
> > > Thanks in advance
> > >
>

> If you want the begin and end date for that period only, just select the
> max(date) and min(date) in the same SQL.  Also, I forgot to add the "from
> TABLE" to the initial query.
>
> If you want the begin and end date regardless of whether or not they fall in
> that period, you will need a join or subquery that selects those dates and
> joins on the empid and project.  Here is an example with a join
>
> select a.empid
> , a.project
> , sum(a.no_of_hours) as TotalHours
> , min(b.date) as startdate
> , max(b.date) as enddate
> from sometable a
> inner join sometable b
> on a.empid = b.empid
> and a.project=b.project
> where a.date >= '2006-08-01'
> and a.date<'2006-09-01'
> group by a.empid
> , a.project
>
> For more examples, check out these sites on beginners SQL programming.
>
> http://www.w3schools.com/sql/sql_intro.asp
>
> http://sqlzoo.net/
>
>
> "dotnet dude" <arpit***@gmail.com> wrote in message
> news:1158245040.232708.20750@b28g2000cwb.googlegroups.com...
> > Thanks guys, looks goood :)
> > Another question, is it possible to also return the actual start and
> > end dates for each project that employee worked on during the date
> > range entered.
> > e.g. if the date entered is 08/01 to 08/31 and during this period
> > employee worked on two projects P1 and P2. I want to display the date
> > when the employee first start working on each project and the last day
> > that he worked on it
> > Something similar to this:
> >
> > EmpId Project  Emp_started_working_on   Last_day_worked_on_this_proj
> > No_of_hours
> > 1         P1          05/15             08/15       30
> > 1         P2          07/01             08/05       15
> >
> >
> > Jim Underwood wrote:
> > > Just a basic group by and sum will do this.  If your date columns have a
> > > time component rather than being set to midnight (the default if no time
> is
> > > specified), you will need to do the range as shown below.
> > >
> > > select empid
> > > , project
> > > , sum(no_of_hours) as TotalHours
> > > where date >= '2006-08-01'
> > > and date<'2006-09-01'
> > > group by empid
> > > , project
> > >
> > > "dotnet dude" <arpit***@gmail.com> wrote in message
> > > news:1158239247.971715.308370@m73g2000cwd.googlegroups.com...
> > > > Here is an interesting problem:
> > > > Lets say I own some imaginary company. Like for all companies, my
> > > > employees report their time to some time reporting application.
> > > > Following table stores these time recording activities:
> > > >
> > > > EmpId    Date    Project    No_of_hours
> > > > 1            08/01     P1              4
> > > > 1            08/01     P2              3
> > > > 1            08/01     P3              1
> > > > 1            08/02     P3              7
> > > > 1            08/02     P1              1
> > > > 1            08/03     P1              4
> > > > 1            08/03     P1              4
> > > >
> > > >
> > > > - In given month, employees spend hours on multiple projects
> > > >
> > > > I want to write a query which entering the begin and end dates and the
> > > > emp id will return the projects the employee worked on during that
> > > > period and the no_of_hours he worked on each
> > > > Output I am looking should be something similar to this:
> > > >
> > > > EmpId  Project   No_of_hours
> > > > 1            P1              30
> > > > 1            P2              15
> > > > 1            P3              20
> > > >
> > > > Any  help is appreciated ...
> > > > Thanks in advance
> > > >
> >