Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

multi table match query Expand / Collapse
Author
Message
Posted Saturday, October 13, 2012 11:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
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).


Post #1372437
Posted Saturday, October 13, 2012 11:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
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
Post #1372438
Posted Monday, October 15, 2012 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
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 Moden's 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)
Post #1372752
Posted Monday, October 15, 2012 12:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:14 AM
Points: 105, Visits: 463
yes satatus is int.
the value should be status 1 and what ever the out will be it should be of status 1.
Post #1372891
Posted Monday, October 15, 2012 12:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
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 Moden's 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)
Post #1372900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse