March 26, 2013 at 8:33 am
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:
IDUserNameErrorDate WorkOrderNbrTimesheetID
23jamesshaffer2013-03-23 00:00:00.000TIALLOC5025
24nfox 2013-03-24 00:00:00.0006554875013
25jamesshaffer2013-03-23 00:00:00.000TIALLOC5025
26nfox 2013-03-24 00:00:00.0006554875013
27jamesshaffer2013-03-23 00:00:00.000TIALLOC5025
28nfox 2013-03-24 00:00:00.0006554875013
(Bold WONbrs are invalid)
Results:
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
wConstruction Inc5013nfox6554873/24/2013Invalid WO
Jim
March 26, 2013 at 8:53 am
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.
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
March 26, 2013 at 9:13 am
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 CorporationNULLNULLNULLNULLInvalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
Air lelectronics chemicNULLNULLNULLNULLInvalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
rINTERNAIONAL INCNULLNULLNULLNULLInvalid WO
cINCNULLNULLNULLNULLInvalid WO
High pSystems, Inc.NULLNULLNULLNULLInvalid WO
d& d NULLNULLNULLNULLInvalid WO
pMechanical CorpNULLNULLNULLNULLInvalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
wLine Construction Inc5013nfox6554873/24/2013Invalid WO
wLine Construction Inc5013nfox6554873/24/2013Invalid WO
wLine Construction Inc5013nfox6554873/24/2013Invalid WO
wLine Construction IncNULLNULLNULLNULLInvalid WO
dSystems IncNULLNULLNULLNULLInvalid WO
Jim
March 26, 2013 at 9:25 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 9:57 am
----------------------
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
March 26, 2013 at 10:44 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 11:17 am
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
March 26, 2013 at 11:36 am
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.:unsure:
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
March 26, 2013 at 12:25 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply