Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem


Problem

Author
Message
Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
I am getting the result of the query as below based on the sample insert input

EID EName Late HALFDAY Absent OffDay SPENDTIME
17074 ABCEmployee 3 0 1 1 51:24
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
I AM SORRY SIR BUT THIS QUERY GIVE ME THAT RESULT

EID-------LATE----HALFDAY---ABSENT--OFFDAY--SPENDTIME
17074-----1---------1----------0--------0----------204:35
Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
Send the sample data in queries ..
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
sir this is my whole data


eid-------date---------------timein------------------timeout--------------spendtime-----------------excessshort--------remarks
17074--2013-01-01-------08:39:00.000-------18:04:00.000---------------09:25:00----------------00:25:00-------Late
17074--2013-01-02-------08:42:00.000-------09:56:00.000---------------08:50:00---------------10:00.000--------null
17074--2013-01-02-------10:09:00.000-------12:23:00.000---------------null---------------------null--------------null
17074--2013-01-02-------12:32:00.000--------3:14:00.000---------------null---------------------null--------------null
17074--2013-01-02-------13:30:00.000-------18:10:00.000---------------null---------------------null--------------null
17074--2013-01-03-------08:40:00.000-------17:55:00.000---------------09:15:00.000----------00:15:00.000------null
17074--2013-01-04-------08:32:00.000-------10:53:00.000---------------08:45:00.000----------00:15:00.000------null
17074--2013-01-04-------11:09:00.000-------12:57:00.000---------------null----------------------null-------------null
17074--2013-01-04-------13:06:00.000-------13:18:00.000---------------null----------------------null-------------null
17074--2013-01-04-------13:45:00.000-------18:09:00.000---------------null----------------------null-------------null
17074--2013-01-05-------08:47:00.000-------18:01:00.000------------ ---09:14:00.000------------00:14:00.000---halfday
17074--2013-01-08-------08:36:00.000-------10:25:00.000------------ ---09:55:00.000------------00:55:00.000-----null
17074--2013-01-08-------10:49:00.000-------13:02:00.000---------------null-----------------------null-------------null
17074--2013-01-08-------13:35:00.000-------15:24:00.000---------------null-----------------------null-------------null
17074--2013-01-08-------15:42:00.000-------19:46:00.000---------------null-----------------------null-----------------null


this is your query

;WITH AllDateCTE
AS
(
SELECT Eid,Date,SpendTime,Remarks FROM ATTEND_LOG WHERE EID=17074
UNION ALL
SELECT Eid,DATEADD(DD,-1,Date) AS Date,NULL AS SpendTime,
CASE
WHEN DATENAME(weekday,DATEADD(DD,-1,Date)) ='SUNDAY'
THEN CONVERT(VARCHAR(50),'OffDay' )
ELSE CONVERT(VARCHAR(50),'Absent' )
END as Remarks
FROM AllDateCTE
WHERE DATEADD(DD,-1,Date) NOT IN ( SELECT Date FROM Attend_log )
AND DATEADD(DD,-1,Date) BETWEEN '2013-01-01 00:00:00.000' and '2013-01-08 00:00:00.000'
)
SELECT
EID,
Late,
[HALFDAY],
Absent,
OffDay,
CAST(((DATEDIFF(dd,0,AggTimedate) * 24) + DATEPART(hh,AggTimedate)) AS varchar(5)) + ':' + DATENAME(mi,AggTimedate) AS SPENDTIME
FROM
(
SELECT
g.EID,
sum(case when g.Remarks = 'Late' then 1 else 0 end) as LATE,
sum(case when g.Remarks = 'HALF DAY' then 1 else 0 end) as [HALFDAY],
sum(case when g.Remarks = 'Absent' then 1 else 0 end) as Absent,
sum(case when g.Remarks = 'OffDay' then 1 else 0 end) as OffDay,
DATEADD(mi,SUM(DATEDIFF(mi,0,spendtime)),0) AS AggTimedate
FROM AllDateCTE g

where g.eid=17074 and date >'20130101' and date <'20130108'
group by g.eid
)t


and this query gives me this result

eid--------late--halfday---absent--offday----spendtime
17074------1------1-------0-------0--------204:35
Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
Send the data as Insert scripts .
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
SIR I AM REALLY SORRY I DONT HAVE AN OPTION OF SCRIPT DATA I JUST SCRIPT MY TABLE


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ATTEND_LOG]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ATTEND_LOG](
[EID] [int] NULL,
[date] [datetime] NULL,
[timein] [datetime] NULL,
[timeout] [datetime] NULL,
[BID] [int] NULL,
[EBID] [int] NULL,
[spendtime] [datetime] NULL,
[excessshort] [datetime] NULL,
[excess] [nvarchar](50) NULL,
[remarks] [varchar](50) NULL,
[SHIFT] [varchar](50) NULL
) ON [PRIMARY]
END


Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
usE Narayana Vyas Kondreddi's stored procedure sp_generate_inserts http://vyaskn.tripod.com/code/generate_inserts.txt in a SQL Server database.

Use this proc to generate the insert statement
immaduddinahmed
immaduddinahmed
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 420
sir here it is just copy and paste it into your sql server


create TABLE atend_log
(
eid int,
date datetime,
timein datetime,
timeout datetime,
spendtime datetime,
excessshort datetime,
remarks varchar(50)
)

insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-01 00:00:00.000','2013-06-19 08:39:00.000','2013-06-19 18:04:00.000','1900-01-01 09:25:00.000','1900-01-01 00:25:00.000','Late')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 08:42:00.000','2013-06-19 09:56:00.000','1900-01-01 08:50:00.000','1900-01-01 00:10:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 10:09:00.000','2013-06-19 12:23:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 12:32:00.000','2013-06-19 13:14:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-02 00:00:00.000','2013-06-19 13:30:00.000','2013-06-19 18:10:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-03 00:00:00.000','2013-06-19 08:40:00.000','2013-06-19 17:55:00.000','1900-01-01 09:15:00.000','1900-01-01 00:15:00.000','Halfday')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 08:32:00.000','2013-06-19 10:53:00.000','1900-01-01 08:45:00.000','1900-01-01 00:15:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 11:09:00.000','2013-06-19 12:57:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 13:06:00.000','2013-06-19 13:18:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-04 00:00:00.000','2013-06-19 13:45:00.000','2013-06-19 18:09:00.000','','','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-05 00:00:00.000','2013-06-19 08:47:00.000','2013-06-19 18:01:00.000','1900-01-01 09:14:00.000','1900-01-01 00:14:00.000','')
insert [dbo].[atend_log] ([eid],[date],[timein],[timeout],[spendtime],excessshort,[remarks])
values(1,'2013-01-08 00:00:00.000','2013-06-19 08:35:00.000','2013-06-19 18:29:00.000','1900-01-01 09:54:00.000','1900-01-01 00:54:00.000','')


Bala'
Bala'
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 620
Things not clarified ,,
More than one rows per date
how spend date was calculated
how late and half date was updTED
etc

GIVE PROPER EXPLANATION ON HOW THE DATA RESIDES , AND WHAT WAS THE BUSINESS RULES TO FRAME THE OUTPUT ..THAT SAVES A TONS OF TIME
Your just giving data and output
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3002 Visits: 5000
Bala' (6/27/2013)
Things not clarified ,,
More than one rows per date
how spend date was calculated
how late and half date was updTED
etc

GIVE PROPER EXPLANATION ON HOW THE DATA RESIDES , AND WHAT WAS THE BUSINESS RULES TO FRAME THE OUTPUT ..THAT SAVES A TONS OF TIME
Your just giving data and output


I am not sure if typing in bold capital letters is going to make the OP understand the importance of properly describing and presenting the question.
We have articles in SSC which we normally refer to such OP's to read and understand first.
If the OP still does not understand, I normally walk off and never waste my time.

@Immaduddin
I am not sure if you are wondering Why there is only one person trying to solve my problem and that nobody else seems to assist?
If you are not wondering so you should. SSC is a place where you get really quick help from many experts if you follow some basic rules.
If you don't follow those rules, you reduce your own chances of being helped
Hence, I would once again refer you to the same article on Forum Etiquette
If you read the article and follow some basic rules, I am sure you will get a lot more help and that too much quicker. Good luck:-)


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search