One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.
I have two datetime fields that I would like to find out how much time has passed between them. First field is "start date" and the second is "end date" the dates in both fields are in this format (03/27/2008 4:00PM). The problem I am having is I need to exclude the time passed from Friday, 6:00PM to Monday, 7:00AM if the dates happen to go over a weekend.
Im using the DateAdd Function to establish a future date base on the required time for a series of events to transpire. I'd like to exclude weekends, does anyone know a way to do this?
I'm trying to write an algorithm that returns the most recent and longest consecutive streak of positive or negative price changes in a given stock. The streak can extend over null weekends, but not over null weekdays (presumably trading days).
For example, lets say Google had end of day positive returns on (any given) Tuesday, Monday, and previous Friday, but then Thursday, it had negative returns. That would be a 3 day streak between Friday and Tuesday. Also, if a date has a null value on a date that is NOT a weekend, the streak ends.
In the following code sample, you can get a simplified idea of what the raw data will look like and what the output should look like.
set nocount on set datefirst 7 go if object_id('tempdb.dbo.#raw') is not null drop table #raw create table #raw
[Code] .....
I should also mention that this has to be done over about half a million symbols so something RBAR is especially unappealing.
I have a table with a list of jobs along with their start and end datetime values.
I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.
Example
Start Date=2014-05-15 12:00:00.000 End Date=2014-05-19 13:00:00.000
Total Time should be: 2 Days, 1 Hour and 0 Minutes
Iam trying to calculate the number of working days between two dates. Iam getting the uouput as only 1 02 r working days??
select building_number as SchoolID,building_name as Campus, count( distinct( CASE WHEN(( DATEPART(dw, CurDate) + @@DATEFIRST)%7 NOT IN (0,1)) tHEN 1 ELSE 0 END)) as NumberofDaysServed from Sales sl join Buildings b on sl.Building_Num =b.Building_number join students2 s on s.Student_Number= sl.Student_Num join Sale_Items SI on si.UID = sl.UID where CONVERT(CHAR(10),CurDate,120) between '2015-05-01' and '2015-05-07' and VoidReview <> 'v' and SI.INum = '1' group by building_number,building_name order by building_number,Building_Name;
I'm having a problem writing a SQL query that excludes certain data. This is for a pay stub application to display current and previous paycheck stubs. To calculate certain data such as YTD figures and time off, we SUM on other tables. However, to display correctly, I can't SUM bonus checks for the current payperiod ONLY - but for previous pay periods, I must SUM bonus checks.
Here's an example of my data:
No code has to be inserted here. No code has to be inserted here.
No code has to be inserted here. No code has to be inserted here.
Right now my SQL is this:
Code: SELECT PR04PTF.PayCheckNo, SUM(PR11ERF_History.PayCheckAmt) AS [TotalSum] FROM PR04PTF
INNER JOIN PR11ERF_History ON PR11ERF_History.EmployeeID = PR04PTF.EmployeeID AND PR11ERF_History.PayPeriodEnd <= PR04PTF.PayPeriodEnd
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
Here's something that I need to do. Might be pretty simple for you guys. :)
I have a table of Employees. All the employees work in some departments. So, I have a table of Department too. Employee table consists of details like EmpID, FirstName, LastName, SAP etc. Dept table consists of TeamID, TeamNo. Now, I have another table called as Emp-Team. This table basically maps the employees to the department by taking EmpID and TeamID. There's one more column in this table which is date. This date is required because when some person resigns (say today) then he won't feature in the headcount for July 08 but till June 08 he was there and this is how I maintain my history. e.g All the employees in the Emp_Team table have date as 01/06/2008 for this month. So, in future if I query for the employees who worked in June I will get this list. Now, I want to copy all this data in the same table again and want to remove any people who have resigned. Their resignation status is in the Employee table, where you have their last working date as well. So, when I add all this data with date 01/07/2008 I want to remove any employees whose last working date is before that.
Can this be done or I have to change my design? In case it can be - How?
I am getting two dates each of which I calculate using a subquery (joining 2 or 3 tables) Then I perform datediff on these two dates to get a interger values (No of Days)
I need to substract the weekends from this No of days How do I accomplish this
I want go get all weekends of the year (year dynamic) with out CTE concept, because I need to implement in 2005 version.I have googled but getting only CTE examples.
I need to extract records for the last day of previous months (Up till January of the same year) from a table SALES, according to a date parameter ASOFDATE that the user enters.
For Example
If user keys in ASOFDATE as 10-May-2014, I would have
The below code works fine to measure the difference in days between two dates. However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. Any ideas on how to accomplish this task, and leverage the below, existing code? Thanks in advance! (SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "
I'm trying to only show yesterday's data based on a date that is stored as a varchar. I converted it to smalldatetime but I'm getting an error that says "Conversion failed when converting date and/or time from character string." I don't know how I have to alter the conversion.
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
I am trying to implement Next and Previous buttons on a web page that uses CE as the database, and having some trouble getting the logic figured out. The data is unbound and I'm not returning it all at once, so paging through it an item at a time isn't possible. Because of the high volume, I was thinking I could pass in the record ID each time I need to retrieve a record and every time the user hits the next or previous button, I'll just requery for the record I need. Does that sound reasonable?
In my main page, I'm using the ViewState to store the record ID that I need to acquire. This part works--it gives me the highest ID number (so that I get the most recent record):
if (!IsPostBack) { // Start out with the most recent record in the selected category. GetHighestConfessionID() will return // the ID of the most recent confession. ViewState ["ConfessionID"] = mySearch.GetHighestConfessionID (int.Parse(ViewState["Category"].ToString())); }
Here's where the trouble comes in--I need to go to the next record in the database when the user hits Next, but I don't know how to do that. My big idea was to issue this command in the Next button:
I have three coloumn in Salary Table Emp ID, Emp Salary , Sequence a1 1000 1 a1 2000 2 a1 2000 3 a2 4000 1 a2 5000 2 a2 5000 3 a2 6000 4
Now I have to calculate the count on salary if the previous salary is different then count +1 else is previous salary same then add +0. so output be EmpID and Updation in Salary a1 = 2 and for a2 =3
Can anyone help me with the query or storeprocedure i can achieve this output counting according to previous data.
Can we show the data of a report for previous month? The report is supposed to run montly basis. But once it is run, it shows the data only for the previous month.
we can show it if its only for previous day. In this case it is like this;
cdtable.SubmittedDate = GETDATE () - 1
But I dont find function like GETMONTH() or smthing.
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths AS ( SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate FROM ( VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
Then I want to run a select in the users table, only with the users that hasn't records in the second table.
In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.
How do I tranfer a previous table and its data to the ASPNETDB.MDF in the Web Site Administration tool. I've tried right, but I can't locate the database in the SQL server on my server. help please.
We have a need to report on historical data when none exists in the database.
Create a tabular report rdl that is run on a regular schedule. This report run is saved as an Excel sheet that overwrites the previous run, which has the same name.
Report is designed to use two data sources--the database and and the previously run Excel sheet. The data is "merged" using the Lookup function thereby creating a new report that includes the history needed.
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!
Here's the basic idea:
SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept FROM Server.Database1, Server.Database2
I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).
I've tried:
WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))
(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)
What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.
The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.
I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.
This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.
The script basically maps ALL products to customer levels. The Delete statement at the start is needed to make sure that any deleted products or temporary mapped products are removed once a week. However I want to map some products to CustomerID's 9,10 and 14 only. But when I run this statement it maps all products to all customer levels. What I want is thisAll products mapped to customer levels 9,10 and 14Then every other product mapped to customer levels 0,5,7, and 8 but not those extra ones in 9,10 and 14.
DELETE FROM ProductCustomerLevel WHERE CustomerLevelID IN (0, 5, 7, 8, 9, 10, 14) INSERT ProductCustomerLevel ( ProductID, CustomerLevelID
I would like to exclude any parameter that is empty from the SELECT command? How do I do this? This is part of a stored procedure.
SELECT PersonID FROM Persons WHERE (FirstName = @firstname) AND (LastName = @lastname) AND (SSN = @ssn) AND (AddressID = @addressid) AND (DOB = @dob) AND (Middle = @middle)
