| Author |
 Topic  |
|
asp__developer
Yak Posting Veteran
74 Posts |
 Posted - 10/03/2012 : 11:45:55
|
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
47173 Posts |
Posted - 10/03/2012 : 12:05:32
|
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 |
 |
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/03/2012 : 12:21:18
|
I would do this:
DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN Start AND END |
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 12:24:40
|
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.
|
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 12:29:56
|
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 ? |
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/03/2012 : 12:37:52
|
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. |
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 12:43:38
|
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 ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/03/2012 : 12:45:02
|
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/
|
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 13:00:00
|
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 ?
 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/03/2012 : 13:05:49
|
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/
|
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/03/2012 : 13:12:15
|
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. |
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 13:15:07
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/03/2012 : 13:19:50
|
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/
|
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 13:23:20
|
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 |
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 13:24:42
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/03/2012 : 13:30:22
|
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/
|
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 13:37:16
|
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 ? |
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 13:57:35
|
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 ? |
|
|
|
asp__developer
Yak Posting Veteran
74 Posts |
Posted - 10/03/2012 : 15:40:28
|
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 |
|
|
|
lazerath
Constraint Violating Yak Guru
USA
278 Posts |
Posted - 10/03/2012 : 16:42:19
|
| What have you tried? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/03/2012 : 17:32:01
|
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/
|
|
|
|
Topic |
|
How to display START date and END date
