 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / August 2008Get a month's days and merge with a table
I have a time table: jobid | dayno | job 1 | 2 | Report finished tasks 2 | 4 | Check all forms ... I want to create a days list of a month and merge it with my jobs table. The result should be like this: dayofmonth | jobid | dayno | job 1 | NULL | NULL | NULL 2 | 1 | 2 | Report finished tasks 3 | NULL | NULL | NULL 4 | 2 | 4 | Check all forms 5 | NULL | NULL | NULL 6 | NULL | NULL | NULL ... 30 | NULL | NULL | NULL Is it possible to do that wihout a dummy days table of months? I created a day table of month like below: dayno 1 2 ... 30 31 > I have a time table: What database? Type and version please. It is almost always relevant. > jobid | dayno | job > 1 | 2 | Report finished tasks > 2 | 4 | Check all forms Can there be more than one job per dayno? > ... > [quoted text clipped - 12 lines] > > Is it possible to do that wihout a dummy days table of months? It depends on the database, but I don't understand the resistance to doing this. A calendar table would make this task trivial select c.dayofmonth,j.jobid,j.dayno,j.job from calendar c left join jobs j on c.dayofmonth=j.dayno I don't see where you are storing the month. Are you planning to clear that jobs table every month? > I created a day table of month like below: > [quoted text clipped - 4 lines] > 30 > 31 It should probably contain entries for the entire year ... not all months have 31 days. Or don't you care? You just want to display 31 rows regardless of the month?  SignatureMicrosoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" MSSQL 2K > MSSQL 2K Errr ... there were other questions. Or did I provide the solution you needed? SignatureMicrosoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" Simply I want to create a timetable from day 1 to day 30/31 I have actually 3 records but I want to show 30 rows... How can I generate whole rows with a simple query? I can do that with ASP but I wonder the sql solution if any... Simply how can I create rows from 1 to 30 without querying any table ... SELECT 1 returns just a row, for exam. I want 30 rows from 1 to 30. >> MSSQL 2K > > Errr ... there were other questions. > Or did I provide the solution you needed? Again, I'm not understanding the aversion to creating a calendar table. However, since you are using SQL Server, you can generate the numbers from 1 to 31 by using a union query. Select 1 as dayofmonth union all Select 2 union all Select 3 ... union all Select 31 My recommendation would be to use the union query to create a view, like this: CREATE VIEW MonthDays AS Select 1 as dayofmonth union all Select 2 union all Select 3 ... union all Select 31 Then simply use the left join I suggested in my first reply (did you read the whole reply?) > Simply I want to create a timetable from day 1 to day 30/31 > I have actually 3 records but I want to show 30 rows... [quoted text clipped - 17 lines] >> I don't check it very often. If you must reply off-line, then remove >> the "NO SPAM" SignatureMicrosoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" Ops, I didnt see the your first reply, completely... I just want to create an agenda like table in a web page. So I've created the hours of a day from 08:00 AM to 08:00 PM like below 08:00 08:30 09:00 .... I've done it with a static agenda_hours table. Then I've wanted to create a monthly report; list of -empty or not- whole days of a month. And I didnt want to loop in ASP and query the server for every possible day. Your last reply is also useful, > Again, I'm not understanding the aversion to creating a calendar table. > However, since you are using SQL Server, you can generate the numbers from [quoted text clipped - 45 lines] >>> I don't check it very often. If you must reply off-line, then remove >>> the "NO SPAM" > I have a time table: > [quoted text clipped - 17 lines] > > Is it possible to do that wihout a dummy days table of months? Sure. Create a stored procedure that uses a cursor, for example, and play games with the while loop that drives the cursor. Use the (silly but clever!) UNION scheme that Bob showed you. Probably could come up with other ways. BUT WHY? The table is _by far_ the most efficient way to do this! It's simple to do, it's clean code, and the SQL query to use it is so much more understandable than any other scheme. I agree completely with Bob: _WHY_ do you want to avoid the table??? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.