IAS ta saat kriterine göre anlık stok çekmek

Yayın tarihi
Cevapla 
SELECT I.COMPANY, I.PLANT, I.WAREHOUSE, I.STOCKPLACE,
         I.SPECIALSTOCK, I.MATERIAL,
         MAX( I.SKUNIT ) AS SKUNIT, MAX( M.STEXT ) AS STEXT
         , SUM( I.SKQUANTITY * ( 1 - 2 * I.QPOSTWAY ) ) AS TOTALSTOCK
         , MAX( I.ISVARIANT ) AS ISVARIANT
         , MAX( I.VARIANTKEY ) AS VARIANTKEY
         , MAX( I.VOPTIONS ) AS VOPTIONS
         ,I.PROJECT,I.CONFIRMATION,I.CONFIRMPOS,I.BATCHNUM 

FROM IASINVITEM I WITH (NOLOCK),  IASMATX M WITH(NOLOCK) 

WHERE I.CLIENT = '00'  AND I.COMPANY LIKE '01'  AND I.PLANT LIKE '01'  
AND I.WAREHOUSE LIKE '008'  AND I.STOCKPLACE LIKE '01'  
AND I.SPECIALSTOCK LIKE '%'  AND I.MATERIAL LIKE '%'  
AND I.CREATEDAT <= '11.21.2012 12:15:00'  
AND M.CLIENT = I.CLIENT  
AND M.COMPANY = I.COMPANY  AND M.PLANT = '*'  AND M.LANGU = 'T'  
AND M.MATERIAL = I.MATERIAL  
AND M.VALIDFROM <= I.CREATEDAT  
AND M.VALIDUNTIL >= I.CREATEDAT 
AND I.ISCANCELED = 0 

GROUP BY I.COMPANY, I.PLANT, I.WAREHOUSE, I.STOCKPLACE
         , I.SPECIALSTOCK,  I.MATERIAL
,I.ISVARIANT ,I.VARIANTKEY,I.VOPTIONS,I.PROJECT,I.CONFIRMATION
,I.CONFIRMPOS,I.BATCHNUM

HAVING SUM( I.SKQUANTITY * ( 1 - 2 * I.QPOSTWAY ) ) > 0.01 OR  
SUM( I.SKQUANTITY * ( 1 - 2 * I.QPOSTWAY ) ) < -0.01

ORDER BY I.COMPANY, I.MATERIAL,I.VOPTIONS, I.PLANT, I.WAREHOUSE
         , I.STOCKPLACE, I.SPECIALSTOCK

Bilgisayarın sistem performansının ölçmek

Yayın tarihi
Cevapla

Bilgisayarım yavaş diye bir şikayet geldiğinde bu yavaşlığın hangi donanımdan kaynaklandığını bulmak için ücretsiz olarak dağıtılan NovaBench programını kullanabilirsiniz.Daha profesiyonal bir program arayanlar SiSoftware Sandra programını kullanabilirler.

Bu programlar bilgisayarın işlemcisini (CPU), belleğini (RAM), sabit diskini (HDD) ve 3D grafik kartına test uygulayıp puanlar vermekte. NovaBench bu sonuçları kendi sitesinde yayınlamakta ve kendi sonuçlarınız ile karşılaştırıp değiştirilmesi gereken bileşeni belirlemenizde size iyi bir fikir vermektedir.

web: http://novabench.com

MS SQL de bir sorgunun sonucunu HTML olarak mail attırmak

Yayın tarihi
1 
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( 
SELECT A.MATERIAL as 'td','',B.STEXT as 'td',''
,CONVERT(INTEGER,SUM(A.QUANTITY)) AS 'td','',A.QUNIT as 'td',''
,CONVERT(DATE,A.DOCDATE) as 'td' 
FROM IASINVITEM A WITH (NOLOCK),IASVAROPTIONX B WITH (NOLOCK)
WHERE A.CLIENT='00' AND A.COMPANY='01'
AND A.INVDOCTYPE='IC' AND A.WAREHOUSE='170' AND A.STOCKPLACE='01'
AND A.ISCANCELED=0
AND (A.MATERIAL='L5402G'
OR A.MATERIAL LIKE 'LMS__G')
AND B.OPTIONKEY LIKE SUBSTRING(A.VOPTIONS,4,2)
AND B.ATTRIBUTEKEY='01'
AND B.VARIANTKEY='B'
AND A.CREATEDAT>GETDATE()-1
GROUP BY A.MATERIAL,B.STEXT,A.QUNIT ,A.DOCDATE
ORDER BY A.MATERIAL,B.STEXT,A.DOCDATE

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body>
<H3>Üretim</H3>
<table border = 1> 
<tr>
<th> Ürün </th> <th> Beden </th> <th> Miktar </th> 
<th> Birim </th> <th> Tarih </th> </tr>'     

SET @body = @body + @xml +'</table></body></html>'
 
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileFabrika',  
@body = @body,
@body_format ='HTML',
@recipients = 'fahridonmez@mail.com', 
@subject = 'Çorap Üretimi' ;

Bozulan IASINVSTOCK u düzelten SQL cümleciği

Yayın tarihi
Cevapla 
-- IASINVSTOCK silinip

DELETE FROM IASINVSTOCK 
WHERE  CLIENT = '00' AND COMPANY = '01' 
AND MATERIAL LIKE 'CP____'

-- IASINVITEM'dan oluşturulmuş stoklar alınıyor.
INSERT INTO IASINVSTOCK 
SELECT CLIENT, COMPANY, PLANT, MATERIAL, WAREHOUSE, STOCKPLACE
, SPECIALSTOCK, BATCHNUM, MAX( ENTRYDATE ) AS ENTRYDATE
, 0 AS ISLOCKED
, SUM( ( 1 - 2 * QPOSTWAY )  * SKQUANTITY ) AS TOTALSTOCK             
, SUM( CASE  WHEN VALUEFIELD = 1 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS AVAILSTOCK
, SUM( CASE  WHEN VALUEFIELD = 4 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS TRANSTOCK
, SUM( CASE  WHEN VALUEFIELD = 3 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS BLOCKSTOCK
, SUM( CASE  WHEN VALUEFIELD = 2 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS QUALITYSTOCK
, SUM( CASE  WHEN VALUEFIELD = 5 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS RESERVESTOCK
, MAX( ENTRYDATE ) AS LINVDATE, 0 AS LASTMONINV, 0 AS PREVMONINV
, SUM( CASE  WHEN VALUEFIELD = 6 
THEN ( 1 - 2 * QPOSTWAY ) * SKQUANTITY ELSE 0 END ) AS REVISESTOCK
, 1 AS PUNIT, MAX( QUNIT ) AS QUNIT, 0 AS PRICE
, MAX( EXPIRYDATE ) AS EXPIRYDATE
, CONVERT( DATETIME, '01.01.2030' ) AS BATCHEXPDATE
, SUM( ( 1 - 2 * QPOSTWAY )  * QUANTITYX ) AS TOTALSTOCK2            
, SUM( CASE  WHEN VALUEFIELD = 1 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS AVAILSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 4 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS TRANSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 3 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS BLOCKSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 2 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS QUALITYSTOCK2
, SUM( CASE  WHEN VALUEFIELD = 5 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS RESERVESTOCK2
, SUM( CASE  WHEN VALUEFIELD = 6 
THEN ( 1 - 2 * QPOSTWAY ) * QUANTITYX ELSE 0 END  )  AS REVISESTOCK2
, MAX( QUNITX ) AS QUNITX, MAX( ISVARIANT ) AS ISVARIANT
, VARIANTKEY, VOPTIONS
FROM IASINVITEM 
WHERE  CLIENT = '00' AND COMPANY = '01' AND MATERIAL LIKE 'CP____'
AND ISCANCELED=0
GROUP BY  CLIENT, COMPANY, PLANT, MATERIAL, WAREHOUSE, STOCKPLACE
, SPECIALSTOCK, BATCHNUM, VARIANTKEY, VOPTIONS
HAVING SUM( ( 1 - 2 * QPOSTWAY )  * SKQUANTITY ) != 0