Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    19

    Unanswered: Trying to avoid a cursor...

    Happy new year to all! Now a question...

    I added a new column to StagePayments table - Activity - which is supposed to end up being the same as the JobActivityID from the JobActivities table. Basically, I need to get JobActivities (JA) info and put it in the StagePayment (SP) column. Problem is there are duplicate JA/Descriptions and SP/Activities, so what I need is to take the first sequence SP/Activity and grab the first JA/JobActivityID that matches for a particular JobID. Then get the next one of each and so on and so on...

    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    Tables...

    CREATE TABLE [dbo].[StagePayment] (
    [PaymentID] [uniqueidentifier] NOT NULL ,
    [JobID] [uniqueidentifier] NOT NULL ,
    [Paid] [bit] NOT NULL ,
    [Sequence] [smallint] NOT NULL ,
    [Amount] [decimal](10, 2) NOT NULL ,
    [Comment] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
    [Activity] [varchar] (30) COLLATE SQL_Latin1_General_CP437_BIN NULL ,
    [ActivityID] [uniqueidentifier] NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
    CONSTRAINT [PK__StagePayment__457442E6] PRIMARY KEY CLUSTERED
    (
    [PaymentID]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[StagePayment] WITH NOCHECK ADD
    CONSTRAINT [DF__StagePayme__Paid__4668671F] DEFAULT (0) FOR [Paid]
    GO

    CREATE INDEX [IX_StagePayment] ON [dbo].[StagePayment]([JobID], [Sequence]) ON [PRIMARY]
    GO

    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    CREATE TABLE [dbo].[JobActivities] (
    [JobActivityID] [uniqueidentifier] NOT NULL ,
    [JobID] [uniqueidentifier] NOT NULL ,
    [Sequence] [smallint] NOT NULL ,
    [Activity_Status] [char] (1) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
    [Description] [char] (30) COLLATE SQL_Latin1_General_CP437_BIN NOT NULL ,
    [PlanStartDate] [datetime] NULL ,
    [PlanEndDate] [datetime] NULL ,
    [ActEndDate] [datetime] NULL ,
    [AmountDue] [decimal](10, 2) NOT NULL ,
    [CanDelete] [bit] NOT NULL ,
    [Comments] [varchar] (2000) COLLATE SQL_Latin1_General_CP437_BIN NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
    CONSTRAINT [PK_JobActivity] PRIMARY KEY CLUSTERED
    (
    [JobActivityID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[JobActivities] WITH NOCHECK ADD
    CONSTRAINT [DF_JobActivities_JobActivityID] DEFAULT (newid()) FOR [JobActivityID]
    GO

    CREATE INDEX [IX_JobActivity] ON [dbo].[JobActivities]([JobID]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IX_JobActivity_1] ON [dbo].[JobActivities]([JobID], [Activity_Status]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IX_JobActivity_2] ON [dbo].[JobActivities]([JobID], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IX_JobActivities] ON [dbo].[JobActivities]([JobID], [PlanEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [JobActivities53] ON [dbo].[JobActivities]([JobID], [Description], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [JobActivities50] ON [dbo].[JobActivities]([JobID], [Description], [PlanEndDate], [ActEndDate]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IX_JobActivities_6] ON [dbo].[JobActivities]([JobActivityID], [Activity_Status], [Description]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IX_JobActivities_1] ON [dbo].[JobActivities]([JobID], [Sequence], [Description], [JobActivityID]) ON [PRIMARY]
    GO

    CREATE INDEX [IX_JobActivities_2] ON [dbo].[JobActivities]([JobID], [Sequence], [ActEndDate]) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[JobActivities] ADD
    CONSTRAINT [FK_JobActivity_Job] FOREIGN KEY
    (
    [JobID]
    ) REFERENCES [dbo].[Jobs] (
    [JobID]
    ) ON DELETE CASCADE
    GO

    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    My example for one single JobID...

    In JA -

    JobActivityID Seq Description
    34969C69-FF70-4313-91CC-43921DB3D74D 18 FOLLOWUP
    48ACFEEE-3349-4F16-83E0-58F9B19E28E5 16 INSTALL
    22507D44-6D0D-42DE-A211-9C23FDFCD19D 5 PLOTPLAN
    BA88D04E-EBAE-40DB-A2C9-F909463D7F22 1 THANKU
    83C48207-895B-4775-A62D-07059D8DEB62 10 NOTCUST
    DBB8DF00-E26B-4E6F-9482-08E8CFE1588D 11 ROOMORD
    BF621E91-E819-4F84-B507-0AA644D5C3F6 0 DWNPAY
    6F595880-59D9-4E55-845D-19B477E8B179 2 THANKU
    86D0A650-3B72-47E1-BDC2-2CA177DC3D53 12 NOTCSTRM
    B0ABCC4C-A626-41C2-890C-3B9580326774 13 ROOMREC
    F131C6FF-A86C-4527-A580-60FF7D3F0164 19 1YRFLWUP
    7132625C-8E8B-4748-9176-6F06E8D0F20F 17 ARCMNT
    AE06A938-323B-46EA-BA11-7D17B0985ACC 15 24HRCALL
    DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A 14 24HRCALL
    EBC15C77-95D5-4C42-AD88-861F9DD7688B 9 RECPERMT
    97D3D755-4B6F-4564-842B-A06945AA8890 8 SUBPERMT
    CAACAACA-3B97-41D5-9A4A-A4E3E963D0BF 6 SUBCAD
    0C3CB2E0-F4E9-43CD-81E4-ACE9F4022033 3 PHONCALL
    42D498BE-308A-413E-965D-ADE7A7A21B97 4 MEASURE
    7654C5E3-BED5-4F78-ADC8-DD4E283ADDEE 7 RECCAD

    In SP -

    Seq Activity ActivityID
    1 NULL
    2 24HRCALL
    3 24HRCALL
    4 INSTALL

    I need to get SP to end up looking like this -

    Seq Activity ActivityID
    1 NULL NULL
    2 24HRCALL DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A
    3 24HRCALL AE06A938-323B-46EA-BA11-7D17B0985ACC
    4 INSTALL 48ACFEEE-3349-4F16-83E0-58F9B19E28E5


    I have tried various versions of this...
    begin transaction
    update StagePayment
    set Activity = (Select J.JobActivityID
    from JobActivities J (nolock)
    inner join StagePayment SP (nolock) on J.JobID = SP.JobID and J.Description = SP.Activity)
    where Activity is not NULL

    way too basic as I get this error...

    Server: Msg 512, Level 16, State 1, Line 2
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    I know somehow I have to get the duplicate Activities in the same sequence as the Descriptions, but that's where I'm stuck?!?

    If anyone is able to offer suggestions on how to get this to update correctly, I'd be very happy to hear about it!
    Thanks in advance!
    Tiffanie

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "grab the first JA/JobActivityID that matches for a particular JobID"??? The order of data in a database has no meaning. So define "first".
    For instance, in your example, why does DBEF21DF-35DA-48DE-8BF5-7F4A0EC0FA7A match with Sequence 2, while AE06A938-323B-46EA-BA11-7D17B0985ACC matches with sequence 3, and not vice-versa?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2005
    Posts
    19
    Well, I guess nevermind on this little problem, turns out there is (way old) dupe data in the JA table and that's part of the problem. So cleanup is under way!

    But for a little more clarification, by first, I meant the first sequence (which is a column defined in each table). Though the sequence numbers don't match from table to table. So the lowest sequence of SP ARCMNT activity should match/get the lowest sequence of JA ARCMNT, the next sequence should get the next, etc.

    For example...
    JA -
    Sequence Description ID
    1 Phonecall 123456
    2 Appointment 456789
    3 Phonecall 789123
    4 Install 123789
    5 Phonecall 789456

    Current SP -
    Sequence Activity
    1 NULL
    2 Phonecall
    3 Phonecall

    Future SP -
    Sequence Activity ID
    1 NULL NULL
    2 Phonecall 123456
    3 Phonecall 789123

    But like I said - this appears to be more of an old dupe data problem <sigh>
    Darn users and developers!
    But thanks to anyone/everyone that took the time to read and maybe even ponder this!
    Tiffanie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •