Joining 4 Tables (Sales with current stock on hand) displayed
-
Dear all, I am trying to join 4 tables (Dailysale table, Discount Header Tabler, Item Table and Storerng [Stock on hand] table )
Not been able to correctly display the SOH.
Dailysale contains daily sales transaction while Storerng contains the stock on hand
I am using the below query
select
DS.DTMTRADEDATE as [DATE],
DS.INTSALEID as RECEIPT,
IT.STRXREFCODE as ARTICLE,
(sum(case when strsaletype='I' then convert (decimal(5,0),ds.DBLQTYSOLD) else null end)) as [QTY SOLD],
(sum(case when strsaletype='I' then convert(decimal(8,2),CURFOREIGNAMT) else null end)) as [GROSS AMT],
(max(case when strsaletype='I' then convert(decimal(8,2),CURSELLPRICE1) else null end)) as [UNIT PRICE],
max(case when strsaletype='W' then DH.STRPOSDESCRIPTION else NULL end) as [DISCOUNT NAME]
--SR.DBLSTOCKONHAND as SOH
from DAILYSALES DS
left join DISCHDR as DH on DS.INTOFFERCODE=DH.CTRCODE or DS.INTDISCOUNTCODE=DH.CTRCODE or DS.INTSALESDISCOUNTCODE=DH.CTRCODE
left join ITEM as IT on DS.LINTITEMNUMBER=IT.LINTITEMNUMBER
--left join STORERNG as SR on DS.STRTRADECODE=SR.STRSTORECODE or DS.LINTITEMNUMBER=SR.LINTITEMNUMBER or IT.LINTITEMNUMBER=SR.LINTITEMNUMBER
where DS.STRTRADECODE='YY07'
and DS.DTMTRADEDATE='2020-07-28'
and DS.STRSALETYPE in ('I','W')
group by DS.DTMTRADEDATE,DS.INTSALEID,IT.STRXREFCODE
order by IT.STRXREFCODE
Unable to get SOH, which is the stock on hand for the particular Article by Storecode in.
The storecode in attached Storerng.xls only contains YY07 but in my DB, there are multiple stores like YY01, YY02, YY03 etc..
So my purpose of this report is to show those article sold, with their current stock on hand displayed.
Thank you very much.
-
Which tables are these from:
and DTMTRADEDATE='2020-07-28'
and STRSALETYPE in ('I','W')If those columns are in any of the tables where you're doing a LEFT JOIN, you should move these criteria up to the JOIN criteria. Otherwise, you're effectively making this into an INNER JOIN.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
from DAILYSALES DS
left join DISCHDR as DH on DS.INTOFFERCODE=DH.CTRCODE
or DS.INTDISCOUNTCODE=DH.CTRCODE
or DS.INTSALESDISCOUNTCODE=DH.CTRCODEIt's a strange bit of code imo. Is there something which enforces the DH.CTRCODE only appears in one column (of the 3 being joined on) in DAILYSALES? If more than 1 condition is true there would be row expansion afaik, I'm not sure tho... Shouldn't this be 3 separate left joins and then use logic to coalesce? The code commented out has a similar 'OR in JOIN condition' pattern. Could the xlsx be reposted as .txt or just embed in the forum?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Sorry, i edited the code. Now it's showing they are from DS table which is Dailysale.
Also my title is Joing 2 tables but i have total of 4 in my code.
-
DISCHDR is the discount header table which contains the discount description.
we have different type of discounts (Offer discount/sales discount/tender discount) stored in different columns, so i'm doing this code to tie back to the discount description to show.
-
I have just added an image to simplify the content of the 4 tables and what i'm trying to do, hopefully it's much clearer this way.
Thank you everyone.
-
Now i achieved by being very specifc in the query.
I want to do away with the below boxed and show the result based on the condition of Dailysales table.
Dailysales table already has the Storecode and Lintitemnumber
-
Maybe the short answer is to uncomment out the line:
--SR.DBLSTOCKONHAND as SOH
and uncomment out the FROM clause and change it to:
--left join STORERNG as SR on DS.STRTRADECODE=SR.STRSTORECODE and DS.LINTITEMNUMBER=SR.LINTITEMNUMBER
No sample data so... that might work It's still a strangely written query. The OR condition in joins is permissible but it's not a good habit imo. This example shows the usually unintended consequence of OR in joins. When the table dbo.TestMultiOr contains more than one c_id in columns code1, code2, code3, a row is selected for each TRUE condition.
drop table if exists dbo.TestCodes;
go
create table dbo.TestCodes(
c_id int identity(1, 1) constraint pk_c_id primary key not null,
amount int not null,
code varchar(12) not null);
go
insert dbo.TestCodes(amount, code) values
(10, 'Get 10'),
(20, 'Get 20'),
(100, 'Free!');
--select * from dbo.TestCodes;
drop table if exists dbo.TestMultiOr;
go
create table dbo.TestMultiOr(
mo_id int identity(1, 1) constraint pk_mo_id primary key not null,
qty int not null,
code1_id int not null /*references dbo.TestCodes(c_id)*/,
code2_id int not null /*references dbo.TestCodes(c_id)*/,
code3_id int not null /*references dbo.TestCodes(c_id)*/);
go
insert dbo.TestMultiOr(qty, code1_id, code2_id, code3_id) values
(50, 1, 2, 3),
(100, 1, 2, 0),
(500, 0, 0, 0);
select * from dbo.TestMultiOr;
select * from dbo.TestMultiOr m left join dbo.TestCodes c on m.code1_id=c.c_id or m.code2_id=c.c_id or m.code3_id=c.c_id;
with summary_cte as (
select * from dbo.TestMultiOr m left join dbo.TestCodes c on m.code1_id=c.c_id or m.code2_id=c.c_id or m.code3_id=c.c_id)
select mo_id, min(qty) min_qty, count(*) cnt_rows, sum(qty) sum_qty
from summary_cte
group by mo_id;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
This is really useful, thanks. I've just started getting into crypto and forex trading. I've just gotten started with stock trading. I've been into crypto for quite a while, but with the pandemic happening, it seemed like the perfect time, and also I have a lot of friends that do it, so why not, right? I started with a leverage trading which the easiest way honestly, especially if you have no idea about this niche of investing. I don't know why, but I find it to be a lot more fun than crypto, not sure what that's all about, maybe because it's something new for me, but I'm excited each day to see how the market is doing. All my increases are thanks to the trading tool that I use, honestly most interesting thing I've done all year.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply