T-SQL:- query to find latest and second latest data in table by locations

Question

0.00/5 (No votes)

See more:

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

SQL

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:

SQL

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 *
	 --hi = (select Data_value from View_5 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel and TimePeriod_NId = @toptime) ,
	 
	 -- lo=(select Data_value from View_5 where IUSNId= iusnid and Source_NId = @sourcenid and Area_Level = @arealevel and TimePeriod_NId = @Stoptime) 
	 
	 
	  from View_5
	 where IUSNId= @iusnid and Source_NId = @sourcenid and Area_Level = @arealevel

Posted 4-Apr-17 7:13am

deonandan

Updated 3-May-17 6:41am

CHill60

v6

Add a Solution

Comments

CHill60 4-Apr-17 13:52pm

The problem is that the stuff you have tried does not access the table that you have given us.
Edit - and the title of the question doesn't match the post. What are you actually trying to achieve?

ZurdoDev 4-Apr-17 14:07pm

It is hard to understand what you are asking. There is no need to post 48 lines of expected output. Just put a couple to give the idea of what you need. Just dumping code here makes it hard to filter through and help you.

deonandan 5-Apr-17 20:14pm

these are three set of data for every region for three time period.
1. find the highest time period
2. get data value of that time period
3. find the second highest time period
4. get data value of that time period
5. subtract the highest time period data from the lowest time period data
6. combine in table such a way that it contain the data of given time period along with the latest trend in data change.
HOW i got this ?????

CHill60 5-Apr-17 3:56am

Now I can see your expected results more clearly can you explain how you expect P_Cange to be calculated and what Trend is?

deonandan 5-Apr-17 19:45pm

for the Data_Nid = 74496

P_Change = 81.9 - 82 = -0.1
or
P_Change = Data_Value at highest TimePeriod(2012-2013) - Data_Value at Second Highest TimePeriod(2011-2012)
Trend is nothing but value negative or positive
In another way our required query generate two information first the data of given time frame and second the latest changes in data in new column.
Thanks

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Asad Mahmood Qammar · Answer 1 · 2017-05-03T06:41:00

select case when (maxDataTable.Data_Value-SecMaxDataTable.Data_Value)>0 then 'P' else 'N' end as Trend,
(maxDataTable.Data_Value-SecMaxDataTable.Data_Value) as P_Change ,maxDataTable.TimePeriod ,  maxDataTable.Area_Name,Data_NId ,IUSNId from 
(Select Data_Value,TimePeriod ,  Area_Name,Data_NId ,IUSNId  from DataTable 
where   TimePeriod = (select max(TimePeriod) from DataTable)) as maxDataTable
inner join
(
Select Data_Value, TimePeriod,  Area_Name from DataTable 
where   TimePeriod = (select max(TimePeriod) from DataTable where TimePeriod<(select max(TimePeriod) from DataTable))
) as SecMaxDataTable on maxDataTable.Area_Name=SecMaxDataTable.Area_Name

hi Deonadan,
I hope this will resolve your problem.

T-SQL:- query to find latest and second latest data in table by locations

1 solution

Solution 1

Add your solution here

Preview 0

T-SQL:- query to find latest and second latest data in table by locations

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us