Have the same row twice in same query

[hos2]

I have a table where I keep up the mutations in rooms and renting rooms for faculties.
the format of the table is

id
roomnr
client
startdate
enddate
area

now I want to make a grouping per client and per month what a client gained in area and what has diminshed

If I group on year-month of startdate I get nice the changes that started that month
but I also need to group the same time on the enddate

for example
room: 2e02
client: math
startdate: 12-01-2003
enddate: 14-02-2003
area: 40m2

I want to get the next result

2003-01 (group by year-month)
...
math 40m2
...
...
...
2003-02
...
...
math -40m2
...
...



so the same record is used twice in the result !! does anybody know how I can handle this ??

 

[swampBoogie]

create a table with integers ranging from 1 to maximum number of months that a rent may have duration.

To get multiple occurances use

select rooms.* from rooms join numbers
on numbers.n >=
(extract(year from enddate) - extract(year from startdate))*12
+ extract(month from enddate) - extract(month from startdate) 
+ 1
where ...

 

[hvass]

hos2
I don't think I understand what you are trying to do. Are you simply looking at the end of the month to see if they had the room? Can a client have many rooms and are you doing some sort of

select
client,
concat(year(startdate),month(enddate)) as yyyymm,
sum(area)
from yourtable
group by client,yyyymm;

2 techniques when you want to get at a record twice that you could try are self joins and inserting multiple times into a results table

 

[swampBoogie]

correction to my previous post

select rooms.* from rooms join numbers
on numbers.n <=
(extract(year from enddate) - extract(year from startdate))*12
+ extract(month from enddate) - extract(month from startdate)
+ 1
where ...

 

[hos2]

it's not a duration I want to calculate. that was the initial setup of the database and why I have a period of usage in the records. I work at a university where there are faculties and institutes having multiple rooms. during a year they have to move sometimes because of renovation or other reasons.

what I need to show is during a year for each month how much they gained and how much they give in. I tried it with a crosstable but the disadvantage is that when I have 1 record where is hold the period of time they occupy a room I want to make visible the surface they gained in the month from startdate (can be more rooms also) but when hey get out the room (enddate) the surface must be substracted (or negative) in the month from the enddate

I can't find a way to calculate with the same record twice since I'm grouping on the month at for instance startdate so I can't use the record in the enddate month where is has to be substracted again

 

[Tbull1001]

I was just trying to mark this for email notification as I am pursuing a similar problem. In my case we are counting the number of new events "created" (which is a datetime field) and then counting the update event, which is another datetime field. The two can have months in between.

I am also thinking in terms of a cross-tab, but with no joy yet.

 

[hos2]

I have solved the problem for my case
I will give a simplified example, perhaps it will work for you to

SELECT sum(if (month(agdate)='01',1, 0)) as month01,
sum(if (month(agdate)='02',1, 0)) as month02,  
sum(if (month(agdate)='03',1, 0)) as month03  
FROM `agenda`

If you have an update event date in the same record
you can make it like

sum(if (month(startdate)='01',1, 0)+if (month(updatedate)='01',1, 0)) as month01

or somethig like that, ofcourse you can also add the year in the if comparison etc etc

good luck

 

[Tbull1001]

Thanks for sharing your solution. I see what you are doing. As the query in our application is dynamically created from a set of forms and can be upto 24 months, programming each month didn't ring my bell. I also checked and can still fall back to passing a start and end date into a query to create an array[dates] and passing it into the actual data retrevial query. Which creates a cross-tab output.

Check out the assist that was offered to me on the self join in the thread "SQL for multi-column?row cross-tab - not exactly".

Best and thanks again,
Tbull1001