My SQL server database :
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);
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
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