Link to home
Start Free TrialLog in
Avatar of Stef Merlijn
Stef MerlijnFlag for Netherlands

asked on

SQL: Workinghours per month per customer

Hello,

I have the following data in two tables.
Table1: ID, DateTimeFrom, DateTimeTo   (agenda/scheduler)
Table2: ID, CustomerID
The ID is the keyfield to connect both tables. Table2 can have zero, one or multiple records for the same ID.

Based on these tables I want to create a report that will display the hours spend to each customer in a given month (see below).

How should I set this up with SQL (MS Access)?
After I've coverred the SQL-statements I will use ReportBuilder 10 to create the actual report.

With kind regards,
Stef
.....Month: Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Okt  Nov  Dec
Customer 1:   4    3    2    5   12    8    6    7   12   14    3    5
Customer 2:   0    0    1    0    0    2    1    0    0    3    1    0
Customer 3:   8    5    7   24   14   18   16    0   16   21   13   15
etc.

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this
SELECT T2.CustomerID
       ,SUM(IIF(Month(DateTimeFrom)=1,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Jan
       ,SUM(IIF(Month(DateTimeFrom)=2,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Feb
       ,SUM(IIF(Month(DateTimeFrom)=3,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Mar
       ,SUM(IIF(Month(DateTimeFrom)=4,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Apr
       ,SUM(IIF(Month(DateTimeFrom)=5,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS May
       ,SUM(IIF(Month(DateTimeFrom)=6,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Jun
       ,SUM(IIF(Month(DateTimeFrom)=7,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Jul
       ,SUM(IIF(Month(DateTimeFrom)=8,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Aug
       ,SUM(IIF(Month(DateTimeFrom)=9,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Sep
       ,SUM(IIF(Month(DateTimeFrom)=10,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Okt
       ,SUM(IIF(Month(DateTimeFrom)=11,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Nov
       ,SUM(IIF(Month(DateTimeFrom)=12,DateDiff('n',T1.DateTimeFrom,T1.DateTimeTo),0)) AS Dec
FROM Table1 T1 INNER JOIN Table2 T2 ON (T1.ID=T2.ID)
GROUP BY T2.CustomerID

Open in new window

Avatar of Stef Merlijn

ASKER

Thank you.
Your solution works fine, but it is giving the number of minutes in stead of hours. So I need to divide the outcome with 60 and round it up.
Also there is no year and therefor all event in january of all years will be acummulated.

Do you have some solution for this too?
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's it. Thank you very much.
Perfect!
you're welcome ;)