Greetings,

I'm wondering if anyone could provide some feedback on how best to design a 
database I'm working on.

I have any number of Projects, which exist for a specific date range.

Each Project will have zero or more expected deliveries each week of the 
project. So I need to track the expected deliveries, and the actual 
deliveries for each of these weeks.

The obvious way seems to be to create a Deliveries table, where each row is 
associated with a Project and a calendar week. The problem with this is that 
it seems kind of awkward to create the reports I need. I need to show a box 
for each week in the Project, with the number of Deliveries scheduled and 
delivered for each week. I know about COUNT, which seems less efficient that 
other potential designs. But I can't even see how to write a query that 
shows a row for each week using this design.

Any input appreciated.

Jonathan

Your query sounds like a simple GROUP BY using COUNT().

I would tend to use a date in the Deliveries table, rather than a
calendar week.  The date can always be converted to week on the fly.
Using a calendar table would probably be the best approach.

Roy Harvey
Beacon Falls, CT

On Tue, 12 Aug 2008 13:54:11 -0600, "Jonathan Wood"
 wrote:

>Greetings,
>
>I'm wondering if anyone could provide some feedback on how best to design a 
>database I'm working on.
>
>I have any number of Projects, which exist for a specific date range.
>
>Each Project will have zero or more expected deliveries each week of the 
>project. So I need to track the expected deliveries, and the actual 
>deliveries for each of these weeks.
>
>The obvious way seems to be to create a Deliveries table, where each row is 
>associated with a Project and a calendar week. The problem with this is that 
>it seems kind of awkward to create the reports I need. I need to show a box 
>for each week in the Project, with the number of Deliveries scheduled and 
>delivered for each week. I know about COUNT, which seems less efficient that 
>other potential designs. But I can't even see how to write a query that 
>shows a row for each week using this design.
>
>Any input appreciated.
>
>Jonathan

Roy,

> Your query sounds like a simple GROUP BY using COUNT().

I'm not sure I understand. Do you mean to group by the date, and then show 
the values returned by COUNT()?

I'm trying to get my head around this. And, since the report will need to by 
kind of like a schedule, I need to also show those weeks where the count is 
0.

> I would tend to use a date in the Deliveries table, rather than a
> calendar week.  The date can always be converted to week on the fly.
> Using a calendar table would probably be the best approach.

Yes, I can use a date instead of a week. Is there anything else you can 
share about your ideas for a "calendar table?"

Thanks!

> On Tue, 12 Aug 2008 13:54:11 -0600, "Jonathan Wood"
>  wrote:
>
>>Greetings,
>>
>>I'm wondering if anyone could provide some feedback on how best to design 
>>a
>>database I'm working on.
>>
>>I have any number of Projects, which exist for a specific date range.
>>
>>Each Project will have zero or more expected deliveries each week of the
>>project. So I need to track the expected deliveries, and the actual
>>deliveries for each of these weeks.
>>
>>The obvious way seems to be to create a Deliveries table, where each row 
>>is
>>associated with a Project and a calendar week. The problem with this is 
>>that
>>it seems kind of awkward to create the reports I need. I need to show a 
>>box
>>for each week in the Project, with the number of Deliveries scheduled and
>>delivered for each week. I know about COUNT, which seems less efficient 
>>that
>>other potential designs. But I can't even see how to write a query that
>>shows a row for each week using this design.
>>
>>Any input appreciated.
>>
>>Jonathan

For calendar tables and their use, check out 
http://www.aspfaq.com/show.asp?id=2519

It even tells you how to show the dates where the count is zero, see the 
section labelled "Getting all dates, even those not represented".

Tom

"Jonathan Wood"  wrote in message 
news:uQKeWFO$IHA.3964@TK2MSFTNGP06.phx.gbl...
> Roy,
>
>> Your query sounds like a simple GROUP BY using COUNT().
>
> I'm not sure I understand. Do you mean to group by the date, and then show 
> the values returned by COUNT()?
>
> I'm trying to get my head around this. And, since the report will need to 
> by kind of like a schedule, I need to also show those weeks where the 
> count is 0.
>
>> I would tend to use a date in the Deliveries table, rather than a
>> calendar week.  The date can always be converted to week on the fly.
>> Using a calendar table would probably be the best approach.
>
> Yes, I can use a date instead of a week. Is there anything else you can 
> share about your ideas for a "calendar table?"
>
> Thanks!
>
>> On Tue, 12 Aug 2008 13:54:11 -0600, "Jonathan Wood"
>>  wrote:
>>
>>>Greetings,
>>>
>>>I'm wondering if anyone could provide some feedback on how best to design 
>>>a
>>>database I'm working on.
>>>
>>>I have any number of Projects, which exist for a specific date range.
>>>
>>>Each Project will have zero or more expected deliveries each week of the
>>>project. So I need to track the expected deliveries, and the actual
>>>deliveries for each of these weeks.
>>>
>>>The obvious way seems to be to create a Deliveries table, where each row 
>>>is
>>>associated with a Project and a calendar week. The problem with this is 
>>>that
>>>it seems kind of awkward to create the reports I need. I need to show a 
>>>box
>>>for each week in the Project, with the number of Deliveries scheduled and
>>>delivered for each week. I know about COUNT, which seems less efficient 
>>>that
>>>other potential designs. But I can't even see how to write a query that
>>>shows a row for each week using this design.
>>>
>>>Any input appreciated.
>>>
>>>Jonathan
>

Many thanks. It looks like it'll take me a while to go through that and 
determine how well it applies to my project, but it looks good.

Thanks again.

Jonathan

"Tom Cooper"  wrote in message 
news:%23rSeGLO$IHA.3380@TK2MSFTNGP04.phx.gbl...
> For calendar tables and their use, check out 
> http://www.aspfaq.com/show.asp?id=2519
>
> It even tells you how to show the dates where the count is zero, see the 
> section labelled "Getting all dates, even those not represented".
>
> Tom
>
> "Jonathan Wood"  wrote in message 
> news:uQKeWFO$IHA.3964@TK2MSFTNGP06.phx.gbl...
>> Roy,
>>
>>> Your query sounds like a simple GROUP BY using COUNT().
>>
>> I'm not sure I understand. Do you mean to group by the date, and then 
>> show the values returned by COUNT()?
>>
>> I'm trying to get my head around this. And, since the report will need to 
>> by kind of like a schedule, I need to also show those weeks where the 
>> count is 0.
>>
>>> I would tend to use a date in the Deliveries table, rather than a
>>> calendar week.  The date can always be converted to week on the fly.
>>> Using a calendar table would probably be the best approach.
>>
>> Yes, I can use a date instead of a week. Is there anything else you can 
>> share about your ideas for a "calendar table?"
>>
>> Thanks!
>>
>>> On Tue, 12 Aug 2008 13:54:11 -0600, "Jonathan Wood"
>>>  wrote:
>>>
>>>>Greetings,
>>>>
>>>>I'm wondering if anyone could provide some feedback on how best to 
>>>>design a
>>>>database I'm working on.
>>>>
>>>>I have any number of Projects, which exist for a specific date range.
>>>>
>>>>Each Project will have zero or more expected deliveries each week of the
>>>>project. So I need to track the expected deliveries, and the actual
>>>>deliveries for each of these weeks.
>>>>
>>>>The obvious way seems to be to create a Deliveries table, where each row 
>>>>is
>>>>associated with a Project and a calendar week. The problem with this is 
>>>>that
>>>>it seems kind of awkward to create the reports I need. I need to show a 
>>>>box
>>>>for each week in the Project, with the number of Deliveries scheduled 
>>>>and
>>>>delivered for each week. I know about COUNT, which seems less efficient 
>>>>that
>>>>other potential designs. But I can't even see how to write a query that
>>>>shows a row for each week using this design.
>>>>
>>>>Any input appreciated.
>>>>
>>>>Jonathan
>>
>
>

I would probably let the reporting engine do all of the grouping and 
counting for me. This sounds like a simple report that the ReportViewer can 
handle.

-- 
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



"Jonathan Wood"  wrote in message 
news:e0AB1UL$IHA.3696@TK2MSFTNGP06.phx.gbl...
> Greetings,
>
> I'm wondering if anyone could provide some feedback on how best to design 
> a database I'm working on.
>
> I have any number of Projects, which exist for a specific date range.
>
> Each Project will have zero or more expected deliveries each week of the 
> project. So I need to track the expected deliveries, and the actual 
> deliveries for each of these weeks.
>
> The obvious way seems to be to create a Deliveries table, where each row 
> is associated with a Project and a calendar week. The problem with this is 
> that it seems kind of awkward to create the reports I need. I need to show 
> a box for each week in the Project, with the number of Deliveries 
> scheduled and delivered for each week. I know about COUNT, which seems 
> less efficient that other potential designs. But I can't even see how to 
> write a query that shows a row for each week using this design.
>
> Any input appreciated.
>
> Jonathan
>

Well, I can search the web to try and figure out what ReportViewer is, but 
the problem is that if I need to show each week whether it has data or not. 
So it seems that requires more than simply different reporting.

Thanks.

-- 
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"William Vaughn (MVP)"  wrote in message 
news:7DA14097-7E8F-439A-A1EE-6391C527A68A@microsoft.com...
>I would probably let the reporting engine do all of the grouping and 
>counting for me. This sounds like a simple report that the ReportViewer can 
>handle.
>
> -- 
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205  (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
>
>
>
> "Jonathan Wood"  wrote in message 
> news:e0AB1UL$IHA.3696@TK2MSFTNGP06.phx.gbl...
>> Greetings,
>>
>> I'm wondering if anyone could provide some feedback on how best to design 
>> a database I'm working on.
>>
>> I have any number of Projects, which exist for a specific date range.
>>
>> Each Project will have zero or more expected deliveries each week of the 
>> project. So I need to track the expected deliveries, and the actual 
>> deliveries for each of these weeks.
>>
>> The obvious way seems to be to create a Deliveries table, where each row 
>> is associated with a Project and a calendar week. The problem with this 
>> is that it seems kind of awkward to create the reports I need. I need to 
>> show a box for each week in the Project, with the number of Deliveries 
>> scheduled and delivered for each week. I know about COUNT, which seems 
>> less efficient that other potential designs. But I can't even see how to 
>> write a query that shows a row for each week using this design.
>>
>> Any input appreciated.
>>
>> Jonathan
>>

The ReportViewer control is included in Visual Studio 2005 and later. It's 
designed to run Report Definition Language (RDL)-based reports sourced 
either from your application (on demand or automatically) or from SQL Server 
Reporting Services (on demand, subscribed or automatically). There are some 
wizards to help lay out the reports and some walkthroughs in the 
documentation. I devote an entire chapter to the control in my book and 
include a number of examples. The ReportViewer control can be built into a 
Browser-launched or Windows Forms application and programmed like any class. 
You can provide parameters to focus the data queries or customize the report 
programmatically. It deals with a number of simple to (very) complex 
grouping issues, cube issues as well as graphics, charting and aggregates 
(sums, counts, averages etc.) without you having to write hardly any code.

Yes, I think it can help. I'm doing a session on the RV control at most of 
the sessions I'm doing in Nashville (in two weeks) (DevLink), at the Ohio 
.NET User Groups (Findlay and Dayton) and at the Dev/SQL Connections 
conference in Las Vegas. See my website for details on my schedule.
-- 
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



"Jonathan Wood"  wrote in message 
news:eI$01cX$IHA.3472@TK2MSFTNGP05.phx.gbl...
> Well, I can search the web to try and figure out what ReportViewer is, but 
> the problem is that if I need to show each week whether it has data or 
> not. So it seems that requires more than simply different reporting.
>
> Thanks.
>
> -- 
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>
> "William Vaughn (MVP)"  wrote in message 
> news:7DA14097-7E8F-439A-A1EE-6391C527A68A@microsoft.com...
>>I would probably let the reporting engine do all of the grouping and 
>>counting for me. This sounds like a simple report that the ReportViewer 
>>can handle.
>>
>> -- 
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205  (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>>
>>
>>
>> "Jonathan Wood"  wrote in message 
>> news:e0AB1UL$IHA.3696@TK2MSFTNGP06.phx.gbl...
>>> Greetings,
>>>
>>> I'm wondering if anyone could provide some feedback on how best to 
>>> design a database I'm working on.
>>>
>>> I have any number of Projects, which exist for a specific date range.
>>>
>>> Each Project will have zero or more expected deliveries each week of the 
>>> project. So I need to track the expected deliveries, and the actual 
>>> deliveries for each of these weeks.
>>>
>>> The obvious way seems to be to create a Deliveries table, where each row 
>>> is associated with a Project and a calendar week. The problem with this 
>>> is that it seems kind of awkward to create the reports I need. I need to 
>>> show a box for each week in the Project, with the number of Deliveries 
>>> scheduled and delivered for each week. I know about COUNT, which seems 
>>> less efficient that other potential designs. But I can't even see how to 
>>> write a query that shows a row for each week using this design.
>>>
>>> Any input appreciated.
>>>
>>> Jonathan
>>>
>

I see MicrosoftReportViewer under Reporting in the Toolbox. I'll have a 
look.

I have your book, but it's so far unread. Unfortunately, I wouldn't be able 
to attend any of those locations mentioned.

Thanks!

Jonathan

> The ReportViewer control is included in Visual Studio 2005 and later. It's 
> designed to run Report Definition Language (RDL)-based reports sourced 
> either from your application (on demand or automatically) or from SQL 
> Server Reporting Services (on demand, subscribed or automatically). There 
> are some wizards to help lay out the reports and some walkthroughs in the 
> documentation. I devote an entire chapter to the control in my book and 
> include a number of examples. The ReportViewer control can be built into a 
> Browser-launched or Windows Forms application and programmed like any 
> class. You can provide parameters to focus the data queries or customize 
> the report programmatically. It deals with a number of simple to (very) 
> complex grouping issues, cube issues as well as graphics, charting and 
> aggregates (sums, counts, averages etc.) without you having to write 
> hardly any code.
>
> Yes, I think it can help. I'm doing a session on the RV control at most of 
> the sessions I'm doing in Nashville (in two weeks) (DevLink), at the Ohio 
> .NET User Groups (Findlay and Dayton) and at the Dev/SQL Connections 
> conference in Las Vegas. See my website for details on my schedule.
> -- 
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205  (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
>
>
>
> "Jonathan Wood"  wrote in message 
> news:eI$01cX$IHA.3472@TK2MSFTNGP05.phx.gbl...
>> Well, I can search the web to try and figure out what ReportViewer is, 
>> but the problem is that if I need to show each week whether it has data 
>> or not. So it seems that requires more than simply different reporting.
>>
>> Thanks.
>>
>> -- 
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>> "William Vaughn (MVP)"  wrote in message 
>> news:7DA14097-7E8F-439A-A1EE-6391C527A68A@microsoft.com...
>>>I would probably let the reporting engine do all of the grouping and 
>>>counting for me. This sounds like a simple report that the ReportViewer 
>>>can handle.
>>>
>>> -- 
>>> __________________________________________________________________________
>>> William R. Vaughn
>>> President and Founder Beta V Corporation
>>> Author, Mentor, Dad, Grandpa
>>> Microsoft MVP
>>> (425) 556-9205  (Pacific time)
>>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>>> ____________________________________________________________________________________________
>>>
>>>
>>>
>>> "Jonathan Wood"  wrote in message 
>>> news:e0AB1UL$IHA.3696@TK2MSFTNGP06.phx.gbl...
>>>> Greetings,
>>>>
>>>> I'm wondering if anyone could provide some feedback on how best to 
>>>> design a database I'm working on.
>>>>
>>>> I have any number of Projects, which exist for a specific date range.
>>>>
>>>> Each Project will have zero or more expected deliveries each week of 
>>>> the project. So I need to track the expected deliveries, and the actual 
>>>> deliveries for each of these weeks.
>>>>
>>>> The obvious way seems to be to create a Deliveries table, where each 
>>>> row is associated with a Project and a calendar week. The problem with 
>>>> this is that it seems kind of awkward to create the reports I need. I 
>>>> need to show a box for each week in the Project, with the number of 
>>>> Deliveries scheduled and delivered for each week. I know about COUNT, 
>>>> which seems less efficient that other potential designs. But I can't 
>>>> even see how to write a query that shows a row for each week using this 
>>>> design.
>>>>
>>>> Any input appreciated.
>>>>
>>>> Jonathan
>>>>
>>