June 24, 2015 at 11:48 pm
create table #try(id int,sourceno int,destno int,quantity int)
insert into #try values(1, 100,null,10)
insert into #try values(2, 200,null,10)
insert into #try values(3, 300,200,20)
insert into #try values(4, 400,300,15)
insert into #try values(5, 500,400,40)
insert into #try values(6, 600,null,2)
insert into #try values(7, 700,null,4)
insert into #try values(8, 800,600,6)
I need the prior Max of Id. The prior max should be calculated based on source and destination keys.
Need the query to get output as below. Please help
Id,sourceno,destno,PriorMax
1,100,null,10
5,500,400,20
6,600,null,2
8,800,600,4
June 25, 2015 at 1:06 am
correction result should be
1,100,null,10
5,500,400,20
7,700,null,4
8,800,600,2
June 25, 2015 at 2:19 am
sreeram.sivakumar 61990 (6/25/2015)
correction result should be1,100,null,10
5,500,400,20
7,700,null,4
8,800,600,2
one rule i didn't understand
i-e
if 8,800,600,2 then 5,500,400,10 instead of 5,500,400,20
as per the sample data if destination no have source then go back to the parent record and consider it quantity.
is it correct?
June 25, 2015 at 3:02 am
sreeram.sivakumar 61990 (6/24/2015)
...The prior max should be calculated based on source and destination keys.
Please list the rules for the calculation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 25, 2015 at 4:15 pm
Yes if there is sourceno then you need to get its quantity till it reaches null. and then calculate the prior max for the lastest ID.
June 26, 2015 at 12:35 am
sreeram.sivakumar 61990 (6/25/2015)
Yes if there is sourceno then you need to get its quantity till it reaches null. and then calculate the prior max for the lastest ID.
so the corrected Output will be
correction result should be
1,100,null,10
5,500,400,10
7,700,null,4
8,800,600,2
is that right?
June 30, 2015 at 4:27 pm
The Result should be
1,100,null,10
5,500,400,20
7,700,null,4
8,800,600,2
June 30, 2015 at 11:32 pm
sreeram.sivakumar 61990 (6/30/2015)
The Result should be1,100,null,10
5,500,400,20
7,700,null,4
8,800,600,2
can you please explain why 5,500,400 have 20 quantity instead of 10? because if we apply the same rule on 8,800,600 then it should have quantity 6 instead of 2.
please list down your rules to desired output.
July 2, 2015 at 12:15 pm
insert into #try values(2, 200,null,10)
insert into #try values(3, 300,200,20)
insert into #try values(4, 400,300,15)
insert into #try values(5, 500,400,40)
we need to get prior max based on source and destno. for each sourceno we need to get the destno count and out of that we need to get the prior max of those.
in the above example.
destno for 500 is 400 so we need to get count of that row.but 400 also has destno as 300 so get the count of 300 source and 300 sourceno has 200 destno so get the count of 200 but 200 sourceno doesn't have destno
so we need to get the count and stop iterating.
the prior max count of 500 sourceno will be max of ( 15,20,10) count . We should not include sourceno 500 count in the calculation.
July 10, 2015 at 3:33 pm
Hello SQL Experts .. Any help on this is highly appreciated.
July 10, 2015 at 4:30 pm
What version of SQL does this need to work for? (You're posting in a SQL 2005 forum... is this correct?)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 12, 2015 at 11:49 pm
Sorry for the delay. Caught up in the work
Following is the query will give you the desire result. in case of any query/ question do let us know.
------------- Create Sample table
create table #try (id int,sourceno int,destno int,quantity int)
------------- Insert data
insert into #try values(1, 100,null,10)
insert into #try values(2, 200,null,10)
insert into #try values(3, 300,200,20)
insert into #try values(4, 400,300,15)
insert into #try values(5, 500,400,40)
insert into #try values(6, 600,null,2)
insert into #try values(7, 700,null,4)
insert into #try values(8, 800,600,6)
;
/************************************************************************************************
Used Jeff moden article "Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets"
URL : http://www.sqlservercentral.com/articles/Hierarchy/94040/
Should read it to understand the problem and the solution
*************************************************************************************************/
with cte
as
(
select a.id, a.sourceno, a.destno, a.quantity
, HLevel = 1
, SortPath = CAST(
CAST(a.sourceno AS BINARY(4))
AS VARBINARY(4000))
from #try a
where a.destno is null
union all
select a.id, a.sourceno, a.destno, a.quantity
, HLevel = cte.HLevel + 1
, SortPath = CAST( --This does the concatenation to build SortPath
cte.SortPath + CAST(a.sourceno AS BINARY(4))
AS VARBINARY(4000))
from #try a
inner join cte on a.destno = cte.sourceno
)
SELECT sourceno = ISNULL(sorted.sourceno,0),
sorted.destno,
HLevel = ISNULL(sorted.HLevel,0),
LeftBower = ISNULL(CAST(0 AS INT),0), --Place holder
RightBower = ISNULL(CAST(0 AS INT),0), --Place holder
NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
NodeCount = ISNULL(CAST(0 AS INT),0), --Place holder
SortPath = ISNULL(sorted.SortPath,sorted.SortPath),
ID = sorted.id,
quantity = sorted.quantity
INTO #Hierarchy
FROM cte AS sorted
OPTION (MAXRECURSION 100)
UPDATE #Hierarchy
SET LeftBower = 2 * NodeNumber - HLevel
;
with ctally
as
(
SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)
N = ISNULL(CAST(
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1
AS INT),0)
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
),
cteCountDownlines AS
( --=== Count each occurrence of EmployeeID in the sort path
SELECT sourceno = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
NodeCount = COUNT(*) --Includes current node
FROM #Hierarchy h,
ctally t
WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate the RightBower
UPDATE h
SET h.NodeCount = downline.NodeCount,
h.RightBower = (downline.NodeCount - 1) * 2 + LeftBower + 1
FROM #Hierarchy h
JOIN cteCountDownlines downline
ON h.sourceno = downline.sourceno
;
----- Once the hierarchies are build following query will do
----- Assuming that you are working on SQL SERVER 2005 and above.
select id, H.sourceno, H.destno, isnull(maxQuantity.quantity, h.quantity)
from #Hierarchy H
inner join
(
Select SUBSTRING(SortPath,1,4) as SortPath, max(HLevel) HLevel
from #Hierarchy H
group by SUBSTRING(SortPath,1,4)
) A on SUBSTRING(H.SortPath,1,4) = A.SortPath and H.HLevel = A.HLevel
Cross apply
(
select max(quantity) as quantity
from #Hierarchy HH
where SUBSTRING(H.SortPath,1,4) = SUBSTRING(HH.SortPath,1,4)
and HH.HLevel < a.HLevel --------- One previous
) maxQuantity
order by id
------ Cleanup
drop table #Hierarchy
drop table #try
Hope it helps.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply