Now I can see, why this

2005-01-01 10:00:00           Customer1                      50

2005-01-01 11:00:00           Customer1                      50

will result in 60 minutes difference. However, why should this

2005-01-01 14:00:00           Customer1                      50

2005-01-02 15:00:00           Customer1                     ...

I would be interested to know where you have read that SQL Server automatically stores dates as UTC dates? Never heard of this before. You might want to read this:...

Äh, may I say that the requirement was to construct the *last* day of a month? I can't see your statement doing this. Let alone the fact that it won't run...

The way your say this, sounds like you have not control over the design, right? However, if you are able to change the table design, you should really do this....

You mean he can visit multiple times a day? And you only have an EntryDate and no ExitDate (or something like that)? How will you measure that duration? And what...

One might guess that you need to put everything including IF EXISTS into a string and execute that. However, you are aware of http://www.sommarskog.se/dynamic_sql.html ? And may I add that...

Something like this?

SELECT

 MIN(location) location

 , MIN([name]) [name]

 , MIN(EntryDate) entrydate

 , DATEDIFF(mi,MIN(EntryDate),MAX(EntryDate)) diff_in_minutes_per_day

FROM 

 < your_table >

WHERE

 EntryDate>='20050101' AND EntryDate<'20050103'

AND

 Location = 50

GROUP BY

 [name]

 , DAY(EntryDate)

?

Haha, that reminds me of some Hägar the horrible comic where they stand in front of Stonehenge and someone says: "Should have been a shopping mall, but it never got...

smalldatetimer

This is likely to be a typo.

CREATE TABLE [dbo].[t1]

(

 [date] [smalldatetime], [amt_recharge] [int] NULL ,

 [amt_food ] [int] NULL ,

 [bc] AS ([amt_recharge] + [amt_food])...

Test?

Stupid me!!!

SET NOCOUNT ON

IF OBJECT_ID('lfdsum_t') IS NOT NULL

     DROP TABLE lfdsum_t

GO

CREATE TABLE lfdsum_t

(

 id int identity

 , pay_amt decimal(8,2)

 , inv_pmt decimal(8,2)

)

INSERT INTO lfdsum_t values (375000,111375);

INSERT INTO lfdsum_t values (375000,21656.25);

INSERT INTO lfdsum_t...

Dinesh, what did you do here?

This might serve as a start.

SET NOCOUNT ON

IF OBJECT_ID('lfdsum_t') IS NOT NULL

     DROP TABLE lfdsum_t

GO

CREATE TABLE lfdsum_t

(

 id int identity

 , pay_amt decimal(8,2)

 , inv_pmt decimal(8,2)

)

INSERT INTO lfdsum_t values (375000,111375);

INSERT INTO...

FWIW, here's a version without using a UDF. It's based on a suggestion provided by SQL Server MVP Steve Kass.

create table #dummy

(

 c1 int

)

insert into #dummy values(202912)

insert into #dummy values(200508)

insert...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=162243