SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PROBLEM
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/29/2013 :  03:40:25  Show Profile  Reply with Quote


ALTER PROCEDURE [dbo].[AT1]
(
@EMPID INT
)
as
begin
CREATE TABLE #TEMP
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)
INSERT INTO #TEMP
SELECT
EID,
DATE,
REMARKS,
TIMEIN,
TIMEOUT
FROM ATTEND_LOG WHERE EID=@EMPID
END


ITS GIVE ME THIS RESULT
(66 row(s) affected)


I WANT TO SHOW 66 ROWS IN RESULT

Edited by - immad on 06/29/2013 04:28:03

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 06/29/2013 :  04:38:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote

ALTER PROCEDURE dbo.AT1
(
    @EMPID INT
)
AS

CREATE TABLE #TEMP
(
    EID INT,
    DATE DATETIME,
    REMARKS VARCHAR(50),
    TIMEIN DATETIME,
    TIMEOUT DATETIME
)
INSERT #TEMP
SELECT 
EID,
DATE,
REMARKS,
TIMEIN,
TIMEOUT
FROM dbo.ATTEND_LOG
WHERE EID = @EMPID

SELECT @@ROWCOUNT
GO



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/29/2013 :  05:05:27  Show Profile  Reply with Quote
its showing me a coolumn and the value is 66 i want 66 rows data
ok i get it

i use this
ALTER PROCEDURE dbo.AT1
(
@EMPID INT
)
AS

CREATE TABLE #TEMP
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)
INSERT #TEMP
SELECT
EID,
DATE,
REMARKS,
TIMEIN,
TIMEOUT
FROM dbo.ATTEND_LOG
WHERE EID = @EMPID

SELECT * from #temp
immad uddin ahmed

Edited by - immad on 06/29/2013 05:07:54
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/29/2013 :  06:28:04  Show Profile  Reply with Quote
i want to insert a select query data into a table and if i run again this query then if data already exists in table then data not insert can u show me the synatx

immad uddin ahmed

Edited by - immad on 06/29/2013 06:31:37
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/29/2013 :  14:34:16  Show Profile  Reply with Quote

INSERT INTO Table
SELECT *
FROM
(
Your Select Query here
) q
WHERE NOT EXISTS (SELECT 1 FROM Table
                  WHERE PK = q.PK)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 07/01/2013 :  01:07:43  Show Profile  Visit sshelper's Homepage  Reply with Quote
Another way is to use a LEFT OUTER JOIN:

INSERT INTO [dbo].[YourTable] ( <Your Column List> )
SELECT <Your Column List>
FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL

SQL Server Helper
http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=8a56306e-6820-461d-80a6-708cac166c46&tid=87&tkw=sql-server-tutorial---update-statement-101
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  00:46:11  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


INSERT INTO Table
SELECT *
FROM
(
Your Select Query here
) q
WHERE NOT EXISTS (SELECT 1 FROM Table
                  WHERE PK = q.PK)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




please can u explain me the syntax written in red words

INSERT INTO Table
SELECT *
FROM
(
Your Select Query here
) q
WHERE NOT EXISTS (SELECT 1 FROM Table
WHERE PK = q.PK)

Q1.what is mean by 1 why u write 1 is query
Q2.i also wannt to use primary key in both table.so there are same fields in both table DATE,EID,TIMEIN AND TIME OUT so in what field i use pk



immad uddin ahmed
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 07/02/2013 :  00:58:36  Show Profile  Reply with Quote
SELECT 1 FROM Table
WHERE PK = q.PK)

Q1.what is mean by 1 why u write 1 is query
Q2.i also wannt to use primary key in both table.so there are same fields in both table DATE,EID,TIMEIN AND TIME OUT so in what field i use pk

1) Here 1 acts as a flag whether that row has to be in result or not...?
If the current row which satisfies the given condition will return 1... instead you can give your primary column name also
(SELECT PK FROM Table WHERE PK = q.PK)
2) PK means the primary key column name which is existed in your table... If there composite primary key, you should compare all columns that are the part of composite key

--
Chandu
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 07/02/2013 :  01:51:59  Show Profile  Reply with Quote
CREATE TABLE attend_log
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)

CREATE TABLE ATTEND_LOG_test
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)

insert into attend_log values(12,getdate(),'Remarks',getdate(),GETDATE())
insert into ATTEND_LOG_test values(21,getdate(),'Remarks',getdate(),GETDATE())


insert into ATTEND_LOG (EID,DATE,REMARKS,TIMEIN,TIMEOUT)
select A.EID,A.DATE,A.REMARKS,A.TIMEIN,A.TIMEOUT from ATTEND_LOG_test A
LEFT OUTER JOIN ATTEND_LOG B
ON A.EID=B.EID
WHERE B.EID IS NULL



select * from ATTEND_LOG
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  02:31:07  Show Profile  Reply with Quote


Another way is to use a LEFT OUTER JOIN:

INSERT INTO [dbo].[YourTable] ( <Your Column List> )
SELECT <Your Column List>
FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL
----------------------------------------------------------

(33 row(s) affected)

(58 row(s) affected)

(66 row(s) affected)

its giving me that result

i want to insert data and show data rows same time is that possible

immad uddin ahmed

Edited by - immad on 07/02/2013 02:32:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  02:58:49  Show Profile  Reply with Quote
quote:
Originally posted by immad



Another way is to use a LEFT OUTER JOIN:

INSERT INTO [dbo].[YourTable] ( <Your Column List> )
SELECT <Your Column List>
FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL
----------------------------------------------------------

(33 row(s) affected)

(58 row(s) affected)

(66 row(s) affected)

its giving me that result

i want to insert data and show data rows same time is that possible

immad uddin ahmed


yes. if you use OUTPUT clause

something like

INSERT INTO Table
OUTPUT INSERTED.*
SELECT *
FROM
(
Your Select Query here
) q
WHERE NOT EXISTS (SELECT 1 FROM Table
WHERE PK = q.PK)


or



INSERT INTO [dbo].[YourTable] ( <Your Column List> )
OUTPUT INSERTED.*
SELECT <Your Column List>
FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  03:08:15  Show Profile  Reply with Quote
quote:
Originally posted by chbala85

CREATE TABLE attend_log
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)

CREATE TABLE ATTEND_LOG_test
(
EID INT,
DATE DATETIME,
REMARKS VARCHAR(50),
TIMEIN DATETIME,
TIMEOUT DATETIME
)

insert into attend_log values(12,getdate(),'Remarks',getdate(),GETDATE())
insert into ATTEND_LOG_test values(21,getdate(),'Remarks',getdate(),GETDATE())


insert into ATTEND_LOG (EID,DATE,REMARKS,TIMEIN,TIMEOUT)
select A.EID,A.DATE,A.REMARKS,A.TIMEIN,A.TIMEOUT from ATTEND_LOG_test A
LEFT OUTER JOIN ATTEND_LOG B
ON A.EID=B.EID
WHERE B.EID IS NULL



select * from ATTEND_LOG



sir your query is working fine but there is a problem

this is my procedure



ALTER procedure [dbo].[AT]
(
@empid nvarchar(10),
@department varchar(10)
)
as
begin
select
[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1 from attend_log
where eid = @empid
group by [date]

insert into abc
(
--Time_Minutes,
cid,
bid,
date,
ebid,
eid,
employeename,
shift,
designation,
department,
timein,
timeout,
spendtime,
excesshorttime,
excessshort,
remarks,
comments
)

select
E.CID,
E.BID,
t2.[date],
t.ebid,
t2.Eid,
e.ename Employeename,
case when datediff(dd,0,t2.date)%7 < 6 then t4.shift else 'O' end as shift,
d.name Designation,
t5.Dname Department,
t.timein as timein,
t.[Timeout] as Timeout,
CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108) as excesshorttime,
case when (t4.minute - Time_Minutes) > 0 then 'Short'
when (t4.minute - Time_Minutes) < 0 then 'Excess'
else NULL end as ExcessShort,
case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT'
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY'
ELSE ''
END AS Remarks,

case when t.BID = 2 and t.EBID = 1 then 'ITL 2'
when t.BID = 1 and t.EBID = 2 then 'ITL 1'
else ''
end
Comments


FROM (SELECT eid,[date]
FROM (select distinct eid from attend_log)a
cross join dbo.calendartable('20130101','20130131',0,0)b
) t2
left join (select row_number() over (partition by [date],eid ORDER BY timein ASC) AS Seq
,sum(DATEDIFF(minute, [Timein], [Timeout])) OVER (PARTITION BY [date],eid) AS Time_Minutes
,*
FROM attend_log) t
on t.[date] = t2.[date]
and t.eid = t2.eid
left OUTER join abc b on t.eid = b.eid[/red]
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@empid) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
where
[/red]b.eid is null[/red] and
t2.eid=@empid or t5.did=@department
order by t2.[date], t.[Timein]

update
ATTEND_LOG
set


SHIFT = case when datediff(dd,0,t2.date)%7 < 6 AND T.TIMEIN IS NOT NULL AND T.TIMEOUT IS NOT NULL then t4.shift else 'O' end ,

excessshort =

case when (t4.minute - Time_Minutes) > 0 Then ' ' else ' ' end
+ CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108)

,
SpendTime =
CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108)
,
remarks =
case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT'
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY'
ELSE ''
END
from

ATTEND_LOG t
left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@EmpID) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
where t.eid=@empid
end


i use your query but its giving me two error
Msg 209, Level 16, State 1, Procedure AT, Line 61
Ambiguous column name 'spendtime'.[/red]

and second when i comment spendtime data is insert in table and also data show but its multiple every time when i excecute

immad uddin ahmed

Edited by - immad on 07/02/2013 03:09:57
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  03:31:43  Show Profile  Reply with Quote
one obvious issue is this


...
CASE WHEN Seq=1 THEN CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) ELSE NULL END AS SpendTime,
CONVERT(VARCHAR(8), DATEADD(minute, ABS(t4.minute - Time_Minutes), 0), 108) as excesshorttime,
case when (t4.minute - Time_Minutes) > 0 then 'Short'
when (t4.minute - Time_Minutes) < 0 then 'Excess'
else NULL end as ExcessShort,
case when t.[timein] is null and t.[timeout] is null and datediff(dd,0,t2.date)%7 < 6 then 'ABSENT' 
when t.[timein] is null and t.[timeout] is null then 'OFF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL AND ( t4.minute - Time_Minutes) >= 120 THEN 'LATE & HALF DAY'
WHEN CONVERT(VARCHAR(10), t.[Timein], 108) >= CONVERT(VARCHAR(10), t4.ltime, 108) AND spendtime IS NOT NULL THEN 'LATE'
WHEN ( t4.minute - Time_Minutes) >= 120 and spendtime is not null THEN 'HALF DAY' ELSE '' 
END AS Remarks,
..


You're creating spendtime as a calculated column and using alias directly in same select which is not allowed. you need to repeat the full case...when expression in the second case.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  04:42:06  Show Profile  Reply with Quote
spent time issue is solve


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  04:45:57  Show Profile  Reply with Quote
quote:
Originally posted by immad

spent time issue is solve


immad uddin ahmed


good
select issue is because each time you execute it records will get inserted again causing multiple. solution is to add acondition check to ensure that records actually dont exists in table before do insert (use pk combination in the check)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  07:14:36  Show Profile  Reply with Quote
i am using this procedure

alter procedure [dbo].[pocc]
(
@empid nvarchar(10)
)
as
begin
select * from attend_log
where eid=@empid
end

INSERT INTO #TempEmployees
EXEC at '26187',''

SELECT * FROM #TempEmployees
--end

but its giving me that result
(32 row(s) affected)
Msg 208, Level 16, State 0, Procedure pocc, Line 11
Invalid object name '#TempEmployees'.


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:17:14  Show Profile  Reply with Quote
you've not created the table anywhere. then how can you use it? you need to have a create table statement before you start using it


(
@empid nvarchar(10)
)
as
begin
select * from attend_log 
where eid=@empid
end

CREATE TABLE #TempEmployees
(
--columns with datatypes here (same as resultset of at procedure)
)

INSERT INTO #TempEmployees
EXEC at '26187',''

SELECT * FROM #TempEmployees
--end



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  07:46:37  Show Profile  Reply with Quote
visak i already did that but problem is

alter procedure [dbo].[pocc]
(
@empid nvarchar(10),
@department varchar(10)
)
as
begin
create TABLE #TempEmployees
(
date datetime,
eid int,
remarks varchar(50)
)
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.at @empid,@department

--Verify the Insert records
SELECT
*
FROM #TempEmployees where eid=@empid
end

exec [pocc] 17074,''

when i exceute its giving me that result

date----------------------------eid--------remarks
2013-01-06 00:00:00.000--17074---OFF DAY
2013-01-07 00:00:00.000--17074---ABSENT
2013-01-07 00:00:00.000--17074----Late
2013-01-08 00:00:00.000--17074----HALFDAY



how i make a summary result like this
-----eid---offday--absent--late------halfday---
-17074------1----------1--------1----------1------

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  07:54:31  Show Profile  Reply with Quote
what does the 1 value represent? record count?

alter procedure [dbo].[pocc]
(
@empid nvarchar(10),
@department varchar(10)
)
as
begin
create TABLE #TempEmployees
(
date datetime,
eid int,
remarks varchar(50)
)
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.at @empid,@department

--Verify the Insert records
SELECT eid,
SUM(CASE WHEN remarks = 'OFF DAY' THEN 1 ELSE 0 END) AS offday,
SUM(CASE WHEN remarks = 'ABSENT' THEN 1 ELSE 0 END) AS absent,
SUM(CASE WHEN remarks = 'Late' THEN 1 ELSE 0 END) AS late,
SUM(CASE WHEN remarks = 'HALFDAY' THEN 1 ELSE 0 END) AS halfday
FROM #TempEmployees where eid=@empid
GROUP BY eid
end



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 07/02/2013 :  08:02:46  Show Profile  Reply with Quote
its take 26 minute to exec and show result
and when result show after 2 or 3 second later its give me that result



(58 row(s) affected)

(1 row(s) affected)



(1 row(s) affected)
Msg 217, Level 16, State 1, Procedure AT, Line 19
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


immad uddin ahmed

Edited by - immad on 07/02/2013 08:40:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/02/2013 :  09:43:55  Show Profile  Reply with Quote
The issue is in procedure dbo.at which we dont have access to and you've not posted yet

So have a loointo it and see if there's a recursive call

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000