Comments by durga63 - CodeProject

durga63 17-May-16 0:20am View

I posted Output please suggest me

durga63 16-May-16 23:44pm View

Dt Hours
15-05-2016 8
14-05-2016 0
13-05-2016 0

durga63 25-Apr-16 7:49am View

Please help me atlease how hide or disable hyper link CC AND BCC Recipients

durga63 25-Apr-16 7:43am View

How send that extra link for TO Recipients

durga63 25-Apr-16 7:42am View

Actually when iam click submit i want send this email but some extra link send to mail

remaing same

durga63 10-Mar-16 7:55am View

Deleted

Above I added only ajax method this displays if if I give input itemname from date and to date then it displays item details but without giving item input i need to display item wise details with samedesign

durga63 10-Mar-16 7:52am View

Deleted

$.ajax({

type: 'POST',
url: invurl,
cache: false,
data: "{'" +
"fromdate':'" + $("#txtInvReportledgerFromDate").val() + "','" +
"todate':'" + $("#txtInvReportledgerToDate").val() + "','" +
"Itemname':'" + $("#dataItemId").val() +
"'}",

contentType: 'application/json; charset=utf-8',
dataType: 'JSON',

success: function (data) {

if (data.d.length > 0) {
for (var index = 0; index < data.d.length; index++) {
$("#lblitemname").text(data.d[index].ITEM_NAME);
$("#lblitemcode").text(data.d[index].ITEM_CODE);
$("#lblInventoryQtyason").text(data.d[index].TodayClosingStock);
$("#lblStockStatusunitprice").text(data.d[index].Todaystockunitprice);
if (Itemcolorindex == index) {
Itemcolor = "background-color:white";
Itemcolorindex = Itemcolorindex + 2;
}
else {
Itemcolor = "background-color:#EFF3FB;";
}

$("#tab-ledger").append("<tr style=" + Itemcolor + ">" +
"<td align='center'>" + data.d[index].TBLID + "</td>" +
"<td align='left'>" + data.d[index].TYPE + "</td>" +
"<td align='center'>" + getOldDate(data.d[index].DATE) + "</td>" +
"<td align='left'>" + data.d[index].TransactionDescription + "</td>" +
"<td align='left'>" + data.d[index].ReferenceDoc + "</td>" +
"<td align='center'>" + (data.d[index].QTYReceipts == null ? '' : data.d[index].QTYReceipts) + "</td>" +
"<td align='center'>" + (data.d[index].UNITPRICEReceipts == null ? '' : data.d[index].UNITPRICEReceipts) + "</td>" +
"<td align='center'>" + (data.d[index].QTYISS == null ? '' : data.d[index].QTYISS) + "</td>" +
"<td align='center'>" + (data.d[index].UNITPRICEiss == null ? '' : data.d[index].UNITPRICEiss) + "</td>" +

"</tr>");
}

$("#tab-ledger").append("</tbody>");

}

durga63 12-Feb-16 3:25am View

DB SIZE IS 1GB,ACTUALLY I HAVE PROBLEM WITH CASE STATEMENTS ITS SELECTED 4 ROWS BUT I NEED SINGLE ROW

durga63 11-Feb-16 23:38pm View

i RE WRITE THE QUERY IAM GETTING OUTPUT WITH IN SEC BUT GETTING O VALUE EVERY TIME
PLEAS
USE [INVATTENDENCEL]
GO
/****** Object: StoredProcedure [dbo].[SP_GET_EXTRA_HOURS_WORKED_NEW] Script Date: 02/12/2016 09:19:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--EXEC [SP_GET_EXTRA_HOURS_WORKED_NN]29,'Mechanical CHP Maintenance','2016-02-11'

ALTER proc [dbo].[SP_GET_EXTRA_HOURS_WORKED_NNN]
@SITE_ID INT,
@DEPT_NAME VARCHAR(100),
@TO_DATE DATETIME
AS
declare @d1 int,@d2 int,@d3 int,@d4 int,@d5 int,@m1 int,@m2 int,@m3 int,@m4 int,@m5 int,@y1 int,@y2 int,@y3 int,@y4 int,@y5 int

BEGIN
set @d1=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-4,@TO_DATE), 5),0,3))
set @m1=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-4,@TO_DATE), 5),4,2))
set @y1=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-4,@TO_DATE), 5),7,2))

set @d2=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-3,@TO_DATE), 5),0,3))
set @m2=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-3,@TO_DATE), 5),4,2))
set @y2=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-3,@TO_DATE), 5),7,2))

set @d3=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-2,@TO_DATE), 5),0,3))
set @m3=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-2,@TO_DATE), 5),4,2))
set @y3=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-2,@TO_DATE), 5),7,2))

set @d4=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-1,@TO_DATE), 5),0,3))
set @m4=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-1,@TO_DATE), 5),4,2))
set @y4=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-1,@TO_DATE), 5),7,2))

set @d5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),0,3))
set @m5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),4,2))
set @y5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),7,2))

create table #temp1(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
create table TMP1(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
create table #temp2(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)

INSERT INTO #temp1 (EMP_CODE,EMP_NAME,G,F,S,T)

select distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_ID
where EMP.SITE_ID=@SITE_ID AND EMP.[STATUS]='Present' AND emp.DEPT_NAME=@DEPT_NAME

INSERT INTO #temp2 (EMP_CODE,EMP_NAME,G1,F1,S1,T1)
select distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T1
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_ID
where EMP.SITE_ID=@SITE_ID AND EMP.[STATUS]='Present' AND emp.DEPT_NAME=@DEPT_NAME

update T1 set T1.G1=isnull(T2.G1,0),T1.F1=is

durga63 11-Feb-16 23:35pm View

I CHANGED QUERY LIKE THIS ITS RUNNEING WITH IN 1 SEC BUT GETTING 0 VALUE
WHAT IS WRONG WITH THIS

USE [INVATTENDENCEL]
GO
/****** Object: StoredProcedure [dbo].[SP_GET_EXTRA_HOURS_WORKED_NEW] Script Date: 02/12/2016 09:19:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--EXEC [SP_GET_EXTRA_HOURS_WORKED_NN]29,'Mechanical CHP Maintenance','2016-02-11'

ALTER proc [dbo].[SP_GET_EXTRA_HOURS_WORKED_NNN]
@SITE_ID INT,
@DEPT_NAME VARCHAR(100),
@TO_DATE DATETIME
AS
declare @d1 int,@d2 int,@d3 int,@d4 int,@d5 int,@m1 int,@m2 int,@m3 int,@m4 int,@m5 int,@y1 int,@y2 int,@y3 int,@y4 int,@y5 int

BEGIN
set @d1=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-4,@TO_DATE), 5),0,3))
set @m1=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-4,@TO_DATE), 5),4,2))
set @y1=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-4,@TO_DATE), 5),7,2))

set @d2=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-3,@TO_DATE), 5),0,3))
set @m2=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-3,@TO_DATE), 5),4,2))
set @y2=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-3,@TO_DATE), 5),7,2))

set @d3=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-2,@TO_DATE), 5),0,3))
set @m3=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-2,@TO_DATE), 5),4,2))
set @y3=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-2,@TO_DATE), 5),7,2))

set @d4=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-1,@TO_DATE), 5),0,3))
set @m4=(select SUBSTRING(CONVERT(VARCHAR(8),DATEadd(DD,-1,@TO_DATE), 5),4,2))
set @y4=(select SUBSTRING(CONVERT(VARCHAR(8), DATEadd(DD,-1,@TO_DATE), 5),7,2))

set @d5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),0,3))
set @m5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),4,2))
set @y5=(select SUBSTRING(CONVERT(VARCHAR(8), @TO_DATE, 5),7,2))

create table #temp1(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
create table TMP1(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)
create table #temp2(
EMP_CODE VARCHAR(1000),
EMP_NAME CHAR(50),G INT,F INT,S INT,T INT,G1 INT,F1 INT,S1 INT,T1 INT)

INSERT INTO #temp1 (EMP_CODE,EMP_NAME,G,F,S,T)

select distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S,
case when et.DAY=@d1 and et.MONTH=@m1 and et.YEAR=@y1 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_ID
where EMP.SITE_ID=@SITE_ID AND EMP.[STATUS]='Present' AND emp.DEPT_NAME=@DEPT_NAME

INSERT INTO #temp2 (EMP_CODE,EMP_NAME,G1,F1,S1,T1)
select distinct emp.EMP_CODE,emp.EMP_NAME,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.GENERAL_SHIFT else 0 end as G1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.FIRST_SHIFT else 0 end as F1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.SECOND_SHIFT else 0 end as S1,
case when et.DAY=@d2 and et.MONTH=@m2 and et.YEAR=@y2 and et.EMP_ID=emp.EMP_CODE and et.site_id=@SITE_ID then et.THIRD_SHIFT else 0 end as T1
from dbo.EMP_FPONM_EMPLOYEE_INFORMATION emp
left join dbo.EMP_FPONM_EMP_TIME_SHEET et on emp.EMP_CODE=et.EMP_ID
where EMP.SITE_ID=@SITE_ID AND EMP.[STATUS]='Present' AND emp.DEPT_NAME=@DEPT_NAME

update T1 set T1.G1=isnull(T2.G1,0)

durga63 11-Feb-16 23:10pm View

ok.Thank u

actually iam creating two temp tables select data from two temp tables finally data updated to one table but iam getting Zero value so please help me here

update t1 set T1.G1=isnull(T2.G1,0),T1.F1=isnull(T2.F1,0),T1.S1=isnull(T2.S1,0),T1.T1=isnull(T2.T1,0)
FROM #temp1 T1 left JOIN #temp2 T2 ON T1.EMP_CODE=T2.EMP_CODE

durga63 11-Feb-16 22:42pm View

So please suggest me what should i do

durga63 11-Feb-16 16:13pm View

Can you please re write the query and help me out

durga63 11-Feb-16 16:10pm View

Can you please re write the entire query and help me out

durga63 11-Feb-16 16:03pm View

ok how to get month and year

durga63 11-Feb-16 14:55pm View

Actually day,month,year is set for select last 5 days from today so iam using like that

durga63 9-Feb-16 4:02am View

iam waiting for your reply i didn't get any idea

durga63 9-Feb-16 3:55am View

Hi any one please suggeset for above question kindly do the needfull

Comments by durga63 (Top 18 by date)