October 13, 2012 at 11:02 am
I 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 product
4) 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 product
problem 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 cropresults
currently 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).
October 13, 2012 at 11:18 am
sorry , 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.00
October 15, 2012 at 8:16 am
Thanks 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,7
What is the logic for nextcropid and nextamtdiff????
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2012 at 12:37 pm
yes satatus is int.
the value should be status 1 and what ever the out will be it should be of status 1.
October 15, 2012 at 12:46 pm
scottichrosaviakosmos (10/15/2012)
yes satatus is int.the value should be status 1 and what ever the out will be it should be of status 1.
That's great. Can you answer the rest of it?
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,7
What is the logic for nextcropid and nextamtdiff????
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply