Kategori arşivi: SQL

MS SQL WHERE içinde CASE WHEN kullanımı ve CAHRINDEX

Yayın tarihi
Cevapla

CHARINDEX : Bir alan içindeki kaydın içinde karakerin kaçıncı sırada olduğunu bulmak.

 

SELECT     F.CUSTOMER,F.NAME1,F.TAXDEPT,F.TAXNUM

FROM         IASVERHEAD AS A WITH (NOLOCK) LEFT OUTER JOIN
IASCUSTOMER AS F WITH (NOLOCK) ON
F.NAME1 LIKE CASE WHEN CHARINDEX(‘//’,A.STEXT)>1 THEN SUBSTRING(A.STEXT,1,CHARINDEX(‘//’,A.STEXT)-1)+’%’
ELSE ” END

WHERE     (A.DOCDATE > GETDATE() – 10);

SQL Declare kullanımı

Yayın tarihi
Cevapla 
DECLARE @BARCODE VARCHAR(30)='005733165'

SELECT WAREHOUSE,STOCKPLACE,MATERIAL,QUANTITY,QUNIT,QPOSTWAY Q
,CREATEDAT C,CREATEDBY 
FROM IASINVITEM WITH (NOLOCK)
WHERE CONFIRMATION=SUBSTRING(@BARCODE,1,8) 
AND CONFIRMPOS=SUBSTRING(@BARCODE,9,5)
AND ISCANCELED=0 
ORDER BY CREATEDAT, QPOSTWAY DESC;
  
SELECT * FROM PNTFDIKISOTOB WHERE CONFMA=@BARCODE;

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' ;