Calculate difference between dates

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate difference between dates

Author

Topic

papaja
Starting Member

8 Posts

Posted - 2007-07-21 : 06:19:33

In simplest: I want to calculate difference between values from two periods for every user.

Table is like this:
UserID DateID Value
1 1 100000
2 1 100000
1 2 95000
2 2 105000
3 2 100000
1 3 114000
2 3 80000
3 3 110000
...

Now I want for every UserID to calculate his LAST Value - his FIRST Value.

Is this possible without cursors?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-21 : 08:47:28

what is the expected result for the sample data that you posted ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 09:10:52

[code]-- Prepare sample data
DECLARE @Sample TABLE (UserID INT, DateID INT, Value MONEY)

INSERT @Sample
SELECT 1, 1, 100000 UNION ALL
SELECT 2, 1, 100000 UNION ALL
SELECT 1, 2, 95000 UNION ALL
SELECT 2, 2, 105000 UNION ALL
SELECT 3, 2, 100000 UNION ALL
SELECT 1, 3, 114000 UNION ALL
SELECT 2, 3, 80000 UNION ALL
SELECT 3, 3, 110000

-- Show the expected result
SELECT d.UserID,
SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END) - SUM(CASE WHEN d.o1 = 1 THEN d.Value ELSE 0 END)
FROM (
SELECT UserID,
Value,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DateID) AS o1,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DateID DESC) AS o2
FROM @Sample
) AS d
WHERE 1 IN (d.o1, d.o2)
GROUP BY d.UserID
ORDER BY d.UserID[/code]
Peter Larsson
Helsingborg, Sweden

papaja
Starting Member

8 Posts

Posted - 2007-07-21 : 11:15:40

Peso,
Thanks. Now let's complicate it a bit.
There are few more requirements. Namely we should have these columns:

UserID - done

Difference between last and first value - done

Yield: Difference you just calculated / first value for every UserID

Yield per day: Difference you just calculated / first value / count(DateID) for every UserID

Last value

And one more thing, there should be option to pass two DateID values and display rows between those two dates.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 14:41:21

I refuse!
Why do you waste our time by NOT posting complete problem statement at once?
Now I face the fact to go through your problem once again, or ignore you.

Peter Larsson
Helsingborg, Sweden

papaja
Starting Member

8 Posts

Posted - 2007-07-21 : 15:22:50

The fact is that I thought I will be able to continue when I see first part and not bother you with it, but I cannot.

However, I understand your objection and I'm grateful for first part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 17:43:00

[code]SELECT d.UserID,
SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END) - SUM(CASE WHEN d.o1 = 1 THEN d.Value ELSE 0 END) AS Diff,
1.0 * (SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END) - SUM(CASE WHEN d.o1 = 1 THEN d.Value ELSE 0 END)) / SUM(CASE WHEN d.o1 = 1 THEN d.Value ELSE 0 END) AS Yield,
1.0 * (SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END) - SUM(CASE WHEN d.o1 = 1 THEN d.Value ELSE 0 END)) / SUM(CASE WHEN d.o1 = 1 THEN d.Value ELSE 0 END) / MAX(d.o2) AS YieldPerDay,
SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END) AS LastValue
FROM (
SELECT UserID,
Value,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DateID) AS o1,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DateID DESC) AS o2
FROM @Sample
) AS d
WHERE 1 IN (d.o1, d.o2)
GROUP BY d.UserID
ORDER BY d.UserID[/code]

Peter Larsson
Helsingborg, Sweden

papaja
Starting Member

8 Posts

Posted - 2007-07-21 : 18:01:53

I didn't have time to check it but I believe this is ok. Not ok. Great.

Thank you very much, and sorry for causing you extra efforts!

papaja
Starting Member

8 Posts

Posted - 2007-07-22 : 05:14:00

Hi,
I was thinkering about your solution and think that "(SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END)" is great trick for having ordinary value presented like aggregate.

Now I'm wondering is there a trick for nvarchar values? What if d.Value supposed to be nvarchar or DateTime?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 05:54:45

No trick. The row numbering are made in the derived table.
The case thingy only makes the subtraction.

Peter Larsson
Helsingborg, Sweden

papaja
Starting Member

8 Posts

Posted - 2007-07-22 : 06:11:47

Maybe I wasn't clear.
(SUM(CASE WHEN d.o2 = 1 THEN d.Value ELSE 0 END) actually returns FIRST d.Value ignoring others. SUM makes it suitable to appear in SELECT list because there is GROUP BY.

Let's suppose that d.Value isn't numeric. Then SUM would not be valid. On the other hand we would have to have aggregates in SELECT list. How would we accomplish this.

For this discussion we should forget about difference calculation

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-22 : 06:14:00

If d.Value is not numeric, you can't subtract the two of them.
If it is non-numeric, you could make use of MAX instead of SUM.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources