SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date diff result fetch from table output
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KCSNirav
Starting Member

4 Posts

Posted - 04/11/2013 :  09:42:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/11/2013 :  11:47:56  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/12/2013 :  04:41:57  Show Profile  Reply with Quote
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
Go to Top of Page

KCSNirav
Starting Member

4 Posts

Posted - 04/12/2013 :  04:59:49  Show Profile  Reply with Quote
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

Edited by - KCSNirav on 04/12/2013 05:02:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/12/2013 :  05:17:30  Show Profile  Reply with Quote
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
Go to Top of Page

KCSNirav
Starting Member

4 Posts

Posted - 04/12/2013 :  05:55:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/12/2013 :  07:13:49  Show Profile  Reply with Quote
Hmm...now your JobID also changed? how did that happen?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

KCSNirav
Starting Member

4 Posts

Posted - 04/12/2013 :  07:21:44  Show Profile  Reply with Quote





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

Thanks,
Nirav
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000