Get a month's days and merge with a table

[Nime]

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

[Bob Barrows [MVP]]

Nime wrote:
> 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?

> ...
>
> 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?

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:
>
> dayno
> 1
> 2
> ...
> 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?

--
Microsoft 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"

[Nime]

MSSQL 2K

[Bob Barrows [MVP]]

Nime wrote:
> MSSQL 2K

Errr ... there were other questions.
Or did I provide the solution you needed?

[Nime]

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.

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom>, iletide şunu yazdı
news:#s#H2ut$IHA....@TK2MSFTNGP03.phx.gbl...

[Bob Barrows [MVP]]

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?)

[Nime]

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,

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom>, iletisinde şunu yazdı,
news:%23dqremv$IHA....@TK2MSFTNGP04.phx.gbl...

[Old Pedant]

"Nime" wrote:

> 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?

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???