Date diff result fetch from table output

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

General SQL Server Forums

New to SQL Server Programming

Date diff result fetch from table output

Author

Topic

KCSNirav
Starting Member

4 Posts

Posted - 2013-04-11 : 09:42:17

Hello All,

I need help in 1 sql query. My requirement describe in below few steps,

JobId JobDate
353 5/1/2013
353 5/2/2013
-1 5/3/2013
353 5/4/2013
352 5/6/2013

I need data from above out put like shown in below, Here I do not need to use cursor or while loop to get this output.

JobId JobDate
353 5/1/2013
353 5/2/2013
-1 5/3/2013
353 5/4/2013

After that insert these tables output into temp table. Then after I have passed (Where condition JobId > 0) so my final output is shown in below,

JobId JobDate
353 5/1/2013
353 5/2/2013
353 5/4/2013

Now I need extend date and my output is like, Mean 1st record add day 1, 2nd record add days 2 and 3rd record add day 1.

JobId JobDate
353 5/2/2013
353 5/4/2013
353 5/5/2013

Thanks & Regards,
Nirav

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-11 : 11:47:56

Assuming that you're okay with the below two requirements and might have sorted out yourself
--------------------------------------
--> I need data from above out put like shown in below, Here I do not need to use cursor or while loop to get this output.
select * from YourTableName WHERE JobDate<'2013-05-06'

-->After that insert these tables output into temp table. Then after I have passed (Where condition JobId > 0) so my final output is shown in below,
SELECT * FROM YouTableName WHERE JobId>0
--------------------------------------

But what logic is this using
--> Mean 1st record add day 1, 2nd record add days 2 and 3rd record add day 1.
how many days should be added for 4th record? for the 5th one? ... for the Nth one?

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 04:41:57

Not fully clear. but based on your small explanation i think this


SELECT JobId,DATEADD(dd,CASE WHEN Seq % 2 <> 0 THEN 1 ELSE 2 END,JobDate)
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY JobDate) AS Seq
FROM Table
WHERE JobId > 0
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

KCSNirav
Starting Member

4 Posts

Posted - 2013-04-12 : 04:59:49

Hello All,

Here I explain my requirement more deeply in below few steps. I have no idea my last date so I am not pass in where condition (Answer of MIK_2008),

JobId JobDate
353 5/1/2013
354 5/2/2013
-1 5/3/2013
355 5/4/2013
356 5/6/2013
-1 5/7/2013

I need last record which has date diff less than 2 so my output generate shown in below (here 5/6/2013 and 5/4/2013 date diff is 2 so neglect all the data after 5/4/2013 date and fetch till 5/4/2013 date,

JobId JobDate
353 5/1/2013
354 5/2/2013
-1 5/3/2013
355 5/4/2013

After that insert these tables output into temp table. Now move all data one by one in next date but here I do not need to move (Where JobID = -1) in next date those data which have JobID = -1 skip moving logic.

If 5/5/2013 date is not empty than this logic execute up to 5/8/2013 or n level of date.

JobId JobDate
353 5/2/2013 (Old date 5/1/2013)
-1 5/3/2013 (Same as old date)
354 5/4/2013 (Old date 5/2/2013)
355 5/5/2013 (Old date 5/4/2013)

Now clear my requirement to all??

Thanks & Regards,
Nirav

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 05:17:30

If 5/5/2013 date is not empty than this logic execute up to 5/8/2013 or n level of date

what determines it should continue upto 5/8/2013 date?

Also how do you determine the days to be added to each of the dates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

KCSNirav
Starting Member

4 Posts

Posted - 2013-04-12 : 05:55:53

Hello visakh16,

Suppose my table list view is like,

JobID JobDate
300 01/Mar/2013
301 02/Mar/2013
-1 03/Mar/2013
303 04/Mar/2013
304 05/Mar/2013
305 06/Mar/2013
306 09/Mar/2013
307 10/Mar/2013

Here 07/Mar/2013 and 08/Mar/2013 is not in table. so here we identify that move down process follow up to 07/Mar/2013

so require out put is,

JobID JobDate
201 01/Mar/2013
300 02/Mar/2013
301 04/Mar/2013
-1 03/Mar/2013 (Skip in any case) (Do not change date)
303 05/Mar/2013
304 06/Mar/2013
305 07/Mar/2013 (Not affect after this record)
306 09/Mar/2013
307 10/Mar/2013

Thanks,
Nirav

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 07:13:49

Hmm...now your JobID also changed? how did that happen?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

KCSNirav
Starting Member

4 Posts

Posted - 2013-04-12 : 07:21:44

Sorry for that updated JobID. By mistake from my side JobID written same time in table output.

Thanks,
Nirav

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources