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
Thanks
sowree
Hi,
to keep things short, I have a query like this : SELECT A AS A, SUM(B) AS B FROM CC WHERE A IN (1,2,3) GROUP BY A
I want to be able to SUM the field B when A is equal to 1, and substract the field B when A is equal to 2 or 3. So I know SUM is incorrect there, cuz sometimes the field B must be added, and sometimes it must be substracted. What's the easiest way to do it?
For example, if the fields B in the database has the values 3, 5, and 7, the query would go something like 3+5-7, giving 1 for B at the end.
Tanx for your help guyz!
Hi
I'm having a bit troubble creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:
Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100
What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.
How do I do that?
Thanks
I need to subtract the values of the totals of two document types in a table. So far I have the following with the results as shown.
SELECT TOP 100 PERCENT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE (DOCID = 'RTD') OR
(DOCID = 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC
This results in the following:
Month DOCID TOTAL
MAY RTD 165752.87
MAY INV 18149416.37
I need one value showing the difference of the totals.
Thanks.
how do I gradually add o substract numbers in a column for example if I have
total
deposito
test
120
120
80
40
77
117
7
124
4
120
i need in the test column:
if the number is in the total column the number in test is added
if the number is in the deposito column the number in test is substracted
i have this query:
SELECT gir_cantidad as total,null as deposito,'operation' as test
FROM giros inner join corresponsales on cor_corresponsal_id = gir_corresponsal_id where gir_fecha >= '11/21/2007' and gir_fecha <= '11/22/2007 23:59:59' and gir_fecha_anul is null and gir_agencia_id = 1
UNION
select null as total,paa_valor as deposito,'operation' as test from pagosagencia where paa_fecha >= '11/21/2007' and paa_fecha <= '11/22/2007 23:59:59' and paa_agencia_id= 1 order by gir_fecha
In column test i need the functionality to add and subtract the columns total and deposito
Please help
Hi, i'm new to the SQL game and have been given the task of removing all the weekends in a report so as it only shows the weeks as mon-fri.
I've checked out a few pieces of code but can't seem to get it to work. Anyone able to help?
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.
select distinct
clm_id1, clm_rcvd, clm_6a, clm_6b, clm_dout, clm_cc1, clm_clir, clm_65a, clm_5, clm_1a, clm_1a1, clm_1a2, clm_1b, clm_1d, clm_1e, clm_1f, clm_tchg, clm_nego, clm_sppo, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clme_fild
from
impact.dbo.clm
left join impact.dbo.clme on clm_id1 = clme_id
where
clm_dout = getdate()-1
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.
Any help would be greatly appreciated.
Thanks
JP
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.
Query to get the weekends in a year.
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?
Thanks in advance
Alex
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 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;
Here I have 2 Dates. CreatedDttm & ModifiedDttm.
I want - DATEDIFF(Day,CreatedDttm,ModifiedDttm) and I have to exclude the Weekend days from that query result.
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.
[Code] ......
Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:
Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100
What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.
How do I do that?
Thanks
Morten
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.