For All Programming solution

Sunday, February 1, 2015

Dynamic Column in PIVOT in SQL 2

DECLARE @buyerName AS VARCHAR(MAX),
@buyerName1 AS VARCHAR(MAX),
    @query AS VARCHAR(MAX)

    SET @buyerName1=STUFF((SELECT Distinct ',Isnull('+ QUOTENAME( Rtrim( Ltrim(O. fldBuyer)) )+',0)'+QUOTENAME( Rtrim( Ltrim(O. fldBuyer))) from tblGreyFabricDelivery D inner join tblGreyFabricOrderDetails O
    on D.fldID=O.fldID where O. fldBuyer !='0' and (Cast(D.fldDeliveryDAte as Date ) between '01-jan-2015' and '20-jan-2015')
    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')

    SET @buyerName=STUFF((SELECT Distinct ','+ QUOTENAME( Rtrim( Ltrim(O. fldBuyer)) ) from tblGreyFabricDelivery D inner join tblGreyFabricOrderDetails O
    on D.fldID=O.fldID where O. fldBuyer !='0' and (Cast(D.fldDeliveryDAte as Date ) between '01-jan-2015' and '20-jan-2015')

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')

SET @query='Select fldDeliveryDAte,'+@buyerName1+' FROM

(
select Cast(fldDeliveryDAte as Date) fldDeliveryDAte , Rtrim( Ltrim(O. fldBuyer))fldBuyer, Sum(fldDeliveryQuantity) Qty from tblGreyFabricDelivery D inner join tblGreyFabricOrderDetails O
on D.fldID=O.fldID where O.fldBuyer !=''0''
   and ( cast( D.fldDeliveryDAte as Date) between ''01-jan-2015'' and ''20-jan-2015'')

Group by Cast(fldDeliveryDAte as Date) , O.fldBuyer

)
A
PIVOT(Sum(Qty) FOR fldBuyer IN ('+@buyerName+'))P order by fldDeliveryDAte';
exec (@query);

Dynamic Column in Pivot in SQL

DECLARE @processID AS VARCHAR(MAX),
@query AS VARCHAR(MAX)
SET @processID=STUFF((SELECT ','+QUOTENAME( ParameterCaption+'//'+CAST(ProcessParameterID AS VARCHAR)+'//'+ProcessName) FROM dbo._TBL_RnDProcessParameter R INNER JOIN dbo._TBL_RnDProcess P ON P.RnDProcessID=R.ProcessID
WHERE R.IsActive=1 FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'')
SET @query=' SELECT RnDNo,BuyerName,OrderNo,SampleType,Color,Note,RequiredWidth,RequiredGSM,'+@processID+' FROM
(SELECT * FROM
(SELECT ProcessDataHeadID,ParameterCaption+''//''+CAST(A.ProcessParameterID AS VARCHAR)+''//''+ProcessName AS FabricHead, COALESCE(Value,'''')Value FROM
(SELECT ParameterCaption,ProcessParameterID,ProcessID FROM dbo._TBL_RnDProcessParameter
WHERE IsActive=1)A
LEFT OUTER JOIN
(SELECT ProcessParameterID,Value,ProcessDataHeadID FROM dbo._TBL_RnDProcessData)B
ON B.ProcessParameterID=A.ProcessParameterID
INNER JOIN
dbo._TBL_RnDProcess P ON P.RnDProcessID=A.ProcessID
)A
PIVOT(MAX(Value) FOR FabricHead IN ('+@processID+'))P
WHERE ProcessDataHeadID IS NOT NULL
)A
LEFT OUTER JOIN
dbo._TBL_RnDProcessDataHead H
ON H.ProcessDataHeadID=A.ProcessDataHeadID
';
EXEC(@query);

Wednesday, June 4, 2014

Returnig Hour From Date Range excluding Selected Day in SQL

CREATE FUNCTION dbo.funcGetHour (@fromDate datetime,@toDate datetime)
RETURNS int
AS BEGIN
    declare @Hourss int
    set @Hourss=0
        WHILE (@fromDate <@toDate)
        BEGIN
            if(datename(dw,@fromDate)<>'Saturday' and datename(dw,@fromDate)<>'Sunday')
                Begin
                    set @Hourss =@Hourss+1

                    --print @Hourss
                    --print datename(dw,@fromDate)
                    --print @fromDate
                    End

        set @fromDate= dateAdd(hour,1,@fromDate)
        END

    RETURN @Hourss
END

Saturday, April 12, 2014

JQuery Confirm Dialogue Box

http://runnable.com/UfNMPnKMU1ZgAACQ/how-to-create-custom-jquery-alert-confirm-and-prompt-dialogs

Wednesday, April 9, 2014

JQuery and Gridview

Monday, April 7, 2014

Bootstrp Code Example

http://bootsnipp.com/?page=5

Monday, January 6, 2014

checkbox in datagridview using jquery

var result = $('[id$=grdFault] tr td input[id*="chkSelectF"][type=checkbox]:checked').map(function () { alert( $(this).closest('tr').find('td').eq(2).text()); }).get().join();