Help with this query (or queries)

[rednoise]

Hi, this is my first post. I am looking for help with a query I can't figure out.



I have a table with four fields: service_id, user_id, date_start, date_end.

And I want a query that shows how many services have had a user opened per month. If a service starts on january and ends in march then this service is counted in january, february and march.

Any ideas are welcome Thank you for reading.

[CJ_London]

And I want a query that shows how many services have had a user opened per month. If a service starts on january and ends in march then this service is counted in january, february and march.

Just to clarify - do you mean opened or do you mean used? A service started in January was opened in January, but used in Jan, feb, march

[ranman256]

You said OPENED. It seems it would OPEN in Jan, but finish in March.
or do you want every month the service existed in the date range?

[orange]

rednoise,

How about describing the business problem/opportunity in more detail so readers can appreciate and understand your environment and needs in context? My guess is that there are additional tables/entities and that your description is just a piece of a bigger opportunity, but you haven't supplied any info.
Based on comments so far, you can see your post is somewhat confusing.

Are these things involved?
Services/ServiceTypes
Users (Names, Address, Phone,...)
ServiceSuppliedToUser

To get more focused responses you need to supply more detail.
Good luck.

[rednoise]

Sorry I'm not english native and maybe I'm not explaining it precisely.

Ajax is right, I mean services used or managed.

As for the additional info about the context that orange asks, i have to say this is a repairings company, they assign services to users and want to know how many different services each user manage or carry per month.
I simplified the table so it's easier to understand, there is also a users table and a servicetypes table but it is not needed for this query.

As an example, if the user 23 had a only 1 service started in jan and closed in march then the query result would be like:

user_id	month	services_count
23	1	1
23	2	1
23	3	1

This way the company can measure the amount of work each user have had per month.

[ranman256]

select user, month([date]),count([service]) as CountOfSvc from table

[CJ_London]

A couple of things:

1. you'll need another table with year/months in it - suggest as 201501, 201502 etc - reason is you need to report a figure for a month even if nothing started that month. and you need the year/month to differentiate between January this year and Januarys in previous years.

2. I presume that a service started in January and reported in February and hasn't finished still need to be reported

So with this in mind and assuming your YearMonth table is populated with say 201501 through to 201512 try the following query I've assumed your field names, and guessed at a table name.

SELECT UserID, right(YearMonth.yrMth,2) as Month,count(*) as services_count
FROM myTable, YearMonth
WHERE format(date_start,"yyyymm")>=yrMth AND (date_end is null OR format(date_end,"yyyymm")<=yrMth)
GROUP BY UserID, right(YearMonth.yrMth,2)

[orange]

rednoise,

Write a description in your native language, then use google translate to put it into English. It will be good enough for us to understand.

Repairings of what?

see this data model for ideas/starting point. It's generic to give some idea of the typical entities involved. But it may not reflect your situation or details.
Equipment repair