Finding individuel dates having date+2 days..

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

Transact-SQL (2008)

Finding individuel dates having date+2 days..

Author

Topic

avols143
Starting Member

1 Post

Posted - 2011-01-26 : 14:51:50

Hi,

I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.

Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.

If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.

seq date
------------------------------
1 01.01.10

2 05.01.10
3 06.01.10

4 10.01.10
5 12.01.10

6 13.01.10
7 14.01.10
8 15.01.10

9 16.01.10
10 18.01.10

11 19.01.10
12 20.01.10
13 21.01.10

14 23.01.10

15 26.01.10
16 27.01.10

17 29.01.10
18 31.01.10

The result should be (Don't use Pl/Sql)

seq date
------------------------------
1 01.01.10
2 05.01.10
3 10.01.10
4 13.01.10
5 16.01.10
6 19.01.10
7 23.01.10
8 26.01.10
9 29.01.10

After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).

I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.

You people can have another best idea to do this.

Thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-26 : 16:20:03

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats, not the local dialect you posted. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Don't worry aobut us using PL/SQL -- This not an Oracle forum.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources