Comments by Member 12314309 (Top 44 by date)

Thanks John Simmons ,i have fixed my issued

Thanks Wendelius .....Too much for opening my mind for thinking out of the box i have done it thanks again

Wndelius ,i used cte but it just input INTIME and OUTTIME and nothing else doing...get much confuse about this ,,,pleas review below code SP

ALTER PROCEDURE [dbo].[GetMachineAttendanceFinal7]
@INTIME Datetime,
@OUTTIME Datetime,
@AttIDS int
AS

BEGIN

SET NOCOUNT ON;


--;WITH Dates (ReportingDate)
--AS (
-- SELECT CONVERT(date, '2018-11-26 00:00:00.000', 120) AS ReportingDate
-- UNION ALL
-- SELECT DATEADD(day, 1, d.ReportingDate)
-- FROM Dates d
-- WHERE d.ReportingDate < CONVERT(date, '2018-12-25 00:00:00.000', 120))

;WITH CTE As
(
select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
),
EAtt
As
(
Select EmployeesAttendance.AttIDS,EmployeesAttendance.ReportingDate,EmployeesAttendance.Date,EmployeesAttendance.Days,EmployeesAttendance.EmpID,EmployeesAttendance.Hours,EmployeesAttendance.INTIME,EmployeesAttendance.OUTTIME,EmployeesAttendance.Late,EmployeesAttendance.Time,EmployeesAttendance.OTAmount From EmployeesAttendance
)
,cte2
as

(
Select EAtt.AttIDS,EATT.ReportingDate,EATT.Date,EATT.Days,EATT.EmpID,
EATT.Hours,EATT.INTIME,EATT.OUTTIME,EATT.Late,EATT.Time,EATT.OTAmount

FROM EATT

),
cteFinal AS (
select cte.EmpID,cte2.ReportingDate,cte2.Date,cte2.INTIME,cte2.OUTTIME,

DATEDIFF(Hour, @INTIME, @OUTTIME) as [Hours]
, CASE WHEN cte2.[Hours] >= 8 THEN 1
WHEN cte2.[Hours] = 0 THEN 0
WHEN cte2.[Hours] >= 6 THEN 0.5 END AS [Days],
CASE WHEN cte2.[Hours] > CTE.Dhour then cte2.[Hours] - CTE.Dhour else 0 End as OT,
CASE when
cte.OTEntitled = 'Yes' AND cte2.[Hours] >= CTE.Dhour
THEN (( cte2.[Hours] - 8) * 100) else 0 END AS OTAMount,
Convert(varchar(10), cte2.INTIME,108) as [Time],
Case When Convert(Time, cte2.INTIME,108) > cte.LTime Then 1 else 0 end as Late
from cte
Left Join cte2 ON cte2.EmpId= cte.EmpID
)
UPDATE cte2
SET cte2.INTIME = @INTIME,
cte2.OUTTIME=@OUTTIME,
cte2.Hours=Hours
where cte2.AttIDS=@AttIDS

Dear MadMyche, I just want to update INTIME and OUTTIME IN Employee Attendance against attendance ID ,rest of query as same execute as i mentioned above,now how will update INTIME and OUTTIME in cte query ,i tried which you said but i think this is not solution of my query...Please please help

INSERT INTO EmployeesAttendance
EXEC GetMachineAttendanceFinal5

Thanks for sharing ....another confusion is that if SP result is inserted then again same date record will insert ,so how to restrict duplication of record of same date for each employee

LOlz....i found solution here is ... INSERT INTO EmployeesAttendance
EXEC GetMachineAttendanceFinal5 ... but how to restrict that record which already exit in a table

Dear Original ,kindly do not take it on headache ,,,just i simply ask that how SP result which are coming in column to insert into table ,
I have attendance machine talbe in which employee INtime and OUttime is coming 1 and 2,from this INTIME and OUTTIME i calculated hours,OT,OTamount and Late,now these column result i want to insert into another table....

i said that SP Result ,means which value i calculated in SP ,i want to insert into Employee attendance table.

i defined col above in attendance table,that is the result of this SP,this result i want to insert into a table of employeeattendance

Dear MadMyche
i am little bit confuse,my table structure is that i have two table one table is Machineattendance and another one is Employee attendance in which INtime and Outime is coming where i will update INtime and OUtime of those employee whose intime and outime will not available

Respected Wendelius,,,,THanks ALot sir ...Most respect for you.....Thanks again

ok thanks

@Wendelius thanks alot for giving me new path for learning sql command,thanks again if you don't mind can you share skype id ,my skype id is akhter.hussain80

Empid is FK in MachineAttendance and link from employee table, and Date is col of Machineattendance

@Wendelius...

Please have a look on my query giving error of Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "EmployeeDetails.EmpID" could not be bound.



;WITH cte1 AS
(
SELECT EmployeeDetails.EmpID, CAST([Date] as Date) AS [Date],
CASE WHEN INOUT = 1 THEN [Date] END AS INTIME,
CASE WHEN INOUT = 2 THEN [Date] END AS OUTTIME
FROM MachineAttendance

), cte2 as
(
select EmployeeDetails.EmpID, [MachineAttendance.Date], MAX(INTIME) AS INTIME, MAX(OUTTIME) AS OUTTIME
, DATEDIFF(Hour, MAX(INTIME), MAX(OUTTIME)) as [Hours]
FROM CTE1
GROUP BY EmployeeDetails. EmpID, [MachineAttendance.Date]
)
select EmployeeDetails.EmpID, [MachineAttendance.Date], INTIME, OUTTIME, [Hours]
, CASE WHEN [Hours] >= 8 THEN 1
WHEN [Hours] = 0 THEN 0
WHEN [Hours] >= 6 THEN 0.5 END AS [Day],
CASE WHEN [Hours] > 8 then [Hours] - 8 else 0 End as OT,
CASE WHEN [Hours] >= 8
then ([Hours] - 8) * 100 else 0 END AS OTAMount,
Convert(varchar(10),MachineAttendance.Date,120) as [Date],
Convert(varchar(10),INTIME,108) as [Time],
Case When Convert(Time,INTIME,108) > '09:10' Then 1 else 0 end as Late

from cte2
INNER JOIN EmployeeDetails ON EmployeeDetails.EMPID=MachineAttendance.EMPID

when txttime.text contain 12-12-2018 08:00 when txttime.Text does not have any value then txthours must be 0,but this code is not giving me desire result when this condition is meet

my problem is that i am using
if (txttime.Text.length == 0 )
{ txthours.Text = "0"
}
this is not working

so what to do?

how to pass 0 in hours if INTIME or OUTTIME does not available in gridview or if one is exit means intime ?

thanks for help...my futher point is that if Hours is more then to 8 hours like 12hours then it get minus from standard duty hours which is 8 hours and multiply to standard OT rate which is 100 rate (12hour - 8hours=4hours) 4hours*100=400 which is Overtime amount =400

it is not executing and giving error

check this Declare
@Hou int
@Hours int
Set @Hou=8

SELECT INTime, Outtime, DATEDIFF(Hour, INTime, Outtime) AS Hours,
if
@Hours= @Hou then Day="1"

FROM EmployeeAttedance

i tried this (SELECT INTime, Outtime, DATEDIFF(Hour, INTime, Outtime) AS Hours
FROM EmployeeAttedance)
Now i want day calculate if Hour=8 then day will be 1

i have gridview in which employee IN and OUT is coming ,now i want that gridview row auto add as per daily date extend for each employee id

this is not my soultion

i have resolve my issue using this code
DateTime dt1 = Convert.ToDateTime(txtTime.Text.Trim());
DateTime dt2 = Convert.ToDateTime(txtLTime.Text.Trim());

if (dt1 > dt2)
{
txtLate.Text = "1";
}

i have debug no error is coming code working fine but result is not coming .i did this code as you told

DateTime dt1;
if (!DateTime.TryParse(txtTime.Text.Trim(), out dt1))
{

return;
}
dt1 = dt1.Date;
DateTime dt2;
if (!DateTime.TryParse(txtLTime.Text.Trim(), out dt2))
{

return;
}
dt2 = dt2.Date;
if (dt1 > dt2)
{
txtLate.Text="1";
}

i did this but not resolved

datetimelocal

For passing data on another form from gridview i used this code

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
string AID = GridView1.SelectedRow.Cells[1].Text;
Response.Redirect("Daily Attendance.aspx?AID=" + AID);
}

on the form on page loading i used this coding

protected void Page_Load(object sender, EventArgs e)
{
{
String mycon = ("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=Institute");
String myquery = "Select AID,Datetime,Outdt,Day,Days,OT,Code from HR3 where AID=" + Request.QueryString["AID"];
SqlConnection con = new SqlConnection(mycon);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = myquery;
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
TBID.Text = ds.Tables[0].Rows[0]["AID"].ToString();
//TBEN.Text = ds.Tables[0].Rows[0]["EmployeeName"].ToString();


//Date Column// TBIN.Text = ds.Tables[0].Rows[0]["Datetime"].ToString();
//Date Column// TBOut.Text = ds.Tables[0].Rows[0]["Outdt"].ToString();
TBHrs.Text = ds.Tables[0].Rows[0]["Day"].ToString();
TBDay.Text = ds.Tables[0].Rows[0]["Days"].ToString();
TBOT.Text = ds.Tables[0].Rows[0]["OT"].ToString();
TBEID.Text = ds.Tables[0].Rows[0]["Code"].ToString();
}
con.Close();


}

when i run this form on Internet explorer its working fine and date are getting coming in textbox,but in Google chrome browser it is not working ..can you tell me what could b the reason.

Now data is importing blank in sql server table and no error is giving.

now getting this error
The given value of type String from the data source cannot be converted to type datetime of the specified target column.

here is my complete code

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;



namespace BindGridviewFromCSVFile
{
public partial class BindGridview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void btnFileUpload_Click(object sender, EventArgs e)
{


//Creating object of datatable
//Upload and save the file.
//getting full file path of Uploaded file
string CSVFilePath = Server.MapPath(FileUpload1.PostedFile.FileName);

//Reading All text
string ReadCSV = File.ReadAllText(CSVFilePath);

DataTable dt = new DataTable("HR");
//DataColumn dateColumn = new DataColumn();

//dateColumn.ColumnName = "Date";
//dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Empcode", typeof(int)),
//new DataColumn("Days", typeof(int)),
// new DataColumn("Date", typeof(int)),
//DateTime Date = Convert.ToDateTime("Date");
// table.Columns.Add("SaleDate", typeof(DateTime));


// new DataColumn("Time", typeof(int)),
// new DataColumn("IN/OUT", typeof(int)),});

// Create Column 1: SaleDate


DataColumn DaysColumn = new DataColumn();
// dateColumn.DataType = Type.GetType("System.int");
DaysColumn.ColumnName = "Days";



DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");

dateColumn.ColumnName = "Date";






// Create Column 2: ProductName
DataColumn timeNameColumn = new DataColumn();
timeNameColumn.ColumnName = "Time";

// Create Column 3: TotalSales
DataColumn INOUTColumn = new DataColumn();
// totalSalesColumn.DataType = Type.GetType("System.int");
INOUTColumn.ColumnName = "IN/OUT";

// EmpcodeColumn.DataType = Type.GetType("int");
DataColumn EmpcodeColumn = new DataColumn();
EmpcodeColumn.ColumnName = "Empcode";

// Add the columns to the ProductSalesData DataTable
dt.Columns.Add(DaysColumn);
dt.Columns.Add(dateColumn);
dt.Columns.Add(timeNameColumn);
dt.Columns.Add(INOUTColumn);
dt.Columns.Add(EmpcodeColumn);
// Let's populate the datatable with our stats.
// You can add as many rows as you want here!

// Create a new row
//DataRow dailyProductSalesRow = dt.NewRow();
//dailyProductSalesRow["Empcode"] = "";
//dailyProductSalesRow["Days"] = "";

//dailyProductSalesRow["Date"] = DateTime.Today;
//dailyProductSalesRow["Time"] = "";
//dailyProductSalesRow["IN/OUT"] = "";



string csvData = File.ReadAllText(CSVFilePath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Institute;Integrated Security=True");
// con.Open();
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName =

how can i do that to get each number from database

how can i do it?

SMS API

sms API,,,