Issue Writing Custom Logic for Computed Field and Parent-Child Relationship

Greetings everyone, I apologize for the less than stellar title. Let me give you a quick run down of my situation.

I'm more of a .NET developer but a former co-worker and I were working on a function for a computed field in a table I'm using, which we managed to get mostly working. However, right before he left for a new opportunity the requirements changed completely and I'm the one now rewriting the logic for this function that I only partially grasped to begin with.

Essentially, I have a parent/child relationship (parent = ShippingPlan_Data, child = ShippingPlanItem_Data) and I am trying to create a VARCHAR based off of fields from each of these tables. The computed field should look something like this: '4/27/16-1' where 4/27/16 is the date and the following number is a semi-unique identifier for that record.

The date is taken from the parent, which means I need to join the child to the parent in order to get the date, but in doing so, my calculations for the count needed to generate the trailing digit gets messed up because I am not meant to generate the digit based off of the parent's idea, but rather, the parent's date.

I probably butchered that explanation, so here is the code I have:

USE [HFINC_Dev]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[fnGetShippingPlanProductDefinitionsSequenceNumber]
(
@ShippingPlanItemId INT
)
RETURNS VARCHAR(128)

AS
BEGIN
DECLARE @retValue VARCHAR(128)

  SELECT @retValue = 
   CONVERT(VARCHAR,MONTH([SPD].[ShippingDate]))+'/'
  +CONVERT(VARCHAR,DAY([SPD].[ShippingDate]))+'/'
  +RIGHT(CONVERT(VARCHAR,YEAR([SPD].[ShippingDate])),2)
  +'-'
 
  CONVERT(VARCHAR,ISNULL((SELECT COUNT([SPID2].[ShippingPlanItemId])
  FROM [dbo].[ShippingPlanItem_Data] [SPID2]
  JOIN [dbo].[ShippingPlan_Data] [SPD2] on [SPD2].[ShippingPlanId] = [SPID2].[ShippingPlanId]
  WHERE [SPD2].[ShippingDate] = [SPD].[ShippingDate]
  AND [SPID2].[BlendingTypeId] = -1
  AND [SPD2].[LocationId] = [SPD].[LocationId]
  AND [SPID2].[ShippingPlanItemNumber] < [SPID].[ShippingPlanItemNumber]),0)+1)

  --SAME DATE AND PROD DEFF
  FROM [dbo].[ShippingPlanItem_Data] [SPID]
  JOIN [dbo].[ShippingPlan_Data] [SPD] on [SPD].[ShippingPlanId] = [SPID].[ShippingPlanId]
  WHERE [SPID].[ShippingPlanItemId] = @ShippingPlanItemId

  RETURN @retValue
END

Essentially what I need is for that trailing digit to increment based on the parent's date, not the parent's id. I'm trying to get a count of all children that share the same date (from the parent) so that the first batch of children (1, 2, or 3, never more than that. By batch, I mean that they share the same parent id) have the computed field of '4/27/16-1' (under normal circumstances, in some instances I'd like the number to increment on the children too, depending on the fields of the children).

It's incredibly difficult to describe so I'll try to mock up some data to help explain it.

Parent1 [ShippingDate = 4/27/16], [LocationId = 1]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = -1], [Computed = 4/27/16-1]

Parent2 [ShippingDate = 4/27/16], [LocationId = 1]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = -1], [Computed = 4/27/16-2]
  • Child2 [ShippingPlanItemNumber = 2], [SizeModeId = -1], [Computed = 4/27/16-2]

Parent3 [ShippingDate = 4/27/16], [LocationId = 1]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = -1], [Computed = 4/27/16-3]
  • Child2 [ShippingPlanItemNumber = 2], [SizeModeId = -1], [Computed = 4/27/16-3]
  • Child3 [ShippingPlanItemNumber = 3], [SizeModeId = -1], [Computed = 4/27/16-3]

Parent4 [ShippingDate = 4/27/16], [LocationId = 1]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = 1], [Computed = 4/27/16-4]
  • Child2 [ShippingPlanItemNumber = 2], [SizeModeId = 2], [Computed = 4/27/16-5]

Parent5 [ShippingDate = 4/27/16], [LocationId = 1]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = -1], [Computed = 4/27/16-6]
  • Child 2 [ShippingPlanItemNumber = 2], [SizeModeId = -1], [Computed = 4/27/16-6]

Parent6 [ShippingDate = 4/27/16], [LocationId = 2]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = -1], [Computed = 4/27/16-1]

Parent7 [ShippingDate = 4/28/16], [LocationId = 1]

  • Child1 [ShippingPlanItemNumber = 1], [SizeModeId = -1], [Computed = 4/28/16-1]

So the digit increments on the child only if the parent has the same date and location. If the pair/group of children (same parent id) have a SizeModeId of > -1, then each of those individual children also get incremented instead of sharing the same incremented digit.

Honestly, I'm having trouble keeping all of this straight so I will entirely understand if I need to try to explain this better :slight_smile: I appreciate your taking a look and trying to help me all the same.

Thanks again.