Hi all, I have a table called DataTable and I have to extract the number of rows for each Area type
This is the Table Script and sample data
CREATE TABLE DataTable
(`Data_NId` int, `IUSNId` int, `Area_Name` varchar(18), `TimePeriod` varchar(9), `Data_Value` int)
;
INSERT INTO DataTable
(`Data_NId`, `IUSNId`, `Area_Name`, `TimePeriod`, `Data_Value`)
VALUES
(74495, 133, 'Pashchim Champaran', '2010-2011', 73.2),
(74496, 133, 'Kishanganj', '2010-2011', 71.9),
(74497, 133, 'Katihar', '2010-2011', 75.9),
(74498, 133, 'Saharsa', '2010-2011', 76.9),
(74499, 133, 'Darbhanga', '2010-2011', 54.4),
(74500, 133, 'Muzaffarpur', '2010-2011', 82.2),
(74501, 133, 'Gopalganj', '2010-2011', 68.3),
(74502, 133, 'Siwan', '2010-2011', 73.4),
(74503, 133, 'Saran', '2010-2011', 56.4),
(74504, 133, 'Vaishali', '2010-2011', 81.4),
(74505, 133, 'Khagaria', '2010-2011', 84.8),
(74506, 133, 'Bhagalpur', '2010-2011', 62.4),
(74507, 133, 'Munger', '2010-2011', 87.1),
(74508, 133, 'Lakhisarai', '2010-2011', 67.2),
(74509, 133, 'Sheikhpura', '2010-2011', 78.5),
(74510, 133, 'Nalanda', '2010-2011', 62.6),
(74511, 133, 'Patna', '2010-2011', 88.5),
(74512, 133, 'Bhojpur', '2010-2011', 88),
(74513, 133, 'Buxar', '2010-2011', 69.8),
(74514, 133, 'Rohtas', '2010-2011', 75.6),
(74515, 133, 'Aurangabad', '2010-2011', 76.7),
(74516, 133, 'Gaya', '2010-2011', 67.1),
(74517, 133, 'Nawada', '2010-2011', 75.6),
(74518, 133, 'Jehanabad', '2010-2011', 83.7),
(74528, 133, 'Pashchim Champaran', '2011-2012', 74.1),
(74529, 133, 'Kishanganj', '2011-2012', 82),
(74530, 133, 'Katihar', '2011-2012', 80),
(74531, 133, 'Saharsa', '2011-2012', 81.5),
(74532, 133, 'Darbhanga', '2011-2012', 59.9),
(74533, 133, 'Muzaffarpur', '2011-2012', 79.4),
(74534, 133, 'Gopalganj', '2011-2012', 78.7),
(74535, 133, 'Siwan', '2011-2012', 77),
(74536, 133, 'Saran', '2011-2012', 62.3),
(74537, 133, 'Vaishali', '2011-2012', 86.7),
(74538, 133, 'Khagaria', '2011-2012', 85.9),
(74539, 133, 'Bhagalpur', '2011-2012', 76.9),
(74540, 133, 'Munger', '2011-2012', 81.4),
(74541, 133, 'Lakhisarai', '2011-2012', 75.8),
(74542, 133, 'Sheikhpura', '2011-2012', 84.7),
(74543, 133, 'Nalanda', '2011-2012', 68.7),
(74544, 133, 'Patna', '2011-2012', 88.4),
(74545, 133, 'Bhojpur', '2011-2012', 86.4),
(74546, 133, 'Buxar', '2011-2012', 73.4),
(74547, 133, 'Rohtas', '2011-2012', 77.2),
(74548, 133, 'Aurangabad', '2011-2012', 75.7),
(74549, 133, 'Gaya', '2011-2012', 66.3),
(74550, 133, 'Nawada', '2011-2012', 75.1),
(74551, 133, 'Jehanabad', '2011-2012', 80.7),
(74561, 133, 'Pashchim Champaran', '2012-2013', 79.1),
(74562, 133, 'Kishanganj', '2012-2013', 81.9),
(74563, 133, 'Katihar', '2012-2013', 83.3),
(74564, 133, 'Saharsa', '2012-2013', 87),
(74565, 133, 'Darbhanga', '2012-2013', 64.4),
(74566, 133, 'Muzaffarpur', '2012-2013', 83.7),
(74567, 133, 'Gopalganj', '2012-2013', 83.4),
(74568, 133, 'Siwan', '2012-2013', 76.7),
(74569, 133, 'Saran', '2012-2013', 64.9),
(74570, 133, 'Vaishali', '2012-2013', 78.4),
(74571, 133, 'Khagaria', '2012-2013', 87.6),
(74572, 133, 'Bhagalpur', '2012-2013', 78),
(74573, 133, 'Munger', '2012-2013', 84.8),
(74574, 133, 'Lakhisarai', '2012-2013', 83.5),
(74575, 133, 'Sheikhpura', '2012-2013', 82.4),
(74576, 133, 'Nalanda', '2012-2013', 76.2),
(74577, 133, 'Patna', '2012-2013', 89.7),
(74578, 133, 'Bhojpur', '2012-2013', 86.9),
(74579, 133, 'Buxar', '2012-2013', 76.3),
(74580, 133, 'Rohtas', '2012-2013', 81.5),
(74581, 133, 'Aurangabad', '2012-2013', 73.3),
(74582, 133, 'Gaya', '2012-2013', 70.5),
(74583, 133, 'Nawada', '2012-2013', 79.2),
(74584, 133, 'Jehanabad', '2012-2013', 78.4)
This is what I’m trying to get:
Data_NId IUSNId Area_Name TimePeriod Data_Value P_Cange Trend
74496 133 Kishanganj 2010-2011 71.9 -0.1 N
74497 133 Katihar 2010-2011 75.9 3.3 P
74498 133 Saharsa 2010-2011 76.9 5.5 P
74499 133 Darbhanga 2010-2011 54.4 4.5 P
74500 133 Muzaffarpur 2010-2011 82.2 4.3 P
74501 133 Gopalganj 2010-2011 68.3 4.7 P
74502 133 Siwan 2010-2011 73.4 -0.3 N
74503 133 Saran 2010-2011 56.4 2.6 P
74504 133 Vaishali 2010-2011 81.4 -8.3 N
74505 133 Khagaria 2010-2011 84.8 1.7 P
74495 133 Pashchim Cha... 2010-2011 73.2 5 P
74506 133 Bhagalpur 2010-2011 62.4 1.1 P
74507 133 Munger 2010-2011 87.1 3.4 P
74508 133 Lakhisarai 2010-2011 67.2 7.7 P
74509 133 Sheikhpura 2010-2011 78.5 -2.3 N
74510 133 Nalanda 2010-2011 62.6 7.5 P
74511 133 Patna 2010-2011 88.5 1.3 P
74512 133 Bhojpur 2010-2011 88 0.5 P
74513 133 Buxar 2010-2011 69.8 2.9 P
74514 133 Rohtas 2010-2011 75.6 4.3 P
74515 133 Aurangabad 2010-2011 76.7 -2.4 N
74516 133 Gaya 2010-2011 67.1 4.2 P
74517 133 Nawada 2010-2011 75.6 4.1 P
74518 133 Jehanabad 2010-2011 83.7 -2.3 N
What I have tried:
alter PROCEDURE data
@iusnid int = 133, @sourcenid int = 124, @arealevel int = 3, @timeperiodnid int = 64
,@toptime int = 0, @Stoptime int = 0
AS
BEGIN
SET NOCOUNT ON;
set @toptime = ( select max(TimePeriod_NId) from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel)
set @Stoptime = (select MAX(TimePeriod_NId) from View_5 where TimePeriod_NId < ( select max(TimePeriod_NId) from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel ) and IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel )
select *
from View_5
where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel