SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


multi table match query


multi table match query

Author
Message
scottichrosaviakosmos
scottichrosaviakosmos
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 691
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).
scottichrosaviakosmos
scottichrosaviakosmos
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 691
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64662 Visits: 17979
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
scottichrosaviakosmos
scottichrosaviakosmos
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 691
yes satatus is int.
the value should be status 1 and what ever the out will be it should be of status 1.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64662 Visits: 17979
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search