Untitled

mail@pastecode.io avatar
unknown
sqlserver
a month ago
1.8 kB
2
Indexable
Never
SELECT 
			CASE WHEN items.UserField_03 is null then items.ItemCode COLLATE DATABASE_DEFAULT
											WHEN items.UserField_03 is not null then items.UserField_03 COLLATE DATABASE_DEFAULT end as product_id, 
			'bl_1' as storage_id, 
			ROUND(ISNULL(SUM(gmt.aantal), 0), 2) - astock.quantity as quantity,ROUND(ISNULL(SUM(gmt.aantal), 0), 2),astock.quantity,
			null as variant_id
			--CASE WHEN Items.IsSerialNumberItem=1 then gmt.facode else null end as NrSeryjny
		FROM
			[yazamco_ht_egn_20211004]..gbkmut gmt WITH(NOLOCK)
			INNER JOIN [yazamco_ht_egn_20211004]..Items WITH(NOLOCK) ON gmt.artcode = Items.ItemCode
			inner JOIN 
			(SELECT
			CASE WHEN items.UserField_03 is null then items.ItemCode COLLATE DATABASE_DEFAULT
											WHEN items.UserField_03 is not null then items.UserField_03 COLLATE DATABASE_DEFAULT end as product_id, 
			a.ItemCode,
			'bl_1' as storage_id, 
			ROUND(ISNULL(SUM(a.ItemCount), 0), 2) as quantity,
			null as variant_id
		FROM
			[yazamco_ht_ese_20230228]..Absences a 
			INNER JOIN [yazamco_ht_egn_20211004]..Items WITH(NOLOCK) ON a.ItemCode = Items.ItemCode
			
		WHERE
			a.Type = 147
			and a.Status = 0
			group by a.ItemCode,items.UserField_03, items.ItemCode) astock on astock.product_id = product_id

		WHERE
			gmt.transtype IN ('N', 'C', 'P')
			AND gmt.artcode IS NOT NULL
			AND (NOT ISNULL(gmt.reknr, 0) = 0)
			AND gmt.reknr = Items.GLAccountDistribution
			AND gmt.docdate <= GETDATE()
			--AND gmt.artcode = '1/4 KOŃCÓWKA DO ELEKTROZAWORU'
			AND gmt.warehouse  IN ('S')
			--and (CASE WHEN Items.IsSerialNumberItem=1 then gmt.facode else null end) is null
			group by gmt.warehouse, gmt.artcode, astock.quantity, items.UserField_03,items.ItemCode, product_id
Leave a Comment