Thanks for offering me some help:
Here is the pseudocode for what i would like to do
Select tblprassignmentDays where BillingStatus = 'billnow'
--process though each selected record
if tblprassignmentDays.ClientRegHours or
tblprassignmentDays.ClientOTHours not = 0 then
insert into
tblARInvoiceDetailTemp
[Invoice_Line] Initially null until Invoice_ID is Generated
Concatenated Inv# + Line # (Unique)
[Invoice_ID] Initially null until Invoice_ID is Generated
[Date] AssignDate from tblprassignmentdays
[Units] see below
[Description] see below
[Quantity] if invoice is credit '-1' otherwise '01'
[BillType] see below
[Amount] see below
[GLAccount] see below
[SWKMonth] concatenated field
Assignid(6),year(2),week(2),month(2) leading zeros on assignid
[Job#] tblprassignment.AssignID
[DEBatchID] Chips batch import#
here is the DDL for tblPRassignmentdays
CREATE TABLE [dbo].[tblPRAssignmentDays] (
[TimesheetDayID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeSheetWeekID] [bigint] NULL ,
[AssignID] [int] NULL ,
[SlotID] [int] NULL ,
[AssignDate] [smalldatetime] NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingStatus] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvRegHours] [numeric](6, 2) NULL ,
[ProvOTHours] [numeric](6, 2) NULL ,
[ProvNightCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ProvWeekendCall] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProvMiscelAmount] [numeric](8, 2) NULL ,
[ProvMiscelBillClient] [bit] NULL ,
[ProvMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProvTotalAmount] [numeric](8, 2) NULL ,
[InsuranceHours] [numeric](6, 2) NULL ,
[InsuranceHoursOR] [bit] NULL ,
[InsuranceClassOR] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[InsuranceTotalAmount] [numeric](8, 2) NULL ,
[InsuranceTotalAmountQ] [numeric](18, 0) NULL ,
[ClientRegHours] [numeric](6, 2) NULL ,
[ClientOTHours] [numeric](6, 2) NULL ,
[ClientNightCharge] [bit] NULL ,
[ClientWeekendCharge] [bit] NULL ,
[ClientMemo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ClientMiscelAmount] [numeric](8, 2) NULL ,
[ClientRegHoursOR] [bit] NULL ,
[ClientOTHoursOR] [bit] NULL ,
[ClientTotalAmount] [numeric](8, 2) NULL ,
[InvoiceCreateDate] [smalldatetime] NULL ,
[PayStatus] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Creator] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created] [smalldatetime] NULL ,
[Updater] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [smalldatetime] NULL ,
[Archive] [bit] NULL
) ON [PRIMARY]
GO
here is the ddl for the table i would like to insert:
CREATE TABLE [dbo].[tblARInvoiceDetailTemp] (
[Invoice_Line] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Invoice_ID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Units] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Quantity] [int] NULL ,
[BillType] [int] NULL ,
[Amount] [money] NULL ,
[GLAccount] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SWKMonth] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Job#] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEBatchID] [int] NULL ,
[BookedDays] [real] NULL ,
[AssignIDMonth] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!