Sales and Sales Return Sequence Display Format.

to better visualize your requirement, can you provide some sample data and the sample output respectively?

Hi Ryan,

Please check the image.


Thank you.

Tipe :

GR = Goods Received.
SJ = Sales
SR = Sales Return

Thank you.

sorry, I not really understand your requirements.

is your image the input or output?

to better visualize your requirement, can you provide some sample data and the sample output respectively?

Please loot at the latest 2 transaction.

If SR transaction is from SJ transaction then the display sequence should be SJ first.

Thank you.

how's your SQL currently looks like?

SELECT
A.ID
, A.TipeTransaksi AS Tipe
, RTRIM(A.NoTransaksi) AS [Nomor Transaksi]
, A.TglTransaksi AS Tanggal
, RTRIM(A.SerialNo) AS [Nomor Lot]
, CASE WHEN A.QtyTransaksi > 0 THEN A.QtyTransaksi ELSE 0 END AS [Debet]
, CASE WHEN A.QtyTransaksi > 0 THEN 0 ELSE A.QtyTransaksi END AS [Kredit]
, CAST(A.ItemUnitCost AS DECIMAL(18,6))  AS [HPP]
, RTRIM(A.NoTransaksiBeli) AS [Nomor Sumber HPP]
, ISNULL(A.IdStock, 0) AS [ID Sumber HPP]
, CAST(0 AS DECIMAL(18,6)) AS [HPP Baru]
, '' AS [Nomor Sumber HPP Baru]
, 0 AS [ID Sumber HPP Baru]
, CAST(0 AS BIT) AS [Hapus Cost]
FROM TMSTOKBARANGDETIL2018 A WITH (NOLOCK)
LEFT JOIN TMSTOKBARANGDETIL2018  B WITH (NOLOCK)
On A.IdStock = B.Id
WHERE A.BarangCode = 'CREPE 218 282'
AND A.GdgCode = 'GDGM2'
AND A.SerialNo = 'A HITAM - 00001'
AND  CONVERT(Char(8), A.TglTransaksi, 112) BETWEEN '20180101' AND '20181103'
ORDER BY A.SerialNo, A.TglTransaksi, A.CostNoSeq

Select allOpen in new window

Thank you.

what if trying:

ORDER BY A.SerialNo, A.TipeTransaksi, A.TglTransaksi, A.CostNoSeq

The Data :

ID	Tipe	Nomor Transaksi	Tanggal	Nomor Lot	Debet	Kredit	HPP	Nomor Sumber HPP	ID Sumber HPP	HPP Baru	Nomor Sumber HPP Baru	ID Sumber HPP Baru	Hapus Cost	CostNoSeq
321083	SR 	SJR-AU-2018-07-0017	2018-07-31 00:00:00.000	A HITAM - 00002	29.86	0.00	67727.272727	SJ-AU-2018-07-0141	300006	0.000000		0	0	3
321151	SJ 	SJ-AU-2018-07-0662	2018-07-31 00:00:00.000	A HITAM - 00002	0.00	-29.86	67727.272727	SJR-AU-2018-07-0017	321083	0.000000		0	0	9

Select allOpen in new window

Thank you.

A.TipeTransaksi :

1. GR (Goods Received)
2. SR (Sales Return)
3. SJ  (Sales)
4. SIA (Stock In Adjust)
5. SOA (Stock Out Adjust)
6. etc

We can not order by TipeTransaksi.

Thank you.

TipeTransaksi	CostNoSeq
BS 	1
GR 	2
SR 	3
SIA	4
STI	5
SJ 	11
BRM	12
SOA	14
STO	15

Select allOpen in new window

Thank you.

For Sales and Sales Return, we can not order by this.

ORDER BY A.SerialNo, A.TglTransaksi, A.CostNoSeq

It depends on which transaction should be arrived first for the same date.

Thank you.

what if trying:

ORDER BY A.SerialNo, A.CostNoSeq

Select allOpen in new window

or

ORDER BY A.SerialNo, A.CostNoSeq, A.TglTransaksi

Select allOpen in new window

?

- ORDER BY A.SerialNo, A.CostNoSeq, A.TglTransaksi
No, it depends on the relation transaction between NoTransaksi and NoTransaksiBeli.

NoTransaksi                     NoTransaksiBeli
SJR-AU-2018-07-0017
SJ-AU-2018-07-0662       SJR-AU-2018-07-0017

or
NoTransaksi                     NoTransaksiBeli
SJ-AU-2018-07-0662
SJR-AU-2018-07-0017       SJ-AU-2018-07-0662

Thank you.

No, it depends on the relation transaction between NoTransaksi and NoTransaksiBeli.

can you explain it a little bit further?

- can you explain it a little bit further?
Which one is the first, Sales or Sales Return.

NoTransaksi                     NoTransaksiBeli
SJR-AU-2018-07-0017
SJ-AU-2018-07-0662       SJR-AU-2018-07-0017

or

NoTransaksi                     NoTransaksiBeli
SJ-AU-2018-07-0662
SJR-AU-2018-07-0017       SJ-AU-2018-07-0662

NoTransaction = Transaction
NoTransaksiBeli = Relation of the Transaction

NoTransaksi                     NoTransaksiBeli
SJR-AU-2018-07-0017       SJ-AU-2018-07-0662

SJR-AU-2018-07-0017, return from Sales SJ-AU-2018-07-0662

or

SJ-AU-2018-07-0662       SJR-AU-2018-07-0017
SJ-AU-2018-07-0662, get stock from Sales Return SJR-AU-2018-07-0017

Thank you.

Part of the challenge is the DATE as to when the transactions happen.

I would imagine that returns can only ever happen after Sales, which in tuen can only happen when there is stock (or on backorder) and so, the sequence of tansactions on a given day do have a natural flow, so we need to lookup those exceptions to the natural flow. Always difficult (if not impossible)  to do without subquery or cross apply (or equivalent - maybe utilising a CTE).

So, if we could get a few transactions as data examples, along with expected results, it would help.

But not as screenshots, as an attachment (even Excel would work).

I am quite confidant we can help come up with a solution, just that it is going to be involved and need to run up queries to actually test.

And apart from being difficult, we havent let you down yet :)

Always enjoy your challenges - part of which is understanding across different languages (and mean that with the utmost respect)  :)

Hi Marks,

Glad to hear from you again.

Please see the attached file.

Thank you.
Sample-Data.xlsx

Revise, 2 scenarios.

Thank you.
Sample-Data.xlsx

Thanks - makes it a bit clearer....

In the second one (with two scenarios), I think there is an error in one of the transactions (on row 13)....

You have :

321151	SJ 	SJ-AU-2018-07-0662	31/07/2018	A HITAM - 00002	0	-29.86	67727.27273	SJR-AU-2018-07-0017	299529	9

Select allOpen in new window

And think it should be :

321151	SJ 	SJ-AU-2018-07-0662	31/07/2018	A HITAM - 00002	0	-29.86	67727.27273	GR 0078/YSL/07/18	299529	9

Select allOpen in new window

Could you please check and verify ?

Actually, just loaded the spreadsheets and it reflects the correction above.....

When there is a dependency, we see that in NoTransaksiBeli - right ?

In which case the NoTransaksi has to already exist for the NoTransaksiBeli to be able to refer to it.....

If ID's are always incremental (though cannot see where the GR NoTransaksiBeli is set to zero), then the correct ORDER BY would be :

ORDER BY A.SerialNo, A.TglTransaksi, A.NoTransaksiBeli

And a little 'trick' instead of / for CostNoSeq

ORDER BY A.SerialNo, A.TglTransaksi, A.NoTransaksiBeli,charindex(A.TipeTransaksi,'BS GR SR SIA STI SJ BRM SOA STO')

Otherwise, we need to follow though the dependencies of NoTransaksiBeli on NoTransaksi  and order them in sequence so we can sort on  that sequence....

Please let us know....

Hi Marks,

- In the second one (with two scenarios), I think there is an error in one of the transactions (on row 13)....
You are right, my mistakes.

GR 0078/YSL/07/18
SJ-AU-2018-07-0662      GR 0078/YSL/07/18
SJR-AU-2018-07-0017    SJ-AU-2018-07-0662
 
Thank you.

- Otherwise, we need to follow though the dependencies of NoTransaksiBeli on NoTransaksi  and order them in sequence so we can sort on  that sequence....
Yes, should make new sequence order, but I have no idea how to do it.

Thank you.

Hi Marks,

- It does work for the two scenarios in your spreadsheet.
Yes, it works. I will find some data sample.

- charindex(A.TipeTransaksi,'BS GR SR SIA STI SJ BRM SOA STO')
What this code for at ORDER?

Thank you.

The CHARINDEX() is about face. Normally you try to find an occurrence within a string e.g. charindex('the','around the world') and it would return 8. but using it the "other way around" you can find the position of a column in a literal string, and effectively gives you a sort order.

Check it out :

select id, tipe, charindex(tipe,'BS GR SR SIA STI SJ BRM SOA STO') as sort_order
from (values (2,'SJ'),(4,'SR'), (6,'STO'),(8,'GR')) data(id,tipe)
order by charindex(tipe,'BS GR SR SIA STI SJ BRM SOA STO')

-- but if I want SR after SJ - cant return a sales item unless it was sold
-- then I can easily reflect that requirement

select id, tipe, charindex(tipe,'BS GR SJ SIA STI SR BRM SOA STO') as sort_order
from (values (2,'SJ'),(4,'SR'), (6,'STO'),(8,'GR')) data(id,tipe)
order by charindex(tipe,'BS GR SJ SIA STI SR BRM SOA STO')

Select allOpen in new window

Not really needed in your case - well, not at this stage - but included anyway in case CostNoSeq was hindering our sort sequence. After all you said above "We can not order by TipeTransaksi." and while I generally agree, we can manually order the way we want to see Tipe.

It is a manual method to derive a different sort sequence that  can be very handy.

Does that make sense ?

Hi Marks,

Great. It works.

Thank you very much for your help.

My pleasure :)

Hi Marks,

- ORDER BY A.SerialNo, A.TglTransaksi,ISNULL(A.IdStock, 0),charindex(RTRIM(A.TipeTransaksi),'BS GR SR SIA STI SJ BRM SOA STO')
'BS GR SR SIA STI SJ BRM SOA STO'.
Since it is from database, I don't want to hardcode it.

I have below code from EE :

declare @nos varchar( max )

select @nos = isnull( @nos, '' ) + concat( quotename( tt.CostNoSeq ), ',' )
from (
	select distinct t.CostNoSeq
	from #transaction t
	)tt
order by
	tt.CostNoSeq asc
;

set @nos = left( @nos, len( @nos) - 1 )
;

declare @sql varchar( max )

set @sql = 'select ' + @nos + 'from #transaction t pivot ( max( t.TipeTransaksi ) for t.CostNoSeq in (' + @nos + ') ) as p'

execute( @sql )
;

How could I replace the hardcode to using select or select it into variable ?

Thank you.

Select allOpen in new window

Well, the sequence I used was to match the sequence you posted above with the corresponding CostNoSeq.

So, you could just use CostNoSeq in place of the charindex.().

Ok.

Thank you.