Author |
Topic |
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 11:45:55
|
I need help with the stored procedure with the following logic.In my database table I have dates like:ID....................................Start.............................END1.......................................08/6/2012....................08/19/20122.......................................8/20/2012....................09/02/20123.......................................9/3/2012....................09/16/20124.......................................9/17/2012....................09/30/2012I 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 rangeTHEN 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) |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 12:05:32
|
just add a condition likeGETDATE() > = StartAND GETDATE() <DATEADD(dd,1,END) sorry didnt get last part of your requirement.can you explain with a data sample?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-03 : 12:21:18
|
I would do this:DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN Start AND END |
|
|
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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 period9/17/2012....................09/30/2012Now 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
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 12:29:56
|
Another example:I am trying this statement SELECT StartDt, EndDtFROM Table_DtWHERE (GetDate() BETWEEN StartDt AND EndDt-2)This gives me the result: (Start:10/1/2012) (End:10/14/2012)But when I try thisSELECT StartDt, EndDtFROM Table_DtWHERE ('09/30/2012' BETWEEN StartDt AND EndDt-2)ORSELECT StartDt, EndDtFROM Table_DtWHERE ('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
343 Posts |
Posted - 2012-10-03 : 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.000However, 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.000The 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.997The 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
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 12:43:38
|
Hello lazerath,I hope you remember our conversation here: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179395[/url]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
52326 Posts |
Posted - 2012-10-03 : 12:45:02
|
[code]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 tOUTER APPLY (SELECT MIN(Start) AS Start,MIN([End]) AS [End] FROM Yourtable WHERE Start > t.Start )t1WHERE GETDATE() >= t.StartAND GETDATE() < DATEADD(dd,1,t.[End])[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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 tOUTER APPLY (SELECT MIN(Start) AS Start,MIN([End]) AS [End] FROM Yourtable WHERE Start > t.Start )t1WHERE GETDATE() >= t.StartAND GETDATE() < DATEADD(dd,1,t.[End]) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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
52326 Posts |
Posted - 2012-10-03 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-03 : 13:12:15
|
Yes, I remember you from that thread. I do agree this is a better route.Did you try this?[CODE]SELECT t.Start, t.EndFROM YourTable tWHERE DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN t.Start AND t.END[/CODE]It should give you the same result and it's far simpler and faster. |
|
|
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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 MVPhttp://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 ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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?[CODE]SELECT t.Start, t.EndFROM YourTable tWHERE DATEADD(d,DATEDIFF(d,0,GETDATE())+2,0) BETWEEN t.Start AND t.END[/CODE]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 simplerI also tested the statement like this:SELECT t.Start, t.EndFROM YourTable tWHERE DATEADD(d,DATEDIFF(d,0,'9/29/2012 12:01:00 AM')+2,0) BETWEEN t.Start AND t.END |
|
|
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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 MVPhttp://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 ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 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 MVPhttp://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 recordset2) 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 MVPhttp://visakhm.blogspot.com/ |
|
|
asp__developer
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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
Posting Yak Master
108 Posts |
Posted - 2012-10-03 : 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
343 Posts |
Posted - 2012-10-03 : 16:42:19
|
What have you tried? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-03 : 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 datessee similar logic used here for generating week dateshttp://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Previous Page&nsp;
Next Page
|