Thursday, 31 January 2013
Friday, 11 January 2013
VLOOKUP Function Argumentslookup_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
Subscribe to:
Posts (Atom)