CODEWITHSUNDEEP
sqlsql-server-2008parent-child
Eclipse
Eclipse

Reputation: 309

parent-child data alignment

I have this stored procedure that presents some inventory data and standard format and does not consider any parent-child relationship. This is the current view of the data along with many other columns:

I'm trying to bring the parent/child relationship in this report. so I've modified all the queries to bring in all parent/child data in. The data needs to be presented in a specific way. pic below:

enter image description here

Basically, need to list each parent first followed by all the child records. Then find these records for all child to display on the parent row. 

the min of all child "1st Receipt Date", 
max of all child "Last Receipt Date", 
Sum of all child "On Hand"
Sum of all child "Sales Unit"
Mths Supply will be calculated from the total On Hand and Sales Units

enter image description here

I have no clue how to arrange the data in this manner. Would greatly appreciate some direction.

Thanks in advance.

Upvotes: 0

Views: 177

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

-- This Section is Just to Stage some Sample Data
--------------------------------------------------------------------------------------------------------
Declare @Inv table (SKU int,FirstReceiptDate Date,LastReceiptDate Date,OnHand int,SalesUnits int)
Insert Into @Inv (SKU,FirstReceiptDate,LastReceiptDate,OnHand,SalesUnits) values
(456,'2014-03-15','2014-12-14',0,15),
(789,'2014-05-30','2014-12-15',10,35),
(321,'2014-07-31','2016-03-16',112,60)

Declare @Hier table (SKU int,PtSKU int,PS int,Title varchar(50)) 
Insert into @Hier (SKU,PtSKU,PS,Title) values
(123,0,0,'SKU Tile 123'),
(456,123,10,'Some Other Title SKU 456'),
(789,123,20,'This is the Title for Title SKU 786'),
(321,123,30,'Finally Tile 321')


-- This Section Builds the Hierarchy with Range Keys  Hierarchies can be variable depth
-- My hierarchies are pretty static so they are rebuilt as needed
-- The real power is using the range key.  You can aggregate data without 
-- a recursive query.
-- I should not that I added a Presentation Sequence (PS) and Title to the Hierarcy
-- The PS is used to control the presentation order.  This can be alphabetical as well
--------------------------------------------------------------------------------------------------------
;With cteOH (SKU,PtSKU,Lvl,PS,SortSeq) as 
     (
        Select SKU,PtSKU,Lvl=1,PS,cast([dbo].[udf-Str-PadL](PS,0,6) +':' +[dbo].[udf-Str-PadL](SKU,0,6) + '/' as varchar(500)) from @Hier where  PtSKU=0
        Union All
        Select h.SKU,h.PtSKU,cteOH.Lvl+1,h.PS,SortSeq=cast(cteOH.SortSeq + [dbo].[udf-Str-PadL](H.PS,0,6) +':' +[dbo].[udf-Str-PadL](H.SKU,0,6) + '/' as varchar(500)) FROM @Hier h INNER JOIN cteOH ON  h.PtSKU = cteOH.SKU
     )
    ,cteR1  as (Select SKU,SortSeq,R1=Row_Number() over (Order by SortSeq) From cteOH)
    ,cteR2  as (Select A.SKU,R2 = max(B.R1) From cteOH A Join cteR1 B on (B.SortSeq Like A.SortSeq+'%') Group By A.SKU)
    Select B.R1
          ,C.R2
          ,A.Lvl
          ,A.SKU
          ,A.PtSKU
          ,A.PS
          ,T.Title
     Into  #TempOH
     From  cteOH A
     Join  cteR1 B on (A.SKU=B.SKU)
     Join  cteR2 C on (A.SKU=C.SKU)
     Join  @Hier T on (A.SKU=T.SKU)
     Order By B.R1


-- This Section illustrates how to aggregate data via the range keys
--------------------------------------------------------------------------------------------------------
Select A.*  
      ,FirstReceiptDate       = min(B.FirstReceiptDate)
      ,LastReceiptDate        = max(B.LastReceiptDate)
      ,OnHand                 = sum(B.OnHand)
      ,SalesUnits             = sum(B.SalesUnits)
      ,MonthsSupply           = cast(sum(B.OnHand*12.)/sum(B.SalesUnits) as money)
      ,FamilyFirstReceiptDate = First_Value(min(B.FirstReceiptDate)) Over (Order By A.R1)
      ,FamilyLastReceiptDate  = First_Value(max(B.LastReceiptDate))  Over (Order By A.R1)
      ,FamilyOnHand           = First_Value(sum(B.OnHand))           Over (Order By A.R1)
      ,FamilySalesUnits       = First_Value(sum(B.SalesUnits))       Over (Order By A.R1)
      ,FamilyMonthsSupply     = First_Value(cast(sum(B.OnHand*12.)/sum(B.SalesUnits) as money))       Over (Order By A.R1)
 From #TempOH A
 Join (Select _R1=B.R1,A.* From @Inv A Join #TempOH B on A.SKU=B.SKU) B on _R1 between A.R1 and A.R2
 Group By A.R1,A.R2,A.Lvl,A.SKU,A.PtSKU,A.PS,A.Title
 Order By A.R1

Returns

R1                   R2                   Lvl         SKU         PtSKU       PS          Title                                              FirstReceiptDate LastReceiptDate OnHand      SalesUnits  MonthsSupply          FamilyFirstReceiptDate FamilyLastReceiptDate FamilyOnHand FamilySalesUnits FamilyMonthsSupply
-------------------- -------------------- ----------- ----------- ----------- ----------- -------------------------------------------------- ---------------- --------------- ----------- ----------- --------------------- ---------------------- --------------------- ------------ ---------------- ---------------------
1                    4                    1           123         0           0           SKU Tile 123                                       2014-03-15       2016-03-16      122         110         13.3091               2014-03-15             2016-03-16            122          110              13.3091
2                    2                    2           456         123         10          Some Other Title SKU 456                           2014-03-15       2014-12-14      0           15          0.00                  2014-03-15             2016-03-16            122          110              13.3091
3                    3                    2           789         123         20          This is the Title for Title SKU 786                2014-05-30       2014-12-15      10          35          3.4286                2014-03-15             2016-03-16            122          110              13.3091
4                    4                    2           321         123         30          Finally Tile 321                                   2014-07-31       2016-03-16      112         60          22.40                 2014-03-15             2016-03-16            122          110              13.3091

Required function

CREATE FUNCTION [dbo].[udf-Str-PadL] (@Value varchar(50),@Pad varchar(10) = '0',@Len int = 10)

-- Syntax : Select [dbo].[udf-Str-PadL](25,0,10)
-- Syntax : Select [dbo].[udf-Str-PadL](25,'-',6)

Returns varchar(50)
AS
  BEGIN
    Return right(concat(Replicate(@Pad,@Len),@Value),@Len)
  END

Upvotes: 1

Related Questions