Joining 4 Tables (Sales with current stock on hand) displayed

  • louislsh

    Old Hand

    Points: 358

    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

    Untitled-1 copy

    W4

    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.

    • This topic was modified 2 months ago by  louislsh.
    • This topic was modified 2 months ago by  louislsh.
    • This topic was modified 2 months ago by  louislsh.
    • This topic was modified 2 months ago by  louislsh. Reason: Add images
    Attachments:
    You must be logged in to view attached files.
  • Grant Fritchey

    SSC Guru

    Points: 396716

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Collins

    Ten Centuries

    Points: 1105

    from DAILYSALES DS
    left join DISCHDR as DH on DS.INTOFFERCODE=DH.CTRCODE
    or DS.INTDISCOUNTCODE=DH.CTRCODE
    or DS.INTSALESDISCOUNTCODE=DH.CTRCODE

    It'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

  • louislsh

    Old Hand

    Points: 358

    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.

  • louislsh

    Old Hand

    Points: 358

    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.

  • louislsh

    Old Hand

    Points: 358

    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.

  • louislsh

    Old Hand

    Points: 358

    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

    W6

  • Steve Collins

    Ten Centuries

    Points: 1105

    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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply