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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 OFF DAY

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 advance

immad uddin ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-15 : 03:00:45
[code]-- 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[/code]


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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-15 : 03:16:44
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

230 Posts

Posted - 2013-06-15 : 06:31:42
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-15 : 09:34:34
[code]-- 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[/code]

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

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

230 Posts

Posted - 2013-06-17 : 01:34:38


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

52326 Posts

Posted - 2013-06-17 : 01:39:46
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

230 Posts

Posted - 2013-06-17 : 01:45:56
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 01:47:12
yep...

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-17 : 01:48:53
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

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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

52326 Posts

Posted - 2013-06-17 : 02:06:56
ok...

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-17 : 03:26:47




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
Go to Top of Page

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

52326 Posts

Posted - 2013-06-17 : 04:17:52
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

230 Posts

Posted - 2013-06-17 : 04:19:17

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
Go to Top of Page

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

230 Posts

Posted - 2013-06-17 : 04:41:13
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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -