I couldn't get or describe the quantity with units using this table schema in one row as a stri-CodePudding

I have a SQL server database where the table schema is like this :

CREATE TABLE Unit (
  id INT primary key,
  UnitName nvarchar(10) 
);
INSERT INTO Unit (id,UnitName) VALUES (1,'Ton');
INSERT INTO Unit (id,UnitName) VALUES (2,'Kg');
INSERT INTO Unit (id,UnitName) VALUES (3,'g');

CREATE  TABLE UnitGroup (
  id INT primary key,
  UnitGroupName nvarchar(50) 
);
INSERT INTO UnitGroup (id,UnitGroupName) VALUES (1,'1Ton = 1000kg = 1000000g');


CREATE TABLE UnitGroupDetail (
  id INT primary key,
  UnitGroupId int,
  UnitId int,
  ConversionRate decimal(19,5),
  FOREIGN KEY (UnitGroupId) REFERENCES UnitGroup(id),
  FOREIGN KEY (UnitId) REFERENCES Unit(id),
);

CREATE TABLE Item (
  id INT primary key,
  ItemName nvarchar(50) ,
  UnitGroupId int
  FOREIGN KEY (UnitGroupId) REFERENCES UnitGroup(id),
);

INSERT INTO Item (id,ItemName,UnitGroupId) VALUES (1,'Item 1',1);

INSERT INTO UnitGroupDetail (id,UnitGroupId,UnitId,ConversionRate) VALUES (1,1,1,1.0);
INSERT INTO UnitGroupDetail (id,UnitGroupId,UnitId,ConversionRate) VALUES (2,1,2,1000.0);
INSERT INTO UnitGroupDetail (id,UnitGroupId,UnitId,ConversionRate) VALUES (3,1,3,1000000.0);

And I want to create a function where I can pass the quantity in the base unit which its conversion is equal to 1.

DECLARE @ItemId int =1
DECLARE @Quantity decimal(19,5)=105.82349  -- Assuming Passed quantity is in base unit which it's conversions equal to 1
DECLARE @UnitDetailId smallint = null


-- This is what I tried 
SELECT *,
       @Quantity / conversionrate,
       @Quantity - ( @Quantity / conversionrate )
FROM   unitgroupdetail
       INNER JOIN item ON ( item.unitgroupid = unitgroupdetail.unitgroupid )
       INNER JOIN unit ON ( unitgroupdetail.unitid = unit.id )
WHERE  item.id = @ItemId
ORDER  BY unitgroupdetail.conversionrate 

-- Expected output should be in one row 105Ton , 823kg , 490g

I'm trying to get an output in one row this way

105Ton, 823kg, 490g

I tried many ideas like running totals but with no luck

CodePudding user response：

This might be helpful...

DECLARE @ItemId int =1
DECLARE @Quantity decimal(19,5)=105.82349  -- Assuming Passed quantity is in base unit which it's conversions equal to 1
DECLARE @UnitDetailId smallint = null

DECLARE @TableUnit table (Val decimal(19,5),Unit nvarchar(20),Rate decimal(19,5),Num int)


insert into @TableUnit (Val,Unit,Rate,Num)
SELECT 
       @Quantity * ugd.conversionrate,U.UnitName,ugd.conversionrate,ROW_NUMBER() OVER (
    ORDER BY ugd.conversionrate 
   ) 
      
FROM   unitgroupdetail ugd
       INNER JOIN item i ON ( i.unitgroupid = ugd.unitgroupid )
       INNER JOIN unit u ON ( ugd.unitid = u.id )
WHERE  i.id = @ItemId
ORDER  BY ugd.conversionrate 



DECLARE @Cnt int = 1,@maxCnt int = 0,@NumString nvarchar(500) = '',@CurrentVal decimal(19,0),@CurrentRate decimal(19,5),
@CurrentUnit nvarchar(20),@UpdatedVal decimal(19,2),@PreviousValDec decimal(19,5),@PreviousVal int = cast(@Quantity as int),
@PreviousRate decimal(19,5);

select @maxCnt = max(Num) from @TableUnit tr
while @Cnt <= @maxCnt
begin

    select @CurrentVal = cast(Val as int),@CurrentRate = Rate,@CurrentUnit = Unit from @TableUnit where Num = @Cnt;

    set @UpdatedVal= @CurrentVal;

    if @cnt = 1
    begin
        set @PreviousValDec = @Quantity;
        set @PreviousRate = @CurrentRate;
    end
    if(@cnt > 1)
    begin
        select @UpdatedVal = ((@PreviousValDec * (@CurrentRate/@PreviousRate))-(@PreviousVal * (@CurrentRate/@PreviousRate)));

        select @PreviousValDec = @UpdatedVal;

        set @UpdatedVal = cast (@UpdatedVal as int);

        set @PreviousVal = cast (@PreviousValDec as int)

    end

    select @NumString = @NumString   IIF (@Cnt = 1,'',', ')   cast (@UpdatedVal as nvarchar(20))   ' '   @CurrentUnit
    set @Cnt = @Cnt   1
    select @PreviousRate = Rate from @TableUnit where Num = (@Cnt - 1)
end

select @NumString as FinalResult;

Result will be like below

CodePudding user response：

I think you should consider a schema change:

DECLARE @Unit TABLE (id INT PRIMARY KEY, UnitName NVARCHAR(10));
INSERT INTO @Unit (id, UnitName) VALUES 
(1,'Ton'),(2,'Kg'),(3,'g');
DECLARE @UnitGroupDetail TABLE (id INT PRIMARY KEY, UnitGroupId INT, MajorUnitID INT, SubUnitID INT, MinorUnitID INT, MajorUnitRate DECIMAL(10,2), SubUnitRate DECIMAL(10,2), MinorUnitRate DECIMAL(10,2));
INSERT INTO @UnitGroupDetail (id, UnitGroupId, MajorUnitID, SubUnitID, MinorUnitID, MajorUnitRate, SubUnitRate, MinorUnitRate)  VALUES 
(1, 1, 1, 2, 3, 1000000, 1000, 1);

Using this instead makes your query much more straight forward:

DECLARE @weight DECIMAL(10,3) = 1234567

SELECT u1.UnitName AS MajorUnitName, u2.UnitName AS SubUnitName, u3.UnitName AS MinorUnitName, 
       FLOOR(@weight/ugd.MajorUnitRate) AS MajorUnits, FLOOR((@weight%ugd.MajorUnitRate)/ugd.SubUnitRate) AS SubUnits, FLOOR((@weight%ugd.SubUnitRate)/ugd.MinorUnitRate) AS MinorUnits,
       CAST(FLOOR(@weight/ugd.MajorUnitRate)                   AS NVARCHAR) u1.UnitName ', ' 
       CAST(FLOOR((@weight%ugd.MajorUnitRate)/ugd.SubUnitRate) AS NVARCHAR) u2.UnitName ', ' 
       CAST(FLOOR((@weight%ugd.SubUnitRate)/ugd.MinorUnitRate) AS NVARCHAR) u3.UnitName AS TextWeight
  FROM @UnitGroupDetail ugd
    INNER JOIN @Unit u1
      ON ugd.MajorUnitID = u1.id
    INNER JOIN @Unit u2
      ON ugd.SubUnitID = u2.id
    INNER JOIN @Unit u3
      ON ugd.MinorUnitID = u3.id

MajorUnitName   SubUnitName MinorUnitName   MajorUnits  SubUnits    MinorUnits  TextWeight
------------------------------------------------------------------------------------------
Ton             Kg          g               1           234         567         1Ton, 234Kg, 567g