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-15 : 02:07:17
|
HI i want to show off days in my report my sql data is like this date----------timein------timeout--shift----------eid 2-May-2013--9:20AM---6:17PM-----G-----------17090 3-May-2013--9:09AM---2:01PM-----G-----------17090 4-May-2013--9:03AM---10:41AM----G-----------17090 sql data doesnot show 5 may data in sql or u can say sunday data becouse sunday is off and no employee come on sunday thats why i cant show off day in report please give me a suggestion to look data like this or any month when sunday come report show O in shift and show date just like below date----------timein------timeout--shift----------------eid 2-May-2013--9:20AM---6:17PM-----G----------------17090 3-May-2013--9:09AM---2:01PM-----G----------------17090 4-May-2013--9:03AM---10:41AM----G----------------17090 5-May-2013--------------------------O----------------17090 i hope u under stand Thank in advanceimmad uddin ahmed |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-15 : 03:00:45
|
[code]-- Prepare sample dataDECLARE @Sample TABLE ( [Date] DATE NOT NULL, TimeIn TIME(0) NOT NULL, [TimeOut] TIME(0) NOT NULL, [Shift] CHAR(1) NOT NULL, EID SMALLINT NOT NULL );INSERT @Sample ( [Date], TimeIn, [TimeOut], [Shift], EID )VALUES ('2-May-2013', '9:20AM', '06:17PM', 'G', 17090), ('3-May-2013', '9:09AM', '02:01PM', 'G', 17090), ('4-May-2013', '9:03AM', '10:41AM', 'G', 17090), ('2-May-2013', '9:20AM', '06:17PM', 'G', 22020), ('3-May-2013', '9:09AM', '02:01PM', 'G', 22020), ('5-May-2013', '9:03AM', '10:41AM', 'G', 22020);-- SwePesoDECLARE @DateMin DATE = '20130502', @DateMax DATE = '20130505';SELECT m.theDate AS [Date], s.[TimeIn], s.[TimeOut], ISNULL(s.[Shift], 'O') AS [Shift], e.EIDFROM ( SELECT DATEADD(DAY, Number, @DateMin) FROM master.dbo.spt_values WHERE Type = 'P' AND Number <= DATEDIFF(DAY, @DateMin, @DateMax) ) AS m(theDate)CROSS JOIN ( SELECT DISTINCT EID FROM @Sample ) AS e(EID)LEFT JOIN @Sample AS s ON s.[Date] = m.theDate AND s.EID = e.EIDORDER BY e.EID, m.theDate[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-15 : 03:16:44
|
quote: Originally posted by SwePeso
-- Prepare sample dataDECLARE @Sample TABLE ( [Date] DATE NOT NULL, TimeIn TIME(0) NOT NULL, [TimeOut] TIME(0) NOT NULL, [Shift] CHAR(1) NOT NULL, EID SMALLINT NOT NULL );INSERT @Sample ( [Date], TimeIn, [TimeOut], [Shift], EID )VALUES ('2-May-2013', '9:20AM', '06:17PM', 'G', 17090), ('3-May-2013', '9:09AM', '02:01PM', 'G', 17090), ('4-May-2013', '9:03AM', '10:41AM', 'G', 17090), ('2-May-2013', '9:20AM', '06:17PM', 'G', 22020), ('3-May-2013', '9:09AM', '02:01PM', 'G', 22020), ('5-May-2013', '9:03AM', '10:41AM', 'G', 22020);-- SwePesoDECLARE @DateMin DATE = '20130502', @DateMax DATE = '20130505';SELECT m.theDate AS [Date], s.[TimeIn], s.[TimeOut], ISNULL(s.[Shift], 'O') AS [Shift], e.EIDFROM ( SELECT DATEADD(DAY, Number, @DateMin) FROM master.dbo.spt_values WHERE Type = 'P' AND Number <= DATEDIFF(DAY, @DateMin, @DateMax) ) AS m(theDate)CROSS JOIN ( SELECT DISTINCT EID FROM @Sample ) AS e(EID)LEFT JOIN @Sample AS s ON s.[Date] = m.theDate AND s.EID = e.EIDORDER BY e.EID, m.theDate N 56°04'39.26"E 12°55'05.63"
sir i am using sql server 2005immad uddin ahmed |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-15 : 06:31:42
|
This is my calendar data structureCREATE TABLE [dbo].[Calendar]( [Date] [datetime] NULL)i insert all 2013 months dates in this tablei want to show offday dates in database like thisdate----------------------------eid----shift--------timein---------------------timeout--2013-05-04 00:00:00.000--17031----G----2013-06-13 09:15:00.000-----2013-06-13 15:23:00.0002013-05-05 00:00:00.000--17031----O-----------NULL------------------------NULLplease help me out |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-06-15 : 09:34:34
|
[code]-- Prepare sample dataDECLARE @Sample TABLE ( [Date] DATETIME NOT NULL, TimeIn TIMETIME NOT NULL, [TimeOut] DATETIME NOT NULL, [Shift] CHAR(1) NOT NULL, EID SMALLINT NOT NULL )INSERT @Sample ( [Date], TimeIn, [TimeOut], [Shift], EID )SELECT '2-May-2013', '9:20AM', '06:17PM', 'G', 17090 UNION ALLSELECT '3-May-2013', '9:09AM', '02:01PM', 'G', 17090 UNION ALLSELECT '4-May-2013', '9:03AM', '10:41AM', 'G', 17090 UNION ALLSELECT '2-May-2013', '9:20AM', '06:17PM', 'G', 22020 UNION ALLSELECT '3-May-2013', '9:09AM', '02:01PM', 'G', 22020 UNION ALLSELECT '5-May-2013', '9:03AM', '10:41AM', 'G', 22020-- SwePesoDECLARE @DateMin DATETIME, @DateMax DATETIMESELECT @DateMin '20130502', @DateMax '20130505'SELECT m.theDate AS [Date], s.[TimeIn], s.[TimeOut], ISNULL(s.[Shift], 'O') AS [Shift], e.EIDFROM ( SELECT DATEADD(DAY, Number, @DateMin) FROM master.dbo.spt_values WHERE Type = 'P' AND Number <= DATEDIFF(DAY, @DateMin, @DateMax) ) AS m(theDate)CROSS JOIN ( SELECT DISTINCT EID FROM @Sample ) AS e(EID)LEFT JOIN @Sample AS s ON s.[Date] = m.theDate AND s.EID = e.EIDORDER BY e.EID, m.theDate[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-15 : 12:53:05
|
if you dont want to use master..spt_values table use thisDECLARE @MinDate datetime,@MinDate datetimeSELECT @MinDate = MIN([Date]),@MaxDate = MAX([Date])FROM TableSELECT p.Date,q.TimeIn,q.TimeOut,q.shift,p.eidFROM(SELECT f.[Date],eidFROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) fCROSS JOIN (SELECT DISTINCT eid FROM Table) t)pLEFT JOIN Table qON q.eid = p.eidAND q.[Date] = p.[Date] calendartable can be found herehttp://visakhm.blogspot.in/2010/02/generating-calendar-table.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 01:34:38
|
Dear visakhi read this blog http://visakhm.blogspot.in/2010/02/generating-calendar-table.htmland write this query and its giving me this result from jan 2013 to dec 2013date----------------------------day----weekday2013-01-01 00:00:00.000----Tuesday---1but i have a problemDECLARE @MinDate datetime,@MinDate datetimeSELECT @MinDate = MIN([Date]),@MaxDate = MAX([Date])FROM TableSELECT p.Date,q.TimeIn,q.TimeOut,q.shift,p.eidFROM(SELECT f.[Date],eidFROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) fCROSS JOIN (SELECT DISTINCT eid FROM Table) t)pLEFT JOIN Table qON q.eid = p.eidAND q.[Date] = p.[Date]in red table name what table name i write in iti am little bit confused please help me outthanksimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 01:39:46
|
that should be table where you've your sql dataie table containing eid,date,timein,timeout etc fields------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 01:45:56
|
quote: Originally posted by visakh16 that should be table where you've your sql dataie table containing eid,date,timein,timeout etc fields------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
u mean i write that table name who have eid date timein and time out fields where table in written well i write this but its giving me errorin this table i dont haveq.shift, fieldimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 01:47:12
|
yep...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 01:48:53
|
quote: Originally posted by visakh16 yep...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
well i write this but its giving me errorin this table i dont haveq.shift, fieldInvalid column name 'shift'.immad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 01:57:35
|
i dont know but your posted table details had that column. so make sure you put correct table name from which you get it. in case you get it from more than one table put the query there------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 02:00:04
|
quote: Originally posted by visakh16 i dont know but your posted table details had that column. so make sure you put correct table name from which you get it. in case you get it from more than one table put the query there------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thanks visakh i correct it now off day and person is absent both this type of dates come in table i just change your query little bitimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 02:06:56
|
ok...------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 03:26:47
|
DECLARE @MinDate datetime,@MaxDate datetimeSELECT @MinDate = MIN([Date]),@MaxDate = MAX([Date])FROM attend_logSELECT p.Date,p.eid,q.TimeIn,q.TimeOut,--q.shift,FROM(SELECT f.[Date],eidFROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) fCROSS JOIN (SELECT DISTINCT eid FROM attend_log) t)pLEFT JOIN attend_log qON q.eid = p.eidAND q.[Date] = p.[Date]where p.eid=17090order by date,eid,timein,timeoutinto this queryselect[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from Atendwhere eid = 17090group by [date]GOselectt.[date],t.eid,t.[Timein] as timein,t.[Timeout] as timeout,CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,FROM Atend tleft join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Recordwhere eid = 17090order by t.[date], t.[Timein]and show result like this after merge querydate-------------------------------eid------timein------------------------timeout-------------------spendtime--------excessshort2013-01-04 00:00:00.000---26446--2013-06-12 09:29:00.000---2013-06-12 18:47:00.000---09:18:00--------00:18:002013-01-05 00:00:00.000---26446--2013-06-12 09:08:00.000---2013-06-12 13:34:00.000---07:41:00-------01:19:002013-01-06 00:00:00.000---26446-------------null---------------------null--------------------------null-----------------nulli am merging this query becouse blue query didnt show sundays or absents becouse the machine where employee swap card only give swap card data if employee is absent or off day then u dont have that type of dataimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 03:53:16
|
for that you can do the same change to code in blueieselectt.[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from (SELECT eid,[date] FROM (select distinct eid from attend)a cross join dbo.calendartable(<your min date value>,<your max date value>,0,0)b)tleft join Atend uon t.eid = u.eidand t.[date] = u.[date]where t.eid = 17090group by t.[date]GOselectt.[date],t.eid,t.[Timein] as timein,t.[Timeout] as timeout,CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,FROM Atend tleft join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Recordwhere t.eid = 17090order by t.[date], t.[Timein] http://visakhm.blogspot.in/2010/02/generating-calendar-table.htmlmin and max date values are ones you need to pass based on what date range you want output for------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 04:17:52
|
do you get error when running first statement alone?ie selectt.[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from (SELECT eid,[date] FROM (select distinct eid from attend)a cross join dbo.calendartable(<your min date value>,<your max date value>,0,0)b)tleft join Atend uon t.eid = u.eidand t.[date] = u.[date]where t.eid = 17090group by t.[date]GOor do you get it when running both?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 04:19:17
|
selectt.[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from (SELECT eid,[date] FROM (select distinct eid from attend)a cross join dbo.calendartable('2013-05-02','2013-05-08',0,0)b)tleft join Atend uon t.eid = u.eidand t.[date] = u.[date]where t.eid = 17090group by t.[date]GOselectt.[date],t.eid,t.[Timein] as timein,t.[Timeout] as timeout,CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,FROM Atend tleft join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Recordwhere t.eid = 17090order by t.[date], t.[Timein]its not giving me correct result ---date---------------------------eid--------timein----------------------timeout---------------------spendtime------excesssshort2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000---2013-06-13 18:17:00.000----08:57:00--------00:03:002013-05-03 00:00:00.000---17090---2013-06-13 09:09:00.000--2013-06-13 14:01:00.000----07:08:00----------01:52:002013-05-03 00:00:00.000--17090--2013-06-13 15:56:00.000--2013-06-13 18:12:00.000------NULL-----------NULL2013-05-04 00:00:00.000--17090--2013-06-13 09:03:00.000--2013-06-13 10:41:00.000------08:53:00----------00:07:002013-05-04 00:00:00.000--17090--2013-06-13 10:51:00.000--2013-06-13 12:10:00.000-------NULL-----------NULL2013-05-04 00:00:00.000--17090--2013-06-13 12:15:00.000--2013-06-13 18:11:00.000-------NULL-----------NULL2013-05-06 00:00:00.000--17090--2013-06-13 09:17:00.000---2013-06-13 18:23:00.000------09:06:00------00:06:00its doesnot giving me 05 may resultimmad uddin ahmed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 04:22:38
|
thats because you're doing join in reverse direction. try thisselectt.[date],min([Timein]) as First_Record,sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutesinto #temp1 from (SELECT eid,[date] FROM (select distinct eid from attend)a cross join dbo.calendartable(<your min date value>,<your max date value>,0,0)b)tleft join Atend uon t.eid = u.eidand t.[date] = u.[date]where t.eid = 17090group by t.[date]GOselectt2.[date],t2.eid,t.[Timein] as timein,t.[Timeout] as timeout,CONVERT(VARCHAR(8), DATEADD(minute, Time_Minutes, 0), 108) AS SpendTime,CONVERT(VARCHAR(8), DATEADD(minute, ABS(540 - Time_Minutes), 0), 108) as excesshorttime,FROM #temp1 t2 left join Atend ton t.[date] = t2.[date] and t.[Timein] = t2.First_Recordwhere t2.eid = 17090order by t2.[date], t.[Timein] ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
immad
Posting Yak Master
230 Posts |
Posted - 2013-06-17 : 04:41:13
|
sir again its not showing me off day-----date-----------------------eid-----------timein----------------------timeout------------------spendtime-----excess2013-05-02 00:00:00.000--17090--2013-06-13 09:20:00.000--2013-06-13 18:17:00.000--08:57:00---00:03:002013-05-03 00:00:00.000--17090--2013-06-13 09:09:00.000--2013-06-13 14:01:00.000---07:08:00--01:52:002013-05-04 00:00:00.000--17090--2013-06-13 09:03:00.000--2013-06-13 10:41:00.000---08:53:00--00:07:002013-05-06 00:00:00.000--17090--2013-06-13 09:17:00.000--2013-06-13 18:23:00.000---09:06:00--00:06:00and it not giving me multiple rowslike this---date---------------------------eid--------timein----------------------timeout---------------------spendtime------excesssshort2013-05-02 00:00:00.000---17090--2013-06-13 09:20:00.000---2013-06-13 18:17:00.000----08:57:00--------00:03:002013-05-03 00:00:00.000---17090---2013-06-13 09:09:00.000--2013-06-13 14:01:00.000----07:08:00----------01:52:002013-05-03 00:00:00.000--17090--2013-06-13 15:56:00.000--2013-06-13 18:12:00.000------NULL-----------NULL2013-05-04 00:00:00.000--17090--2013-06-13 09:03:00.000--2013-06-13 10:41:00.000------08:53:00----------00:07:002013-05-04 00:00:00.000--17090--2013-06-13 10:51:00.000--2013-06-13 12:10:00.000-------NULL-----------NULL2013-05-04 00:00:00.000--17090--2013-06-13 12:15:00.000--2013-06-13 18:11:00.000-------NULL-----------NULL2013-05-06 00:00:00.000--17090--2013-06-13 09:17:00.000---2013-06-13 18:23:00.000------09:06:00------00:06:00 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 04:54:33
|
what did you pass as minimum and maximum date values inside function? show your full query used------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Next Page
|
|
|
|
|