How to use store procedure of CTE query for update table from gridview C# ASP.NET?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

i need help that how CTE query will be used for updating in asp.net c# web application gridview.

Just i want to update INTime and OUTTime result of values will auto calcualte as per define condition.

What I have tried:

SQL

<pre>PROCEDURE [dbo].[GetMachineAttendanceFinal2]
@StartDate Datetime,
@Enddate   Datetime,

@INTIME    Datetime,
@OUTTIME   DateTime
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))

,CTE As
    (
    select EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeeDetails.OTEntitled,EmployeeDetails.Empcur,EmployeeDetails.Dhour,EmployeeDetails.LTime from EmployeeDetails where Empcur='Join'
    )
    ,CTE4 As
    (
    Select MachineAttendance.EmpID,MachineAttendance.Datetime,MachineAttendance.INOUT from MachineAttendance
    ) 
    
    ,cte1 AS
    (
    SELECT CTE4.EmpID, CAST(CTE4.Datetime as Date) AS [Date], 
    CASE WHEN CTE4.INOUT = 1 THEN CTE4.DateTime END AS INOUT_INTIME,
    CASE WHEN CTE4.INOUT = 2 THEN CTE4.DateTime END AS INOUT_OUTTIME
    From 
     CTE4
    
    ), 
   cte2 
    as
    (
    select cte1.EmpID, Date, MAX(INOUT_INTIME) AS INTIME, 
                          MAX(INOUT_OUTTIME) AS OUTTIME
    , DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
    FROM CTE1
    GROUP BY EmpID, [Date]
    )
    select cte.EmpID,cte.EmpName,d.ReportingDate,cte2.Date, cte.OTEntitled,CTE.Empcur,CTE.Dhour,cte.LTime,cte2.INTIME,  cte2.OUTTIME,  cte2.[Hours]
    , CASE WHEN  cte2.[Hours] >= 8 THEN 1
    WHEN  cte2.[Hours] = 0 THEN 0
    WHEN  cte2.[Hours] >= 6 THEN 0.5 END AS [Day],
    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 Cross Apply Dates d 
		Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
    order by cte.EmpID asc 
     
  
END

Posted 7-Jan-19 3:43am

Member 12314309

Updated 7-Jan-19 5:28am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-01-07T05:28:00

What you would need to do is to add an UPDATE query to your procedure; probably the easiest way to do this would be create yet another CTE in your procedure to wrap that final SELECT and then JOIN it to your destination table within the aforementioned JOIN

As you have not provided the table schema for the destination only assumptions can be made which I really do not care to do, so I will only provide the basics. I am also not going to be check your prior code even though it is visibly questionable.

SQL

-- earlier CTEs not listed
; WITH cteFinal AS (
	select	cte.EmpID,cte.EmpName
	,	d.ReportingDate,cte2.Date
	,	cte.OTEntitled
	,	CTE.Empcur
	,	CTE.Dhour
	,	cte.LTime
	,	cte2.INTIME
	,	cte2.OUTTIME
	,	cte2.[Hours]
	,	CASE
			WHEN cte2.[Hours] >= 8 THEN 1
			WHEN cte2.[Hours] = 0 THEN 0
			WHEN cte2.[Hours] >= 6 THEN 0.5
		END AS [Day]
	,	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
	Cross Apply Dates d 
	Left Join cte2 ON cte2.EmpId= cte.EmpID AND cte2.Date=d.ReportingDate
)

UPDATE  dt
SET     dt.[Date] = cF.[Date]
,       dt.OTEntitled = cD.OTEntitled
-- other values being set
FROM   [DestinationTable]  dt
LEFT JOIN cteFinal         cF ON dt.EmpID = cF.EmpID