Create "report query"
-
Hi, thanks for looking Im trying to make a query that will show me something like a report I have a Homes table and a History table Table Homes: Info about homes Col: HomeID Col: AddressID Col: ... Table History: A record for every status in wich a home has been Col: HistoryID Col: HomeID Col: StatusID Col: DateStarted Col: DateFinished Col: ... A home can have up to 4 rows in the History (Status 1 - Status 4), what i need to do is a query that return something like this: HomeID - Status1 - Status2 - Status3 - Status4 1 - 10/10/09 - 10/12/09 - 10/14/09 - 10/18/09 2 - ................ Here, im showing a home and the DateFinished column from the History table In case that a home doesnt have statusX, null is ok I was trying to create this view with the view designer, first i draged the Homes and History and made the query by filtering history with status1, so i got homeid and status1, then i had to redrag history with an alias and filter status2 to get status2 and so on It seems really unefficient this way Please let me know if you know another way to do it Thanks in advance
Alexei Rodriguez
-
Hi, thanks for looking Im trying to make a query that will show me something like a report I have a Homes table and a History table Table Homes: Info about homes Col: HomeID Col: AddressID Col: ... Table History: A record for every status in wich a home has been Col: HistoryID Col: HomeID Col: StatusID Col: DateStarted Col: DateFinished Col: ... A home can have up to 4 rows in the History (Status 1 - Status 4), what i need to do is a query that return something like this: HomeID - Status1 - Status2 - Status3 - Status4 1 - 10/10/09 - 10/12/09 - 10/14/09 - 10/18/09 2 - ................ Here, im showing a home and the DateFinished column from the History table In case that a home doesnt have statusX, null is ok I was trying to create this view with the view designer, first i draged the Homes and History and made the query by filtering history with status1, so i got homeid and status1, then i had to redrag history with an alias and filter status2 to get status2 and so on It seems really unefficient this way Please let me know if you know another way to do it Thanks in advance
Alexei Rodriguez
-
Hi, thanks for looking Im trying to make a query that will show me something like a report I have a Homes table and a History table Table Homes: Info about homes Col: HomeID Col: AddressID Col: ... Table History: A record for every status in wich a home has been Col: HistoryID Col: HomeID Col: StatusID Col: DateStarted Col: DateFinished Col: ... A home can have up to 4 rows in the History (Status 1 - Status 4), what i need to do is a query that return something like this: HomeID - Status1 - Status2 - Status3 - Status4 1 - 10/10/09 - 10/12/09 - 10/14/09 - 10/18/09 2 - ................ Here, im showing a home and the DateFinished column from the History table In case that a home doesnt have statusX, null is ok I was trying to create this view with the view designer, first i draged the Homes and History and made the query by filtering history with status1, so i got homeid and status1, then i had to redrag history with an alias and filter status2 to get status2 and so on It seems really unefficient this way Please let me know if you know another way to do it Thanks in advance
Alexei Rodriguez
You can try something similar to this. select homeid, max(case when statusid=1 then datefinished else null end) status1, max(case when statusid=2 then datefinished else null end) status2, max(case when statusid=3 then datefinished else null end) status3, max(case when statusid=4 then datefinished else null end) status4, from history group by homeid; <edit>fixed typo</edit>
modified on Friday, May 15, 2009 4:36 AM
-
You can try something similar to this. select homeid, max(case when statusid=1 then datefinished else null end) status1, max(case when statusid=2 then datefinished else null end) status2, max(case when statusid=3 then datefinished else null end) status3, max(case when statusid=4 then datefinished else null end) status4, from history group by homeid; <edit>fixed typo</edit>
modified on Friday, May 15, 2009 4:36 AM
-
Thanks lot!!! This reduced my query a lot I just have a question What is the max for?? :confused: I removed the max as well as the group by and still works Thanks again
Alexei Rodriguez
AlexeiXX3 wrote:
Thanks lot!!!
You're welcome
AlexeiXX3 wrote:
What is the max for?? I removed the max as well as the group by and still works
In case you have duplicated values in the table, the query will then select the last date (use Min() for the first date). This is of course not necessary if you have HomeID and StatusID as a composite key in the table.
-
AlexeiXX3 wrote:
Thanks lot!!!
You're welcome
AlexeiXX3 wrote:
What is the max for?? I removed the max as well as the group by and still works
In case you have duplicated values in the table, the query will then select the last date (use Min() for the first date). This is of course not necessary if you have HomeID and StatusID as a composite key in the table.