How do I create a series of specific days in a SQL table? -


i have table called classtimes has column of type bool each day of week (sunday,monday,tuesday...etc) , contains startdate , enddate column. example, have row has monday , wednesday selected startdate of june 3, 2013 , enddate of june 26, 2013.

what i'd able create sproc insert series of rows table called classcalendar contains of dates between startdate , enddate each selected day column.

for example, if user selected monday , wednesay classtimes table, generate:

 row    date 1      2013-06-03 2      2013-06-10 3      2013-06-17 4      2013-06-24 5      2013-06-05 6      2013-06-12 7      2013-06-19 8      2013-06-26

i have tried set bit on head. appreciated.

here's sql statement might work you.

select course,        convert(varchar(25), course_date, 101) course_date   (select course_no course,                dateadd(dd, rn-1, startdate) course_date,                datename(dw, dateadd(dd, rn-1, startdate)) dow           (select row_number() on (order c1) rn                   dummy) sub1,                classtimes          rn <= (datediff(dd, startdate, enddate)+1)        ) list_of_dates,        (select course_no,                (case monday when 1 'monday' end) dow           classtimes         union         select course_no,                (case tuesday when 1 'tuesday' end)           classtimes         union         select course_no,                (case wednesday when 1 'wednesday' end)                     classtimes         union         select course_no,                (case thursday when 1 'thursday' end)           classtimes         union         select course_no,                (case friday when 1 'friday' end)           classtimes         union         select course_no,                (case saturday when 1 'saturday' end)           classtimes         union         select course_no,                (case sunday when 1 'sunday' end)           classtimes         ) class_days  list_of_dates.dow = class_days.dow    , list_of_dates.course = class_days.course_no  order course_no,           course_date

i used query 

select course_no course,        dateadd(dd, rn-1, startdate) course_date,        datename(dw, dateadd(dd, rn-1, startdate)) dow   (select row_number() on (order c1) rn           dummy) sub1,        classtimes  rn <= (datediff(dd, startdate, enddate)+1)

to generate list of dates between startdate , enddate each course. in order query work correctly, dummy table must contain @ least many rows number of days between startdate , enddate each course. result of list of days between startdate , enddate each course, along day of week date.

| course | course_date |       dow | ------------------------------------ |   math |  06/03/2013 |    monday | |   math |  06/04/2013 |   tuesday | |   math |  06/05/2013 | wednesday | |   math |  06/06/2013 |  thursday | |   math |  06/07/2013 |    friday | ......... ......... |   math |  06/24/2013 |    monday | |   math |  06/25/2013 |   tuesday | |   math |  06/26/2013 | wednesday |

i have subquery uses series of unions take day of week columns classtimes table , generate days of week classes held. join list_of_dates subquery class_days subquery dates classes held.

|  course | course_date | ------------------------- |    math |  06/03/2013 | |    math |  06/05/2013 | |    math |  06/10/2013 | |    math |  06/12/2013 | |    math |  06/17/2013 | |    math |  06/19/2013 | |    math |  06/24/2013 | |    math |  06/26/2013 |

i'm sure there's more efficient/elegant way generate list of days of week class classtimes table (the class_days subquery), couldn't think of one.


Comments

Post a Comment

Popular posts from this blog

python - How to create a legend for 3D bar in matplotlib? -

Image
given ax = plt.subplot() : ax.bar()[0] can passed plt.legend() . however, ax.bar3d() returns none . how create legend displayed bars? update: passing legend="stuff" ax.bar3d() , calling ax.legend() raises /usr/lib/python2.6/site-packages/matplotlib/axes.py:4368: userwarning: no labeled objects found. use label='...' kwarg on individual plots. warnings.warn("no labeled objects found. " you need use proxy artist legends not supported. this code: from mpl_toolkits.mplot3d import axes3d import matplotlib.pyplot plt import numpy np fig = plt.figure() ax = fig.add_subplot(111, projection='3d') x, y = np.random.rand(2, 100) * 4 hist, xedges, yedges = np.histogram2d(x, y, bins=4) elements = (len(xedges) - 1) * (len(yedges) - 1) xpos, ypos = np.meshgrid(xedges[:-1]+0.25, yedges[:-1]+0.25) xpos = xpos.flatten() ypos = ypos.flatten() zpos = np.zeros(elements) dx = 0.5 * np.ones_like(zpos) dy = dx.copy() dz = hist.flatten() ax...
Read more

java - Multi-Label Document Classification -

i have database in store data based upon following 3 fields: id, text, {labels}. note each text has been assigned more 1 label \ tag \ class. want build model (weka \ rapidminer \ mahout) able recommend \ classify bunch of labels \ tags \ classes given text. i have heard svm , naive bayes classifier, not sure whether support multi-label classification or not. guides me right direction more welcome! the basic multilabel classification method one-vs.-the-rest (ovr), called binary relevance (br). basic idea take off-the-shelf binary classifier, such naive bayes or svm, create k instances of solve k independent classification problems. in python-like pseudocode: for each class k: learner = svm(settings) # example labels = [class_of(x) == k x in samples] learner.learn(samples, labels) then @ prediction time, run each of binary classifiers on sample , collect labels predict positive. (both training , prediction can done in parallel, since problems assumed ...
Read more

php - Dynamic url re-writing using htaccess -

how can rewrite http://mydomain.com/test/master.php?catid=2&subcatid=8 http://mydomain.com/test/master.php/catname/subcat using .htaccess file. once rewritten, how can access these parameters in master.php? note: untested, let me know if doesn't work. rewriterule ^master.php/([a-za-z0-9_-]+)/([a-za-z0-9_-]+)$ /master.php?catid$1&subcatid=$2 [l]
Read more