WITH cte AS (
SELECT
contact_id,
first_name,
last_name,
email,
ROW_NUMBER() OVER (
PARTITION BY
first_name,
last_name,
email
ORDER BY
first_name,
last_name,
email
) row_num
FROM
sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;
Tag: SERVER
LOGO PAYTRANS ÇEK SENET DETAY RAPORU
SELECT
T2.TUR,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV WITH(NOLOCK) WHERE FIRMNR=210 and NR=T2.BRANCH) ISYERI
,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_KOD
,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_AD
,
CEK_NO,ISLEM_TARIHI,VADE_TARIHI,CEK_DOVIZ,TUTAR,KAPAMA_TUTAR,KARSI_ISLEM_BILGILERI
FROM (
SELECT DISTINCT
*,
CASE T.TRCURR WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END CEK_DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
STUFF(
(SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.PROCDATE) AS varchar)+'.'+CAST(MONTH(PP.PROCDATE) AS varchar) +'.'+CAST(YEAR(PP.PROCDATE) AS varchar)+'] /'+format((ROUND(PP.PAID,2)),N'','tr-TR') FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
P.BRANCH,
P.TRCURR,
P.CARDREF ,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/
(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,
P.PROCDATE ISLEM_TARIHI,
P.DATE_ VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.MODULENR=6/*CEKLER*/ /* AND P.CARDREF=5571 */
/*AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8*/
) AS T
) AS T2
LOGO STLINE HATALI MALZEME BİRİMLERİ
SELECT * FROM (
SELECT *,
CASE WHEN MALZEMEUNITSETREF<>STLINEUSREF THEN 'ESIT DEGIL' ELSE '' END UNITSETREF_ESITLIKDURUMU
,
CASE WHEN MALZEMEUOMREF<>STLINEUOMREF THEN 'ESIT DEGIL' ELSE '' END UOMREF_ESITLIKDURUMU
FROM (
SELECT DISTINCT
(select TOP 1 I.CODE from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF) MALZEMEKODU
,
(select TOP 1 I.NAME from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF) MALZEMEADI
,
(select TOP 1 I.UNITSETREF from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF) MALZEMEUNITSETREF,
(SELECT TOP 1 CODE FROM LG_211_UNITSETL U WHERE U.UNITSETREF=
(select TOP 1 I.UNITSETREF from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF)) MALZEMEBIRIMADI
,
(SELECT TOP 1 LOGICALREF FROM LG_211_UNITSETL U WHERE U.UNITSETREF=
(select TOP 1 I.UNITSETREF from LG_211_ITEMS I WHERE I.LOGICALREF=S.STOCKREF)) MALZEMEUOMREF
,
USREF STLINEUSREF
,
UOMREF STLINEUOMREF
,
(SELECT TOP 1 UNT.CODE FROM LG_211_UNITSETL UNT WHERE UNT.UNITSETREF=S.USREF AND LINENR=1 ) STLINEBIRIMADI
FROM LG_211_01_STLINE S WHERE LINETYPE=0
/* AND STOCKREF=(select TOP 1 I.LOGICALREF from LG_211_ITEMS I WHERE I.CODE='BYK-0008') */
) AS T WHERE MALZEMEKODU IS NOT NULL
) AS T2 WHERE UNITSETREF_ESITLIKDURUMU<>'' OR UOMREF_ESITLIKDURUMU<>''
LOGO İRSALİYELERİN TARİHİNİ FATURA TARİHLERİNE EŞİTLEME
--STLINE TARİHİ EŞİTLE
UPDATE INVDATEUPDT SET STLDATE=INVDATE FROM
(
SELECT STL.DATE_ AS STLDATE,
INV.DATE_ AS INVDATE
FROM LG_201_01_STLINE AS STL
LEFT OUTER JOIN
LG_201_01_INVOICE AS INV ON INV.LOGICALREF=STL.INVOICEREF
WHERE STL.INVOICEREF0 AND STL.DATE_INV.DATE_
)AS INVDATEUPDT
----------------------------------------------
--IRSALYE TARİHİ EŞİTLE
UPDATE STFICHEDATEUPD SET STFDATE=INVDATE FROM
(
SELECT INV.DATE_ AS INVDATE,
STF.DATE_ AS STFDATE
FROM LG_201_01_INVOICE AS INV
LEFT OUTER JOIN
LG_201_01_STFICHE AS STF ON INV.LOGICALREF=STF.INVOICEREF
WHERE STF.INVOICEREF0 AND STF.DATE_INV.DATE_
) AS STFICHEDATEUPD
*/
sql server -20122014 express download
Microsoft® SQL Server® 2014 Express
https://www.microsoft.com/en-us/download/details.aspx?id=42299
sql server 2012 iso olan express deildir https://www.microsoft.com/en-us/download/details.aspx?id=29066
SQL SERVER PIVOT TABLE SINIRSIZ COLUMN
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ADI)
FROM ZV_LOGO_MALZEME GROUP BY ADI
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT KODU,' + @cols + ' from
(
select KODU,ADI
from ZV_LOGO_MALZEME
) x
pivot
(
COUNT(ADI)
for ADI in (' + @cols + ')
) p
'
execute(@query)
--------------
İKİ FARKLI TABLODAN
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(DEFINITION_)
FROM LG_SLSMAN GROUP BY DEFINITION_
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT STGRPCODE,' + @cols + ' from
(
select LG_001_ITEMS.STGRPCODE,DEFINITION_
from LG_SLSMAN
CROSS JOIN LG_001_ITEMS
) x
pivot
(
MIN(DEFINITION_)
for DEFINITION_ in (' + @cols + ')
) p
'
execute(@query)
Blog of Sem 