Otomatik raporlarda rapor çekme gününü alıp istenilen saatler i sorguya ekleyip sonuç almak.
SELECT CONVERT(DATETIME, CONVERT(varchar(11),GETDATE()-1, 111 ) + ‘ 08:30:00’, 111) BeginDate,
CONVERT(DATETIME, CONVERT(varchar(11),GETDATE(), 111 ) + ‘ 08:30:00’, 111) EndDate
BeginDate EndDate
2013-05-27 08:30:00.000 2013-05-28 08:30:00.000
Sorgu sonucu gelen düşey listeyi virgüller ile birleştirerek yatay hale getirmek.
SELECT A.COMPONENT
,STUFF( (SELECT ‘, ‘ + SUBSTRING(B.MATERIAL,LEN(B.MATERIAL)-6,7)
from IASBOMITEM B WITH (NOLOCK)
WHERE B.CLIENT=’00’ AND B.COMPANY=’01’ AND B.COMPONENT=A.COMPONENT
AND B.MATERIAL LIKE ‘%D’ FOR XML PATH(”)),1,1,”) AS MATERIALS
FROM IASBOMITEM A WITH (NOLOCK),IASMATBASIC C WITH (NOLOCK)
WHERE A.CLIENT=’00’ AND A.COMPANY=’01’ AND A.COMPONENT=C.MATERIAL
AND C.CLIENT=A.CLIENT AND C.COMPANY=A.COMPANY AND C.MATGRP LIKE ‘C-___’ AND C.ISDELETE=0
GROUP BY A.COMPONENT
ORDER BY A.COMPONENT
COMPONENT MATERIALS ------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 806860 NULL D1028657 DPDNAHD, DPDNAHD D1028657K NULL D1028657S NULL D1059 NULL D140MM2X10S LILYAHD, LRYALHD D1446 LB178ZD D2266 LMS2GZD, LF51GZD, LMS2GZD D2565 LF82MZD, LF82MZD D267 LL261ZD, LL61ZQD, LM85SZD, LM87MZD, LMS84ZD, LMS94ZD, LU884ZD, LL261ZD, LM85SZD, LM87MZD, LMS84ZD, LMS94ZD D267-2 NULL D2837 LB175ZD D2837S LB175ZD D4035 DJ17ZAD D6236 DJP17HD D727907 F16MD D, LF80MZD, LF80MZD D728117 DLLADHD, FBASIHD, DLLADHD, FBASIHD D728135 LU0A4HD, LG18UHD, LB112HD, DP354HD, FBASIHD, LB112HD, FBBASHD D728135SS LU164HD, LB13UHD, LB13UHD D7849/S8 LTV20HD, DP492KD, DB392KD, DD392KD D806860 LP530HD, LTA16HD, LP530HD, LTS15HD, LU115HD, LTA16HD D806860/01 NULL D806860/02 LU118HD D80MM1X40S LP0A1HD D80MM2X10S LSKA1HD, DVICTHD, LL115HD, LP0A1HD D8300 LG11SHD D830-0015 LP120HD DG11780089 LBA24HD F001EL0707044CL FBBASKD FLBSC01 FPLW1KD FLBST01 FBASIAD, FBASIHD, FBASITD, FBBASHD, FBBASTD, FTAS2HD GUPUR-01 DP354ED, DP355ED, DP354HD (32 row(s) affected)
Datepart fonksiyonu ile tarih bilgisi içeren bir alanın, gününü, ayını, yılını vs. almak mümkün. Hatta week parametresi ile yılın kaçıncı haftası olduğunu almak da mümkün. Ancak bu hafta bilgisi hafta başını Pazar kabul ederek veriyor. Hafta başını pazartesi olarak hesaplaması için isowk parametresi kullanılmalı.
SELECT A.MATERIAL,A.VOPTIONS,B.OUTPUT,B.CONFIRMDATE,YEAR(B.CONFIRMDATE) AS YIL,MONTH(B.CONFIRMDATE) AS AY
,DATEPART(isowk,B.CONFIRMDATE) AS HAFTA
FROM MAC2012..IASPRDORDER A WITH (NOLOCK),MAC2012..IASPRDCONF B WITH (NOLOCK)
WHERE A.CLIENT=’00’ AND A.COMPANY=’01’
AND A.PLANT=’01’ AND A.POTYPE=’OI’
AND B.CLIENT=A.CLIENT AND B.COMPANY=A.COMPANY AND B.PLANT=A.PLANT AND B.POTYPE=A.POTYPE AND B.PRDORDER=A.PRDORDER
AND (A.MATERIAL NOT LIKE ‘%P’ OR A.MATERIAL NOT LIKE ‘%V’ OR A.MATERIAL NOT LIKE ‘%W’ OR A.MATERIAL NOT LIKE ‘%R’)
AND CONFIRMDATE>=’2012-01-01 00:00:00.000′
AND CONFIRMDATE<‘2013-01-01 00:00:00.000’
SQL tablolarına excel veya txt gibi farklı biçimli dosyalardan veri aktardığınızda verilerle birlikte istenmeyen boşluk satır başı enter karakterleri de gelir. Bunların tespiti ve silinmesi gerekir.
DECLARE @NewLine char(2)
SET @NewLine=char(13)+char(10)
SELECT *
FROM PNTFICRONDIGERGR
WHERE CHARINDEX(@NewLine,[ISMERKEZI]) > 0
Update PNTFICRONDIGERGR
Set [ISMERKEZI] = replace([ISMERKEZI], @NewLine, ”);
SELECT * FROM PNTFICRONDIGERGR
IS NULL veya IS NOT NULL a alternatif olarak COALESCE kullanımı.
SELECT A.MACHINEID, A.MACHINECODE, A.STARTTIME, CASE WHEN A.ENDTIME IS NULL THEN A.CANCELTIME ELSE A.ENDTIME END AS ENDTIME, A.JOBORDER, A.OPRNAME, A.FABRIC_WEIGHT, B.MATERIAL, C.STEXT, A.PROGRAMNOLIST, D.USERNAME + ' ' + D.USERLASTNAME ,D.ACTIVITYDATE AS OPRNAME2 FROM TELESKOP.OMUR.dbo.BADATA AS A INNER JOIN PNTFSARJ AS B ON A.JOBORDER = B.PROJECT INNER JOIN IASVAROPTIONX AS C ON SUBSTRING( B.VOPTIONS , 10 , 3 ) = C.OPTIONKEY AND ( C.VARIANTKEY = 'L' ) LEFT OUTER JOIN VW_TELESKOP_KAZAN_GC_OPERATOR AS D WITH ( NOLOCK ) ON D.ACTIVITYDATE <= CASE WHEN COALESCE( A.ENDTIME , 0)=0 THEN A.CANCELTIME ELSE A.ENDTIME END AND D.ACTIVITYDATE2 >= CASE WHEN COALESCE(A.ENDTIME , 0)=0 THEN A.CANCELTIME ELSE A.ENDTIME END AND A.MACHINEID = D.MACHINEID WHERE (A.JOBORDER LIKE '12120706' ) AND (A.STARTTIME >= '12.10.2012 00:00:00' ) AND (A.STARTTIME <= '12.17.2012 15:34:00' ) AND (A.MACHINEID LIKE '%' ) AND (A.OPRNAME LIKE '%' ) GROUP BY A.MACHINEID, A.MACHINECODE, A.STARTTIME, A.ENDTIME, A.JOBORDER, A.OPRNAME , A.FABRIC_WEIGHT, B.MATERIAL, C.STEXT, A.PROGRAMNOLIST, D.USERNAME, D.USERLASTNAME,A.CANCELTIME ,D.ACTIVITYDATE ORDER BY A.STARTTIME