michael weaver
|
| Posted: 11/01/2002, 11:23 AM |
|
Hi, I'm stumped and I was wondering if someone could help with some MySQL
structure.
I'm building an reporting app that allows a property management company to
receive weekly reports from their properties.
The table reports has the following fields: id, number, date, numvac, numdep
I also have a table "property" has the following fields: number, name
I'm currently using this SELECT statement:
SELECT number, date, numvac, numdep
FROM reports
WHERE date <= NOW() AND date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY date DESC, number ASC
That returns the correct information, BUT i need for it to display numvac
and numdep in the same row per property, depending on the week. For
instance, the final report should return rows that look something like this:
number
week1numvac
week1numdep
week2numvac
week2numdep
week3numvac
week3numdep
week4numvac
week4numdep
I don't need separate rows per property.
week1 would be the current week, and week4 would be every report subitted
between 21 and 28 days ago.
Does that make any sense? Any ideas?
michael
|
|
|
 |
David Harrison
|
| Posted: 11/01/2002, 12:04 PM |
|
I think what your looking for is something like this. I have not used MySql
so I know the syntax for the date functions are a little different but the
essence will be the same as for MS SQL2K.
SELECT Property.Number, sum(Week1.numvac), sum(Week1.numdep),
sum(Week2.numvac), sum(Week2.numdep), sum(Week3.numvac), sum(Week3.numdep) ,
sum(Week4.numvac), sum(Week4.numdep)
FROM Property
JOIN Reports Week1 on Property.Number = Week1.Number and Week1.Date
between getdate() and datesub(day, 6, getdate())
JOIN Reports Week2 on Property.Number = Week1.Number and Week2.Date
between datesub(day,7,getdate()) and datesub(day,13,getdate())
JOIN Reports Week3 on Property.Number = Week1.Number and Week3.Date
between datesub(day,14,getdate()) and datesub(day,20,getdate())
JOIN Reports Week4 on Property.Number = Week1.Number and Week4.Date
between datesub(day,21,getdate()) and datesub(day,27,getdate())
group by Property.Number
ORDER BY Property.Number ASC
"michael weaver" <zero@michaelweaver.org> wrote in message
news:apukbe$v5u$1@news.codecharge.com...
> Hi, I'm stumped and I was wondering if someone could help with some MySQL
> structure.
>
> I'm building an reporting app that allows a property management company to
> receive weekly reports from their properties.
>
> The table reports has the following fields: id, number, date, numvac,
numdep
> I also have a table "property" has the following fields: number, name
>
> I'm currently using this SELECT statement:
>
> SELECT number, date, numvac, numdep
> FROM reports
> WHERE date <= NOW() AND date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
> ORDER BY date DESC, number ASC
>
> That returns the correct information, BUT i need for it to display numvac
> and numdep in the same row per property, depending on the week. For
> instance, the final report should return rows that look something like
this:
>
> number
> week1numvac
> week1numdep
> week2numvac
> week2numdep
> week3numvac
> week3numdep
> week4numvac
> week4numdep
>
> I don't need separate rows per property.
> week1 would be the current week, and week4 would be every report subitted
> between 21 and 28 days ago.
>
> Does that make any sense? Any ideas?
>
> michael
>
>
>
|
|
|
|
michael weaver
|
| Posted: 11/01/2002, 3:27 PM |
|
Thanks David - I think that did it! I started working on something just like
that and didn't get that far on it.
Here's what I ended up with:
SELECT property.number,
sum(week1.numvac) AS 1Vac, sum(week1.numdep) AS 1Dep,
sum(week2.numvac) AS 2Vac, sum(week2.numdep) AS 2Dep,
sum(week3.numvac) AS 3Vac, sum(week3.numdep) AS 3Dep,
sum(week4.numvac) AS 4Vac, sum(week4.numdep) AS 4Dep
FROM property
LEFT JOIN reports week1 ON property.number = week1.number and week1.date
BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
LEFT JOIN reports week2 ON property.number = week2.number and week2.date
BETWEEN DATE_SUB(NOW(), INTERVAL 14 DAY) AND DATE_SUB(NOW(), INTERVAL 8 DAY)
LEFT JOIN reports week3 ON property.number = week3.number and week3.date
BETWEEN DATE_SUB(NOW(), INTERVAL 21 DAY) AND DATE_SUB(NOW(), INTERVAL 15
DAY)
LEFT JOIN reports week4 ON property.number = week4.number and week4.date
BETWEEN DATE_SUB(NOW(), INTERVAL 28 DAY) AND DATE_SUB(NOW(), INTERVAL 22
DAY)
GROUP BY property.number
ORDER BY property.number ASC;
I certainly couldn't have done it without your help. Thanks for taking the
time!
michael
"David Harrison" <davidh@saltydog.net> wrote in message
news:apumo8$3td$1@news.codecharge.com...
> I think what your looking for is something like this. I have not used
MySql
> so I know the syntax for the date functions are a little different but the
> essence will be the same as for MS SQL2K.
>
> SELECT Property.Number, sum(Week1.numvac), sum(Week1.numdep),
> sum(Week2.numvac), sum(Week2.numdep), sum(Week3.numvac), sum(Week3.numdep)
,
> sum(Week4.numvac), sum(Week4.numdep)
> FROM Property
> JOIN Reports Week1 on Property.Number = Week1.Number and Week1.Date
> between getdate() and datesub(day, 6, getdate())
> JOIN Reports Week2 on Property.Number = Week1.Number and Week2.Date
> between datesub(day,7,getdate()) and datesub(day,13,getdate())
> JOIN Reports Week3 on Property.Number = Week1.Number and Week3.Date
> between datesub(day,14,getdate()) and datesub(day,20,getdate())
> JOIN Reports Week4 on Property.Number = Week1.Number and Week4.Date
> between datesub(day,21,getdate()) and datesub(day,27,getdate())
> group by Property.Number
> ORDER BY Property.Number ASC
>
> "michael weaver" <zero@michaelweaver.org> wrote in message
>news:apukbe$v5u$1@news.codecharge.com...
> > Hi, I'm stumped and I was wondering if someone could help with some
MySQL
> > structure.
> >
> > I'm building an reporting app that allows a property management company
to
> > receive weekly reports from their properties.
> >
> > The table reports has the following fields: id, number, date, numvac,
> numdep
> > I also have a table "property" has the following fields: number, name
> >
> > I'm currently using this SELECT statement:
> >
> > SELECT number, date, numvac, numdep
> > FROM reports
> > WHERE date <= NOW() AND date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
> > ORDER BY date DESC, number ASC
> >
> > That returns the correct information, BUT i need for it to display
numvac
> > and numdep in the same row per property, depending on the week. For
> > instance, the final report should return rows that look something like
> this:
> >
> > number
> > week1numvac
> > week1numdep
> > week2numvac
> > week2numdep
> > week3numvac
> > week3numdep
> > week4numvac
> > week4numdep
> >
> > I don't need separate rows per property.
> > week1 would be the current week, and week4 would be every report
subitted
> > between 21 and 28 days ago.
> >
> > Does that make any sense? Any ideas?
> >
> > michael
> >
> >
> >
>
>
|
|
|
|
|
