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
 OFF DAY
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/15/2013 :  02:07:17  Show Profile  Reply with Quote

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 advance

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 06/15/2013 :  03:00:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare sample data
DECLARE	@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);

-- SwePeso
DECLARE	@DateMin DATE = '20130502',
	@DateMax DATE = '20130505';

SELECT		m.theDate AS [Date],
		s.[TimeIn],
		s.[TimeOut],
		ISNULL(s.[Shift], 'O') AS [Shift],
		e.EID
FROM		(
			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.EID
ORDER BY	e.EID,
		m.theDate



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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/15/2013 :  03:16:44  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

-- Prepare sample data
DECLARE	@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);

-- SwePeso
DECLARE	@DateMin DATE = '20130502',
	@DateMax DATE = '20130505';

SELECT		m.theDate AS [Date],
		s.[TimeIn],
		s.[TimeOut],
		ISNULL(s.[Shift], 'O') AS [Shift],
		e.EID
FROM		(
			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.EID
ORDER BY	e.EID,
		m.theDate



N 56°04'39.26"
E 12°55'05.63"





sir i am using sql server 2005

immad uddin ahmed
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/15/2013 :  06:31:42  Show Profile  Reply with Quote
This is my calendar data structure

CREATE TABLE [dbo].[Calendar]
(
[Date] [datetime] NULL
)


i insert all 2013 months dates in this table

i want to show offday dates in database like this


date----------------------------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.000
2013-05-05 00:00:00.000--17031----O-----------NULL------------------------NULL



please help me out

Edited by - immad on 06/17/2013 01:43:57
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 06/15/2013 :  09:34:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare sample data
DECLARE	@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 ALL
SELECT	'3-May-2013', '9:09AM', '02:01PM', 'G', 17090 UNION ALL
SELECT	'4-May-2013', '9:03AM', '10:41AM', 'G', 17090 UNION ALL
SELECT	'2-May-2013', '9:20AM', '06:17PM', 'G', 22020 UNION ALL
SELECT	'3-May-2013', '9:09AM', '02:01PM', 'G', 22020 UNION ALL
SELECT	'5-May-2013', '9:03AM', '10:41AM', 'G', 22020

-- SwePeso
DECLARE	@DateMin DATETIME,
	@DateMax DATETIME

SELECT	@DateMin '20130502',
	@DateMax '20130505'

SELECT		m.theDate AS [Date],
		s.[TimeIn],
		s.[TimeOut],
		ISNULL(s.[Shift], 'O') AS [Shift],
		e.EID
FROM		(
			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.EID
ORDER BY	e.EID,
		m.theDate


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 06/15/2013 09:35:04
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/15/2013 :  12:53:05  Show Profile  Reply with Quote
if you dont want to use master..spt_values table use this

DECLARE @MinDate datetime,@MinDate datetime

SELECT @MinDate = MIN([Date]),
@MaxDate = MAX([Date])
FROM Table

SELECT p.Date,
q.TimeIn,
q.TimeOut,
q.shift,
p.eid
FROM
(
SELECT f.[Date],eid
FROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) f
CROSS JOIN (SELECT DISTINCT eid FROM Table) t
)p
LEFT JOIN Table q
ON q.eid = p.eid
AND q.[Date] = p.[Date]



calendartable can be found here

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  01:34:38  Show Profile  Reply with Quote


Dear visakh

i read this blog


http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

and write this query and its giving me this result from jan 2013 to dec 2013
date----------------------------day----weekday
2013-01-01 00:00:00.000----Tuesday---1


but i have a problem


DECLARE @MinDate datetime,@MinDate datetime

SELECT @MinDate = MIN([Date]),
@MaxDate = MAX([Date])
FROM Table

SELECT p.Date,
q.TimeIn,
q.TimeOut,
q.shift,
p.eid
FROM
(
SELECT f.[Date],eid
FROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) f
CROSS JOIN (SELECT DISTINCT eid FROM Table) t
)p
LEFT JOIN Table q
ON q.eid = p.eid
AND q.[Date] = p.[Date]


in red table name what table name i write in it
i am little bit confused please help me out
thanks

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  01:39:46  Show Profile  Reply with Quote
that should be table where you've your sql data

ie table containing eid,date,timein,timeout etc fields

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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  01:45:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

that should be table where you've your sql data

ie table containing eid,date,timein,timeout etc fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 error
in this table i dont have
q.shift, field

immad uddin ahmed

Edited by - immad on 06/17/2013 01:47:42
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  01:47:12  Show Profile  Reply with Quote
yep...

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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  01:48:53  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

yep...

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




well i write this but its giving me error
in this table i dont have
q.shift, field


Invalid column name 'shift'.


immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  01:57:35  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  02:00:04  Show Profile  Reply with Quote
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 MVP
http://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 bit

immad uddin ahmed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  02:06:56  Show Profile  Reply with Quote
ok...

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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  03:26:47  Show Profile  Reply with Quote




DECLARE @MinDate datetime,
@MaxDate datetime



SELECT @MinDate = MIN([Date]),
@MaxDate = MAX([Date])
FROM attend_log

SELECT p.Date,
p.eid,
q.TimeIn,
q.TimeOut,
--q.shift,

FROM
(
SELECT f.[Date],eid
FROM dbo.CalendarTable(@MinDate,@MaxDate,0,0) f
CROSS JOIN (SELECT DISTINCT eid FROM attend_log) t
)p
LEFT JOIN attend_log q
ON q.eid = p.eid
AND q.[Date] = p.[Date]
where p.eid=17090
order by date,eid,timein,timeout




into this query




select
[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #temp1 from Atend
where eid = 17090
group by [date]
GO
select
t.[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 t
left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
where eid = 17090
order by t.[date], t.[Timein]






and show result like this after merge query


date-------------------------------eid------timein------------------------timeout-------------------spendtime--------excessshort
2013-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:00
2013-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:00
2013-01-06 00:00:00.000---26446-------------null---------------------null--------------------------null-----------------null




i 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 data

immad uddin ahmed

Edited by - immad on 06/17/2013 03:47:56
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  03:53:16  Show Profile  Reply with Quote
for that you can do the same change to code in blue

ie


select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #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
)t
left join Atend u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO
select
t.[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 t
left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
where t.eid = 17090
order by t.[date], t.[Timein]


http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

min and max date values are ones you need to pass based on what date range you want output for

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  04:17:52  Show Profile  Reply with Quote
do you get error when running first statement alone?

ie
select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #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
)t
left join Atend u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO


or do you get it when running both?

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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  04:19:17  Show Profile  Reply with Quote

select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #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

)t
left join Atend u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO
select
t.[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 t
left join #temp1 t2 on t.[date] = t2.[date] and t.[Timein] = t2.First_Record
where t.eid = 17090
order by t.[date], t.[Timein]



its not giving me correct result


---date---------------------------eid--------timein----------------------timeout---------------------spendtime------excesssshort
2013-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:00
2013-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:00
2013-05-03 00:00:00.000--17090--2013-06-13 15:56:00.000--2013-06-13 18:12:00.000------NULL-----------NULL
2013-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:00
2013-05-04 00:00:00.000--17090--2013-06-13 10:51:00.000--2013-06-13 12:10:00.000-------NULL-----------NULL
2013-05-04 00:00:00.000--17090--2013-06-13 12:15:00.000--2013-06-13 18:11:00.000-------NULL-----------NULL
2013-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


its doesnot giving me 05 may result

immad uddin ahmed

Edited by - immad on 06/17/2013 04:21:20
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  04:22:38  Show Profile  Reply with Quote
thats because you're doing join in reverse direction. try this


select
t.[date],
min([Timein]) as First_Record,
sum(DATEDIFF(minute, [Timein], [Timeout])) as Time_Minutes
into #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
)t
left join Atend u
on t.eid = u.eid
and t.[date] = u.[date]
where t.eid = 17090
group by t.[date]
GO
select
t2.[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 t
on t.[date] = t2.[date] 
and t.[Timein] = t2.First_Record
where t2.eid = 17090
order by t2.[date], t.[Timein]


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

immad
Posting Yak Master

Pakistan
218 Posts

Posted - 06/17/2013 :  04:41:13  Show Profile  Reply with Quote
sir again its not showing me off day


-----date-----------------------eid-----------timein----------------------timeout------------------spendtime-----excess
2013-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:00
2013-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:00
2013-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:00
2013-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



and it not giving me multiple rows

like this

---date---------------------------eid--------timein----------------------timeout---------------------spendtime------excesssshort
2013-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:00
2013-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:00
2013-05-03 00:00:00.000--17090--2013-06-13 15:56:00.000--2013-06-13 18:12:00.000------NULL-----------NULL
2013-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:00
2013-05-04 00:00:00.000--17090--2013-06-13 10:51:00.000--2013-06-13 12:10:00.000-------NULL-----------NULL
2013-05-04 00:00:00.000--17090--2013-06-13 12:15:00.000--2013-06-13 18:11:00.000-------NULL-----------NULL
2013-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

Edited by - immad on 06/17/2013 04:43:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  04:54:33  Show Profile  Reply with Quote
what did you pass as minimum and maximum date values inside function? show your full query used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 3 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.17 seconds. Powered By: Snitz Forums 2000