Removing Weekends??

Oct 24, 2006

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?

View 6 Replies


ADVERTISEMENT

Substract Weekends

Oct 22, 2007



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

View 10 Replies View Related

Data From Previous Day Excluding Weekends

Jan 23, 2014

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

View 5 Replies View Related

Excluding Weekends When Finding The (dd/hh/mm/ss) Between Two Dates

Mar 27, 2008

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

View 6 Replies View Related

Transact SQL :: Get Date Of All Weekends Of The Year Without CTE

Sep 1, 2015

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.

View 3 Replies View Related

DateAdd Function - Excluding Weekends

Jan 4, 2008

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

View 1 Replies View Related

Date Difference Measurement And Weekends / Holidays

Jun 4, 2008

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 "

View 2 Replies View Related

T-SQL (SS2K8) :: Consecutive Streak Excluding Weekends

Aug 8, 2014

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.

View 5 Replies View Related

SQL Server 2012 :: Difference Between Two Dates (Excluding Weekends)

May 19, 2014

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

View 5 Replies View Related

Transact SQL :: Calculate Working Days Excluding Weekends

Jun 7, 2015

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;

View 8 Replies View Related

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

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.

View 10 Replies View Related

SQL Server 2012 :: Calculate Number Of Days From A Date - Exclude Weekends And Holidays

Feb 2, 2014

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] ......

View 4 Replies View Related

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

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.

View 1 Replies View Related

Removing *

Aug 18, 2005

Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?

Thanks for your help.

Scott

View 6 Replies View Related

Removing Old Bak

Jan 15, 2008

Started with maintenance cleanup task set up bak and directory did not delete the files.

Then found this http://blog.solidsoft.com/blogs/richards_infrastructure_blog/archive/2007/07/12/369.aspx

Copy script

use master
declare @dt datetime
select @dt=getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N'D:BackupAcctDB',N'BAK',@dt

Get message

Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'

Anyone else get problem with removing BAK files

View 4 Replies View Related

Removing Padding From Var

Feb 7, 2008

I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
 EG: 000000123  would be entered as 123.
 But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123
 

View 2 Replies View Related

Removing A Default...

Sep 4, 2002

Hi,

I'm using SQL Server 7.0 SP3. I added a column to a table like so:

ALTER TABLE T704_RELATIONSHIP_INDEX_CLIENT_REPORT_STORE
ADD Universe_Member_Set_cd INT NOT NULL DEFAULT 1

What is the syntax for dropping the 'DEFAULT' from this column once it has been created? I've looked in BOL but can't seem to get the syntax correct.

Thanks in advance,
Darrin

View 1 Replies View Related

Removing The Duplicates

Aug 2, 2000

I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.

I need a query to do this.

Can anybody help.

-Rajesh

View 3 Replies View Related

Removing BuiltinAdministrators

Mar 20, 2002

Hi all,

I am using SQL 2k SP2 on Win 2K Advance server Cluster. My problem is how to remove builtinAdministrators login. Earlier when I tried it was doing failovers from one node to another and not stopping. I was not able to do anything. Any help is appriciated

thanks,
Minesh.

View 3 Replies View Related

Removing DB Suspect

Aug 31, 2000

I wonder if one can give a hint on remove a DB suspect,
Mind you I have used SP_dbremove still did not remove the DB
This is on SQL 7.0 box

Any idea welcomed

Saad

View 2 Replies View Related

Removing A Filegroup

Apr 25, 2000

Hello,

A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened.
To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup.
How can I get rid of this filegroup?

Stef

View 2 Replies View Related

Removing Transaction Log From SQL 7 DB

Apr 7, 1999

I am trying to remove a transaction log that has grown too large for its databse and server. I have truncated the log and have run dbcc shrinkfile with the EmptyFile Option, but when I try to use alter database remove file and am I told that the object is not empty and cannot be removed. The server is in dbo use only.

What am I missing?

View 1 Replies View Related

Removing Filegroup

Mar 8, 2001

Hi all,

I am trying to remove one of the filegroups in the development database to shrink its size down. I started with emptying and removing its files and ran:
dbcc shrinkfile
(pubs_data_1,emptyfile)
go

and it ran fine, with 'DBCC execution completed' message

but when I tried :

alter database pubs
remove file pubs_data_1
go

I got 'pubs_data_1 cannot be removed because it is not empty'.
I even tried to go to each of the tables and delete from them first which didn't work, then drop all tables in that filegroup (I knew which tables are located on that filegroup), that didn't work either.

Any ideas? Is there a way to check what else sits on that filegroup?

Thanks

View 2 Replies View Related

Removing Replication

May 8, 2003

Hi,

We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.

How can I completely remove this whole replication from publisher and distributor?

Please help.

Thanks,
Shaili

View 5 Replies View Related

Removing SQL 6.5 Software

May 28, 1999

Dear Concerned DBAs,

I am wanting to pick someones brain and find out if there is anything I can do to successfully remove and reinstall SQL Server Version 6.5? I have already removed the software from the SQL Setup icon in ProgramsMSSQL Server 6.5 and went and removed the setup.exe and mssql files associated with the software from the server. Now when I attempt to reinstall the software back onto the server after a hard reboot, I do not get the option once the SQL Server Version 6.5 disk is placed into the CD-Rom to install SQL Server. The only options I get are upgrade, server configurations, etc.

Can anyone or everyone give me a hand as to where to take this dilemma other than rebuilding the server from the ground up?

Anticipatorily Yours,
Daimon Russell

View 3 Replies View Related

Removing Certain Characters

Aug 18, 2006

Hello all,

I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.

Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial

Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial

View 5 Replies View Related

Removing Duplicates

Jan 27, 2005

I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.

It doesnt matter which duplicate i remove as long as there are unique email addresses.

so here is the format of the table:

id
email
firstname
lastname
datebirth

if i do a:

SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer

it returns 21345

and

SELECT Count(Email)
FROM Customer

returns 28987

I can get the unique email addresses into another table by going:

SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer

but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?

Thanks in advance

Tom the SQL beginner

View 5 Replies View Related

Removing A Paratiotion

Jun 24, 2008

i want to build a paratiotin which the key of the paratition will be a datre in this structure :
20080101235959
20080102235959
20080103235959
20080104235959
20080105235959
and so on.....
and i want that when there is a paration that it's "date" is more then 30 days to delete it (and that its data will be deleted too)
how do i do that?
thnaks in advance
peleg





Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

View 5 Replies View Related

Removing Primary Key

Nov 27, 2005

Hi How do i remove the primary key for this table


create table table2
(col1 int,
col2 int,
primary key(col2))


Thanks
Vic

View 2 Replies View Related

Removing Last Two Char

Apr 25, 2007

Afternoon all

What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.

cheers

View 4 Replies View Related

Removing Data

Jul 23, 2005

I have a table that I need to delete some data from and put the deleteddata into a different table.How do I script the following.If Field1 in Table1 is null, remove that row from Table1 and put it ina new table called Table2Regards,Ciarán

View 5 Replies View Related

Removing Duplicates

Jul 20, 2005

HiI have inherited a web app with the following table structure, and need toproduce a table without any duplicates. Email seems like the best uniqueidentifier - so only one of each e-mail address should be in the table.Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able toget a duplicate count working:select Email, count(*) as UserCountfrom dbo.Membersgroup by Emailhaving count(*) > 1order by UserCount descBut the methods for create a new table without duplicates fail. My code forthe 2nd method is:sp_rename 'Members', 'temp_Members'select distinct *into Membersfrom temp_MembersTable....CREATE TABLE [dbo].[Members] ([MemberID] [int] IDENTITY (1, 1) NOT NULL ,[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,[DateAdded] [smalldatetime] NOT NULL ,[SendMail] [smallint] NOT NULL) ON [PRIMARY]GOThanks B.

View 2 Replies View Related

Removing Zeros

Sep 24, 2007

Hi,
I have a column cost with sample record as '00003433' . how can i remove all zeros in left side alone?.

Out put : 3433.

Thanks
Raj

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved