1. Finding last time table was updated
SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate
2. USING CURSOR IN SQL SERVER
-- exec sp_sc_daily_green_leaf_report_mod '2009-11-04',0,'C1','G1'
/****** Object: Stored Procedure dbo.sp_sc_daily_green_leaf_report_mod Script Date: 11/02/2002 2:15:51 PM ******/
ALTER PROC [dbo].[sp_sc_daily_green_leaf_report_mod]
@TODATE VARCHAR(10) ,
@DIVISION_ID INT=0 ,
@COMP_CODE VARCHAR(8) ,
@GRDN_CODE VARCHAR(8)
AS
DECLARE @COMPANY VARCHAR(100)
DECLARE @GARDEN VARCHAR(100)
DECLARE @DIVN_ID INT
DECLARE @DIVISION_NAME VARCHAR(100)
DECLARE @FROMDATE VARCHAR(10)
DECLARE @SECTION_ID INT
DECLARE @NO_OF_PLUCKERS SMALLINT
DECLARE @AREA_USED MONEY
DECLARE @LEAF_PLUCKED MONEY
DECLARE @AREA1 MONEY
DECLARE @AREA2 MONEY
DECLARE @AREA3 MONEY
DECLARE @AREA4 MONEY
DECLARE @AMT1 MONEY
DECLARE @AMT2 MONEY
DECLARE @AMT3 MONEY
DECLARE @AMT4 MONEY
SELECT @COMPANY=COMP_NAME FROM M_COMPANY WHERE COMP_CODE=@COMP_CODE
SELECT @GARDEN=GRDN_NAME FROM M_GARDEN WHERE GRDN_CODE=@GRDN_CODE
CREATE TABLE #TEMP_1
(
DIVISION_ID INT ,
DIVISION_NAME VARCHAR(50) ,
SECTION_ID INT ,
NO_OF_PLUCKERS SMALLINT ,
AREA_USED NUMERIC(10,4) ,
LEAF_PLUCKED MONEY ,
AREA1 MONEY ,
AREA2 MONEY ,
AREA3 MONEY ,
AREA4 MONEY ,
AMT1 MONEY ,
AMT2 MONEY ,
AMT3 MONEY ,
AMT4 MONEY
)
IF @DIVISION_ID =0
BEGIN
DECLARE CUR1 CURSOR FOR
SELECT S.DIVISION_ID,S.SECTION_ID
FROM MS_SECTION S,MS_SUB_ACTIVITY A,M_ACTIVITY C,
TS_SEC_DAILY_PROG G WHERE
G.SECTION_ID = S.SECTION_ID
AND A.SUB_ACTIVITY_ID=G.ACTIVITY_ID AND
A.ACTIVITY_ID = C.ACTIVITY_ID
--AND C.ACTIVITY_CODE = 6
AND
convert(datetime,convert(varchar(10),G.TO_DATE,101)) = convert(datetime,convert(varchar(10),@TODATE,101))
END
ELSE
BEGIN
DECLARE CUR1 CURSOR FOR
SELECT S.DIVISION_ID,S.SECTION_ID
FROM MS_SECTION S,MS_SUB_ACTIVITY A,M_ACTIVITY C,
TS_SEC_DAILY_PROG G
WHERE S.DIVISION_ID=@DIVISION_ID AND G.SECTION_ID = S.SECTION_ID
AND A.SUB_ACTIVITY_ID=G.ACTIVITY_ID AND
A.ACTIVITY_ID = C.ACTIVITY_ID
--AND C.ACTIVITY_CODE = 6
AND
convert(datetime,convert(varchar(10),G.TO_DATE,101)) = convert(datetime,convert(varchar(10),@TODATE,101))
END
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @DIVN_ID,@SECTION_ID
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @FROMDATE=' '
SELECT @NO_OF_PLUCKERS=0
SELECT @AREA_USED=0
SELECT @LEAF_PLUCKED=0
SELECT @AREA1=0
SELECT @AREA2=0
SELECT @AREA3=0
SELECT @AREA4=0
SELECT @AMT1=0
SELECT @AMT2=0
SELECT @AMT3=0
SELECT @AMT4=0
--RETRIVE LAST PLUCKED DATE FOR EACH SECTION
SELECT @FROMDATE=(CASE WHEN MAX(A.TO_DATE) IS NULL THEN ' ' ELSE CONVERT(VARCHAR(10),MAX(A.TO_DATE),101) END)
FROM ((TS_SEC_DAILY_PROG A INNER JOIN MS_SUB_ACTIVITY B ON A.ACTIVITY_ID=B.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY C ON B.ACTIVITY_ID=C.ACTIVITY_ID)
INNER JOIN MS_SECTION D ON A.SECTION_ID=D.SECTION_ID
WHERE D.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID
--AND C.ACTIVITY_CODE=6
AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))
IF @FROMDATE = ' ' --IF @FROMDATE IS NULL EXIT THE LOOP
select @FROMDATE=convert(varchar(10),(convert(datetime,convert(varchar(10),@TODATE,101))-10),101) ---mod
IF @FROMDATE <> ' ' --IF @FROMDATE IS NULL EXIT THE LOOP
BEGIN
--INSERT TOTAL NO OF PLUCKERS,TOTAL AREA PLUCKED,TOTAL AMOUNT OF LEAF PLUCKED
--FOR EACH SECTION OF THE DIVISION IN BETWEEN LAST PLUCKED DATE AND TODATE
SELECT @DIVISION_NAME=DIVISION_NAME
FROM MS_DIVISION
WHERE DIVISION_ID=@DIVN_ID
SELECT @NO_OF_PLUCKERS =(ISNULL(SUM(A.PERM_MAN_ACTUAL),0) + ISNULL(SUM(A.PERM_WOMAN_ACTUAL),0) +
ISNULL(SUM(A.PERM_ADOL_ACTUAL),0) + ISNULL(SUM(A.TEMP_MAN_ACTUAL),0) +
ISNULL(SUM(A.TEMP_WOMAN_ACTUAL),0) + ISNULL(SUM(A.TEMP_ADOL_ACTUAL),0))
FROM (((TS_SEC_DAILY_PROG A
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID
AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))= convert(datetime,convert(varchar(10),@TODATE,101))
SELECT @AREA_USED=SUM(A.AREA_USED)
FROM (((TS_SEC_DAILY_PROG A
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))= convert(datetime,convert(varchar(10),@TODATE,101))
SELECT @LEAF_PLUCKED=(ISNULL(SUM(D.SHIFT1_LEAF_PLUCKED),0) + ISNULL(SUM(D.SHIFT2_LEAF_PLUCKED),0) +
ISNULL(SUM(D.SHIFT3_LEAF_PLUCKED),0) + ISNULL(SUM(D.SHIFT4_LEAF_PLUCKED),0) +
ISNULL(SUM(D.SHIFT5_LEAF_PLUCKED),0))
FROM ((((TS_SEC_DAILY_PROG A
INNER JOIN TS_PLUCK_PROGRESS D ON A.SEC_DAILY_PROG_ID=D.SECTION_PROG_ID)
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101)) = convert(datetime,convert(varchar(10),@TODATE,101))
-- CALCULATE AREA PLUCKED IF DIFFERENCE BETWEEN LAST PLUCKED DATE AND
-- TODATE IS <=7 DAYS .
SELECT @AREA1=A.AREA_USED
FROM (((TS_SEC_DAILY_PROG A
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) <=7
-- CALCULATE AREA PLUCKED ON 8TH DAY FROM LAST PLUCKED DATE.
SELECT @AREA2=A.AREA_USED
FROM (((TS_SEC_DAILY_PROG A
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) =8
-- CALCULATE AREA PLUCKED ON 9TH DAY FROM LAST PLUCKED DATE.
SELECT @AREA3=A.AREA_USED
FROM (((TS_SEC_DAILY_PROG A
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) =9
-- CALCULATE AREA PLUCKED IN BETWEEN 10 DAYS AFTER LAST PLUCKED DATE AND TODATE.
SELECT @AREA4=A.AREA_USED
FROM (((TS_SEC_DAILY_PROG A
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) >=10
-- CATCULATE AMT. LEAF PLUCKED IN BETWEEN LAST PLUCKED DATE AND 7 DAYS THEREAFTER.
SELECT @AMT1=(ISNULL(D.SHIFT1_LEAF_PLUCKED,0) + ISNULL(D.SHIFT2_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT3_LEAF_PLUCKED,0) + ISNULL(D.SHIFT4_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT5_LEAF_PLUCKED,0))
FROM ((((TS_SEC_DAILY_PROG A
INNER JOIN TS_PLUCK_PROGRESS D ON A.SEC_DAILY_PROG_ID=D.SECTION_PROG_ID)
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) <=7
-- CALCULATE AMT.LEAF PLUCKED ON 8TH DAY FROM LAST PLUCKED DATE.
SELECT @AMT2=(ISNULL(D.SHIFT1_LEAF_PLUCKED,0) + ISNULL(D.SHIFT2_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT3_LEAF_PLUCKED,0) + ISNULL(D.SHIFT4_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT5_LEAF_PLUCKED,0))
FROM ((((TS_SEC_DAILY_PROG A
INNER JOIN TS_PLUCK_PROGRESS D ON A.SEC_DAILY_PROG_ID=D.SECTION_PROG_ID)
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) =8
-- CALCULATE AMT.LEAF PLUCKED ON 9TH DAY FROM LAST PLUCKED DATE.
SELECT @AMT3=(ISNULL(D.SHIFT1_LEAF_PLUCKED,0) + ISNULL(D.SHIFT2_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT3_LEAF_PLUCKED,0) + ISNULL(D.SHIFT4_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT5_LEAF_PLUCKED,0))
FROM ((((TS_SEC_DAILY_PROG A
INNER JOIN TS_PLUCK_PROGRESS D ON A.SEC_DAILY_PROG_ID=D.SECTION_PROG_ID)
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) =9
-- CALCULATE AMT. LEAF PLUCKED IN BETWEEN 10 DAYS AFTER LAST PLUCKED DATE AND TODATE.
SELECT @AMT4=(ISNULL(D.SHIFT1_LEAF_PLUCKED,0) + ISNULL(D.SHIFT2_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT3_LEAF_PLUCKED,0) + ISNULL(D.SHIFT4_LEAF_PLUCKED,0) +
ISNULL(D.SHIFT5_LEAF_PLUCKED,0))
FROM ((((TS_SEC_DAILY_PROG A
INNER JOIN TS_PLUCK_PROGRESS D ON A.SEC_DAILY_PROG_ID=D.SECTION_PROG_ID)
INNER JOIN MS_SECTION B ON A.SECTION_ID=B.SECTION_ID)
INNER JOIN MS_DIVISION C ON B.DIVISION_ID=C.DIVISION_ID)
INNER JOIN MS_SUB_ACTIVITY E ON A.ACTIVITY_ID=E.SUB_ACTIVITY_ID)
INNER JOIN M_ACTIVITY F ON E.ACTIVITY_ID=F.ACTIVITY_ID
WHERE C.DIVISION_ID=@DIVN_ID AND A.SECTION_ID=@SECTION_ID AND
--F.ACTIVITY_CODE=6 AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))>=convert(datetime,convert(varchar(10),@FROMDATE,101)) AND
convert(datetime,convert(varchar(10),A.TO_DATE,101))<=convert(datetime,convert(varchar(10),@TODATE,101)) AND
DATEDIFF(DD,CONVERT(DATETIME,@FROMDATE),CONVERT(DATETIME,@TODATE)) >=10
-- INSERT ALL VALUES TO THE TABLE
INSERT INTO #TEMP_1
(
DIVISION_ID ,
DIVISION_NAME ,
SECTION_ID ,
NO_OF_PLUCKERS ,
AREA_USED ,
LEAF_PLUCKED ,
AREA1 ,
AREA2 ,
AREA3 ,
AREA4 ,
AMT1 ,
AMT2 ,
AMT3 ,
AMT4
)
SELECT @DIVN_ID ,
@DIVISION_NAME ,
@SECTION_ID ,
@NO_OF_PLUCKERS ,
@AREA_USED ,
@LEAF_PLUCKED ,
@AREA1 ,
@AREA2 ,
@AREA3 ,
@AREA4 ,
@AMT1 ,
@AMT2 ,
@AMT3 ,
@AMT4
END
FETCH NEXT FROM CUR1 INTO @DIVN_ID,@SECTION_ID
END
CLOSE CUR1
DEALLOCATE CUR1
-- SELECT DIVISION_ID AS [Division ID] ,
-- DIVISION_NAME AS [Division Name] ,
-- SUM(NO_OF_PLUCKERS) AS [No of Pluckers] ,
-- SUM(AREA_USED) AS [Area Used] ,
-- SUM(LEAF_PLUCKED) AS [Leaf Plucked] ,
-- SUM(AREA1) AS [7 Days & Less] ,
-- SUM(AREA2) AS [8 Days] ,
-- SUM(AREA3) AS [9 Days] ,
-- SUM(AREA4) AS [10 Days & Above] ,
-- SUM(AMT1) AS [7 Days & Less] ,
-- SUM(AMT2) AS T_AMT2 ,
-- SUM(AMT3) AS T_AMT3 ,
-- SUM(AMT4) AS T_AMT4 ,
-- @COMPANY AS COMPANY ,
-- @GARDEN AS GARDEN ,
-- CONVERT(DATETIME,@TODATE)AS TO_DATE
-- FROM #TEMP_1
-- GROUP BY DIVISION_ID,DIVISION_NAME
SELECT
DIVISION_NAME AS [Division Name] ,
SUM(LEAF_PLUCKED) AS [Leaf Plucked] ,
SUM(NO_OF_PLUCKERS) AS [No of Pluckers] ,
ROUND(SUM(AREA_USED),2) AS [Area Used],
CAST(SUM(NO_OF_PLUCKERS) / SUM(AREA_USED) AS NUMERIC(18,2)) AS [Pluckers Per Hectare]
FROM #TEMP_1
GROUP BY DIVISION_NAME
SELECT
DIVISION_NAME AS [Division Name] ,
--'Area (Hect)' as Days,
SUM(AREA1) AS [7 Days & Less] ,
SUM(AREA2) AS [8 Days] ,
SUM(AREA3) AS [9 Days] ,
SUM(AREA4) AS [10 Days & Above] ,
SUM(AREA1) + SUM(AREA2) + SUM(AREA3) + SUM(AREA4) AS Total
FROM #TEMP_1
GROUP BY DIVISION_NAME
SELECT
DIVISION_NAME AS [Division Name] ,
--'Quantity (Kgs)' AS Days,
SUM(AMT1) AS [7 Days & Less] ,
SUM(AMT2) AS [8 Days] ,
SUM(AMT3) AS [9 Days] ,
SUM(AMT4) AS [10 Days & Above],
SUM(AMT1) + SUM(AMT2) + SUM(AMT3) + SUM(AMT4) AS Total
FROM #TEMP_1
GROUP BY DIVISION_NAME
DROP TABLE #TEMP_1
**********************************************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- exec MIS_sp_st_grn_pay_pend '2009-11-01','2009-12-01','C1','G1',1,0
-- =============================================
-- Author:
-- Create date: <15-12-2009>
-- Description:
-- =============================================
ALTER PROC [dbo].[MIS_sp_st_grn_pay_pend]
@FROMDATE VARCHAR(11),
@TODATE VARCHAR(11),
@COMP_CODE VARCHAR(4) ,
@GRDN_CODE VARCHAR(4),
@CMP1 INT,
@CMP2 INT
AS
IF @CMP1 = 1
BEGIN
IF @CMP2 = 0
BEGIN
SELECT D.STORE_NAME as [Store Name],E.PARTY_NAME_FIRST as [Party Name],J.BILL_NO as [Bill No],dbo.fn_Date(C.GRN_DATE) as [GRN Date],C.GRN_NO as [GRN No],ISNULL(C.CUST_CHALLAN_NO," ") AS CHALLAN,B.ITEM_NAME as [Item Name],M.UNIT_NAME as Unit,A.ITEM_QTY as [Item QTY] , A.ITEM_RATE as Price,dbo.fn_DecimalPlaces( A.ITEM_QTY*A.ITEM_RATE) as Amount
FROM TI_GRN_DETAIL A
INNER JOIN MI_ITEM B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TI_GRN_MAIN C ON A.GRN_ID =C.GRN_ID
INNER JOIN MI_STORE D ON C.STORE_ID=D.STORE_ID
INNER JOIN MS_PARTY E ON C.VEND_CUST_ID=E.PARTY_ID
INNER JOIN M_COMPANY H ON C.COMP_CODE=H.COMP_CODE
INNER JOIN M_GARDEN I ON C.GRDN_CODE=I.GRDN_CODE
-- INNER JOIN TI_BILL_MAIN J ON C.GRN_ID=J.BILL_ID
INNER JOIN TI_BILL_DETAIL K ON A.GRN_DETAIL_ID = K.GRN_DETAIL_ID
INNER JOIN TI_BILL_MAIN J ON K.BILL_ID = J.BILL_ID
INNER JOIN MS_UNIT M ON B.UNIT_ID=M.UNIT_ID
WHERE CONVERT(DATETIME,C.GRN_DATE) BETWEEN
@FROMDATE
AND @TODATE AND J.BILL_NO LIKE 'FB%' AND
C.COMP_CODE=@COMP_CODE AND
C.GRDN_CODE=@GRDN_CODE AND C.GRN_ID IN (SELECT DISTINCT(GRN_ID) FROM TI_GRN_DETAIL WHERE GRN_DETAIL_ID IN (SELECT GRN_DETAIL_ID FROM TI_BILL_DETAIL WHERE comp_code=@COMP_CODE AND grdn_code=@GRDN_CODE))
ORDER BY D.STORE_NAME,C.GRN_DATE,E.PARTY_NAME_FIRST
END
ELSE
BEGIN
SELECT D.STORE_NAME as [Store Name],E.PARTY_NAME_FIRST as [Party Name],J.BILL_NO as [Bill No],dbo.fn_Date(C.GRN_DATE) as [GRN Date],C.GRN_NO as [GRN No],ISNULL(C.CUST_CHALLAN_NO," ") AS CHALLAN,B.ITEM_NAME as [Item Name],M.UNIT_NAME as Unit,A.ITEM_QTY as [Item QTY] , A.ITEM_RATE as Price,dbo.fn_DecimalPlaces( A.ITEM_QTY*A.ITEM_RATE) as Amount
FROM TI_GRN_DETAIL A
INNER JOIN MI_ITEM B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TI_GRN_MAIN C ON A.GRN_ID =C.GRN_ID
INNER JOIN MI_STORE D ON C.STORE_ID=D.STORE_ID
INNER JOIN MS_PARTY E ON C.VEND_CUST_ID=E.PARTY_ID
INNER JOIN M_COMPANY H ON C.COMP_CODE=H.COMP_CODE
INNER JOIN M_GARDEN I ON C.GRDN_CODE=I.GRDN_CODE
-- INNER JOIN TI_BILL_MAIN J ON C.GRN_ID=J.BILL_ID
INNER JOIN TI_BILL_DETAIL K ON A.GRN_DETAIL_ID = K.GRN_DETAIL_ID
INNER JOIN TI_BILL_MAIN J ON K.BILL_ID = J.BILL_ID
INNER JOIN MS_UNIT M ON B.UNIT_ID=M.UNIT_ID
WHERE CONVERT(DATETIME,C.GRN_DATE) BETWEEN
@FROMDATE
AND @TODATE AND J.BILL_NO LIKE 'FB%' AND
C.COMP_CODE=@COMP_CODE AND C.STORE_ID=@CMP2 AND
C.GRDN_CODE=@GRDN_CODE AND C.GRN_ID IN (SELECT DISTINCT(GRN_ID) FROM TI_GRN_DETAIL WHERE GRN_DETAIL_ID IN (SELECT GRN_DETAIL_ID FROM TI_BILL_DETAIL WHERE comp_code = @COMP_CODE AND grdn_code = @GRDN_CODE))
ORDER BY D.STORE_NAME,C.GRN_DATE,E.PARTY_NAME_FIRST
END
END
ELSE
IF @CMP1 = 2
BEGIN
IF @CMP2 = 0
BEGIN
SELECT D.STORE_NAME as [Store Name],E.PARTY_NAME_FIRST as [Party Name],J.BILL_NO as [Bill No],dbo.fn_Date(C.GRN_DATE) as [GRN Date],C.GRN_NO as [GRN No],ISNULL(C.CUST_CHALLAN_NO," ") AS CHALLAN,B.ITEM_NAME as [Item Name],M.UNIT_NAME as Unit,A.ITEM_QTY as [Item QTY] , A.ITEM_RATE as Price,dbo.fn_DecimalPlaces( A.ITEM_QTY*A.ITEM_RATE) as Amount
FROM TI_GRN_DETAIL A
INNER JOIN MI_ITEM B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TI_GRN_MAIN C ON A.GRN_ID =C.GRN_ID
INNER JOIN MI_STORE D ON C.STORE_ID=D.STORE_ID
INNER JOIN MS_PARTY E ON C.VEND_CUST_ID=E.PARTY_ID
INNER JOIN M_COMPANY H ON C.COMP_CODE=H.COMP_CODE
INNER JOIN M_GARDEN I ON C.GRDN_CODE=I.GRDN_CODE
-- INNER JOIN TI_BILL_MAIN J ON C.GRN_ID=J.BILL_ID
INNER JOIN TI_BILL_DETAIL K ON A.GRN_DETAIL_ID = K.GRN_DETAIL_ID
INNER JOIN TI_BILL_MAIN J ON K.BILL_ID = J.BILL_ID
INNER JOIN MS_UNIT M ON B.UNIT_ID=M.UNIT_ID
WHERE CONVERT(DATETIME,C.GRN_DATE) BETWEEN
@FROMDATE
AND @TODATE AND J.BILL_NO LIKE 'FB%' AND
C.COMP_CODE=@COMP_CODE AND
C.GRDN_CODE=@GRDN_CODE AND C.GRN_ID IN (SELECT DISTINCT(GRN_ID) FROM TI_GRN_DETAIL WHERE GRN_DETAIL_ID IN (SELECT GRN_DETAIL_ID FROM TI_BILL_DETAIL WHERE comp_code=@COMP_CODE AND grdn_code=@GRDN_CODE))
ORDER BY B.ITEM_CODE,D.STORE_NAME,C.GRN_DATE,E.PARTY_NAME_FIRST
END
ELSE
BEGIN
SELECT D.STORE_NAME as [Store Name],E.PARTY_NAME_FIRST as [Party Name],J.BILL_NO as [Bill No],dbo.fn_Date(C.GRN_DATE) as [GRN Date],C.GRN_NO as [GRN No],ISNULL(C.CUST_CHALLAN_NO," ") AS CHALLAN,B.ITEM_NAME as [Item Name],M.UNIT_NAME as Unit,A.ITEM_QTY as [Item QTY] , A.ITEM_RATE as Price,dbo.fn_DecimalPlaces( A.ITEM_QTY*A.ITEM_RATE) as Amount
FROM TI_GRN_DETAIL A
INNER JOIN MI_ITEM B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TI_GRN_MAIN C ON A.GRN_ID =C.GRN_ID
INNER JOIN MI_STORE D ON C.STORE_ID=D.STORE_ID
INNER JOIN MS_PARTY E ON C.VEND_CUST_ID=E.PARTY_ID
INNER JOIN M_COMPANY H ON C.COMP_CODE=H.COMP_CODE
INNER JOIN M_GARDEN I ON C.GRDN_CODE=I.GRDN_CODE
-- INNER JOIN TI_BILL_MAIN J ON C.GRN_ID=J.BILL_ID
INNER JOIN TI_BILL_DETAIL K ON A.GRN_DETAIL_ID = K.GRN_DETAIL_ID
INNER JOIN TI_BILL_MAIN J ON K.BILL_ID = J.BILL_ID
INNER JOIN MS_UNIT M ON B.UNIT_ID=M.UNIT_ID
WHERE CONVERT(DATETIME,C.GRN_DATE) BETWEEN
@FROMDATE
AND @TODATE AND J.BILL_NO LIKE 'FB%' AND
C.COMP_CODE=@COMP_CODE AND A.ITEM_ID = @CMP2 AND
C.GRDN_CODE=@GRDN_CODE AND C.GRN_ID IN (SELECT DISTINCT(GRN_ID) FROM TI_GRN_DETAIL WHERE GRN_DETAIL_ID IN (SELECT GRN_DETAIL_ID FROM TI_BILL_DETAIL WHERE comp_code=@COMP_CODE AND grdn_code=@GRDN_CODE))
ORDER BY B.ITEM_CODE,D.STORE_NAME,C.GRN_DATE,E.PARTY_NAME_FIRST
END
END
ELSE
BEGIN
IF @CMP2 = 0
BEGIN
SELECT D.STORE_NAME as [Store Name],E.PARTY_NAME_FIRST as [Party Name],J.BILL_NO as [Bill No],dbo.fn_Date(C.GRN_DATE) as [GRN Date],C.GRN_NO as [GRN No],ISNULL(C.CUST_CHALLAN_NO," ") AS CHALLAN,B.ITEM_NAME as [Item Name],M.UNIT_NAME as Unit,A.ITEM_QTY as [Item QTY] , A.ITEM_RATE as Price,dbo.fn_DecimalPlaces( A.ITEM_QTY*A.ITEM_RATE) as Amount
FROM TI_GRN_DETAIL A
INNER JOIN MI_ITEM B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TI_GRN_MAIN C ON A.GRN_ID =C.GRN_ID
INNER JOIN MI_STORE D ON C.STORE_ID=D.STORE_ID
INNER JOIN MS_PARTY E ON C.VEND_CUST_ID=E.PARTY_ID
INNER JOIN M_COMPANY H ON C.COMP_CODE=H.COMP_CODE
INNER JOIN M_GARDEN I ON C.GRDN_CODE=I.GRDN_CODE
-- INNER JOIN TI_BILL_MAIN J ON C.GRN_ID=J.BILL_ID
INNER JOIN TI_BILL_DETAIL K ON A.GRN_DETAIL_ID = K.GRN_DETAIL_ID
INNER JOIN TI_BILL_MAIN J ON K.BILL_ID = J.BILL_ID
INNER JOIN MS_UNIT M ON B.UNIT_ID=M.UNIT_ID
WHERE CONVERT(DATETIME,C.GRN_DATE) BETWEEN
@FROMDATE
AND @TODATE AND J.BILL_NO LIKE 'FB%' AND
C.COMP_CODE=@COMP_CODE AND
C.GRDN_CODE=@GRDN_CODE AND C.GRN_ID IN (SELECT DISTINCT(GRN_ID) FROM TI_GRN_DETAIL WHERE GRN_DETAIL_ID IN (SELECT GRN_DETAIL_ID FROM TI_BILL_DETAIL WHERE comp_code=@COMP_CODE AND grdn_code=@GRDN_CODE))
ORDER BY E.PARTY_NAME_FIRST,D.STORE_NAME,C.GRN_DATE
END
ELSE
BEGIN
SELECT D.STORE_NAME as [Store Name],E.PARTY_NAME_FIRST as [Party Name],J.BILL_NO as [Bill No],dbo.fn_Date(C.GRN_DATE) as [GRN Date],C.GRN_NO as [GRN No],ISNULL(C.CUST_CHALLAN_NO," ") AS CHALLAN,B.ITEM_NAME as [Item Name],M.UNIT_NAME as Unit,A.ITEM_QTY as [Item QTY] , A.ITEM_RATE as Price,dbo.fn_DecimalPlaces( A.ITEM_QTY*A.ITEM_RATE) as Amount
FROM TI_GRN_DETAIL A
INNER JOIN MI_ITEM B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TI_GRN_MAIN C ON A.GRN_ID =C.GRN_ID
INNER JOIN MI_STORE D ON C.STORE_ID=D.STORE_ID
INNER JOIN MS_PARTY E ON C.VEND_CUST_ID=E.PARTY_ID
INNER JOIN M_COMPANY H ON C.COMP_CODE=H.COMP_CODE
INNER JOIN M_GARDEN I ON C.GRDN_CODE=I.GRDN_CODE
-- INNER JOIN TI_BILL_MAIN J ON C.GRN_ID=J.BILL_ID
INNER JOIN TI_BILL_DETAIL K ON A.GRN_DETAIL_ID = K.GRN_DETAIL_ID
INNER JOIN TI_BILL_MAIN J ON K.BILL_ID = J.BILL_ID
INNER JOIN MS_UNIT M ON B.UNIT_ID=M.UNIT_ID
WHERE CONVERT(DATETIME,CONVERT(VARCHAR(10),C.GRN_DATE,101))
BETWEEN CONVERT(DATETIME,@FROMDATE)
AND CONVERT(DATETIME,@TODATE)AND
C.COMP_CODE=@COMP_CODE AND C.VEND_CUST_ID = @CMP2 AND
C.GRDN_CODE=@GRDN_CODE AND C.GRN_ID IN (SELECT DISTINCT(GRN_ID) FROM TI_GRN_DETAIL WHERE GRN_DETAIL_ID IN (SELECT GRN_DETAIL_ID FROM TI_BILL_DETAIL WHERE comp_code=@COMP_CODE AND grdn_code=@GRDN_CODE)) AND J.BILL_NO='FB'
ORDER BY E.PARTY_NAME_FIRST,D.STORE_NAME,C.GRN_DATE
END
END
3. Using UNION IN QUERY SQL SERVER:
///*********************************************///
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GET_ITEM_WISE_INDENT_HEAD_WISE]
(
@FromDate AS DATETIME,
@ToDate AS DATETIME,
@CatHeadID VARCHAR
)
AS
BEGIN
SELECT INDENT_MASTER.INDENT_CODE, INDENT_MASTER.INDENT_DATE, INDENT_MASTER.REQUIRED_DATE,
INDENT_MASTER.INDENT_REMARKS, INDENT_MASTER.INDENT_STATUS, INDENT_DETAIL.ITEM_QTY_REQ, INDENT_DETAIL.ITEM_QTY_PO,
INDENT_DETAIL.ITEM_QTY_BAL, INDENT_MASTER.INDENT_NO, ITEM_MASTER.ITEM_NAME, DIVISION_SETTINGS.DIVISION_NAME,
dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME
FROM dbo.INDENT_DETAIL AS INDENT_DETAIL INNER JOIN
dbo.INDENT_MASTER AS INDENT_MASTER ON INDENT_DETAIL.INDENT_ID = INDENT_MASTER.INDENT_ID INNER JOIN
dbo.ITEM_MASTER AS ITEM_MASTER ON INDENT_DETAIL.ITEM_ID = ITEM_MASTER.ITEM_ID INNER JOIN
dbo.DIVISION_SETTINGS AS DIVISION_SETTINGS ON INDENT_MASTER.DIVISION_ID = DIVISION_SETTINGS.DIV_ID INNER JOIN
dbo.ITEM_CATEGORY_HEAD_MASTER ON ITEM_MASTER.ITEM_CATEGORY_ID = dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID
WHERE (CAST(dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME AS VARCHAR) LIKE @CatHeadID) AND INDENT_MASTER.INDENT_DATE BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate)
ORDER BY INDENT_MASTER.INDENT_NO
END
/////**************************************************************////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GET_MATERIAL_ISSUE_CC_ITEM_WISE_HEAD_WISE]
@FromDate datetime,
@ToDate datetime,
@costid VARCHAR,
@CatID varchar
As
BEGIN
SELECT ITEM_MASTER.ITEM_NAME, UNIT_MASTER.UM_Name, MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ISSUED_QTY,
MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_DATE,
MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_CODE + CAST(MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_NO AS varchar)
AS issue_slip_no, MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ITEM_RATE, MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.REQ_QTY,
STOCK_DETAIL.Batch_no, ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID, ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME,dbo.ITEM_CATEGORY.ITEM_CAT_ID
FROM MATERIAL_ISSUE_TO_COST_CENTER_DETAIL INNER JOIN
MATERIAL_ISSUE_TO_COST_CENTER_MASTER ON
MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.MIO_ID = MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_ID INNER JOIN
ITEM_MASTER ON MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ITEM_ID = ITEM_MASTER.ITEM_ID INNER JOIN
UNIT_MASTER ON ITEM_MASTER.UM_ID = UNIT_MASTER.UM_ID INNER JOIN
STOCK_DETAIL ON MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.STOCK_DETAIL_ID = STOCK_DETAIL.STOCK_DETAIL_ID INNER JOIN
ITEM_CATEGORY ON ITEM_MASTER.ITEM_CATEGORY_ID = ITEM_CATEGORY.ITEM_CAT_ID INNER JOIN
ITEM_CATEGORY_HEAD_MASTER ON ITEM_CATEGORY.fk_ITEM_CAT_Head_ID = ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID
WHERE (dbo.fn_Format(MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_DATE) BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate)) AND
(cast(ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID as varchar) like @costid) AND
(cast(ITEM_CATEGORY.ITEM_CAT_ID as varchar) like @CatID)
UNION
SELECT ITEM_MASTER.ITEM_NAME, UNIT_MASTER.UM_Name, NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Bal_Item_Qty AS ISSUED_QTY,
MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_Date AS MIO_DATE,
MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_PREFIX + CAST(MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_NO AS varchar)
AS issue_slip_no, NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_Rate, NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_Qty,
NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.batch_no AS Batch_no, ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID,
ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME,dbo.ITEM_CATEGORY.ITEM_CAT_ID
FROM ITEM_MASTER INNER JOIN
UNIT_MASTER ON ITEM_MASTER.UM_ID = UNIT_MASTER.UM_ID INNER JOIN
NON_STOCKABLE_ITEMS_MAT_REC_WO_PO ON ITEM_MASTER.ITEM_ID = NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_ID INNER JOIN
MATERIAL_RECIEVED_WITHOUT_PO_MASTER ON
NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Received_ID = MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_ID INNER JOIN
ITEM_CATEGORY ON ITEM_MASTER.ITEM_CATEGORY_ID = ITEM_CATEGORY.ITEM_CAT_ID INNER JOIN
ITEM_CATEGORY_HEAD_MASTER ON ITEM_CATEGORY.fk_ITEM_CAT_Head_ID = ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID
WHERE (dbo.fn_Format(MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_Date) BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate)) AND
(cast(ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID as varchar) like @costid) AND
(cast(ITEM_CATEGORY.ITEM_CAT_ID as varchar) like @CatID)
End
******************************************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GET_ITEM_WISE_INDENT_HEAD_WISE]
(
@FromDate AS DATETIME,
@ToDate AS DATETIME,
@CatHeadID VARCHAR,
@CatID VARCHAR
)
AS
BEGIN
SELECT INDENT_MASTER.INDENT_CODE, INDENT_MASTER.INDENT_DATE, INDENT_MASTER.REQUIRED_DATE, INDENT_MASTER.INDENT_REMARKS,
INDENT_MASTER.INDENT_STATUS, INDENT_DETAIL.ITEM_QTY_REQ, INDENT_DETAIL.ITEM_QTY_PO, INDENT_DETAIL.ITEM_QTY_BAL,
INDENT_MASTER.INDENT_NO, ITEM_MASTER.ITEM_NAME, DIVISION_SETTINGS.DIVISION_NAME,
dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME, dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID,
dbo.ITEM_CATEGORY.ITEM_CAT_NAME, dbo.ITEM_CATEGORY.ITEM_CAT_ID
FROM dbo.INDENT_DETAIL AS INDENT_DETAIL INNER JOIN
dbo.INDENT_MASTER AS INDENT_MASTER ON INDENT_DETAIL.INDENT_ID = INDENT_MASTER.INDENT_ID INNER JOIN
dbo.ITEM_MASTER AS ITEM_MASTER ON INDENT_DETAIL.ITEM_ID = ITEM_MASTER.ITEM_ID INNER JOIN
dbo.DIVISION_SETTINGS AS DIVISION_SETTINGS ON INDENT_MASTER.DIVISION_ID = DIVISION_SETTINGS.DIV_ID INNER JOIN
dbo.ITEM_CATEGORY ON ITEM_MASTER.ITEM_CATEGORY_ID = dbo.ITEM_CATEGORY.ITEM_CAT_ID INNER JOIN
dbo.ITEM_CATEGORY_HEAD_MASTER ON dbo.ITEM_CATEGORY.fk_ITEM_CAT_Head_ID = dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID
WHERE
(CAST(dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID AS VARCHAR) LIKE @CatHeadID)
AND (CAST(dbo.ITEM_CATEGORY.ITEM_CAT_ID AS VARCHAR) LIKE @CatID)
AND dbo.fn_Format(INDENT_MASTER.INDENT_DATE) BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate)
ORDER BY INDENT_MASTER.INDENT_NO
END
***********************************************************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GET_MATERIAL_ISSUE_CC_ITEM_WISE]
@FromDate datetime,
@ToDate datetime,
@costid VARCHAR,
@CatID VARCHAR
As
BEGIN
SELECT dbo.COST_CENTER_MASTER.CostCenter_Name, dbo.ITEM_MASTER.ITEM_NAME, dbo.UNIT_MASTER.UM_Name,
dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ISSUED_QTY, dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_DATE,
dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_CODE + CAST(dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_NO AS varchar)
AS issue_slip_no, dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ITEM_RATE, dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.REQ_QTY,
dbo.COST_CENTER_MASTER.CostCenter_Id, dbo.STOCK_DETAIL.Batch_no, dbo.ITEM_CATEGORY.ITEM_CAT_ID
FROM dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL INNER JOIN
dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER ON
dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.MIO_ID = dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_ID INNER JOIN
dbo.COST_CENTER_MASTER ON dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.CS_ID = dbo.COST_CENTER_MASTER.CostCenter_Id INNER JOIN
dbo.ITEM_MASTER ON dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ITEM_ID = dbo.ITEM_MASTER.ITEM_ID INNER JOIN
dbo.UNIT_MASTER ON dbo.ITEM_MASTER.UM_ID = dbo.UNIT_MASTER.UM_ID INNER JOIN
dbo.STOCK_DETAIL ON dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.STOCK_DETAIL_ID = dbo.STOCK_DETAIL.STOCK_DETAIL_ID INNER JOIN
dbo.ITEM_CATEGORY ON dbo.ITEM_MASTER.ITEM_CATEGORY_ID = dbo.ITEM_CATEGORY.ITEM_CAT_ID
WHERE (dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_DATE BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate) AND
(CAST(dbo.COST_CENTER_MASTER.CostCenter_Id AS VARCHAR) LIKE @costid)) AND (cast(ITEM_CATEGORY.ITEM_CAT_ID as varchar) like @CatID)
UNION
SELECT dbo.COST_CENTER_MASTER.CostCenter_Name, dbo.ITEM_MASTER.ITEM_NAME, dbo.UNIT_MASTER.UM_Name,
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Bal_Item_Qty, dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_Date,
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_PREFIX + CAST(dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_NO AS varchar)
AS issue_slip_no, dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_Rate, dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_Qty,
dbo.COST_CENTER_MASTER.CostCenter_Id, dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.batch_no, dbo.ITEM_CATEGORY.ITEM_CAT_ID
FROM dbo.ITEM_MASTER INNER JOIN
dbo.UNIT_MASTER ON dbo.ITEM_MASTER.UM_ID = dbo.UNIT_MASTER.UM_ID INNER JOIN
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO ON
dbo.ITEM_MASTER.ITEM_ID = dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_ID INNER JOIN
dbo.COST_CENTER_MASTER ON
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.CostCenter_ID = dbo.COST_CENTER_MASTER.CostCenter_Id INNER JOIN
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER ON
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Received_ID = dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_ID INNER JOIN
dbo.ITEM_CATEGORY ON dbo.ITEM_MASTER.ITEM_CATEGORY_ID = dbo.ITEM_CATEGORY.ITEM_CAT_ID
WHERE (CAST(dbo.COST_CENTER_MASTER.CostCenter_Id AS VARCHAR) LIKE @costid) AND
(cast(ITEM_CATEGORY.ITEM_CAT_ID as varchar) like @CatID) AND
(dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_Date BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate))
End
********************************************************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GET_MATERIAL_ISSUE_CC_ITEM_WISE_HEAD_WISE]
@FromDate datetime,
@ToDate datetime,
@costid VARCHAR,
@CatHeadId VARCHAR,
@CatID varchar
As
BEGIN
SELECT dbo.ITEM_MASTER.ITEM_NAME, dbo.UNIT_MASTER.UM_Name, dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ISSUED_QTY,
dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_DATE,
dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_CODE + CAST(dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_NO AS varchar)
AS issue_slip_no, dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ITEM_RATE, dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.REQ_QTY,
dbo.STOCK_DETAIL.Batch_no, dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID,
dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME, dbo.ITEM_CATEGORY.ITEM_CAT_ID,
dbo.COST_CENTER_MASTER.CostCenter_Name
FROM dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL INNER JOIN
dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER ON
dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.MIO_ID = dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_ID INNER JOIN
dbo.ITEM_MASTER ON dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.ITEM_ID = dbo.ITEM_MASTER.ITEM_ID INNER JOIN
dbo.UNIT_MASTER ON dbo.ITEM_MASTER.UM_ID = dbo.UNIT_MASTER.UM_ID INNER JOIN
dbo.STOCK_DETAIL ON dbo.MATERIAL_ISSUE_TO_COST_CENTER_DETAIL.STOCK_DETAIL_ID = dbo.STOCK_DETAIL.STOCK_DETAIL_ID INNER JOIN
dbo.ITEM_CATEGORY ON dbo.ITEM_MASTER.ITEM_CATEGORY_ID = dbo.ITEM_CATEGORY.ITEM_CAT_ID INNER JOIN
dbo.ITEM_CATEGORY_HEAD_MASTER ON
dbo.ITEM_CATEGORY.fk_ITEM_CAT_Head_ID = dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID INNER JOIN
dbo.COST_CENTER_MASTER ON dbo.MATERIAL_ISSUE_TO_COST_CENTER_MASTER.CS_ID = dbo.COST_CENTER_MASTER.CostCenter_Id
WHERE (dbo.fn_Format(MATERIAL_ISSUE_TO_COST_CENTER_MASTER.MIO_DATE) BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate)) AND
(CAST(dbo.COST_CENTER_MASTER.CostCenter_Id AS VARCHAR) LIKE @costid) AND
(cast(ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID as varchar) like @CatHeadId) AND
(cast(ITEM_CATEGORY.ITEM_CAT_ID as varchar) like @CatID)
UNION
SELECT dbo.ITEM_MASTER.ITEM_NAME, dbo.UNIT_MASTER.UM_Name, dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Bal_Item_Qty,
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_Date,
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_PREFIX + CAST(dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_NO AS varchar)
AS issue_slip_no, dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_Rate, dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_Qty,
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.batch_no, dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID,
dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_NAME, dbo.ITEM_CATEGORY.ITEM_CAT_ID,
dbo.COST_CENTER_MASTER.CostCenter_Name
FROM dbo.ITEM_MASTER INNER JOIN
dbo.UNIT_MASTER ON dbo.ITEM_MASTER.UM_ID = dbo.UNIT_MASTER.UM_ID INNER JOIN
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO ON
dbo.ITEM_MASTER.ITEM_ID = dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Item_ID INNER JOIN
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER ON
dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.Received_ID = dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_ID INNER JOIN
dbo.ITEM_CATEGORY ON dbo.ITEM_MASTER.ITEM_CATEGORY_ID = dbo.ITEM_CATEGORY.ITEM_CAT_ID INNER JOIN
dbo.ITEM_CATEGORY_HEAD_MASTER ON
dbo.ITEM_CATEGORY.fk_ITEM_CAT_Head_ID = dbo.ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID INNER JOIN
dbo.COST_CENTER_MASTER ON dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO.CostCenter_ID = dbo.COST_CENTER_MASTER.CostCenter_Id
WHERE (dbo.fn_Format(MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_Date) BETWEEN dbo.fn_Format(@FromDate) AND dbo.fn_Format(@ToDate)) AND
(CAST(dbo.COST_CENTER_MASTER.CostCenter_Id AS VARCHAR) LIKE @costid) AND
(cast(ITEM_CATEGORY_HEAD_MASTER.ITEM_CAT_Head_ID as varchar) like @CatHeadId) AND
(cast(ITEM_CATEGORY.ITEM_CAT_ID as varchar) like @CatID)
End
********************************************************************************************************
SELECT dbo.ACCOUNT_MASTER.ACC_NAME, dbo.ACCOUNT_MASTER.ACC_ID, dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_NO,
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.MRN_PREFIX,dbo.ITEM_MASTER.Item_ID, dbo.ITEM_MASTER.ITEM_NAME,
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Item_Qty, MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Item_Rate,
dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Invoice_Date, dbo.ITEM_MASTER.ITEM_DESC, dbo.UNIT_MASTER.UM_Name,
dbo.DIVISION_SETTINGS.DIVISION_NAME, dbo.ACCOUNT_MASTER.DIVISION_ID
FROM dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER INNER JOIN
(SELECT
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Bal_Item_Qty as item_qty,MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Bal_Item_Rate as item_rate, MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Division_Id,
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Bal_Item_vat as item_vat, MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Bal_Item_exice as item_exice,
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Batch_No,dbo.fn_Format(MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Expiry_Date) AS Expiry_Date ,
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Stock_Detail_Id,
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Item_ID,MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Received_ID AS Received_ID
FROM dbo.MATERIAL_RECEIVED_WITHOUT_PO_DETAIL
UNION ALL
SELECT
SUM(Bal_Item_Qty) as item_qty,Bal_Item_Rate as item_rate,
0 AS Division_Id,
Bal_Item_Vat as item_vat, Bal_Item_Exice as item_exice,
batch_no AS Batch_No,dbo.fn_Format(batch_date) AS Expiry_Date,
0 AS Stock_Detail_Id,
Item_ID,Received_ID AS Received_ID
FROM dbo.NON_STOCKABLE_ITEMS_MAT_REC_WO_PO
GROUP BY Item_ID,Received_ID,Bal_Item_Rate,Bal_Item_Vat,Bal_Item_Exice,batch_no,dbo.fn_Format(batch_date)
) AS MATERIAL_RECEIVED_WITHOUT_PO_DETAIL
ON
MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Received_ID = dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Received_ID INNER JOIN
dbo.ACCOUNT_MASTER ON dbo.MATERIAL_RECIEVED_WITHOUT_PO_MASTER.Vendor_ID = dbo.ACCOUNT_MASTER.ACC_ID INNER JOIN
dbo.ITEM_MASTER ON MATERIAL_RECEIVED_WITHOUT_PO_DETAIL.Item_ID = dbo.ITEM_MASTER.ITEM_ID INNER JOIN
dbo.UNIT_MASTER ON dbo.ITEM_MASTER.UM_ID = dbo.UNIT_MASTER.UM_ID INNER JOIN
dbo.DIVISION_SETTINGS ON dbo.ACCOUNT_MASTER.DIVISION_ID = dbo.DIVISION_SETTINGS.DIV_ID
_______________________________________________________________________________
Top 3rd Top nth Salary Queries
SELECT MAX(salary) AS salary FROM [SalaryMaster]
WHERE salary IN((SELECT salary FROM [SalaryMaster]
WHERE salary <(SELECT MAX(salary) FROM [SalaryMaster])))
--- nth salary ----
SELECT salary FROM
(SELECT ROW_NUMBER() OVER (ORDER BY salary desc) AS rowno, salary FROM salarymaster GROUP BY salary)t
WHERE rowno = 3
-- ORDER BY salary DESC
SELECT * FROM [SalaryMaster] ORDER BY [Salary] DESC
---- Creating salary table -----
--CREATE TABLE [dbo].[Salary](
-- [EmpID] [varchar](10) NULL,
-- [Mnth] [int] NULL,
-- [FY] [int] NULL,
-- [SalType] [varchar](10) NULL,
-- [Amt] [int] NULL
--) ON [PRIMARY]
--- nth salary ----
with tbl
as
(SELECT (ROW_NUMBER() OVER (ORDER BY Amt desc)) as topnsalary, Amt FROM SALARY)
select * from tbl where
topnsalary = 4
--- nth salary ----
SELECT Amt,EMPID FROM
( SELECT Amt,EMPID,ROW_NUMBER() OVER(ORDER BY Amt) As RowNum
FROM SALARY ) As A
WHERE A.RowNum = 10
----- 2nd Highest salary ------
SELECT MAX(Amt) AS salary FROM [Salary]
WHERE Amt IN((SELECT Amt FROM [Salary]
WHERE Amt <(SELECT MAX(Amt) FROM [Salary])))
________________________________________________________________________________
SWAP Query swap column values
--Create table tb1 (ID int, Name Varchar(50))
--Create table tb2 (ID varchar(50), Name Varchar(50))
--Insert into tb1
--Select 1, 'A'
--Union All
--Select 2, 'B'
--Union All
--Select 3, 'C'
--Union All
--Select 4, 'D'
--Insert into tb2
--Select 'Kamal', 'A,B'
--Union All
--Select 'Ganesh', 'B,C'
--Union All
--Select 'Pankaj', 'C,D'
--Union All
--Select 'Sandeep', 'D,B'
--Union All
--Select 'Vikash', 'D,A'
--Select * from tb1
--Select * from tb2
Select DISTINCT A.ID, Substring(C.Name,0,len(C.Name)) as [OUTPUT] from tb2 A
CROSS APPLY
(
Select D.Name + ', '
FROM
(
Select ID, (Select max(Cast(ID as varchar)) as ID from tb1 where tb1.Name = Final.String) as Name
FROM
(
SELECT B.ID, Split.a.value('.', 'VARCHAR(100)') AS String
FROM
(
SELECT ID, CAST('
FROM tb2
) AS B CROSS APPLY String.nodes('/M') AS Split(a)
) as Final
) As D Where A.ID = D.ID FOR XML Path('')
) AS C (Name)
Select tb2.ID, tb2.Name, Stuff((Select ',' + CAST(tb1.ID as Varchar) from tb1 where tb2.Name like '%'+tb1.Name+'%' FOR XML Path('')),1,1,'') as ConvName
from tb2
SQL SERVER – 2005 – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER
USE AdventureWorks
GOSELECTt.*FROM(SELECTe1.*,row_number() OVER (ORDER BY e1.Rate DESC) AS _RankFROMHumanResources.EmployeePayHistory AS e1) AS tWHEREt._Rank = 4
No comments:
Post a Comment