/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Wednesday, July 24, 2013

SQL SERVER - Important Query Part-1


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('' + REPLACE(Name, ',', '') + '' AS XML) AS String
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
GO
SELECTt.*FROM(SELECTe1.*,row_number() OVER (ORDER BY e1.Rate DESCAS _RankFROMHumanResources.EmployeePayHistory AS e1AS tWHEREt._Rank 4

No comments:

Post a Comment

Recent Posts

My Blog List