Thursday, 31 January 2013

**************************** -- SQL server --BULK insert query -- Notepad Document files **************************** BULK INSERT nagu FROM 'D:\sss.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

Friday, 11 January 2013

VLOOKUP Function Arguments
lookup_value: What value do you want to look up? In this example, the product code is in cell A7, and you want to find its product name. table_array: Where is the lookup table? If you use an absolute reference ($A$2:$C$5), instead of a relative reference (A2:C5), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name. col_index_num: Which column has the value you want returned? In this example, the product names are in the second column of the lookup table. [range_lookup]: Do you want an exact match? Is an approximate match okay? If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of TRUE.) Ex1: =VLOOKUP(O2,M$2:M$15,1,0) o2-Search values M$2-Start pointer M$15- Enter point 0/1-true,false Ex2: =VLOOKUP(A7,Products!$A$2:$C$5,3,FALSE)

Thursday, 10 January 2013

ALTER PROCEDURE sp_Name ( parameter varchar(10), parameter varchar(8), parameter varchar(8) ) AS ;WITH CTE AS ( SELECT row_number() OVER(ORDER BY A.DateFull ASC)AS ROWID, convert (VARCHAR,convert(DATETIME,a.DateFull,112),103)AS DATE,A.WEEKDAYNAME, ISNULL(B.TOTAL_SCANNED,0) AS TOTAL_SCANNED, ISNULL(N.DELETE_INVOICES,0)AS SDELETE_INVOICES, ISNULL(E_INVOICE,0)AS e_INVOICE, ISNULL(OBLogged,0) as LOPENING, ISNULL(LOGGED_COUNT,0) AS LOGGED_COUNT, ISNULL(K.LDELETE_INVOICE,0)AS LDELETE_INVOICE, ISNULL(OBVerified,0) as VOPENING, ISNULL(VERIFIED_COUNT,0) AS VERIFIED_COUNT, ISNULL(L.VDELETE_INVOICE,0)AS VDELETE_INVOICE, ISNULL(PROCESSED_COUNT,0) AS PROCESSED_COUNT, ISNULL(PAID_COUNT,0) AS PAID_COUNT FROM ( SELECT DATEKEY ,DateFull, CHARACTERDATE,WEEKDAYNAME FROM L_DATE WHERE DateFull>='20120101' AND DateFull<=getdate() ) A LEFT OUTER JOIN (SELECT convert(VARCHAR,OBDate,112)AS OBDate,Location,OBLogged FROM Table1 WHERE Location=@LOC )S ON A.DateFull=S.OBDate LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) AS SCANNED_DATE, count(ITHE_LOGSRLNO) AS TOTAL_SCANNED FROM tagble2 WHERE LOC=@LOC AND ITHE_DELETE_FLAG IS NULL GROUP BY CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) )B ON A.DateFull=B.SCANNED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) AS SCANNED_DATE, count(ITHE_LOGSRLNO) AS DELETE_INVOICES FROM VU_ITRK_HEADER WHERE LOC=@LOC AND ITHE_DELETE_FLAG='D' GROUP BY CONVERT(VARCHAR,ITHE_ENTEREDDT ,112) )N ON A.DateFull=N.SCANNED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,BUDAT,112) AS Einvoice_DATE,count(LOGSRL) AS E_INVOICE FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(BUDAT)=1 AND BUDAT>'20120101' AND left(LOGSRL,2)='80' AND LOC=@LOC GROUP BY BUDAT )E_IN ON A.DateFull=E_IN.Einvoice_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,BUDAT,112) AS LOGGED_DATE,count(LOGSRL) AS LOGGED_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(BUDAT)=1 AND BUDAT>'20120101' AND left(LOGSRL,2)<>'80' AND DELFLG='' AND LOC=@LOC GROUP BY BUDAT )C ON A.DateFull=C.LOGGED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,BUDAT,112) AS LOGGED_DATE,count(LOGSRL) AS LDELETE_INVOICE FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(BUDAT)=1 AND BUDAT>'20120101' AND left(LOGSRL,2)<>'80' AND DELFLG='X' AND LOC=@LOC GROUP BY BUDAT )K ON A.DateFull=K.LOGGED_DATE LEFT OUTER JOIN (SELECT convert(VARCHAR,OBDate,112)AS OBDate,Location,OBVerified FROM M_DASHBOARD_GALSS_OPENING_BALANCE WHERE Location=@LOC )R ON A.DateFull=R.OBDate LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,APRDDAT,112) AS VERIFIED_DATE,count(LOGSRL) AS VERIFIED_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(APRDDAT)=1 AND DELFLG='' AND LOC=@LOC AND USTATUS = 'PA' GROUP BY APRDDAT )D ON A.DateFull=D.VERIFIED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,APRDDAT,112) AS VERIFIED_DATE,count(LOGSRL) AS VDELETE_INVOICE FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(APRDDAT)=1 AND LOC=@LOC AND DELFLG='X' AND USTATUS = 'PA' GROUP BY APRDDAT )L ON A.DATEKEY=L.VERIFIED_DATE LEFT OUTER JOIN (SELECT convert(VARCHAR,OBDate,112)AS OBDate,Location,OBProcessed FROM M_DASHBOARD_GALSS_OPENING_BALANCE WHERE Location=@LOC )X ON A.DateFull=X.OBDate LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,PTDAT,112) AS PROCESSED_DATE,count(LOGSRL) AS PROCESSED_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(PTDAT)=1 AND LOC=@LOC GROUP BY PTDAT )E ON A.DateFull=E.PROCESSED_DATE LEFT OUTER JOIN (SELECT CONVERT(VARCHAR,AUGDT,112) AS PAID_DATE,count(LOGSRL) AS PAID_COUNT FROM VU_ZXP_ZMMIV_FLOAT WHERE ISDATE(AUGDT)=1 AND LOC=@LOC GROUP BY AUGDT )F ON A.DateFull=F.PAID_DATE ) ,CTE1 AS ( SELECT ROWID=row_number() OVER(ORDER BY ROWID ASC),DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,LOPENING,LOGGED_COUNT,LDELETE_INVOICE, VOPENING,VERIFIED_COUNT,VDELETE_INVOICE,PROCESSED_COUNT,PAID_COUNT FROM CTE ) ,CTE2 AS ( SELECT ROWID,DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,CAST(LOPENING AS INT)AS LOPENING,LOGGED_COUNT,LDELETE_INVOICE, (CAST(LOPENING AS INT)+CAST(LOGGED_COUNT AS INT))AS TOTAL, ((CAST(TOTAL_SCANNED AS INT)+CAST(LOPENING AS INT))-CAST(LOGGED_COUNT AS INT))AS CLOSING, CAST(VOPENING AS INT) AS VOPENING,VERIFIED_COUNT,VDELETE_INVOICE, (CAST(VOPENING AS INT)+CAST(VERIFIED_COUNT AS INT))as VTOTAL, (((CAST(LOGGED_COUNT AS INT)+CAST(E_INVOICE AS INT))+CAST(VOPENING AS INT))-CAST(VERIFIED_COUNT AS INT))as VCLOSING, PROCESSED_COUNT,PAID_COUNT FROM CTE1 D WHERE ROWID = '1' UNION SELECT ROWID,DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,NULL AS LOPENING,LOGGED_COUNT,LDELETE_INVOICE,NULL AS TOTAL, NULL AS CLOSING, NULL AS VOPENING,VERIFIED_COUNT,VDELETE_INVOICE,NULL AS VTOTAL,NULL AS VCLOSING,PROCESSED_COUNT,PAID_COUNT FROM CTE1 D WHERE ROWID > '1' ) ,CTE3 AS ( SELECT ROWID,DATE,WEEKDAYNAME,TOTAL_SCANNED, SDELETE_INVOICES,e_INVOICE,LOPENING,LOGGED_COUNT,LDELETE_INVOICE, (CAST(LOPENING AS INT)+CAST(LOGGED_COUNT AS INT))AS TOTAL, ((CAST(TOTAL_SCANNED AS INT)+CAST(LOPENING AS INT))-CAST(LOGGED_COUNT AS INT))AS CLOSING, VOPENING,VERIFIED_COUNT,VDELETE_INVOICE, (CAST(VOPENING AS INT)+CAST(VERIFIED_COUNT AS INT))AS VTOTAL, (((CAST(LOGGED_COUNT AS INT)+CAST(E_INVOICE AS INT))+CAST(VOPENING AS INT))-CAST(VERIFIED_COUNT AS INT))AS VCLOSING, PROCESSED_COUNT,PAID_COUNT FROM CTE2 AS D WHERE ROWID = '1' UNION ALL SELECT D1.ROWID,D1.DATE,D1.WEEKDAYNAME,D1.TOTAL_SCANNED,D1.SDELETE_INVOICES,D1.e_INVOICE, D2.CLOSING AS LOPENING,D1.LOGGED_COUNT,D1.LDELETE_INVOICE, (ISNULL(D2.CLOSING,0)+ISNULL(D1.LOGGED_COUNT,0))AS TOTAL, ((ISNULL(D1.TOTAL_SCANNED,0)+ISNULL(D2.CLOSING,0))-ISNULL(D1.LOGGED_COUNT,0))AS CLOSING, D2.VCLOSING AS VOPENING,D1.VERIFIED_COUNT,D1.VDELETE_INVOICE, (ISNULL(D2.VCLOSING,0)+ISNULL(D1.VERIFIED_COUNT,0))AS VTOTAL, (((ISNULL(D1.LOGGED_COUNT,0)+ISNULL(D1.E_INVOICE,0))+ISNULL(D2.VCLOSING,0))-ISNULL(D1.VERIFIED_COUNT,0))AS VCLOSING, D1.PROCESSED_COUNT,D1.PAID_COUNT FROM CTE2 AS D1 CROSS JOIN CTE3 AS D2 WHERE D2.ROWID+1=D1.ROWID ) SELECT * FROM CTE3 WHERE convert(DATETIME,DATE,103) BETWEEN CONVERT(DATETIME,@FROMDATE,103) AND CONVERT(DATETIME,@TODATE,103) OPTION (MAXRECURSION 0) Result