ADDING DATES

[sereleg]

After executing the next query I obtain the data bellow..
Query:
SELECT datechanged,id,posid,upby,day,month,year,seto
FROM tblPostingStatusTrail a
WHERE a.seto = 'on'

datechanged--id---posgid--upby----day-month-year-seto
2003-03-17 1 500 Gerar 4 1 2005 on
2005-04-11 55 475 Suque 6 4 2005 on
2005-04-11 56 475 Suquet 6 7 2005 on
2005-04-11 57 475 Suquet 6 8 2005 on
2005-04-26 58 895 RAUL 2 9 2005 on
2005-04-26 58 895 Raul 2 11 2005 on



I need some help to obtain:

posgid--upby---- Totaltime -seto
500 Gerar 1moth on
475 Suquet 4month on
895 Raul 2month on

To get this result I should add the dates and calculate this into month related with a postingID.

I would appreciate any help.

Thanks

 

[hmckillop]

in your example for 475 suquet, how do you end up with thr result 4 with the actual month data 4, 7, 8)?
What do you mean when you say add dates?, generally when people refer to adding dates they mean add for example 2 months to the current date, or 300 days on to the current date i.e. 300 days in the future.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLBill]

If I understand you correctly, you want to GROUP the information by 'usby' and subtract the MIN(month) from the MAX(month) to get the TotalTime. Correct?

-SQLBill

Posting advice: FAQ481-4875

 

[manjarikosaraju]

Try this, Its not clear from your query if year will be same always. If it is same, then just take the difference of month values. Else convert day,month,year into a datetime and get the difference of min, max dates grouped by posgid, upby.

For the user Gerar, if you want 0 months as result, take of the isnull and nullif functions around the datediff function.

select posgid, upby,
isnull(nullif(datediff(mm,min(convert(datetime,convert(varchar(4),syear) + '-' + convert(varchar(4),smonth) + '-' + convert(varchar(4),sday))),max(convert(datetime,convert(varchar(4),syear) + '-' + convert(varchar(4),smonth) + '-' + convert(varchar(4),sday)))),0),1)
from #Table
group by posgid, upby

Hope this helps. It must be cumbersome to have everything in the same query.