InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 18:43:58 GMT20http://www.sqlservercentral.com/Forums/Topic1372437-338-1.aspx[quote][b]scottichrosaviakosmos (10/15/2012)[/b][hr]yes satatus is int.the value should be status 1 and what ever the out will be it should be of status 1.[/quote]That's great. Can you answer the rest of it?[quote]Can you explain the logic for the output? It seems that your description and your posted desired output don't match. You said you want cropid where the status is 1 but in your output you have cropids 1,6,7. The IDs where status =1 is 1,2,7What is the logic for nextcropid and nextamtdiff????[/quote]Mon, 15 Oct 2012 12:46:12 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1372437-338-1.aspxyes satatus is int.the value should be status 1 and what ever the out will be it should be of status 1.Mon, 15 Oct 2012 12:37:23 GMTscottichrosaviakosmoshttp://www.sqlservercentral.com/Forums/Topic1372437-338-1.aspxThanks for the ddl and sample data. I assume that the status column in cropresults should be a datatype int?Can you explain the logic for the output? It seems that your description and your posted desired output don't match. You said you want cropid where the status is 1 but in your output you have cropids 1,6,7. The IDs where status =1 is 1,2,7What is the logic for nextcropid and nextamtdiff????Mon, 15 Oct 2012 08:16:11 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1372437-338-1.aspxsorry , there is a little correction: the below table will have cropid instead of date.create table amountdifference( cropid int, productid int,amtdiff float) insert into amountdifference select 1,100,200.00 union select 1,101,210.00 union select 2,100,110.00 union select 2,101,130.00 union select 6,100,230.00Sat, 13 Oct 2012 11:18:20 GMTscottichrosaviakosmoshttp://www.sqlservercentral.com/Forums/Topic1372437-338-1.aspxI have 5 tables 1) cropid( this is the id for each time my procedure runs) create table croprerults( cropid int, cropdate varchar(20),status )--status 1 means enable insert into croprerults select 1,20120101,1 union select 2,20120102,1 union select 6,20120103,2 union select 7,20120105,1 2) productrelation(used for making hierarchy of product: create table productrelation( cropid int, productid int,parentproductid int) insert into productrelation select 1,100,102 union select 1,102,101 union select 2,100,102 union select 2,102,103 union select 6,100,102 union select 3,102,101 3) Salesaccount(will have sales values of the product with respect to cropid) create table Salesaccount( cropid int, productid int,salesamount float) insert into Salesaccount select 1,100,100.00 union select 1,101,110.00 union select 1,102,110.00 union select 2,102,130.00 union select 2,100,110.00 union select 7,102,300.00 --there is no 101 product4) amountdifference(this will have the amount diff for products from previous day) create table amountdifference( cropdate varchar(20), productid int,amtdiff float) insert into amountdifference select 20120101,100,200.00 union select 20120101,101,210.00 union select 20120102,100,110.00 union select 20120102,101,130.00 union select 20120103,100,230.00 --there is no 102 productproblem is that there are some cropid which is present in salesaccount table but not in amountdifference table(cropid=cropdate).there are some productid which are present in salesaccount but not in amountdifference for same cropdate(cropid).this goes viceversa also.there are some cropid which may be not present saleaamount but present in cropresults.I want the resultset to take all cropid with status=1 and all productid which are present in productrelation table.So if the value from amtdiff for the product for a cropid is null then it should show 0(Zero) for that.Also I want the desired output to get a column "nextamtdiff" which will have amtdiff for a productid on its next cropid.final desired output: create table finaloutput(cropid int, productid int, cropdate varchar(20), salesamount float,amtdiff float,nextcropid int,nextamtdiff float) insert into finaloutput select 1,100,20120101,100.00,200.00,2,110.00 union select 1,101,20120101,0,210.00,2,130.00 union select 6,102,20120103,0,230.00,7,0 union select 7,102,20120105,300.00,0,null,null ---since no next cropid in master table cropresultscurrently i have a query using CTE but not getting desired result and query working a bit slow. So would like to have a query with CTE with data of only cropid which have status=1(i know that possible and best option).Sat, 13 Oct 2012 11:02:43 GMTscottichrosaviakosmos