Date Calculation - SQL Server Forums

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

Transact-SQL (2012)

Date Calculation

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2015-02-17 : 10:27:34

Hello, I am really stuck on a query. I need to update some information.

I have data that looks like this:
COL1 COL2 COL3 COL4
CAT 2 9-30-2014 NULL
DOG 4 9-30-2014 NULL
RAT 5 9-30-2014 NULL
CAT 6 10-30-2014 NULL
DOG 7 10-30-2014 NULL
RAT 8 10-30-2014 NULL
CAT 11 11-30-2014 NULL
DOG 10 11-30-2014 NULL
RAT 13 11-30-2014 NULL

I need the output of COL4 to be the difference between the previous month. If there isnt a previous month (ie 9-30-2014) then ignore it. The output should look like this:

COL1 COL2 COL3 COL4
CAT 2 9-30-2014 NULL
DOG 4 9-30-2014 NULL
RAT 5 9-30-2014 NULL
CAT 6 10-30-2014 4
DOG 7 10-30-2014 3
RAT 8 10-30-2014 3
CAT 11 11-30-2014 5
DOG 10 11-30-2014 3
RAT 13 11-30-2014 5

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 11:34:41

where do you get the previous month? e.g. for the first row, where do you the previous month?

Johnph
Posting Yak Master

103 Posts

Posted - 2015-02-17 : 11:36:47

If there isn't a previous month then it will be kept NULL.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 11:52:29

If there is a previous month, where is it? For the first row, is there a previous month? (I can't tell from your data). If not, why not? If so, then where is the previous month? (there's only one date in the row)

Johnph
Posting Yak Master

103 Posts

Posted - 2015-02-17 : 13:12:42

9-30-2014 dates dont have previous dates. But the 10-30-2014 and 11-30-2014 do.

Basically (CAT 6 10-30-2014) will subtract from (CAT 2 9-30-2014 NULL). 6-2 and get 4 for COL4.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 13:54:39

Oh -- you see that detail was missing! In that case, you can use lead/lag

e.g. something like:

SELECT Col1, Col2, Col3,
datediff(month, lag(col3) over (partition by col1, order by col2), col3) as col3

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 13:58:46

this works:


declare @t table (col1 char(3), col2 int, col3 date, col4 date)
insert into @t (col1, col2, col3, col4) values
('cat', 1, '2014-10-10',null),
('cat', 2, '2014-12-10',null),
('cat', 4, '2015-1-10',null)

select col1, col2, col3,
	   datediff(month, lag(col3) over (partition by col1 order by col2), col3) as col4
from @t

Johnph
Posting Yak Master

103 Posts

Posted - 2015-02-17 : 14:21:19

Hmm,

I think we are close. But the output isn't 100%

declare @t table (col1 char(3), col2 int, col3 date, col4 date)
insert into @t (col1, col2, col3, col4) values
('cat', 1, '2014-09-10',null),
('cat', 2, '2014-10-10',null),
('cat', 4, '2014-11-10',null)

select col1, col2, col3,
datediff(month, lag(col3) over (partition by col1 order by col2), col3) as col4
from @t

------------------

COL4 should be
cat 1 2014-09-10 NULL
cat 2 2014-10-10 1
cat 4 2014-11-10 2

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 15:11:07

WHy? In the sample data, the previous month for CAT at Nov is Oct. There IS a previous month (Oct) so the difference is 1. Right?

Johnph
Posting Yak Master

103 Posts

Posted - 2015-02-17 : 16:14:17

Use my example that has sept, oct, nov.

The output should be:
cat 1 2014-09-10 NULL
cat 2 2014-10-10 1
cat 4 2014-11-10 2

Your code gives:

COL4 should be
cat 1 2014-09-10 NULL
cat 2 2014-10-10 1
cat 4 2014-11-10 1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 17:58:41

My code is correct according to your specifications. There is one month from Sep to Oct and one from Oct to Nov (not 2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources