select help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select help needed

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-22 : 11:41:06

I have built this sql that gave me all the records I need except One field, the name of the person who placed the order. The business requirement is to get a contact list of all the customers who have placed an order in the given time period. But for those with many orders, only the last one is needed.

SELECT act.account_number, Count(jt.CO_Number) AS NumofOrder,
Max(jt.DateEntered) AS LastOrdDate
FROM account act JOIN JobTracking jt ON act.account_number = jt.AccountNumber
WHERE jt.DateEntered>'7/1/1999'
GROUP BY act.account_number

However, when I added the contact name from the order header table in following syntax, I could not get it to return only the order info when there are many orders/many contact names associated with a given account. Above qry returns 27,000 rows, which is the right number, but this one gives me 90,000.

SELECT act.account_number, Count(jt.CO_Number) AS NumofOrder,
Max(jt.DateEntered) AS LastOrdDate, oh.head_contact_name,
FROM account act JOIN stsJobTracking jt ON act.account_number = jt.AccountNumber)
join order_header oh on oh.head_order_nbr = jt.co_number
WHERE jt.DateEntered>'7/1/1999' Left(act.account_name,1)<>'*' and
GROUP BY account_number, oh.head_contact_name

I have tried different kind of join, adding additional where, or using sub select, but none of them work.

Thanks for your help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-22 : 11:44:01

what's the PK of the JobTracking table? that is important to ensure we can join to it, to return just the latest order per account number.

this would be much easier if you gave us your table structure and some sample data to work with.

- Jeff

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-22 : 11:56:27

CO_Number is the PK.
Here is the script for it.

CREATE TABLE [dbo].[stsJobTracking] (
[CO_Number] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AccountNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ln_No] [int] NULL ,
[Item] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dataset_id] [int] NULL ,
[ProductFamily] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qty] [int] NULL ,
[DateEntered] [datetime] NULL ,
[ProofDt] [datetime] NULL ,
[ShipDt] [datetime] NULL ,
[ShipMethod] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DieNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoUp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GangNo] [int] NULL ,
[InGang] [smallint] NOT NULL ,
[Material] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SecondaryMat] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarketSegment] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ndim1] [float] NULL ,
[ndim2] [float] NULL ,
[Copy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Proof] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CutMethod] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrevJob] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ArtCode] [smallint] NULL ,
[Colors] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumColors] [int] NULL ,
[NumberingSize] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumberingSeqn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumberingColor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rush] [smallint] NOT NULL ,
[IsProSample] [smallint] NOT NULL ,
[Numbering] [smallint] NOT NULL ,
[InHouseNum] [smallint] NOT NULL ,
[Remake] [smallint] NOT NULL ,
[SpecPack] [smallint] NOT NULL ,
[PrintProcut] [smallint] NOT NULL ,
[Procut] [smallint] NOT NULL ,
[Doming] [smallint] NOT NULL ,
[MousePad] [smallint] NOT NULL ,
[FourColorProcess] [smallint] NOT NULL ,
[ProofReqd] [smallint] NOT NULL ,
[Released] [smallint] NOT NULL ,
[CreditCard] [smallint] NOT NULL ,
[ReturnDisk] [smallint] NOT NULL ,
[ReturnArt] [smallint] NOT NULL ,
[SendSamples] [smallint] NOT NULL ,
[xMatlPicked] [smallint] NOT NULL ,
[PctOfGang] [decimal](6, 3) NULL ,
[NumOfSheets] [decimal](10, 2) NULL ,
[NumOfShpmnts] [smallint] NULL ,
[NumOfPkg] [smallint] NULL ,
[TotMfgQty] [int] NULL ,
[TotShipQty] [int] NULL ,
[TotBillQty] [int] NULL ,
[CurrentLoc] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentLocDate] [datetime] NULL ,
[CurrentLocUser] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastActivity] [datetime] NULL ,
[St] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateShipped] [datetime] NULL ,
[ArtClearDate] [datetime] NULL ,
[PrePressClearDate] [datetime] NULL ,
[PressClearDate] [datetime] NULL ,
[SchedulingNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuoteNumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelatedOrders] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IVC_TOTAL] [decimal](15, 5) NULL ,
[LastShippingChange] [datetime] NULL ,
[LastShipAck] [datetime] NULL ,
[Flexo] [smallint] NOT NULL ,
[CellGroup] [int] NULL ,
[JobTotal] [decimal](15, 5) NULL ,
[SupvCheck] [smallint] NULL ,
[UserEnteredPrice] [smallint] NULL ,
[Hot] [smallint] NULL ,
[CatalogProdFam] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [smallint] NULL ,
[WorkDays] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And here is a sample set of data. For any given acct#/address#, if there are many orders, only the last Order rows are needed.

acct#/#ofOrd/LastOrdDate/address#/contact_name/address
000002 1 2000-08-04 2 BILL 111 SOUTH CHERRY
000002 2 2003-09-26 2 NOLAND 111 SOUTH CHERRY
000009 1 2003-08-04 34 MICHAEL 4738 WURTZ RD.
000009 2 2002-09-23 34 Sales 34738 WURTZ RD.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-11-22 : 13:22:30

I got it. I have to make the result of the first qry into a temp table, then join it with my order header table both on the PK and the date field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources