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...

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.

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...

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...

Thank you very much! it's working now.

I am able to show my report for those stocked but not show within a date range.

Really very very grateful for your kind help.

Hi Everyone!

I did a search and amend my code to

SUM([DBLSTOCKONHAND]) OVER(PARTITION BY STRSTORECODE,IT.STRFAMILYCODE) AS [Family Level Qty],

It's working now, thank you everyone and have a great day! Cheers to such...

Thank you Mark and everyone who replied, many thanks for all the help.

I am now using this code

select

STRSTORECODE as STORE,

IT.STRXREFCODE as ARTICLE,

IT.STRFAMILYCODE as FAMILY,

DBLSTOCKONHAND as [Article Level Qty],

SUM([DBLSTOCKONHAND]) OVER(PARTITION BY...

Sorry for the double post.

Gotten a timeout message and thought it didn't went through.

Thank you Jonathan, it works like a charm.

Am very greatful and thanks for sharing your knowledge.

I'll learn from this and hopefully can give back to the community as much as...

select

CT.LINTCUSTOMERNUM as CUSTID,

SUM(ACD.CURAMOUNT) as [TOTAL AMOUNT],

CASE when ACD.DTMTRANS < '2018-12-31' and ACD.STRTYPE='SALE' then SUM(ACD.CURDEBIT) Else '0' END as EARNED,

CASE when ACD.DTMTRANS < '2018-12-31' and ACD.STRTYPE='PAY' then SUM(ACD.CURCREDIT) Else '0' END...