That helped a ton. I was haveing SQL Writers Block lol. On 5/7/06, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > > Richard Dillman wrote: > > > > Children are featured in various special publications at least 3 of > which i > > need to track their appearances. Ill need to output > > the publication and the month(s) they appeared > > Months willl probably not be sequential. > > Display 12 months in a row with the year at the end. > > CSS to show a Month green if it was a Feature Month. > > Loop through all years sence 2000 to the present Month. > > 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 2004 > > 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 2005 > > 1 | 2 | 3 | 4 | 5 | | 2006 > > .... > > > > How would you store this in SQL 2000 if each group of Month/years is to > be > > displayed per record and childrens records will reach 2000 this month? > > In SQL-2003: > > CREATE TABLE publications ( > publicationID INTEGER PRIMARY KEY, > publicationName VARCHAR > ); > CREATE TABLE children ( > childID INTEGER PRIMARY KEY, > childName VARCHAR, > etc); > CREATE TABLE features ( > featureID INTEGER PRIMARY KEY, > childID INTEGER REFERENCES children, > publicationID INTEGER REFERENCES publications, > featureDate DATE, > etc); > CREATE TABLE featurePeriod ( > featureYear INTEGER, > featureMonth INTEGER > ); > INSERT INTO featureMonths VALUES ((2000,1),(2000,2),(2000,3)etc.) > > > SELECT > fp.featureYear, > fp.featureMonth, > f.childID > FROM > featurePeriod fp Left JOIN > ( > SELECT featureDate, childID > FROM features > WHERE > childID = <cfqueryparam value="#childID#"> > AND > publicationID = <cfqueryparam value="#publicationID#"> > ) f ON > ( > EXTRACT(YEAR FROM f.featureDate) = fp.featureYear > AND > EXTRACT(MONTH FROM f.featureDate) = fp.featureMonth > ) > ORDER BY > fp.featureYear, > p.featureMonth > > Jochem > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:239849 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
