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

Query Help?

Author

Topic

anne
Starting Member

4 Posts

Posted - 2005-01-07 : 15:57:20

I'm trying to extract records from a DB of over 70000 records, by project, by Priority and date. I'm getting every record in the project specified repeated every time the record status changes. I've tried changing the joins to right outer with no success....any assistance would be greatly appreciated.

-----------------------------------------------------------------

Select p.projectname as 'Project', o.origintypename AS 'Origin', t.bugtypename as 'Type',
ps.progressstatusname as 'Status', b.bugid as 'Bug ID', b.datecreated, b.bugtitle, cf.desc_custom_2 AS 'Customer Impact',
cf.status_custom_1 AS 'Priority', cf.custom_203 AS 'Build Number', cf.custom_309 AS 'Failed in Build',
cf.custom_303 AS 'Number of times failed', cf.custom_302 AS 'Passed in Build', cf.custom_304 AS 'Date Passed',
cf.status_custom_2 AS 'Patch'
from bug b
inner join origintypes o
on o.origintypeid = b.crntorigintypeid
inner join project p
on p.projectid = b.projectid
inner join bugtypes t
on b.crntbugtypeid = t.bugtypeid
inner join progressstatustypes ps
on ps.progressstatusid = b.progressstatusid
inner join customerfieldtrackext cf
on cf.bugid = b.bugid
Where b.projectid = 29
and b.ifclosed = 0
And b.datecreated > '2003-12-31'

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-07 : 16:32:42

Let's back up a bit... can you post DDL and sample data, along with sample output? I have no idea what you're trying to accomplish based only on looking at this query...

http://www.aspfaq.com/etiquette.asp?id=5006

anne
Starting Member

4 Posts

Posted - 2005-01-10 : 08:46:52

OK, here's the DDL

CREATE TABLE [Bug] (
[ProjectID] [int] NOT NULL ,
[BugID] [int] NOT NULL ,
[ProblemID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BugTitle] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedTypeID] [int] NULL ,
[PrevBugIDIfReopen] [int] NULL ,
[CreatedByPerson] [int] NULL ,
[DateCreated] [datetime] NULL ,
[CurrentOwner] [int] NULL ,
[ProgressStatusID] [int] NULL ,
[AssignedByPersonID] [int] NULL ,
[DateAssigned] [datetime] NULL ,
[ClosedByPerson] [int] NULL ,
[CloseStatusID] [int] NULL ,
[DateClosed] [datetime] NULL ,
[CloseDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CrntPriorityID] [int] NULL ,
[ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsActive] [smallint] NULL ,
[IfClosed] [smallint] NULL ,
[CrntOriginTypeID] [int] NULL ,
[CrntBugTypeID] [int] NULL ,
[NoOfHistories] [int] NULL ,
[LockOwner] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CrntPlatformID] [int] NULL ,
[CrntVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CrntVersionID] [int] NULL ,
[TargetReleaseID] [int] NULL ,
[TargetDate] [datetime] NULL ,
[EstimatedHours] [int] NULL ,
[ActualHours] [int] NULL ,
[DateFixed] [datetime] NULL ,
[CrntForwardTypeID] [int] NULL ,
[IsModified] [bit] NOT NULL CONSTRAINT [DF__Bug__IsModified__2D27B809] DEFAULT (0),
[IsSharedIssue] [bit] NOT NULL CONSTRAINT [DF__Bug__IsSharedIss__2E1BDC42] DEFAULT (0),
[CrntGroupID] [int] NULL ,
[PlanedStartDate] [datetime] NULL ,
[PlanedEndDate] [datetime] NULL ,
[CrntFolderID] [int] NULL ,
[LastUpdateNo] [int] NULL ,
[CustomerID] [int] NULL ,
[ContactMemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoOfResponses] [int] NULL ,
[AvailableToCustomer] [int] NULL ,
[ContactID] [int] NULL ,
[AnswerToCustomer] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PublishedKnowledgeID] [int] NULL CONSTRAINT [DF__Bug__PublishedKn__2F10007B] DEFAULT (0),
[EmailStatusBits] [int] NULL ,
[DateModified] [datetime] NULL ,
[DateLastOpenMail] [datetime] NULL ,
[DateLastUntouchMail] [datetime] NULL ,
[AccessCustomerMethod] [int] NULL ,
[ServicePlanID] [int] NULL ,
[InvoiceID] [int] NULL ,
[Ready4Billing] [int] NULL ,
[IfClosedByCustomer] [int] NULL ,
[DateLastDueDateMail] [datetime] NULL ,
[EstimatedCost] [float] NULL ,
[EnableLiveSupport] [int] NULL ,
[IfFinishDateLinked] [int] NULL ,
[TotalSale] [float] NULL ,
[SalesOrderTemplateID] [int] NULL ,
[SalesPossibility] [int] NULL ,
[SalesStatusID] [int] NULL ,
[ExpectedPODate] [datetime] NULL ,
[PrimaryQuoteID] [int] NULL ,
[PODate] [datetime] NULL ,
[SubProjectID] [int] NULL ,
[PriorityValue] [int] NULL ,
[PriorityOrder] [int] NULL ,
[LinkedMSProjectTaskID] [int] NULL ,
[LastStatusDate] [datetime] NULL ,
[SalesLostDate] [datetime] NULL ,
[InstallSiteID] [int] NULL ,
[PrimaryEventID] [int] NULL ,
[AttachmentID] [int] NULL ,
[AttachmentTypeID] [int] NULL ,
[IfBillable] [int] NULL ,
[PrimaryContactID] [int] NULL ,
[ExternalBugID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesPersonID] [int] NULL ,
[TotalNetSales] [float] NULL ,
[IfAssetLink4SalesProcessed] [int] NULL ,
[ActiveStartDate] [datetime] NULL ,
[ActiveEndDate] [datetime] NULL ,
[IfNewSales] [int] NULL ,
[IfResponseTimeLinked] [int] NULL ,
[PlanResponseTimeID] [int] NULL ,
[TimeResponded] [datetime] NULL ,
[TimeResolved] [datetime] NULL ,
[LostToCompetitorID] [int] NULL ,
[LinkedQATestKnowledgeID] [int] NULL ,
[LinkedQAIssueTemplateID] [int] NULL ,
[IssueType] [int] NULL ,
[CompetitorSurveyTmpltID] [int] NULL ,
[CreatedFromWebClickID] [int] NULL ,
[LinkedFormTemplateID] [int] NULL ,
[ParentIfParent] [int] NULL ,
[ParentIssueID] [int] NULL ,
[LinkedShortCutIssueID] [int] NULL ,
[LinkedSalesTotal] [float] NULL ,
[SubmittedFromEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[ProjectID],
[BugID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [BugTypes] (
[ProjectID] [int] NOT NULL ,
[BugTypeID] [int] NOT NULL ,
[BugTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight] [float] NULL ,
[StatusID] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[ProjectID],
[BugTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
Go

CREATE TABLE [CustomerFieldTrackExt] (
[ProjectID] [int] NOT NULL ,
[BugID] [int] NOT NULL ,
[SequenceNo] [int] NOT NULL ,
[Custom_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_4] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_5] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_7] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_8] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_9] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_10] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_11] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_12] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Desc_Custom_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Desc_Custom_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Desc_Custom_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status_Custom_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status_Custom_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_201] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_202] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_203] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_204] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_205] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_206] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_207] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_208] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_209] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_210] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_211] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_212] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_301] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_302] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_303] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_304] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_305] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_306] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_307] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_308] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_309] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_310] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_311] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Custom_312] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Desc_Custom_1_Double] [float] NULL ,
[Desc_Custom_1_Int] [int] NULL ,
[Desc_Custom_1_Date] [datetime] NULL ,
[Desc_Custom_2_Double] [float] NULL ,
[Desc_Custom_2_Int] [int] NULL ,
[Desc_Custom_2_Date] [datetime] NULL ,
[Desc_Custom_3_Double] [float] NULL ,
[Desc_Custom_3_Int] [int] NULL ,
[Desc_Custom_3_Date] [datetime] NULL ,
PRIMARY KEY CLUSTERED
(
[ProjectID],
[BugID],
[SequenceNo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [OriginTypes] (
[ProjectID] [int] NOT NULL ,
[OriginTypeID] [int] NOT NULL ,
[OriginTypeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight] [float] NULL ,
[StatusID] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[ProjectID],
[OriginTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Project] (
[ProjectID] [int] NOT NULL ,
[ProjectName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Memo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartingBugID] [int] NULL ,
[BugPrefix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LockOwner] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AttachedFilePath] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManagerPersonID] [int] NULL ,
[IsActiveProject] [bit] NOT NULL CONSTRAINT [DF__Project__IsActiv__77BFCB91] DEFAULT (1),
[ShowCustomPage] [bit] NOT NULL CONSTRAINT [DF__Project__ShowCus__78B3EFCA] DEFAULT (0),
[ShowLinkPage] [bit] NOT NULL CONSTRAINT [DF__Project__ShowLin__79A81403] DEFAULT (0),
[CustomReportPath] [varchar] (125) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShowVSSPage] [bit] NOT NULL CONSTRAINT [DF__Project__ShowVSS__7A9C383C] DEFAULT (0),
[SupportFolder] [bit] NOT NULL CONSTRAINT [DF__Project__Support__7B905C75] DEFAULT (0),
[ShowCustomePage2] [bit] NOT NULL CONSTRAINT [DF__Project__ShowCus__7C8480AE] DEFAULT (0),
[ShowCrntStatusInSubmit] [bit] NOT NULL CONSTRAINT [DF__Project__ShowCrn__7D78A4E7] DEFAULT (0),
[ShowCustomInSubmit] [bit] NOT NULL CONSTRAINT [DF__Project__ShowCus__7E6CC920] DEFAULT (0),
[ShowCustom2InSubmit] [bit] NOT NULL CONSTRAINT [DF__Project__ShowCus__7F60ED59] DEFAULT (0),
[TabOrder] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DocumentTemplatePath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportDocVw] [bit] NOT NULL CONSTRAINT [DF__Project__Support__00551192] DEFAULT (0),
[RevisionSystem] [int] NULL ,
[NoOfRevisions] [int] NULL ,
[WebUpload] [int] NULL ,
[WebDownload] [int] NULL ,
[ProjectTypeID] [int] NULL ,
[EnableTimeTrack] [int] NULL ,
[ProjectPlannedStartDate] [datetime] NULL ,
[ProjectPlannedFinishDate] [datetime] NULL ,
[EnableEmail] [int] NULL ,
[EnableChangeLog] [int] NULL ,
[DeleteOldLog] [int] NULL ,
[PriorDateForLog] [datetime] NULL ,
[WebSupportPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CopyForwardDesc] [int] NULL CONSTRAINT [DF__Project__CopyFor__014935CB] DEFAULT (0),
[EnableWorkflow] [int] NULL ,
[EmailTimeInterval] [int] NULL ,
[DefaultInitialState] [int] NULL ,
[StateFromNullTo] [int] NULL ,
[SettingsChanged] [int] NULL ,
[KLinkedFieldID1] [int] NULL ,
[KLinkedFieldID2] [int] NULL ,
[RuntimeKey] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WebLoginMemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WebCustomerLoginMemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SupportType] [int] NULL ,
[EnableAutoRouting] [int] NULL ,
[EnableEscalation] [int] NULL ,
[FilePathLocal] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WebNewCustomerLoginTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WebCustomerLoginTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WebLoginTitle] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DefaultHourlyRate] [float] NULL ,
[EnableIssueTimeTrack] [int] NULL ,
[EnableCostTrack] [int] NULL ,
[EnableApplicableUser] [int] NULL ,
[ShowTimeTrackDialog] [int] NULL ,
[ShowEventSelectionInSubmit] [int] NULL ,
[CustomerBaseProjectID] [int] NULL ,
[EnableServiceAgreement] [int] NULL ,
[NameDisplayFormat] [int] NULL ,
[LinkedAssetProjectID] [int] NULL ,
[EnableLiveSupport] [int] NULL ,
[IfAssetManagement] [int] NULL ,
[AssetManagerID] [int] NULL ,
[KBProjectID] [int] NULL ,
[EnableSpellCheck] [int] NULL ,
[EnableWebSpellCheck] [int] NULL ,
[EnableEventNotification] [int] NULL ,
[SupportProjectType] [int] NULL ,
[VersionCtrlSystem] [int] NULL ,
[EnableWhoCanChangeState] [int] NULL ,
[EnableStateReadOnlyFields] [int] NULL ,
[EnableStateInvisibleFields] [int] NULL ,
[EnableStateMandatoryFields] [int] NULL ,
[ShowCustom3InSubmit] [int] NULL ,
[PageOrder] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IfBatchBasedSales] [int] NULL ,
[SaleQuoteOption] [int] NULL ,
[IfDetailPageOneWindow] [int] NULL ,
[IfSupportQuickAction] [int] NULL ,
[MandatoryEnforceOption] [int] NULL ,
[ProjectSubType] [int] NULL ,
[MemoFieldFormat] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[ProjectID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

And sample result data is:

Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research Other 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research Other 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Issue Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Issue Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Issue Research Other 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Issue Research Other 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Issue Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Issue Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research Other 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research Other 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records NULL NULL NULL NULL NULL NULL NULL 0
Service Bureau PPS Defect Research 2327 2004-10-08 09:31:21.000 Beg Bal UI is adding extraneous 0.0 Batch Records 1- High 1 - Critical NULL NULL NULL NULL NULL 0

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-10 : 09:25:43

use source safe...

Go with the flow & have fun! Else fight the flow

anne
Starting Member

4 Posts

Posted - 2005-01-11 : 15:57:44

I guess I am missing the humor in the source safe comment. Perhaps it would be more amusing if there were an attempt to help along with it.

Anyone else have any suggestions for this query request?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-11 : 16:08:59

You posted the sample expected result set. What we need is the before image of the data. This needs to be in the form of INSERT INTO statements so that we can copy your DDL and INSERT INTO statements onto our machines, see what's going on, then come up with a solution.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-11 : 16:58:28

Sounds like you're storing the history of the project in the same table.

What doe sthis give you


  SELECT ProjectId, COUNT(*) 
    FROM Project 
GROUP BY ProjectId 
  HAVING COUNT(*) > 1 
ORDER BY 2 DESC

Because if you do, you'll have to figure out what the last entry for the Project is. And since I don't see a status datetime field or an identity column this should be interesting.

Is this the Case?

Brett

8-)

anne
Starting Member

4 Posts

Posted - 2005-01-12 : 09:37:18

Brett,

Your query returns no results.

This DB is a mess. and not something I can mess with, as it's a commercial product managed by another group in my company. I am just trying to extract reporting information...

My biggest issue here is trying to get just the most recent status change on each issue. the datetime field appears useless, as you can see they are all the exact same entry. regardless, if there were a way to only return one instance of each issue number regardless of how many entries there was for each......

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-12 : 10:45:04

you can. just modify this to your correct columns and tables
select *
from MyTable t1 inner join (select id, max(Col1) as Col1 from MyTable group by id) t2
on t1.id = t2.id and t1.Col1 = t2.Col1

i thought you were trying to build a version of source safe in sql. that's why i said it... sorry if it offended you anne.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources