Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-29 : 03:40:25
|
ALTER PROCEDURE [dbo].[AT1](@EMPID INT)asbeginCREATE TABLE #TEMP(EID INT,DATE DATETIME,REMARKS VARCHAR(50),TIMEIN DATETIME,TIMEOUT DATETIME)INSERT INTO #TEMPSELECT EID,DATE,REMARKS,TIMEIN,TIMEOUTFROM ATTEND_LOG WHERE EID=@EMPIDENDITS GIVE ME THIS RESULT(66 row(s) affected)I WANT TO SHOW 66 ROWS IN RESULT |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-29 : 04:38:29
|
[code]ALTER PROCEDURE dbo.AT1( @EMPID INT)ASCREATE TABLE #TEMP( EID INT, DATE DATETIME, REMARKS VARCHAR(50), TIMEIN DATETIME, TIMEOUT DATETIME)INSERT #TEMPSELECT EID,DATE,REMARKS,TIMEIN,TIMEOUTFROM dbo.ATTEND_LOGWHERE EID = @EMPIDSELECT @@ROWCOUNTGO[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-29 : 05:05:27
|
its showing me a coolumn and the value is 66 i want 66 rows dataok i get iti use thisALTER PROCEDURE dbo.AT1( @EMPID INT)ASCREATE TABLE #TEMP( EID INT, DATE DATETIME, REMARKS VARCHAR(50), TIMEIN DATETIME, TIMEOUT DATETIME)INSERT #TEMPSELECT EID,DATE,REMARKS,TIMEIN,TIMEOUTFROM dbo.ATTEND_LOGWHERE EID = @EMPIDSELECT * from #tempimmad uddin ahmed |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-29 : 06:28:04
|
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 synatximmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-29 : 14:34:16
|
[code]INSERT INTO TableSELECT *FROM(Your Select Query here) qWHERE NOT EXISTS (SELECT 1 FROM Table WHERE PK = q.PK)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2013-07-01 : 01:07:43
|
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] BON A.[ID] = B.[ID]WHERE B.[ID] IS NULLSQL Server Helperhttp://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 |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 00:46:11
|
quote: Originally posted by visakh16
INSERT INTO TableSELECT *FROM(Your Select Query here) qWHERE NOT EXISTS (SELECT 1 FROM Table WHERE PK = q.PK) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
please can u explain me the syntax written in red wordsINSERT INTO TableSELECT *FROM(Your Select Query here) qWHERE NOT EXISTS (SELECT 1 FROM Table WHERE PK = q.PK)Q1.what is mean by 1 why u write 1 is queryQ2.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 pkimmad uddin ahmed |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-07-02 : 00:58:36
|
SELECT 1 FROM TableWHERE PK = q.PK)Q1.what is mean by 1 why u write 1 is queryQ2.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 pk1) 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 |
|
|
chbala85
Starting Member
49 Posts |
Posted - 2013-07-02 : 01:51:59
|
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 ALEFT OUTER JOIN ATTEND_LOG B ON A.EID=B.EID WHERE B.EID IS NULL select * from ATTEND_LOG |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 02:31:07
|
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] BON 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 resulti want to insert data and show data rows same time is that possibleimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 02:58:49
|
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] BON 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 resulti want to insert data and show data rows same time is that possibleimmad uddin ahmed
yes. if you use OUTPUT clausesomething likeINSERT INTO TableOUTPUT INSERTED.*SELECT *FROM(Your Select Query here) qWHERE NOT EXISTS (SELECT 1 FROM TableWHERE PK = q.PK) orINSERT INTO [dbo].[YourTable] ( <Your Column List> )OUTPUT INSERTED.*SELECT <Your Column List>FROM [dbo].[YourSourceTable] A LEFT OUTER JOIN [dbo].[YourTable] BON A.[ID] = B.[ID]WHERE B.[ID] IS NULL ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 03:08:15
|
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 ALEFT 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 problemthis is my procedureALTER procedure [dbo].[AT](@empid nvarchar(10),@department varchar(10))asbeginselect[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from attend_logwhere eid = @empidgroup by [date]insert into abc ( --Time_Minutes,cid,bid,date,ebid,eid,employeename,shift,designation,department,timein,timeout,spendtime,excesshorttime,excessshort,remarks,comments)selectE.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 ''endComments 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) ton t.[date] = t2.[date] and t.eid = t2.eidleft OUTER join abc b on t.eid = b.eid[/red]left join employee e on e.eid = t.eidleft join designation d on e.designationid = d.designationidLEFT 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.DayLEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.ShiftLEFT OUTER JOIN Department T5 ON T5.did = e.didwhere [/red]b.eid is null[/red] andt2.eid=@empid or t5.did=@department order by t2.[date], t.[Timein] update ATTEND_LOG setSHIFT = 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 tleft join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Recordleft join employee e on e.eid = t.eidleft join designation d on e.designationid = d.designationidLEFT 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.DayLEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.ShiftLEFT OUTER JOIN Department T5 ON T5.did = e.didwhere t.eid=@empidendi use your query but its giving me two errorMsg 209, Level 16, State 1, Procedure AT, Line 61Ambiguous 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 excecuteimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 03:31:43
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 04:42:06
|
spent time issue is solveimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 04:45:57
|
quote: Originally posted by immad spent time issue is solveimmad uddin ahmed
goodselect 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 07:14:36
|
i am using this procedurealter procedure [dbo].[pocc](@empid nvarchar(10))asbeginselect * from attend_log where eid=@empidendINSERT INTO #TempEmployeesEXEC at '26187',''SELECT * FROM #TempEmployees--endbut its giving me that result(32 row(s) affected)Msg 208, Level 16, State 0, Procedure pocc, Line 11Invalid object name '#TempEmployees'.immad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 07:17:14
|
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))asbeginselect * from attend_log where eid=@empidendCREATE TABLE #TempEmployees(--columns with datatypes here (same as resultset of at procedure))INSERT INTO #TempEmployeesEXEC at '26187',''SELECT * FROM #TempEmployees--end ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 07:46:37
|
visak i already did that but problem isalter procedure [dbo].[pocc](@empid nvarchar(10),@department varchar(10))asbegincreate TABLE #TempEmployees(date datetime,eid int,remarks varchar(50))-- Insert result from the SP to temp tableINSERT INTO #TempEmployeesEXEC dbo.at @empid,@department--Verify the Insert recordsSELECT * FROM #TempEmployees where eid=@empidendexec [pocc] 17074,''when i exceute its giving me that resultdate----------------------------eid--------remarks2013-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----Late2013-01-08 00:00:00.000--17074----HALFDAYhow i make a summary result like this-----eid---offday--absent--late------halfday----17074------1----------1--------1----------1------immad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 07:54:31
|
what does the 1 value represent? record count?alter procedure [dbo].[pocc](@empid nvarchar(10),@department varchar(10))asbegincreate TABLE #TempEmployees(date datetime,eid int,remarks varchar(50))-- Insert result from the SP to temp tableINSERT INTO #TempEmployeesEXEC dbo.at @empid,@department--Verify the Insert recordsSELECT 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 halfdayFROM #TempEmployees where eid=@empidGROUP BY eidend ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-07-02 : 08:02:46
|
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 19Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).immad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-02 : 09:43:55
|
The issue is in procedure dbo.at which we dont have access to and you've not posted yetSo have a loointo it and see if there's a recursive call------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Next Page
|
|
|
|
|