Comments by akhter86 (Top 62 by date)

i did as you told ,but not getting expected output

No ,it will just filter data from containerno table

Order by Entrydate

why your query eliminating this record (1003,2,1,500,'2022-02-01',null) ,it inserted into conIssuance on date 01/02/2022 ,it must be part of from 01/02/2022 to 15/02/20222.

Second thing Closing is not getting carry forwarding for the next day date opening as per date parameter.

thanks ....

i do not want to repeat inv_ID,as your provided query ,is repeating Inv_Id

hi OrigingalGriff,

Currently query which i provided above,is fetching that employee which asd.sal_Id is null,

I tried to use different join,but result which is i am expecting is not coming.

THere is no any error messaage

I am stuck ,that my expecting result is not coming.

I need help to retrieve all employees ,with condition of asd.sal_id is null

i tried below query ,many time modify
SELECT (M.Emp_ID) , M.EMp_Name , M.EMP_Salary ,D.Designation_Name as Designation,
SUM(CASE T.DA_Name WHEN 'Loan' THEN (ASD.assign_amt)ELSE 0 END) Loan,
SUM(CASE T.DA_Name WHEN 'Advance' THEN (ASD.assign_amt)ELSE 0 END) Advance,
SUM(CASE T.DA_Name WHEN 'Mobile' THEN (ASD.assign_amt)ELSE 0 END) Mobile,
SUM(CASE T.DA_Name WHEN 'Conveyance' THEN (ASD.assign_amt)ELSE 0 END) Conveyance,
SUM(CASE T.DA_Name WHEN 'Medical' THEN (ASD.assign_amt)ELSE 0 END) Medical,
SUM(CASE T.DA_Name WHEN 'Rent' THEN (ASD.assign_amt)ELSE 0 END) House_Rent,
isnull(M.emp_OT,0) as emp_OT
,0 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'txthour',asd.Sal_ID
FROM #tbl_Employee_Master M
inner join #tbl_designation D on D.Designation_ID=M.Designation_ID
left join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
left outer join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
where asd.sal_ID is null
group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,M.emp_OT,asd.Sal_ID

i check my whole code,

I check debug ,code is executing but no changes is done

Means that textbox is not getting enable false

so what is the best practice

?

for avoid to duplicating

is this way? but still same issue
( Select (ISNULL(Small_Bale_weight,0))/(ISNULL(SELECT SUM(val)
FROM (VALUES (Bigbale_Weight),
(Small_Bale_weight ))AS value(val)
))*100.0,0)) as Percentage,

here is my desired result https://ibb.co/Jz98hwh

thanks for your guidance alot ,, i never used hardcore values in query,,no idea that how it will get done [2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05], so still looking for solution ,,,

Dear Chill60

I have done UNION QUERY here is my in store procedure ...

i tried this ( ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;




SELECT Distinct Entrydate INTO #Dates26
FROM Probale
WHERE EntryDate BETWEEN @StartDate AND @Enddate
ORDER BY EntryDate



SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate) )
--FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')


SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where CONVERT(varchar(10), EntryDate) =stat.ddate ) as total


FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),Probale.EntryDate) as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from cte'

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate


END
)
but
error is coming ( Invalid column name 'ddate'.)

Dear OriginalGriff,
i tried this ( ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;




SELECT Distinct Entrydate INTO #Dates26
FROM Probale
WHERE EntryDate BETWEEN @StartDate AND @Enddate
ORDER BY EntryDate



SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate) )
--FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')


SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where CONVERT(varchar(10), EntryDate) =stat.ddate ) as total


FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),Probale.EntryDate) as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from cte'

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate


END
)

error is coming ( Invalid column name 'ddate'.)

Dear OriginalGriff,
I want Row and Column wise Total ,,above store procedure just display date wise Total of item ,,Now i want Rows Total or each item and Column wise Total of each Date.

i didn't get what you saying

OriginalGriff i did #Date but result is not coming

i just run this query on sql not print col

how to print out

what you mean to say?

Yes it will report for each employee in a day.

This error is coming...
(62 row(s) affected)
Msg 8156, Level 16, State 1, Line 21
The column '27' was specified multiple times for 'stat'.
Msg 8156, Level 16, State 1, Line 23
The column '27' was specified multiple times for 'Pvt'.

i used
PIVOT(COUNT(Status) FOR Status IN ([P],[CL],[A])) AS New_Status

ORDER BY RNO_ROWTOTAL,''p'' ;
but same issue coming..please guide

thanks for providing soultion

Sorry for mistake ,,but your provided how to use , i tried us integrate with query but not giving resultMsg 102, Level 15, State 1, Line 29
Incorrect syntax near ' & @qry & '

Dear @Santosh Kumar here is error coming
(8 row(s) affected)
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'RNO_ROWTOTAL'.

i Have data in column then i transfer it into rows,now i want row total as i Mentioned above ?
P CL A

thanks Wendelius....
For you support and thanks again ...

Dear @Wendelius

if i am giving apostrophes then executing Store Procedure it generating error (Msg 102, Level 15, State 1, Procedure Pivot_Attendance2, Line 13
Incorrect syntax near 'CL'.
Msg 137, Level 15, State 2, Procedure Pivot_Attendance2, Line 28
Must declare the scalar variable "@qry".)

hmmm

i want to mentioned leave type(PL,CL,SL) in attendance register ,i have created separate employee leave table ,here i stuck that how this table will join in employee attendance.

this error is coming
(6 row(s) affected)
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.

which solution

MadMyche

i have developed SP,it just updating INTTIME and OUTIME rest of column values are not getting calculate....Please review my code

Update EmployeesAttendance
set EmployeesAttendance.INTIME=@INTime,
EmployeesAttendance.OUTTIME=@OUTTIME,

EmployeesAttendance.Hours= ( (DATEDIFF(Hour,INTIME,OUTTIME))),
EmployeesAttendance.Day=( CASE WHEN EmployeesAttendance.Hours >= 8 THEN 1
WHEN EmployeesAttendance.Hours = 0 THEN 0
WHEN EmployeesAttendance.Hours >= 6 THEN 0.5 end),
EmployeesAttendance.OT=(CASE WHEN EmployeesAttendance.Hours > EmployeeDetails.Dhour then
EmployeesAttendance.Hours - EmployeeDetails.Dhour else 0 End),
EmployeesAttendance.OTAmount =(CASE when EmployeeDetails.OTEntitled = 'Yes'
AND EmployeesAttendance .Hours >= EmployeeDetails.Dhour
THEN (( EmployeesAttendance.Hours - 8) * 100) else 0 END ),
EmployeesAttendance.Time=( Convert(varchar(10),EmployeesAttendance.INTIME,108)),
EmployeesAttendance.Late=(Case When Convert(Time, EmployeesAttendance .INTIME,108) > EmployeeDetails.LTime Then 1 else 0
end)
From EmployeeDetails
INNER Join EmployeesAttendance ON EmployeeDetails.EmpId = EmployeesAttendance.EmpID
-- Order by EmployeeDetails.EmpID asc
where EmployeesAttendance.AttdID=@AttdID

sql server 2008 r2

my this query is working fine on select but not working on update?

Thanks for sharing CTE

i have added comment plz check thanks

@Chill60 i tried to defined you ,if you have any query please ask

Lolz,,,thanks for comment

i want if employee date of leaving is exit then that employee does not come in row of gridview

here
protected void GVEmpDetails_RowDataBound(object sender, GridViewRowEventArgs e)

error coming on this line
int customerId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);

This error i am getting (The given value of type String from the data source cannot be converted to type datetime of the specified target column. )

i didn't copy and paste ,having confusion so that why i am asking question,

ok

ok,

will you do coding for LOOP then i can get an idea...

Thanks for your kind suggestion , i am beginner,so that why asking these kind of question ,i want just little help that how to connect this coding to SQL,and this coding is working which i share is working fine.