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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to display START date and END date
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  11:45:55  Show Profile  Reply with Quote
I need help with the stored procedure with the following logic.

In my database table I have dates like:

ID....................................Start.............................END

1.......................................08/6/2012....................08/19/2012

2.......................................8/20/2012....................09/02/2012

3.......................................9/3/2012....................09/16/2012

4.......................................9/17/2012....................09/30/2012

I want to display START and END date on my webpage based on this logic from store procedure:

If current Date = Search In range of the database Start and End-2 days range

THEN display that START and END date (of that specific range)

(P.S, if the current date is in those 2 days that we are deducting, then it should give the START and END date period for current date + 2 days)

Edited by - asp__developer on 10/03/2012 12:12:08

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  12:05:32  Show Profile  Reply with Quote
just add a condition like

GETDATE() > = Start
AND GETDATE() <DATEADD(dd,1,END)


sorry didnt get last part of your requirement.
can you explain with a data sample?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 10/03/2012 12:14:35
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/03/2012 :  12:21:18  Show Profile  Reply with Quote
I would do this:

DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN Start AND END
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  12:24:40  Show Profile  Reply with Quote
Sure,

This requirement is for displaying payroll period start and end date values.

The payroll runs on every 2nd friday. So user filling out payroll sheet need to know the current payroll period.

Every 2nd friday is the last day of filling out the payroll sheet.

So based on the current date I came up with the logic that by this I can display the payroll period.

So lets say a user filled out payroll sheet for period
9/17/2012....................09/30/2012

Now Sep 30 is sunday.. so obviously user is filled out payroll sheet on Friday(Sep 28) - which is the last date to fill out the sheet.

So now what if any user is filling out the payroll sheet in advance for next payroll on Sep 29 ? then I need to display the next payroll period (Start and End).

I hope now you understand why I am deducting the 2 days for comparison ?

I hope my example cleared up all the confusion and gave you an idea about the situation and my logic ?

Please let me know if that was confusing, I will try to give an another example.



Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  12:29:56  Show Profile  Reply with Quote
Another example:

I am trying this statement

SELECT StartDt, EndDt
FROM Table_Dt
WHERE (GetDate() BETWEEN StartDt AND EndDt-2)

This gives me the result: (Start:10/1/2012) (End:10/14/2012)

But when I try this

SELECT StartDt, EndDt
FROM Table_Dt
WHERE ('09/30/2012' BETWEEN StartDt AND EndDt-2)

OR

SELECT StartDt, EndDt
FROM Table_Dt
WHERE ('09/29/2012' BETWEEN StartDt AND EndDt-2)

I get NULL because those 2 days are getting lost... I hope you got my point ?
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/03/2012 :  12:37:52  Show Profile  Reply with Quote
The code I posted above will meet your needs asp__developer. Let's say it's Friday, Sept 28th, GETDATE() will evaluate to:

2012-09-28 11:29:59.017

We convert it to DATE ONLY using DATEADD(d,DATEDIFF(d,0,[This is the input date]),0). What this does is find the difference in days between 0 (baseline date) and the entered date, in this case GETDATE(). We then add that number of days back to 0, which will result in:

2012-09-28 00:00:00.000

However, in that expression is also +2, which means the date will be incremented by an additional 2 days. Thus, the date for evaluation is:

2012-09-30 00:00:00.000

The BETWEEN statement is inclusive, so it will find Pay Period #4. However, if we were working with a date time value from Saturday:

2012-09-29 18:32:01.997

The time will be truncated and the date will be incremented by 2, so it will now match the Pay Period #5 from 10/1 to 10/14.

By doing the expressions exclusively on GETDATE() and NOT on START and END (as visakh16 posted), you are allowing those columns to be SARGable, which gives SQL Server the option to use indexes on those fields if they exist. If you wrap a column in an expression, SQL cannot make proper use of an index.

For this query, I'd create an index on START, END for efficiency.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  12:43:38  Show Profile  Reply with Quote
Hello lazerath,

I hope you remember our conversation here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179395

I came up with this logic in order to avoid all that stuff, what do you think - this is a good easy logic or solution for the payroll situation ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  12:45:02  Show Profile  Reply with Quote

SELECT CASE WHEN DATEDIFF(dd,0,GETDATE())%7 <=4 THEN t.Start ELSE t1.Start END AS Start,
CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 <= 4 THEN t.[End] ELSE t1.[End] END AS [End]
FROM YourTable t
OUTER APPLY (SELECT  MIN(Start) AS Start,MIN([End]) AS [End]
             FROM Yourtable
             WHERE Start > t.Start
            )t1
WHERE GETDATE() >= t.Start
AND GETDATE() < DATEADD(dd,1,t.[End])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  13:00:00  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


SELECT CASE WHEN DATEDIFF(dd,0,GETDATE())%7 <=4 THEN t.Start ELSE t1.Start END AS Start,
CASE WHEN DATEDIFF(dd,0,GETDATE()) % 7 <= 4 THEN t.[End] ELSE t1.[End] END AS [End]
FROM YourTable t
OUTER APPLY (SELECT  MIN(Start) AS Start,MIN([End]) AS [End]
             FROM Yourtable
             WHERE Start > t.Start
            )t1
WHERE GETDATE() >= t.Start
AND GETDATE() < DATEADD(dd,1,t.[End])


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Great it seems to be working, why I am getting this error whenever I try to run the command ? even tho the result is correct ?


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  13:05:49  Show Profile  Reply with Quote
where are you trying it? It wont throw the error if you use it in default SSMS editor. Not sure whether same is the case with the query panes for view editor and SSRS dataset editor though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/03/2012 :  13:12:15  Show Profile  Reply with Quote
Yes, I remember you from that thread. I do agree this is a better route.

Did you try this?

SELECT t.Start, t.End
FROM YourTable t
WHERE DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN t.Start AND t.END


It should give you the same result and it's far simpler and faster.
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  13:15:07  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

where are you trying it? It wont throw the error if you use it in default SSMS editor. Not sure whether same is the case with the query panes for view editor and SSRS dataset editor though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





May be because I am trying to run the command in Visual Studio.

1) How can I have 2 variable in this statement that stores the final result of Start and End Date ? Like @StartD and @EndD so that I can use the values in those my code ?

2) Since we are getting the results based on current date, I am assuming that the output result payroll time will change to next payroll period on 28 12am (night), right ?

Edited by - asp__developer on 10/03/2012 13:18:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  13:19:50  Show Profile  Reply with Quote
command in Visual studio? do you mean reporting services?

for 2 yes it will change automatically for each payroll period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  13:23:20  Show Profile  Reply with Quote
quote:
Originally posted by lazerath

Yes, I remember you from that thread. I do agree this is a better route.

Did you try this?

SELECT t.Start, t.End
FROM YourTable t
WHERE DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN t.Start AND t.END


It should give you the same result and it's far simpler and faster.



Yes I agree, I am getting the same result and it is much simpler

I also tested the statement like this:

SELECT t.Start, t.End
FROM YourTable t
WHERE DATEADD(d,DATEDIFF(d,0,'9/29/2012 12:01:00 AM')+2,0) BETWEEN t.Start AND t.END
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  13:24:42  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

command in Visual studio? do you mean reporting services?

for 2 yes it will change automatically for each payroll period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





There is a query feature in visual studio where we can test SQL command for SQL database.. but nevermind.. it is working fine.

Could anyone answer these questions ?

1) How can I have 2 variable in this statement that stores the final result of Start and End Date ? Like @StartD and @EndD so that I can use the values in those my code ? Because on my webpage, I want to display the outcome from the SQL statement as start date and end date, so for that I need to define in the code, what values from SP = my variable defined in coding of page...

2) Since we are getting the results based on current date, I am assuming that the output result payroll time will change to next payroll period on 28 12am (night), right ?

Edited by - asp__developer on 10/03/2012 13:25:56
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  13:30:22  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

quote:
Originally posted by visakh16

command in Visual studio? do you mean reporting services?

for 2 yes it will change automatically for each payroll period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





There is a query feature in visual studio where we can test SQL command for SQL database.. but nevermind.. it is working fine.

Could anyone answer these questions ?

1) How can I have 2 variable in this statement that stores the final result of Start and End Date ? Like @StartD and @EndD so that I can use the values in those my code ? Because on my webpage, I want to display the outcome from the SQL statement as start date and end date, so for that I need to define in the code, what values from SP = my variable defined in coding of page...
load a recordset in webpage with result of this select and you can extract individual fields from recordset
2) Since we are getting the results based on current date, I am assuming that the output result payroll time will change to next payroll period on 28 12am (night), right ?
yep it will as suggested before



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  13:37:16  Show Profile  Reply with Quote
quote:

2) Since we are getting the results based on current date, I am assuming that the output result payroll time will change to next payroll period on 28 12am (night), right ?
yep it will as suggested before



------------------------------------------------------------------------------------------------------
[/quote]

In that case, is it possible to do this based on time also along with the date ? Like right now on SEP 28 11:59 pm, it will give the current pay period and at SEP 29 12:01 am, it will give next pay period.

Is it possible to give the next payroll period after specific time of current payroll period ? For example,

SEP 28 11:00 AM, it should give current pay period and at SEP 28 11:01AM onwards it should give next pay period ?

I know for this I need to have time saved with the dates, but just curious and want to learn, how to do this ?
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  13:57:35  Show Profile  Reply with Quote
Also, can you please help me with this:

What if I am displaying current payroll period, what would be a SQL command to show next 5 payroll periods based on current date and same logic ?
Go to Top of Page

asp__developer
Posting Yak Master

108 Posts

Posted - 10/03/2012 :  15:40:28  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

Also, can you please help me with this:

What if I am displaying current payroll period, what would be a SQL command to show next 5 payroll periods based on current date and same logic ?



Anyone ? Please
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
328 Posts

Posted - 10/03/2012 :  16:42:19  Show Profile  Reply with Quote
What have you tried?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/03/2012 :  17:32:01  Show Profile  Reply with Quote
quote:
Originally posted by asp__developer

quote:
Originally posted by asp__developer

Also, can you please help me with this:

What if I am displaying current payroll period, what would be a SQL command to show next 5 payroll periods based on current date and same logic ?



Anyone ? Please


Use a CTE to loop through and add 2 weeks each time to get next payroll start and end dates

see similar logic used here for generating week dates

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.12 seconds. Powered By: Snitz Forums 2000