Partial Classes in C#

Partial classes is a new feature of OOPs in .NET2.0 

Partial classes means split the class into multiple files.
When compiled all the files will be treated as a single class.

it may be helpful in large projects,so many people can work
on same class.

Advantage: 


It is especially useful for: Allowing multiple developers to work on a single class at
the same time without the need for later merging files in
source control.


One of the greatest benefits of partial classes is that it 
allows a clean separation of business logic and the user 
interface (in particular the code that is generated by the 
visual designer). 

Using partial classes, the UI code can be
hidden from the developer, who usually has no need to
access it anyway. Partial classes will also make debugging
easier, as the code is partitioned into separate files.

Example:

Program that uses partial class: C#

class Program
{
    static void Main()
    {
 A.A1();
 A.A2();
    }
}

Contents of file A1.cs: C#

using System;

partial class A
{
    public static void A1()
    {
 Console.WriteLine("A1");
    }
}

Contents of file A2.cs: C#

using System;

partial class A
{
    public static void A2()
    {
 Console.WriteLine("A2");
    }
}

Output

A1
A2

To split a class definition, use the partial keyword modifier, as shown below:

C#








public partial class Employee
{
    public void DoWork()
    {
    }
}

public partial class Employee
{
    public void GoToLunch()
    {
    }
}






The partial modifier can only appear immediately before the keywords class, struct, or interface.

Partial Class :

We were declaring a class in a single file but Partial class is a feature which allows us to write class across multiple files.
The partial indicates that the parts of the class, struct, or interface can be defined in the namespace. All the parts must be used with the partial keyword. All the parts must be available at compile time to form the final type or final class. All the parts must have the same accessibility level, such as public, private, protected, and so on.
If any part of the class is declared abstract, then the whole type is considered to be as abstract.
If any part is declared sealed, then the whole type is considered to be as sealed.
If any part declares a base type, then the whole type inherits that class.


Example: Test1.cs:-
namespace PartialClass
{
    public partial class MyTest
    {
        private int a;
        private int b;

        public void getAnswer(int a, int b)
        {
            this.a = a;
            this.b = b;
        }
    }
}
Test2.cs:-
namespace PartialClass
{
    public partial class MyTest
    {
        public void PrintCoOrds()
        {
            Console.WriteLine("Integer values: {0},{1}", a, b);
            Console.WriteLine("Addition: {0}", a+b);
            Console.WriteLine("Mulitiply: {0}", a * b);
        }
    }
}
Program.cs:-
namespace PartialClass
{
    class Program
    {
        static void Main(string[] args)
        {
            MyTest ts = new MyTest();
            ts.getAnswer(12, 25);
            ts.PrintCoOrds();
            Console.Read();
        }
    }
}
OUTPUT:
Integer values: 12,25
Addition: 37 
Mulitiply: 300

Partial Method :

Partial class or struct can contain Partial method.
One part of class contains signature or declaration of the method and the implementation or definition of method can be in same class or different class.
Partial methods enable the implementer of one part of a class to define a method, similar to an event. The implementer of the other part of the class can decide whether to implement the method or not. If the method is not implemented, then the compiler removes the method signature and all calls to the method .
A partial method declaration consists of two parts: 1. definition and 2. Implementation.

partial void onNameChanged();

// Implementation in file2.cs
partial void onNameChanged()
{
  // method body
}

Partial methods can have ref but not out parameters.
Partial method can have static or unsafe modifiers but can not be extern as presence of body decide whether they are defining or implementing.
Partial method can be Generic.
Partial methods are implicitly private, and therefore they cannot be virtual.

Read More »

SQL SERVER - Using sp_msforeachtable in sql server

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure

Display the size of all tables in a database

USE NORTHWIND

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Display Number of Rows in all Tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'

Rebuild all indexes of all tables in a database

USE YOURDBNAME

GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO


Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."

Disable all constraints of all tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Disable all Triggers of all tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'


Delete all data from all tables in your database

-- disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?
'

GO

-- enable referential integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


To RESEED all table to 0, use this script

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)
'

GO

The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx

Reclaim space from dropped variable-length columns in tables or indexed views

USE YOURDBNAME

EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';

Update Statistics of all Tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

Read More »

SQL SERVER - Important Query Part-2

1. COPYING WHOLE DATA OF A TABLE

SELECT * INTO TABLE_DESTINATION FROM TABLE_SOURCE

2. SELECT ONLY DATE PART FROM DATETIME – BEST PRACTICE

Just a week ago my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with date.

SELECT GETDATE()

ResuleSet : 2007-06-10 7:00:56.107

The required outcome was only 2007/06/10.

I asked him to come up with solution by using date functions. The method he suggested was to use

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

I approved his method though, I finally suggested my method using function CONVERT.

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed on MSDN.

Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.

3. QUERY FOR UPDATING IN THE EXISTING TABLE COMMON COLUMN IN ALL THE TABLES

EXEC SP_MSFOREACHTABLE'

DECLARE @TBLNAME VARCHAR(255);

SET @TBLNAME =  PARSENAME("?",1);

DECLARE @SQL NVARCHAR(1000);

IF EXISTS(

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 

 WHERE TABLE_NAME = @TBLNAME AND COLUMN_NAME = ''ISDELETED''

) 

BEGIN        

SET @SQL = N''ALTER TABLE '' +  @TBLNAME + N'' ALTER COLUMN ISDELETED BIT NOT NULL;''       

EXEC SP_EXECUTESQL @SQL

END'

_________________________________________________________________
LIKE Query

--Using Like in SQL Query
SELECT * FROM ADDRESS WHERE CITY LIKE '%NAINITAL%'

-- Like Query with a Parameter
@word AS VARCHAR = NAINITAL
SELECT * FROM ADDRESS WHERE CITY LIKE '%' + @word + '%'

Read More »

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
GOSELECTt.*FROM(SELECTe1.*,row_number() OVER (ORDER BY e1.Rate DESC) AS _RankFROMHumanResources.EmployeePayHistory AS e1) AS tWHEREt._Rank = 4

Read More »