Project

General

Profile

Feature #4015 » PRPTAIDATA_20251120.sql

Tri Rizqiaty, 11/21/2025 02:23 PM

 
1
??ALTER PROCEDURE [dbo].[PRPTAIDATA] 
2

3
(
4

5
	@SelectionID VARCHAR(20)
6

7
	,@Year VARCHAR(20)
8

9
	,@Month VARCHAR(20)
10

11
)
12

13
AS
14

15

16

17
--DECLARE @SelectionID VARCHAR(20) = '0001'
18

19
--DECLARE @Year VARCHAR(20) = ''
20

21
--DECLARE @Month VARCHAR(20) = ''
22

23

24

25
DECLARE @TblRefAI VARCHAR(200)
26

27
SELECT @TblRefAI = TableRefAI FROM AI_SELECTIONS
28

29
WHERE Selection_ID = @SelectionID
30

31

32

33
DECLARE @TblField TABLE ( FieldName VARCHAR(250), SeqNum DECIMAL(18,0))
34

35
INSERT INTO @TblField
36

37
SELECT FieldName, SeqNum FROM dbo.SDATATABLEFIELD WHERE TableName = @TblRefAI ORDER BY SeqNum
38

39

40

41
DECLARE @StrAI VARCHAR(MAX)
42

43

44

45
DECLARE @TblDt TABLE
46

47
(
48

49
	SeqID DECIMAL(18,0)
50

51
	,Year VARCHAR(200)
52

53
	,Month VARCHAR(200)
54

55
	,SeqNum DECIMAL(18,0)
56

57
	,FieldName VARCHAR(200)
58

59
	,IsiField VARCHAR(MAX)
60

61
)
62

63

64

65
DECLARE @SeqID DECIMAL(18,0) = 0
66

67
DECLARE @Seq VARCHAR(250)
68

69
DECLARE @c_FieldName VARCHAR(250)
70

71
DECLARE @c_SeqNum VARCHAR(250)
72

73
DECLARE cur_tbl CURSOR FOR SELECT FieldName, SeqNum FROM @TblField WHERE FieldName NOT IN ('Year', 'Month')
74

75
OPEN cur_tbl
76

77
FETCH cur_tbl INTO @c_FieldName ,@c_SeqNum
78

79
WHILE @@Fetch_Status = 0
80

81
	BEGIN
82

83
		SET @SeqID = @SeqID + 1
84

85
		SET @StrAI = 'SELECT ROW_NUMBER() OVER(ORDER BY Year,Month ASC) AS SeqID, Year, '
86

87
					+ ' dbo.fn_formatdatetime((Year+Month+''01''),''mmmm'') AS Month, '
88

89
					+ '''' + @c_SeqNum + ''' AS SeqNum, '
90

91
					+ '''' + @c_FieldName + ''' AS FieldName, [' + @c_FieldName + '] FROM ' + @TblRefAI 
92

93
					+ ' WHERE (Year = ''' + @Year + ''' OR ''' + @Year + ''' = '''')'
94

95
					+ ' AND (Month = ''' + @Month + ''' OR ''' + @Month + ''' = '''')'
96

97
		--SELECT @StrAI
98

99
		INSERT INTO @TblDt
100

101
		EXEC(@StrAI)	
102

103
	FETCH cur_tbl INTO @c_FieldName ,@c_SeqNum  
104

105
	END
106

107
CLOSE cur_tbl
108

109
DEALLOCATE cur_tbl
110

111

112

113
SELECT * FROM @TblDt
114

115

(1-1/4)