如何从其他表列中获取值来为表列生成值 [英] How to generate value for a table column from taking values from other table column
问题描述
在我的项目中我有一个表名，其中有一个列名为total_qty，从中取值我必须为另一个名为Depot_No的列生成值。
逻辑是卡车到达仓库，里面有一些材料，
表中有_Qty列，其中插入了材料的total_Qty。
例如第一条记录total_qty是200然后我必须发出Depot_No值1-200。
如果为下一条记录，total_qty是300则我必须生成Depot_No值201-300，
并且如果total_qty为100那么下一条记录则我必须生成Depot_No值301-400，
每次插入记录时，
都是这样的，我必须在最后一条记录中添加total_qty值code_No。
- 有不同的软件仓库，它们有唯一的Depot_Codes。
- 这个Depot_No对于每个Depot_Code都是唯一的，并且每个depot_Code从1开始，并且
-并且每年fr om 10月1日这个Depot_No从1开始为每个Depot_code重新启动
i为此创建了一个程序，但它无法正常工作：
创建 程序 [dbo]。[SP_m_Receipt_GenerateDepotNo ]（ @ Receipt_ID bigint ， @ Vechicle_Arrival_Date datetime ， @ Total_No_Qty int ）
AS
BEGIN
SET NOCOUNT ON ;
声明 @ num int ， @ dd int ， @ mm < span class =code-keyword> int ， @ length int ， @ sub nvarchar （ 60 ）;
声明 @ Depot_No nvarchar （ 50 ）， @ tq int ;
SELECT @ dd = CONVERT （ int ，DATEPART（MONTH，GETDATE（）））;
选择 @ mm = CONVERT （ int ，DATEPART（DAY，GETDATE（）））;
选择 @ num = MAX（Receipt_ID）来自 dbo.m_Depot_Receipt_Register
print @ num ;
if （ @ dd < = 31 和 @ mm < = 9 ）
开始
如果（ @ num = 1 ）
set < span class =code-sdkkeyword> @ Depot_No = CONVERT （ char （ 1 ）， @ num ）+ ' - ' + CONVERT （ char ， @ Total_No_Qty ）;
其他
开始
选择 @ sub = SUBSTRING（rtrim（ @ Depot_No ），CHARINDEX（' - '， @ Depot_No ）+ 1， 51 ）来自 m_Depot_Receipt_Register 其中 Receipt_ID = @民;
print @ sub ;
选择 @ tq = CONVERT （ int ， @ sub ）+ 1;
set @ Depot_No = CONVERT （ CHAR ， @ tq ）+ ' - ' + CONVERT （ char ， （@ TQ-1 + @ Total_No_Qty））;
print @ Depot_No ;
end ;
end ;
更新 m_Depot_Receipt_Register SET Depot_No = @ Depot_No WHERE Receipt_ID = @ Receipt_ID;
END
这有什么问题。
请帮助如果有其他逻辑，请提供。
制表减少
分配给@dd和@mm变量的值错误，分配给@dd变量的月值和@mm的日期值可能会失败IF条件请检查一次
< blockquote>看看例子：
- 声明变量，类型：表
DECLARE @ tmp TABLE （depot_no INT ，arr_date DATETIME ，depot_code INT ）
- 插入样本数据
INSERT INTO @ tmp （depot_no，arr_date，depot_code ）
VALUES （ 250 ，' 2012-02-28'， 2 ），（ 1550 ，' 2013-11-30'， 1 ）
- 声明变量：新的财务日期
DECLARE @ nfd DATETIME = ' 2013-10-01'
- 声明变量：depot_code
DECLARE @ dc INT = 1
- 声明变量：到达日期
DECLARE @ ad DATETIME = GETDATE（）
- 声明变量：方框数
DECLARE @cob INT = 100
- 在第一辆卡车之前
SELECT < span class =code-digit> 0 AS CountOfBoxes，MAX（depot_no） AS ActualMaxOfDepotNo
FROM @ tmp
WHERE arr_date BETWEEN @ nfd AND @ ad AND depot_code = @ dc
- 第一辆卡车
; WITH Dptnos AS
（
- 获取初始值
SELECT COALESCE （MAX（depot_no）， 0 ）+ 1 AS depot_no， 1 AS MyCounter
FROM @ tmp
WHERE ar r_date BETWEEN @ nfd AND @ ad AND depot_code = @ dc
UNION ALL
- 选择下一个depot_no直到计数框
SELECT depot_no +1 AS depot_no，MyCounter +1 AS MyCounter
FROM Dptnos
WHERE MyCounter + 1< = @ cob
）
INSERT INTO @ tmp （depot_no，arr_date，depot_code）
SELECT depot_no， @ ad ， @ dc
FROM Dptnos
OPTION （MAXRECURSION 0 ）
SELECT @ cob AS CountOfBoxes，MAX（depot_no） AS ActualMaxOfDepotNo
FROM @ tmp
WHERE arr_date BETWEEN @ nfd AND @ ad AND depot_code = @ dc
- 第二辆卡车包含300箱
SET @cob = 300
; WITH Dptnos AS
（
SELECT COALESCE （MAX（ depot_no）， 0 ）+ 1 AS depot_no， 1 AS MyCounter
FROM @ tmp
WHERE arr_date BETWEEN @ nfd AND @ ad AND depot_code = @ dc
UNION ALL
SELECT depot_no +1 AS depot_n o，MyCounter +1 AS MyCounter
FROM Dptnos
WHERE MyCounter + 1< = @ cob
）
INSERT INTO @ tmp （depot_no，arr_date，depot_code）
SELECT depot_no， @ ad ， @ dc
FROM Dptnos
OPTION （MAXRECURSION 0 ）
SELECT @ cob AS CountOfBoxes，MAX（depot_no） AS ActualMaxOfDepotNo
FROM @ tmp
WHERE arr_date BETWEEN @ nfd AND @ ad AND depot_code = @ dc < br $>
以上查询回收3套：
COB ActualMaxOfDeptoNo
0 1550 - >之前1.卡车抵达
100 1650 - >在1. truk到达之后，100 dept_no已被添加
300 1950 - > 2.卡车到达后，已经添加了300 dept_no
这就是你想要的吗？
hi,
in my project i have a table in which there is column name total_qty,taking value from this i have to generate value for another column named Depot_No.
The logic is a truck is arrived in depot having some material in it ,
there is Total _Qty column in table in which material's total_Qty is inserted.
for example for the first record total_qty is 200 then i have to issue Depot_No value 1-200.
and if for the next record total_qty is 300 then i have to generate Depot_No value 201-300 ,
and for the next record if total_qty is 100 then i have to generate Depot_No value 301-400 ,
like this every time a record is inserted i have to add total_qty value in last record code_No.
-There are different depots which have unique Depot_Codes.
- this Depot_No is unique for each Depot_Code and starts from 1 for each depot_Code and
-and every year from 1st of October this Depot_No restart from 1 for each Depot_code
i have created a procedure for this but its not working properly:
create PROCEDURE [dbo].[SP_m_Receipt_GenerateDepotNo](@Receipt_ID bigint,@Vechicle_Arrival_Date datetime,@Total_No_Qty int)
AS
BEGIN
SET NOCOUNT ON;
declare @num int,@dd int,@mm int,@length int,@sub nvarchar(60);
declare @Depot_No nvarchar(50),@tq int;
SELECT @dd = CONVERT(int,DATEPART(MONTH, GETDATE()));
select @mm = CONVERT(int,DATEPART(DAY, GETDATE())) ;
select @num = MAX(Receipt_ID) from dbo.m_Depot_Receipt_Register
print @num;
if(@dd <= 31 and @mm <= 9 )
begin
if (@num = 1)
set @Depot_No = CONVERT(char(1),@num) + '-' + CONVERT(char,@Total_No_Qty);
else
begin
select @sub = SUBSTRING(rtrim(@Depot_No),CHARINDEX('-', @Depot_No)+1,51) from m_Depot_Receipt_Register where Receipt_ID=@Num;
print @sub;
select @tq = CONVERT(int,@sub)+1;
set @Depot_No = CONVERT(CHAR,@tq) + '-' + CONVERT(char,(@tq-1+@Total_No_Qty));
print @Depot_No;
end;
end;
UPDATE m_Depot_Receipt_Register SET Depot_No =@Depot_No WHERE Receipt_ID=@Receipt_ID;
END
what is wrong in this.
please help and if there is another logic please provide.
tabulation reduced
Wrong values assigned to @dd and @mm variables, Month value assigned to @dd variable and Date value to @mm which may fail the IF condition please check once
Have a look at example:
--declare variable, type: table DECLARE @tmp TABLE (depot_no INT, arr_date DATETIME, depot_code INT) --insert sample data INSERT INTO @tmp (depot_no, arr_date, depot_code) VALUES(250, '2012-02-28',2),(1550, '2013-11-30',1) --declare variable: new financial date DECLARE @nfd DATETIME = '2013-10-01' --declare variable: depot_code DECLARE @dc INT = 1 --declare variable: arrival date DECLARE @ad DATETIME =GETDATE() --declare variable: Count of boxes DECLARE @cob INT = 100 --before first truck SELECT 0 AS CountOfBoxes, MAX(depot_no) AS ActualMaxOfDepotNo FROM @tmp WHERE arr_date BETWEEN @nfd AND @ad AND depot_code = @dc --first truck ;WITH Dptnos AS ( --get initial value SELECT COALESCE(MAX(depot_no),0)+1 AS depot_no, 1 AS MyCounter FROM @tmp WHERE arr_date BETWEEN @nfd AND @ad AND depot_code = @dc UNION ALL --select next depot_no till the count of boxes SELECT depot_no +1 AS depot_no, MyCounter +1 AS MyCounter FROM Dptnos WHERE MyCounter+1<=@cob ) INSERT INTO @tmp (depot_no, arr_date, depot_code) SELECT depot_no, @ad, @dc FROM Dptnos OPTION(MAXRECURSION 0) SELECT @cob AS CountOfBoxes, MAX(depot_no) AS ActualMaxOfDepotNo FROM @tmp WHERE arr_date BETWEEN @nfd AND @ad AND depot_code = @dc --second truck contains 300 boxes SET @cob = 300 ;WITH Dptnos AS ( SELECT COALESCE(MAX(depot_no),0)+1 AS depot_no , 1 AS MyCounter FROM @tmp WHERE arr_date BETWEEN @nfd AND @ad AND depot_code = @dc UNION ALL SELECT depot_no +1 AS depot_no, MyCounter +1 AS MyCounter FROM Dptnos WHERE MyCounter+1<=@cob ) INSERT INTO @tmp (depot_no, arr_date, depot_code) SELECT depot_no, @ad, @dc FROM Dptnos OPTION(MAXRECURSION 0) SELECT @cob AS CountOfBoxes, MAX(depot_no) AS ActualMaxOfDepotNo FROM @tmp WHERE arr_date BETWEEN @nfd AND @ad AND depot_code = @dc
Above query returs 3 sets:
COB ActualMaxOfDeptoNo 0 1550 -> before 1. truck arrived 100 1650 -> after 1. truk arrived, 100 dept_no have been added 300 1950 -> after 2. truck arrived, 300 dept_no have been added
Is that what you want?
这篇关于如何从其他表列中获取值来为表列生成值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
