Need to count unique values

sapator · October 5, 2017, 12:07pm

Hi.
Doing this:

from tblTrans_Inventory I
  INNER JOIN VISTA.dbo.tblItem IT
	    ON IT.Item_strItemId = I.Item_strItemId
    INNER JOIN VISTA.dbo.tblSalesTax 
        ON tblSalesTax.STax_strCode = IT.STax_strCode

    LEFT JOIN ZZ_tblTrans_Inventory_EXT BI
        ON  I.TransI_lgnNumber   = BI.TransI_lgnNumber
        AND I.TransI_intSequence = BI.TransI_intSequence
    LEFT JOIN tblBooking_Header BH ON BH.TransC_lgnNumber = I.TransI_lgnNumber
	LEFT JOIN tblItem_Class IC
					ON	IC.Class_strCode = IT.Class_strCode 

                LEFT JOIN tblWorkstation W
					ON	W.Workstation_strCode = ISNULL(BI.TransI_strPickupWorkstn,I.Workstation_strCode)

--Where I.TransI_dtmDateCollected < '99990101'
--where I.TransI_strType = 'S' --if like so we do not get refunded items
          where IT.Item_strBookingFee <> 'Y'                              --Exclude Booking Fees
          AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%' 		


and 
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
                 ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
            END  between '20171005 06:00:00' and '20171006 05:59:59'
		

				-- remove inline recipre
			and TransI_curValueEach > 0

I get (rows on interest)
TransI_lgnNumber --- Gross Value(that deducts with negative decNoOfitems ) , workstationcode
122240 4,5 x
122240 4,5 x
122240 4,5 x
122240 4,5 x

so we have 4 TransI_lgnNumber with the same number the calculations for the value and a workstation code

When I group this one I will get

select  I.TransI_lgnNumber,
--,
--sum (ISNULL(TransI_decNoOfItems,0)) AS Quantity
sum(CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) AS MONEY))  AS GrossValue
 ,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as workstationcode
 --,BH.BookingH_intNextBookingNo
 ,sum( CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) -
	            ((ISNULL(TransI_curSTaxEach,0)+
		          ISNULL(TransI_curSTaxEach2,0)+
		          ISNULL(TransI_curSTaxEach3,0)+
		          ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decNoOfItems,0)) AS MONEY))  AS NetValue
--,IT.Item_strItemDescription 

 from tblTrans_Inventory I
  INNER JOIN VISTA.dbo.tblItem IT
	    ON IT.Item_strItemId = I.Item_strItemId
    INNER JOIN VISTA.dbo.tblSalesTax 
        ON tblSalesTax.STax_strCode = IT.STax_strCode

    LEFT JOIN ZZ_tblTrans_Inventory_EXT BI
        ON  I.TransI_lgnNumber   = BI.TransI_lgnNumber
        AND I.TransI_intSequence = BI.TransI_intSequence
    LEFT JOIN tblBooking_Header BH ON BH.TransC_lgnNumber = I.TransI_lgnNumber
	LEFT JOIN tblItem_Class IC
					ON	IC.Class_strCode = IT.Class_strCode 

                LEFT JOIN tblWorkstation W
					ON	W.Workstation_strCode = ISNULL(BI.TransI_strPickupWorkstn,I.Workstation_strCode)

--Where I.TransI_dtmDateCollected < '99990101'
--where I.TransI_strType = 'S' --if like so we do not get refunded items
          where IT.Item_strBookingFee <> 'Y'                              --Exclude Booking Fees
         AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%' 		

		
and 
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
                 ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
            END between '20171005 06:00:00' and '20171006 05:59:59'
		





			and IT.Item_strItemDescription is not null

			-- remove inline recipre
			and TransI_curValueEach > 0
			group by BH.BookingH_strPickupWorkstn,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode)
		--	,BH.BookingH_intNextBookingNo
		--	,IT.Item_strItemDescription
			,I.TransI_lgnNumber

TransI_lgnNumber --- Gross Value(that deducts with negative decNoOfitems ) , workstationcode
122240 0,00 x

That is fine as the transaction number is grouped correctly and the value is correctly deducted.

Now if i need to say that since 122240 is the same value I just only need to show a number that shows that this is ONE transaction that included other stuff.
But number 122240, although it appears 4 times, it is the same number and thus is counted as one transaction
So I need to show Transactions 1 , Value 0 , workstation X
If i use distinct count, it will count the transactions and it will give me 4 , not one.
how would i go about do that:

Here is the distinct example:

select distinct count( I.TransI_lgnNumber) as X1,
--,
--sum (ISNULL(TransI_decNoOfItems,0)) AS Quantity
sum(CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) AS MONEY))  AS GrossValue
 ,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as workstationcode


-------- and so on, it is the same query all the way

Result X1 = 4 , Gross 0 ,workstation X

I would like to expect
Result X1=1 , Gross 0, workstation X.

How can I do that?

thanks.

sapator · October 5, 2017, 1:34pm

Hi.
this does not work but it will also not work in it's simplest form:

select  distinct count(TransI_lgnNumber)
			 ,sum( TransI_curValueEach)
	          
			 from tblTrans_Inventory where TransI_dtmRealTransTime  between '20171005 06:00:00' and '20171006 05:59:59'
			 group by TransI_lgnNumber

It counts the 4 lines

sapator · October 5, 2017, 2:30pm

Hi.

On a very simple test, this seems to work, but i haven't tried it on the big query yet

  SELECT  COUNT(*), a.Gross
FROM
        (
            	select  distinct TransI_lgnNumber
				,sum( TransI_curValueEach)	as Gross

            FROM    tblTrans_Inventory
			 where TransI_dtmRealTransTime  between '20171005 06:00:00' and '20171006 05:59:59'
			  group by TransI_lgnNumber
        ) a
		group by a.Gross

sapator · October 5, 2017, 2:37pm

No, actually it does not work as I need to split per workstation.
This gives me (on 2 transactions from the same workstation)

1,4.5,workstation1
1,5.5,workstation1

Now i need to group that per workstation


		  SELECT  COUNT(*), a.Gross,a.Workstation_strCode
FROM
        (
            	select  distinct TransI_lgnNumber
				,sum( TransI_curValueEach)	as Gross
				,Workstation_strCode
            FROM    tblTrans_Inventory
			 where TransI_dtmRealTransTime  between '20171005 06:00:00' and '20171006 05:59:59'
			  group by TransI_lgnNumber,Workstation_strCode
        ) a
		group by a.Gross,a.Workstation_strCode

sapator · October 5, 2017, 2:41pm

To sum it up.

I need to have number of transactions NUMBER, that came from a specific workstation and the gross.

This sums the workstation but it will only count 1 as transaction where there are actually 2.

		  SELECT  COUNT(*), a.Gross,a.Workstation_strCode
FROM
        (
            	select  distinct Workstation_strCode
				,sum( TransI_curValueEach)	as Gross
				
            FROM    tblTrans_Inventory
			 where TransI_dtmRealTransTime  between '20171005 06:00:00' and '20171006 05:59:59'
			  group by Workstation_strCode
        ) a
		group by a.Gross,a.Workstation_strCode

Any ideas?