query help 12-05-09

i am using sql server 2000 and i need to do the following.  sql server
2005 made stuff like this easy.

i have a table like this (pseudo coded)

CREATE TABLE CONTRACT (CONTRACT_ID INT, CONTRACT_NUMBER VARCHAR(10),
CONTRACT_REGION VARCHAR(10), STATUS VARCHAR(20), CONTRACT_DATE
DATETIME)

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (1, '1000', 'EAST', 'IN PROGRESS', '2007-01-01')

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (2, '2000', 'EAST', 'IN PROGRESS', '2007-01-01')

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (3, '3000', 'EAST', 'COMPLETE', '2007-01-01')

INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
STATUS, CONTRACT_DATE)
VALUES (3, '3000', 'EAST', 'IN PROGRESS', '2008-01-01')

I need to get a list of of of this data with a total count of
CONTRACT_ID by CONTRACT_NUMBER and CONTRACT_REGION

example output i'm looking for

CONTRACT_ID   CONTRACT_NUMBER CONTRACT_REGION   STATUS
TOTAL_CONTRACT_ID_COUNT
1                       1000
EAST                           IN PROGRESS     1
2                       2000
EAST                           IN PROGRESS     1
3                       3000
EAST                          COMPLETE           2
4                       3000
EAST                           IN PROGRESS     2


i can't seem to get the query right.  can anyone help me out?
0
Derek
12/5/2009 12:00:21 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
627 Views

Similar Articles

[PageSpeed] 14

I think what you want is

Select c.CONTRACT_ID, c.CONTRACT_NUMBER, c.CONTRACT_REGION, c.STATUS,
  (Select Count(*) From CONTRACT c2 Where c.CONTRACT_NUMBER = 
c2.CONTRACT_NUMBER And c.CONTRACT_NUMBER = c2.CONTRACT_NUMBER) As 
CONTRACT_ID_COUNT
From CONTRACT c;

Tom

"Derek" <gepetto_2000@yahoo.com> wrote in message 
news:71031f81-76d7-430e-a46f-ba4f2aa45ab7@m16g2000yqc.googlegroups.com...
>i am using sql server 2000 and i need to do the following.  sql server
> 2005 made stuff like this easy.
>
> i have a table like this (pseudo coded)
>
> CREATE TABLE CONTRACT (CONTRACT_ID INT, CONTRACT_NUMBER VARCHAR(10),
> CONTRACT_REGION VARCHAR(10), STATUS VARCHAR(20), CONTRACT_DATE
> DATETIME)
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (1, '1000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (2, '2000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (3, '3000', 'EAST', 'COMPLETE', '2007-01-01')
>
> INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER,  CONTRACT_REGION,
> STATUS, CONTRACT_DATE)
> VALUES (3, '3000', 'EAST', 'IN PROGRESS', '2008-01-01')
>
> I need to get a list of of of this data with a total count of
> CONTRACT_ID by CONTRACT_NUMBER and CONTRACT_REGION
>
> example output i'm looking for
>
> CONTRACT_ID   CONTRACT_NUMBER CONTRACT_REGION   STATUS
> TOTAL_CONTRACT_ID_COUNT
> 1                       1000
> EAST                           IN PROGRESS     1
> 2                       2000
> EAST                           IN PROGRESS     1
> 3                       3000
> EAST                          COMPLETE           2
> 4                       3000
> EAST                           IN PROGRESS     2
>
>
> i can't seem to get the query right.  can anyone help me out? 

0
Tom
12/5/2009 12:20:51 AM
thanks!!!!!

On Dec 4, 7:20=A0pm, "Tom Cooper" <tomcoo...@comcast.net> wrote:
> I think what you want is
>
> Select c.CONTRACT_ID, c.CONTRACT_NUMBER, c.CONTRACT_REGION, c.STATUS,
> =A0 (Select Count(*) From CONTRACT c2 Where c.CONTRACT_NUMBER =3D
> c2.CONTRACT_NUMBER And c.CONTRACT_NUMBER =3D c2.CONTRACT_NUMBER) As
> CONTRACT_ID_COUNT
> From CONTRACT c;
>
> Tom
>
> "Derek" <gepetto_2...@yahoo.com> wrote in message
>
> news:71031f81-76d7-430e-a46f-ba4f2aa45ab7@m16g2000yqc.googlegroups.com...
>
> >i am using sql server 2000 and i need to do the following. =A0sql server
> > 2005 made stuff like this easy.
>
> > i have a table like this (pseudo coded)
>
> > CREATE TABLE CONTRACT (CONTRACT_ID INT, CONTRACT_NUMBER VARCHAR(10),
> > CONTRACT_REGION VARCHAR(10), STATUS VARCHAR(20), CONTRACT_DATE
> > DATETIME)
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (1, '1000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, =A0CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (2, '2000', 'EAST', 'IN PROGRESS', '2007-01-01')
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, =A0CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (3, '3000', 'EAST', 'COMPLETE', '2007-01-01')
>
> > INSERT INTO CONTRACT (CONTRACT_ID, CONTRACT_NUMBER, =A0CONTRACT_REGION,
> > STATUS, CONTRACT_DATE)
> > VALUES (3, '3000', 'EAST', 'IN PROGRESS', '2008-01-01')
>
> > I need to get a list of of of this data with a total count of
> > CONTRACT_ID by CONTRACT_NUMBER and CONTRACT_REGION
>
> > example output i'm looking for
>
> > CONTRACT_ID =A0 CONTRACT_NUMBER CONTRACT_REGION =A0 STATUS
> > TOTAL_CONTRACT_ID_COUNT
> > 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 IN PROGRESS =
=A0 =A0 1
> > 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 2000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 IN PROGRESS =
=A0 =A0 1
> > 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0COMPLETE =A0 =
=A0 =A0 =A0 =A0 2
> > 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3000
> > EAST =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 IN PROGRESS =
=A0 =A0 2
>
> > i can't seem to get the query right. =A0can anyone help me out?

0
Derek
12/5/2009 12:46:42 AM
Reply:

Similar Artilces:

Wiener Access Stammtisch (Nr. 75): Einladung f�r den 12.01.2010 ("Pizzeria Fantastica" Kagraner Platz)
Wiener Access Stammtisch http://access.primary.at/ access.st.wien@gmx.at Liebe Accessler, ich m�chte Euch hiermit zum 75. Wiener Access Stammtisch einladen. Termin: Dienstag, 12.01.2010, ab 19:00 Ort: Lokal "Fantastica, Pizzeria", Wagramer Stra�e 154, A 1220 Wien ------------------------------------------ Fantastica, Pizzeria http://www.fantastica.at/ pizza22@fantastica.at pizzeria@fantastica.at 1220 - Wagramer Stra�e 154 Restaurant, Italienische K�che Telefon: 203 75 39, Fax: 209 15 21 �ffnungszeiten t�glich 09.00 - 23.00 Gastgarte...

Help with MSN Synchronization-Money 2007
Hello, I have been using money for several years and have just recently upgraded to money 2007. Anyhow, I've noticed since I started using it my information has not updated on MSN money the web site (account transactions,balances, etc.) It also does not show my balance on my checking account (it says N/A) and it used to show it before. Is there some setting that has gotten screwed up somehow? Has anyone else had this problem? Does anyone know if any fixes? I've done everything the knowledge base article said-no results. Any help is appreciated. Thanks, Dan Money comes wit...

sort macro 02-05-10
Hello, I would like to write a macro to sort data from a list which is getting longer and longer. How to tell the macro to go to the last non empty cell? Thanks in advance, Hi, You are always better posting your code. The line below sets a range of the used cells in column A Set myrange = Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row) If you wanted to start in (say) A4 then you would change it to this Set myrange = Range("A4:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row) -- Mike When competing hypotheses ...

Pie Chart #12
I have created multiple pie charts that are not displaying correct percentages. For instance I key in 100 in one cell and 50 in the next cell. I create the graph and ask for percentage, not value. The percentage displays as 67% and 33%. Hi, It is correct is you are not displaying any precision. 100/(100+50) = 0.66666 = 67% 50/(100+50) = 0.33333 = 33% What values did you expect to see? Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "hr" <hr@discussions.microsoft.com> wrote in message news:36452498-8C81-4452-8DB5-32E6B56738C7@microsoft.co...

Need help with Serialization
I'm about beaten down with this stuff. When the big program's compilation failed because of these two errors, I created as simple an MFC SDI project as I could and still got them error C2678: binary '<<' : no operator defined which takes a left-hand operand of type 'class CArchive' (or there is no acceptable conversion) .... ditto C2679 for >> It occurs to me that I might not be #include-ing the right header files but no example I've found anywhere shows anything I haven't done. I'm stuck and would really appreciate some answers, hints,...

Need help grouping charts
I have identical charts which occur on multiple sheets. Is there any wa to group them for editing? When I group the sheets themselves, it seems to deactivate the charts I tried doing it with a macro as an alternative and then flipping th sheets one by one and keying the macro, but I get a message box with: "Run-time error 1004", "Unable to get the ChartObjects property of th worksheet class". Does anyone have any ideas -- Izz ----------------------------------------------------------------------- Izzy's Profile: http://www.excelforum.com/member.php?action=getin...

Onchange Event 06-09-04
I want to send an Email to a user whenever a drop down change from item 1 to any other item. Can any body give me some sample codes? Thank you very much! May May, This is something best suited for a Callout or by some extensive workfow configuration. There is a post in the group from the other week about how to configure workflow to check for a field value changing, but it is a little complicated. Matt Parks ---------------------------------------- ---------------------------------------- On Wed, 9 Jun 2004 07:41:50 -0700, "May Fan" <may.fan@metrixtechnologies.com> wro...

help with SQL query for HQ
Hello! Need two queries. 1. to clear the bin location field for all items 2. to assign a numberical value for the bin location field for each of our stores. ex. store 1 bin location field to read 1, store store bin location field to read 2 any help would as always be greatly appreciated... thank you hi zcsf, 1. To Clear bin location Run this UPDATE ITEM SET BINLOCATION='' 2. To Update the bin location with store id UPDATE ITEM SET BINLOCATION=storeid from Configuration Rate please. "ZCSF" wrote: > Hello! > Need two queries. > 1. to clear the bin location field ...

Help, with formatting issue?
Under Outlook in tool/options / mail format, if I have it set to plain text and I create new mail, I do not have the ability to select formatting options. Try it, it shows up under customize ok, as soon as you select ok under customize, it blanks out. If I choose to use Microsoft Outlook Rich Text, I can't send some attachments to Outlook express users. Any one able to confirm this? Its driving me nuts.... -- Greg Eshleman ETEMCO 1370 Arcadia Road Lancaster, PA. 17601 717-393-9653 http://www.etemco.net geshlema@etemco.net What about using HTML format instead? It's open st...

Need help w/stock status report
We are on 9.0 using manufacturing with all items set up as average cost. The canned stock status reports no longer work to tie to GL in 9.0/10.0 so I am wriitng my own in either Smartlist Builder with a SQL view or Crystal Reports. I just want to get close in tying GL to inventory value. I am thinking on the IV10200, with this (qty received * Unit Cost) - (qty sold * adjusted cost) for each layer. Then the sum of each layer "should" be close to GL. Am I on the right track? Microsoft used to report that using the Average Costing valuation method is never going to tie to the ...

I need help in writing/modifying an EXCEL macro
I'm running EXCEL 2002. I need help in writing/modifying an EXCEL macro. If anyone can point me to a resource for sample code or examples to help with the following I would appreciate it. Every month I download a report from a state agency that I need to reformat, create charts from and send reminder emails based on some of the data. The report is downloaded as an EXCEL workbook containing a single worksheet. The worksheet is preformatted as a printed report. I have no control of this formatting and I can not get the data as a .csv file. The report contains 3 sections. See detailed...

Pics in Pub 2000 docs are distorted when view in Pub 2003? Help?
Some pictures that are on pages in my Publisher 2000 documents are showing up distorted when I open the document/file in Publisher 2003. Any ideas? These are embedded pics. Thanks. WordArt? WordArt objects in a Publisher 2000 publication may appear distorted when opened in Publisher 2003 http://support.microsoft.com/default.aspx?scid=kb;en-us;826358 Can you adjust the images? Or re-insert? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "CLC" <CLC@discussions.microsoft.com> wrote in message news:9CCE5A98-E8B3-4F...

help creating dialogue windows
Hi! I just started to learn programming in Excel and I really excited abou it. I have this project that I want to do. At the moment at my company we have a minor technical test we do wit new recruits, most of the times this test is done by phone. The sheet where the answeres are entered is a spreadsheet. I.e there a question in cell A4 and the answer is to be typed in B4. This takes some time, navigating the spreadsheet manually and this ca lead to mistakes. What I want to do is create a dialouge (is it called that?) box fo every question. what I mean is a window that pop ups, with th questi...

Money 2003: help - how can I add trended items back into the cashflow analysis
I deleted some trended items from my cashflow analysis by accident on mny 2003, Is there any way of getting them back in there??? thanks in advance. Click on the 'customise cash flow' option which is either on the left hand side of the cash flow display, or right clicking on the chart. To put it back, you need to select an option in there. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or...

Hotmail Problem Please Help
I am running Exchange 2003 Standard on a Windows 2003 Server. I also have ISA 2000 on the same box. Everything is working fine. The problem I am having is getting mail out to a hotmail address. When I send then mail, I see it sits in the Queue for less then a minute then disappears. It never makes it to the Hotmail address nor do I get any return errors back in my client indicating the mail never made it to its destination. On Sun, 13 Nov 2005 15:51:04 -0800, "Kevin Mc" <Kevin Mc@discussions.microsoft.com> wrote: >I am running Exchange 2003 Standard on a Windows 200...

Exchange Recovery Help
I have what used to be a SBS 2003 Server that died a horrid death due to a corrupt registry with NO backup. UGH. I have done a parallel install to get file system access and can get to all the files. If I backup the MDBDATA folder can I recover everything? Is there some other files I need to backup while I have file access to make my life easier? Any help would be appreciated because I am diving into this tonight. Thanks -- Shane Young SharePoint911 - "Your SharePoint Help Desk" Hi shane, It depends. Backup your EDB, STM and all the log files. Then re-install your...

Invest 3325/-. & get 2,04,750/- in 12 periods without working. or 32,00,000/- with some working
Hi I made my choice when faced this opportunity to lock my position. The compnay is going on mass way to sell Bajaj Allianz New unit gain policy . How you and me can benefit by taking policy earlier. We can enter into pool to earn 2,04,750/- without working. IF we introduce 8 members we can earn 32,00,000/- (not necessary 8 members has to work) This is called grwoth plan.. & many companies are successfully running this concpet. see the testimonals for proof. Investment is 3325/- & we get policy when 12,000/- gets deposited in your account. 9000./- gets deduced from your earning....

Access Help!!
Hi I have a small query with regards to my database, basically whatI've got is a form which shows item number and cost. The cost changeson a yearly basis e.g. for year 2006 Item 1 was priced at =A32.00however for 2007 Item 1 is now priced at =A33.00. What I am after iswhen the user clicks on the following year i.e. from 2006 to 2007 Iwant the cost column for 2006 to be shown on the form as well as thecost for the current year (2007) and then when the users clicks onthefollowing year the column for 2007 cost is shown and column for 2008etc so only two columns are shown one for previous year...

Help
When I create a task, is there a way for this task to automatically show up on the calendar? I can drag and drop the task on the calendar, but I'd like it to show up automatically. Thanks. No. Tasks and appointments/meetings/events are entirely different beasts. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Tom_0127 asked: | When I create a task, is there a way for this task to a...

drop down menus #12
I am a new Excel user who has basically learned everyething I know by practicing and making MANY mistakes, but my question is: I use ddata validation to make drop down menus for particualar cells that I need the data in and I seem to be limited as to the amount of data I can use in the drop down menu. Is there any way I can make a drop down menu for cells that can accomodate "unlimited data"? These are basic speadsheets I am making but I need alot of selectons in a few of the drop down menus. If I gave my users a dropdown with hundreds of listed items, they'd come at me w...

Help #28
I am trying to create a daily,MTD, YTD production report. and we have some days schedule outages in April and Oct. and these are not fixed days. What i want ,on these schedule outages the daily budget should show 'O' and MTD and YTD shoud not count these schedule outage days. How can i acomplish this?. Any help will be appriciated. Thanks In advance For Example DAILY MONTH TO DATE YEAR TO DATE BUDGET ACTUAL BUDGET ACTUAL BUDGET ACTUAL 3,828.00 3,824.15 7,656.00 7,647.41 7,656.00 7,647.41 ...

Activation Button and Key... PLZ NEED HELP ASAP
I got a 60 day free trial of Microsoft office and student 2007 addician and everything went great until after I Copied the Key to put in the activation Part to Paste the key (like the instructions told me to). The problem is that when I click the activation key it does nothing. When I right click and force it to open, it comes up website not found, but yet my internet is working fine. Another tip said to turn off your firewall and then it will open. Have any sugestions? Chelsea27 wrote: > I got a 60 day free trial of Microsoft office and student 2007 addician and >eve...

Newbie needs help with 'multiple XML records'
I am not even sure I have used valid words in my subject line. I have an object that I can serialize as XML and then deserialize it. I want to 'export' a static copy of the object and then 'import' it into another program on another system. My idea is to serialize it, transport the serialized output to the other system and then deserialize it into the 2 nd program. Here comes the hard part. I need to do two things. 1) - Persist the imported object (again serialize/deserialize seems reasonable) 2) - Keep multi instances of this type of object. I am only talking about a max of...

Q: open a query with parameters
All, I think I took a stupid pill today because I can't figure this one out. I want to programmatically open up a query like when you use the docmd.openquery("query name") function. But the query I'm using has parameters which need to be filled in. I vaguely remember something about querydef objects, but I can't seem to get it to work. Can someone point me in the right direction? Thanks so much! -Mark ...

Last transaction in a query?
I have a table with 5 fields ID PersonID EDate Amount and TransType I want a query with the last record of each person based on date and I want the results like this PersonID LastOfEDate Amount TransType if a person has tow transactions in the max date I want only one of them How can i do that? thanks You must have a "main" table with a single list of PersonID. Lets assume it is called tblPeople, PK = "ID" So, use that table to provide a unquite list of PersonID. The query can be like: select FirstName, Lastname, WorkPhone, PersonID, EDate, Amount, Tr...