Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Problem Expand / Collapse
Author
Message
Posted Wednesday, June 26, 2013 4:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:40 AM
Points: 418, Visits: 588
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

Post #1467550
Posted Wednesday, June 26, 2013 5:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 5:38 AM
Points: 102, Visits: 312
I AM SORRY SIR BUT THIS QUERY GIVE ME THAT RESULT

EID-------LATE----HALFDAY---ABSENT--OFFDAY--SPENDTIME
17074-----1---------1----------0--------0----------204:35


Post #1467598
Posted Wednesday, June 26, 2013 6:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:40 AM
Points: 418, Visits: 588
Send the sample data in queries ..
Post #1467621
Posted Thursday, June 27, 2013 1:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 5:38 AM
Points: 102, Visits: 312
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
Post #1467982
Posted Thursday, June 27, 2013 1:55 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:40 AM
Points: 418, Visits: 588
Send the data as Insert scripts .
Post #1467990
Posted Thursday, June 27, 2013 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 5:38 AM
Points: 102, Visits: 312
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

Post #1468011
Posted Thursday, June 27, 2013 2:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:40 AM
Points: 418, Visits: 588
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
Post #1468021
Posted Thursday, June 27, 2013 3:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 5:38 AM
Points: 102, Visits: 312
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','')

Post #1468040
Posted Thursday, June 27, 2013 3:23 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:40 AM
Points: 418, Visits: 588
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
Post #1468046
Posted Thursday, June 27, 2013 3:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
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/
Post #1468060
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse