Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mixed joins Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 8:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
I have a table (tblTimesheetWOErrors) containing an error list with (ErrorID, WONbr, TimesheetID, Username, Date). The WONbr refers to a unique (Non-PK) index on the tblWorkOrder table, and may be invalid. I have a Timesheet Master that identifies the Vendor as well. Problem is, I can't seem to get the mixed joins to work properly. Either I get no rows returned, or only valid WOs returned...that's what is happening here. I read up on mixed joins, and followed the advice I found to eliminate them by using "sub-selects". Herewith my SQL. Can anyone help me here?

SELECT  
CASE WHEN v.[Vendor Name] IS NULL THEN 'No Vendor' ELSE v.[Vendor Name] END AS TD, e.TimesheetID AS TD, e.UserName AS TD, e.WorkOrderNbr AS TD,
format(e.ErrorDate, 'd') AS TD, CASE WHEN w.WOExpiry IS NULL THEN 'Invalid WO' ELSE format(w.WOExpiry, 'd') END AS TD
FROM
tblTimesheetWOErrors e
join (select e1.TimesheetID, wo.* from tblTimesheetWOErrors e1 left join tblWorkOrders wo on e1.WorkOrderNbr=wo.WONbr ) w on e.TimesheetID=w.TimesheetID
join (select m1.tsid, v1.* from tblTimesheetMaster m1 join tblVendor v1 on m1.TSVendorID=v1.ID) v on e.TimesheetID=v.TSID

tblTimesheetWOErrors:
ID UserName ErrorDate WorkOrderNbr TimesheetID
23 jamesshaffer 2013-03-23 00:00:00.000 TIALLOC 5025
24 nfox 2013-03-24 00:00:00.000 655487 5013
25 jamesshaffer 2013-03-23 00:00:00.000 TIALLOC 5025
26 nfox 2013-03-24 00:00:00.000 655487 5013
27 jamesshaffer 2013-03-23 00:00:00.000 TIALLOC 5025
28 nfox 2013-03-24 00:00:00.000 655487 5013
(Bold WONbrs are invalid)

Results:

wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wConstruction Inc 5013 nfox 655487 3/24/2013 Invalid WO


Jim
Post #1435537
Posted Tuesday, March 26, 2013 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 7,224, Visits: 13,696
Here's a guess.
SELECT 
CASE WHEN v.[Vendor Name] IS NULL THEN 'No Vendor' ELSE v.[Vendor Name] END AS TD,
e.TimesheetID AS TD,
e.UserName AS TD,
e.WorkOrderNbr AS TD,
format(e.ErrorDate, 'd') AS TD,
CASE WHEN w.WOExpiry IS NULL THEN 'Invalid WO' ELSE format(w.WOExpiry, 'd') END AS TD
FROM tblTimesheetMaster m
INNER JOIN tblVendor v on v.ID = m.TSVendorID
LEFT JOIN tblTimesheetWOErrors e ON e.TimesheetID = m.TSID
LEFT JOIN tblWorkOrders w on w.WONbr = e.WorkOrderNbr

If it's close, explain the differences between the output from this and the output you expect. If it's miles off, set up some sample data as per "read this" in my sig.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1435549
Posted Tuesday, March 26, 2013 9:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
Selects all the tblTimesheetWOErrors correctly, but now I'm getting nulls on "e" table when the WO is invalid, and Invalid WO when the WO Is Valid.

There will always be a match on TimesheetMaster. Only Workorders are allowed to not match....

tServices Corporation NULL NULL NULL NULL Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
Air lelectronics chemic NULL NULL NULL NULL Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
rINTERNAIONAL INC NULL NULL NULL NULL Invalid WO
cINC NULL NULL NULL NULL Invalid WO
High pSystems, Inc. NULL NULL NULL NULL Invalid WO
d& d NULL NULL NULL NULL Invalid WO
pMechanical Corp NULL NULL NULL NULL Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
wLine Construction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wLine Construction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wLine Construction Inc 5013 nfox 655487 3/24/2013 Invalid WO
wLine Construction Inc NULL NULL NULL NULL Invalid WO
dSystems Inc NULL NULL NULL NULL Invalid WO


Jim
Post #1435566
Posted Tuesday, March 26, 2013 9:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
Then as Chris eluded to we are going to need ddl and sample data in addition to desired output as outlined by the article found at the first link in my signature. Without these details we are shooting in the dark at an unknown target.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435575
Posted Tuesday, March 26, 2013 9:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
----------------------
I included the WOError table data above. In all cases, the VendorID in the WOError table matches a tblVendor. In all cases, the TimesheetID refers to a valid tblTimesheetMaster row. Not all WONbrs refer to a tblWorkorders row. In this example, "TIALLOC" is a valid tblWorkOrders row, while "655487" does not. The work order error table captures usage on timesheets of invalid work orders, which may be valid rows, but be expired, or may be invalid rows. TIALLOC refers to a valid row, and it is not expired.


/****** Object: Table [dbo].[tblTimesheetWOErrors] Script Date: 3/26/2013 11:46:04 AM ******/

CREATE TABLE [dbo].[tblTimesheetWOErrors](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[ErrorDate] [datetime] NOT NULL,
[WorkOrderNbr] [varchar](50) NOT NULL,
[TimesheetID] [int] NOT NULL,
CONSTRAINT [PK_tblTimesheetWOErrors] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblTimesheetWOErrors] ADD CONSTRAINT [DF_tblTimesheetWOErrors_ErrorDate] DEFAULT (getdate()) FOR [ErrorDate]
GO
--------------------------------------------------------

/****** Object: Table [dbo].[tblTimesheetMaster] Script Date: 3/26/2013 11:47:19 AM ******/
CREATE TABLE [dbo].[tblTimesheetMaster](
[TSID] [int] IDENTITY(5000,1) NOT NULL,
[TSVendorID] [int] NOT NULL,
[TSStartDate] [date] NOT NULL,
[TSAuthor] [varchar](50) NOT NULL,
[TSApprover] [varchar](50) NOT NULL,
[TSOverrideApprover] [varchar](50) NULL,
[TSRequiresOverride] [bit] NULL,
[TSComments] [varchar](256) NULL,
[TSStatus] [int] NULL,
[TSLastAuditDateTime] [datetime] NULL,
[TSApprovedby] [varchar](50) NULL,
[TSApprovalDate] [datetime] NULL,
CONSTRAINT [PK_tblTimesheetMaster] PRIMARY KEY CLUSTERED
(
[TSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblTimesheetMaster] ADD CONSTRAINT [DF_tblTimesheetMaster_TSRequiresOverride] DEFAULT ((0)) FOR [TSRequiresOverride]
GO

ALTER TABLE [dbo].[tblTimesheetMaster] ADD CONSTRAINT [DF_tblTimesheetMaster_TSStatus] DEFAULT ((1)) FOR [TSStatus]
GO

ALTER TABLE [dbo].[tblTimesheetMaster] WITH CHECK ADD CONSTRAINT [FK_tblTimesheetMaster_tblTimesheetStatus] FOREIGN KEY([TSStatus])
REFERENCES [dbo].[tblTimesheetStatus] ([TSStatusID])
GO

ALTER TABLE [dbo].[tblTimesheetMaster] CHECK CONSTRAINT [FK_tblTimesheetMaster_tblTimesheetStatus]
GO

ALTER TABLE [dbo].[tblTimesheetMaster] WITH CHECK ADD CONSTRAINT [FK_tblTimesheetMaster_tblVendor] FOREIGN KEY([TSVendorID])
REFERENCES [dbo].[tblVendor] ([ID])
GO

ALTER TABLE [dbo].[tblTimesheetMaster] CHECK CONSTRAINT [FK_tblTimesheetMaster_tblVendor]
GO
-----------------------------------------------------
/****** Object: Table [dbo].[tblVendor] Script Date: 3/26/2013 11:48:19 AM ******/

CREATE TABLE [dbo].[tblVendor](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Vendor Name] [nvarchar](50) NULL,
[Vendor Short Name] [nvarchar](10) NULL,
[Vendor Workweek Start] [int] NULL,
[VendorLastDay] [datetime] NULL,
[VendorOTCode] [nvarchar](1) NULL,
[VendorGroupCode] [tinyint] NULL,
CONSTRAINT [tblVendor$PrimaryKey] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblVendor] ADD DEFAULT ((0)) FOR [Vendor Workweek Start]
GO

ALTER TABLE [dbo].[tblVendor] ADD DEFAULT ((0)) FOR [VendorGroupCode]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[ID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'ID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[Vendor Name]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'Vendor Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[Vendor Short Name]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'Vendor Short Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[Vendor Workweek Start]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'Vendor Workweek Start'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[VendorLastDay]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'VendorLastDay'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[VendorOTCode]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'VendorOTCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[VendorGroupCode]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'COLUMN',@level2name=N'VendorGroupCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblVendor].[PrimaryKey]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblVendor', @level2type=N'CONSTRAINT',@level2name=N'tblVendor$PrimaryKey'
GO
-----------------------------------------------

/****** Object: Table [dbo].[tblWorkOrders] Script Date: 3/26/2013 11:49:22 AM ******/

CREATE TABLE [dbo].[tblWorkOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WONbr] [nvarchar](50) NOT NULL,
[Description] [nvarchar](150) NULL,
[ProjectID] [int] NOT NULL,
[WODiscipline] [int] NULL,
[HexNbr] [nvarchar](10) NULL,
[MICAP] [nvarchar](10) NULL,
[WOGLAcct] [nvarchar](50) NULL,
[WOCostCenter] [nvarchar](50) NULL,
[WOExpiry] [datetime] NULL,
[HoursBudget] [int] NULL,
[SpendBudget] [money] NULL,
[ChallengeBudget] [money] NULL,
[PercentComplete] [float] NULL,
[PercentCompleteDate] [datetime] NULL,
[HoursCurve] [int] NULL,
[SpendCurve] [int] NULL,
[WODateCreated] [datetime] NULL,
[SSMA_TimeStamp] [timestamp] NOT NULL,
CONSTRAINT [tblWorkOrders$Primary Key] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblWorkOrders] ADD CONSTRAINT [DF__tblWorkOr__Proje__5BAD9CC8] DEFAULT ((0)) FOR [ProjectID]
GO

ALTER TABLE [dbo].[tblWorkOrders] ADD CONSTRAINT [DF__tblWorkOr__WOExp__5CA1C101] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))+(1095)) FOR [WOExpiry]
GO

ALTER TABLE [dbo].[tblWorkOrders] ADD CONSTRAINT [DF__tblWorkOr__Perce__5D95E53A] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))) FOR [PercentCompleteDate]
GO

ALTER TABLE [dbo].[tblWorkOrders] ADD CONSTRAINT [DF__tblWorkOr__WODat__5E8A0973] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))) FOR [WODateCreated]
GO

ALTER TABLE [dbo].[tblWorkOrders] WITH NOCHECK ADD CONSTRAINT [tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblDisciplinetblWorkOrders] FOREIGN KEY([WODiscipline])
REFERENCES [dbo].[tblDiscipline] ([ID])
GO

ALTER TABLE [dbo].[tblWorkOrders] CHECK CONSTRAINT [tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblDisciplinetblWorkOrders]
GO

ALTER TABLE [dbo].[tblWorkOrders] WITH NOCHECK ADD CONSTRAINT [tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblMICAPtblWorkOrders] FOREIGN KEY([MICAP])
REFERENCES [dbo].[tblMICAP] ([MICAP])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblWorkOrders] CHECK CONSTRAINT [tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblMICAPtblWorkOrders]
GO

ALTER TABLE [dbo].[tblWorkOrders] WITH NOCHECK ADD CONSTRAINT [tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblProjectstblWorkOrders] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[tblProjects] ([ID])
GO

ALTER TABLE [dbo].[tblWorkOrders] CHECK CONSTRAINT [tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblProjectstblWorkOrders]
GO

ALTER TABLE [dbo].[tblWorkOrders] WITH NOCHECK ADD CONSTRAINT [SSMA_CC$tblWorkOrders$PercentComplete$validation_rule] CHECK (([PercentComplete]>=(0) AND [PercentComplete]<=(1)))
GO

ALTER TABLE [dbo].[tblWorkOrders] CHECK CONSTRAINT [SSMA_CC$tblWorkOrders$PercentComplete$validation_rule]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[ID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'ID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[WONbr]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'WONbr'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[Description]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[ProjectID]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'ProjectID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[WODiscipline]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'WODiscipline'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[HexNbr]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'HexNbr'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[MICAP]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'MICAP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[WOGLAcct]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'WOGLAcct'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[WOCostCenter]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'WOCostCenter'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[WOExpiry]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'WOExpiry'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[HoursBudget]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'HoursBudget'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[SpendBudget]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'SpendBudget'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[ChallengeBudget]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'ChallengeBudget'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[PercentComplete]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'PercentComplete'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[PercentCompleteDate]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'PercentCompleteDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[HoursCurve]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'HoursCurve'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[SpendCurve]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'SpendCurve'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[WODateCreated]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'COLUMN',@level2name=N'WODateCreated'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[Primary Key]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'CONSTRAINT',@level2name=N'tblWorkOrders$Primary Key'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblDisciplinetblWorkOrders]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'CONSTRAINT',@level2name=N'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblDisciplinetblWorkOrders'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblMICAPtblWorkOrders]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'CONSTRAINT',@level2name=N'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblMICAPtblWorkOrders'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'CPAS.[tblWorkOrders].[[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb]].tblProjectstblWorkOrders]' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblWorkOrders', @level2type=N'CONSTRAINT',@level2name=N'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblProjectstblWorkOrders'
GO



Jim
Post #1435591
Posted Tuesday, March 26, 2013 10:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
JimS-Indy (3/26/2013)
----------------------
I included the WOError table data above. In all cases, the VendorID in the WOError table matches a tblVendor. In all cases, the TimesheetID refers to a valid tblTimesheetMaster row. Not all WONbrs refer to a tblWorkorders row. In this example, "TIALLOC" is a valid tblWorkOrders row, while "655487" does not. The work order error table captures usage on timesheets of invalid work orders, which may be valid rows, but be expired, or may be invalid rows. TIALLOC refers to a valid row, and it is not expired.



The idea of posting data is to post it in a consumable format. That means that the volunteers working on your issue don't have to spend their time turning the unformatted data into insert statements.

The ddl you posted will not work as it is. You seem to have a lot of foreign keys to tables that you haven't posted.


Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_tblTimesheetMaster_tblTimesheetStatus' references invalid table 'dbo.tblTimesheetStatus'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_tblTimesheetMaster_tblTimesheetStatus' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK_tblTimesheetMaster_tblVendor' references invalid table 'dbo.tblVendor'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'FK_tblTimesheetMaster_tblVendor' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblDisciplinetblWorkOrders' references invalid table 'dbo.tblDiscipline'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblDisciplinetblWorkOrders' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblMICAPtblWorkOrders' references invalid table 'dbo.tblMICAP'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblMICAPtblWorkOrders' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblProjectstblWorkOrders' references invalid table 'dbo.tblProjects'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 2
Constraint 'tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblProjectstblWorkOrders' does not exist.
Msg 4916, Level 16, State 0, Line 2
Could not enable or disable the constraint. See previous errors.
Msg 15135, Level 16, State 19, Procedure sp_addextendedproperty, Line 37
Object is invalid. Extended properties are not permitted on 'dbo.tblWorkOrders.tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblDisciplinetblWorkOrders', or the object does not exist.
Msg 15135, Level 16, State 19, Procedure sp_addextendedproperty, Line 37
Object is invalid. Extended properties are not permitted on 'dbo.tblWorkOrders.tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblMICAPtblWorkOrders', or the object does not exist.
Msg 15135, Level 16, State 19, Procedure sp_addextendedproperty, Line 37
Object is invalid. Extended properties are not permitted on 'dbo.tblWorkOrders.tblWorkOrders$[H:\mtv\secure\Construction\Access\CPAS\Backend\Composite_be.accdb].tblProjectstblWorkOrders', or the object does not exist.


The point here is to help us build an environment that is close enough to the real one so we can help you with tested and fast code. I could probably spend an hour or so and clean this up so there are tables created. However, we still don't have data to help figure out what the problem is with the query that you created. Honestly we want to help but without the details we just can't do it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435614
Posted Tuesday, March 26, 2013 11:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
Yeah, the app has 116 separate tables, all fully normalized, so there are lots of foreign keys all about. Also, they were migrated using SSMA, so they're very unwieldy in name.... I'll fix that someday.

I'll see if I can't improve on my post.


Jim
Post #1435629
Posted Tuesday, March 26, 2013 11:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 12:49 PM
Points: 318, Visits: 440
OK, I'm an idiot. My original SQL code works. When I seeded the WO Error table with (honestly, only 2, repeated) rows, I inserted a TimesheetID that I had earlier deleted in testing. When I changed the WO Error table to contain valid Timesheet IDs in all cases, everything worked fine.

Chris' code still doesn't work. I'll work with it a little and see if I can figure it out.

Sorry to have wasted your time, guys. I hope you'll still answer my questions in the future.

New Results -- This worked:
SELECT DISTINCT
CASE WHEN v.[Vendor Name] IS NULL THEN 'No Vendor' ELSE v.[Vendor Name] END AS TD,
e.TimesheetID AS TD,
e.UserName AS TD,
e.WorkOrderNbr AS TD,
format(e.ErrorDate, 'd') AS TD,
CASE WHEN w.WOExpiry IS NULL THEN 'Invalid WO' ELSE format(w.WOExpiry, 'd') END AS TD
FROM tblTimesheetWOErrors e left join tblWorkOrders w on w.WONbr=e.WorkOrderNbr join tblTimesheetMaster m on e.TimesheetID=m.TSID join tblVendor v on m.TSVendorID=v.ID



Jim
Post #1435640
Posted Tuesday, March 26, 2013 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
No problem Jim. Glad you figured it out. I will be happy to help you in the future. I can't speak for Chris but I am pretty sure he will help too.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse