Assistance developing Query

[Bill]

Good Day;

I would appreciate assistance developing a query that I haven't been
able to develop without using a second table. I wish to count the
number of records that are still open on the first of each month.
Each record has an open date and a close date or the close date is
null i.e., the record is not yet closed. I've previously beaten this
by building a table, simply a list of the dates for the first of each
month for the next ten years or so, and then selecting values based
upon a date selected from that table. However I'd be happier if I
could do it without the second table. I'd be prepared to accept the
Min(Date) for each month as being the first of the month.

I've included some DDL statements to build and populate the table if
that helps. Since the selection is rather small and all the open
dates are very close together I think the result will be simply a
decreasing count from the month the first record is opened till today.

A pseudo code select statement might look like

Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
FROM DetailT
Where DateReceived > DateOfInterest or DateReceived is Null and
DateOpened < DateOfInterest
Group by Min(DateOpened)
Order by Min(DateOpened)

I hope I've explained it sufficiently well.

CREATE TABLE [dbo].[DetailT] (
[Autonum] [int] IDENTITY (1, 1) NOT NULL ,
[QDNumber] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[DateOpened] [smalldatetime] NOT NULL ,
[DateReceived] [smalldatetime] NULL ,

)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C15788' , '06/04/2005 9:35', 07/04/2005)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B16091' , '06/04/2005 9:36', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B15001' , '06/04/2005 9:51', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('M18696' , '06/04/2005 9:56', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C14969' , '06/04/2005 10:05', '10/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('O10091' , '06/04/2005 10:08', '12/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('D01197' , '06/04/2005 10:13')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('H15001' , '06/04/2005 10:15', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('J15090' , '06/04/2005 10:24', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('J01202' , '06/04/2005 10:31')
Insert into DetailT (QDNumber, DateOpened)
VALUES('G01193' , '06/04/2005 10:32')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01164' , '06/04/2005 10:35')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01162' , '06/04/2005 10:48')
Insert into DetailT (QDNumber, DateOpened)
VALUES('F01124' , '06/04/2005 10:59')
Insert into DetailT (QDNumber, DateOpened)
VALUES('H01147' , '06/04/2005 11:01')
Insert into DetailT (QDNumber, DateOpened)
VALUES('S15068' , '06/04/2005 11:10')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('E12322' , '06/04/2005 11:32', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('A12205' , '06/04/2005 11:37', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('D12259' , '06/04/2005 11:40', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('C03394' , '06/04/2005 11:51')

If you made it this far thank you for your patience. Any help would be
appreciated.

Thank you.

Bill

[DA Morgan]

Re: Assistance developing Query

Bill wrote:
[color=blue]
> Good Day;
>
> I would appreciate assistance developing a query that I haven't been
> able to develop without using a second table. I wish to count the
> number of records that are still open on the first of each month.
> Each record has an open date and a close date or the close date is
> null i.e., the record is not yet closed. I've previously beaten this
> by building a table, simply a list of the dates for the first of each
> month for the next ten years or so, and then selecting values based
> upon a date selected from that table. However I'd be happier if I
> could do it without the second table. I'd be prepared to accept the
> Min(Date) for each month as being the first of the month.
>
> I've included some DDL statements to build and populate the table if
> that helps. Since the selection is rather small and all the open
> dates are very close together I think the result will be simply a
> decreasing count from the month the first record is opened till today.
>
> A pseudo code select statement might look like
>
> Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
> FROM DetailT
> Where DateReceived > DateOfInterest or DateReceived is Null and
> DateOpened < DateOfInterest
> Group by Min(DateOpened)
> Order by Min(DateOpened)
>
> I hope I've explained it sufficiently well.
>
> CREATE TABLE [dbo].[DetailT] (
> [Autonum] [int] IDENTITY (1, 1) NOT NULL ,
> [QDNumber] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [DateOpened] [smalldatetime] NOT NULL ,
> [DateReceived] [smalldatetime] NULL ,
>
> )
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C15788' , '06/04/2005 9:35', 07/04/2005)
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B16091' , '06/04/2005 9:36', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B15001' , '06/04/2005 9:51', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('M18696' , '06/04/2005 9:56', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C14969' , '06/04/2005 10:05', '10/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('O10091' , '06/04/2005 10:08', '12/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('D01197' , '06/04/2005 10:13')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('H15001' , '06/04/2005 10:15', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('J15090' , '06/04/2005 10:24', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('J01202' , '06/04/2005 10:31')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('G01193' , '06/04/2005 10:32')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01164' , '06/04/2005 10:35')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01162' , '06/04/2005 10:48')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('F01124' , '06/04/2005 10:59')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('H01147' , '06/04/2005 11:01')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('S15068' , '06/04/2005 11:10')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('E12322' , '06/04/2005 11:32', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('A12205' , '06/04/2005 11:37', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('D12259' , '06/04/2005 11:40', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('C03394' , '06/04/2005 11:51')
>
> If you made it this far thank you for your patience. Any help would be
> appreciated.
>
> Thank you.
>
> Bill[/color]

Where the SQL statement you would use to load the table?
Put parentheses around it.
Go from there.
There may be far more elegant solutions but your the one getting
the paycheck. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@x.wash ington.edu
(replace 'x' with 'u' to respond)

[Hugo Kornelis]

Re: Assistance developing Query

On 18 Apr 2005 16:36:38 -0700, Bill wrote:
[color=blue]
>I would appreciate assistance developing a query that I haven't been
>able to develop without using a second table.[/color]
(snip)[color=blue]
>I've previously beaten this
>by building a table, simply a list of the dates for the first of each
>month for the next ten years or so, and then selecting values based
>upon a date selected from that table. However I'd be happier if I
>could do it without the second table.[/color]

Hi Bill,

Why do you want to do it wothout a second table? Having a permanent
auxiliary calendar table in your database is actually quite useful and I
think that no database should ever be without one.
Here's a link to an article that shows how to create a general
all-purpose calendar table, how to fill it with data and several
examples of how to use it: http://www.aspfaq.com/show.asp?id=2519.

And here's how I'd write your query, using the table described above:

SELECT c.dt,
COUNT(*) as "Qty Still Open"
FROM Calendar AS c
INNER JOIN DetailT
ON ( DateReceived > c.dt OR DateReceived IS NULL )
AND DateOpened < c.dt
WHERE c.D = 1
AND c.dt BETWEEN (SELECT MIN(DateOpened)
FROM DetailT)
AND DATEADD(month, 1, (SELECT MAX(DateReceive d)
FROM DetailT))
GROUP BY c.dt
ORDER BY c.dt

This one cuts off the listing at the lowest number. If you increase the
number (1) in the DATEADD expression, you can see that the number of
open cases remains constant after the last month listed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Bill]

Re: Assistance developing Query

DA Morgan <damorgan@x.was hington.edu> wrote in message news:<111389173 0.819997@yasure >...[color=blue]
> Bill wrote:
>[color=green]
> > Good Day;
> >
> > I would appreciate assistance developing a query that I haven't been
> > able to develop without using a second table.[/color][/color]
[color=blue][color=green]
> > Thank you.
> >
> > Bill[/color]
>
> Where the SQL statement you would use to load the table?
> Put parentheses around it.
> Go from there.
> There may be far more elegant solutions but your the one getting
> the paycheck. ;-)[/color]

Dan;

I'm sorry but I don't understand what you're trying to tell me. If
your first question is "Where is the SQL statement you would use to
load the table?" I don't have one, the data base is interactively
updated through an ASP based HTML form over the web. I did go to the
work of providing insert statements to assist anyone who might try to
help.

I don't understand what putting parentheses around it would do?

I hope there is a more elegant solution but unfortuantely I haven't
figured it out and was simply asking for assistance. If you don't
wish to help thats ok with me. Yes, I'm getting paid for the work I
do, but developing this solution is not what I was trained or educated
to do an so I'm learning as I go. I appologize if I've bothered you
by asking for help.

I will continue to explore the solution.

Cheers;

Bill

[Bill]

Re: Assistance developing Query

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<8oqa61p4r gmhtcfh7rgsc8du p9pj1a9d1t@4ax. com>...[color=blue]
> On 18 Apr 2005 16:36:38 -0700, Bill wrote:
>[color=green]
> >I would appreciate assistance developing a query that I haven't been
> >able to develop without using a second table.[/color]
> (snip)[color=green]
> >I've previously beaten this
> >by building a table, simply a list of the dates for the first of each
> >month for the next ten years or so, and then selecting values based
> >upon a date selected from that table. However I'd be happier if I
> >could do it without the second table.[/color]
>
> Hi Bill,
>
> Why do you want to do it wothout a second table? Having a permanent
> auxiliary calendar table in your database is actually quite useful and I
> think that no database should ever be without one.[/color]
....[color=blue]
> This one cuts off the listing at the lowest number. If you increase the
> number (1) in the DATEADD expression, you can see that the number of
> open cases remains constant after the last month listed.
>
> Best, Hugo[/color]

Hugo;

Thank you for your assistance. I've reviewed the article you pointed
me to and have spent my spare time today building a number table and a
date table. I was reluctant to duplicate my earlier date table, which
only contained the dates for the first of the month into the SQL
Server environment since I felt that it was both cheating and
confusing. However the article showed that this can be a very useful
table and since it's been published I don't feel too bad about
emulating someone's work who know more about application development
than I do.

I do have one question, based mainly on my lack of formal training in
SQL Server and my experience this morning building the calendar table.
I was copying the code from the article and pasting it into SQL Query
Analyzer and running it, as I'm not certain where or how this code
should be executed. Most things ran very quickly after I modified
them properly to meet my environment. However the adding of row's to
the Calendar table (4,096) took several hours (2-3) I was really
surprised by this and wondered if I was doing something wrong but
since it finished successfully and subsequent code samples executed
quickly I moved on. My question is am I using the right part of the
SQL Server environment for this sort of work?

Thank you once again for your time and your assistance. Now that I've
overcome my reluctance to using the calendar table I'm comfortable
enough to go on and using you sample query to get what I was looking
for.

Thank you.

Cheers;

Bill

[Hugo Kornelis]

Re: Assistance developing Query

On 20 Apr 2005 16:06:36 -0700, Bill wrote:

(snip)[color=blue]
>the article showed that this can be a very useful
>table and since it's been published I don't feel too bad about
>emulating someone's work who know more about application development
>than I do.[/color]

Hi Bill,

You certainly should not feel bad about it - sharing the code is exactly
the reason why Aaron has published it on his site.

Copying work from others may be a sin in artistic creative work, but in
software development, it's a sin NOT to copy and adapt proven solutions.


(snip)[color=blue]
>Most things ran very quickly after I modified
>them properly to meet my environment. However the adding of row's to
>the Calendar table (4,096) took several hours (2-3) I was really
>surprised by this[/color]

And so am I. Okay, the server does have a bit of work to do when
populating the table, but under normal circumstances, I would not expect
it to run for so long! A couple of minutes, maybe. Not hours.

I'm not sure if you still care to investigate this (since you now have
the table, and it's a one-time job after all), but if you do, then could
you please post the exact code you used to create and popultae the
tables?
[color=blue]
>My question is am I using the right part of the
>SQL Server environment for this sort of work?[/color]

Yes, Query Analyzer is exactly the tool to use for these jobs.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)