Minus The Weekend

Jan 18, 2005

If I was asked "How many days passed" the query is (below) and the answer is 33. But, then the curve ball is "can you subtract out the weekends?" and I said....well, I said I think so, but not sure how. So far, I have had no luck. Any advise?



SELECT
DATEDIFF ( dd , dbo.table_case.creation_time , dbo.table_close_case.close_date ) AS no_of_days,
dbo.table_case.id_number,
dbo.table_case.creation_time CreateTime,
dbo.table_close_case.close_date CloseDate

FROM
dbo.table_case,
dbo.table_close_case

WHERE
dbo.table_close_case.last_close2case=dbo.table_case.objid AND
dbo.table_case.id_number = '969382'

ORDER BY no_of_days ASC

View 2 Replies


ADVERTISEMENT

Weekend Dates

Jun 24, 2008

I was wondering could someone help me. i have two dates a start_date and an end_date i need to work out if a weekend falls with in the dates. can this be done or is there an easy way of working it out

View 7 Replies View Related

Date / Time Query To Account For Weekend

Jun 9, 2014

I am looking to create a query to select data to accomodate the weekend. For example, If today is Monday, then select Friday's records. This only has to be done for Monday's. For each additional day there would be a date difference of just 1 not 3. I hard code the date diff when Monday rolls around. how to make that specific so the CT.workeddate = what I have in my Where clause.

SELECT CT.ContactId, CT.OutreachId, CT.SchedulerId, CT.WorkedDate, CT.OutreachStatusId, CT.UpdateBy, C.CampaignID, C.PlanID, C.ProgramId, M.MarketID,
M.MarketStateName, CT.AppDate, DATENAME(dw, CT.WorkedDate) AS DayofWeek, DATEADD(Day, CASE DATENAME(WEEKDAY, GETDATE())

[code]....

View 5 Replies View Related

Swapping Out Drives On Our Production SQL Server This Weekend

Jul 20, 2005

All,To make a long story short, we are swapping out the "knock-off" drivesthat the NA purchased on E-Bay in one of our production SQL Servers(SQL Server 2000 Enterprise) this weekend for brand new ones (Compaq15K RPM 32GB drives). We are currently experiencing ASR almost on adaily basis and it is really causing a disription in service. SO, TheNetwork Admin ahs made this decison to replace these drives in attemptto solve this. These new drives will be imaged with ALL of the currentdata on the "knock-off' drives and will be plugged back in to thisdatabase server and brought back up .This Server also happens tocurrently be a subscriber in Merge Replication as well. Besidesstopping replication to this subscriber is there any other tasks thatI need to do or concerns that I need to be knowledgable about or lookfor when we bring this database server backup on line this weekend?Thanks Travis. :)

View 1 Replies View Related

Transact SQL :: How To Find Weekend Days Per Month

May 13, 2015

I want to find the first weekend day, second weekend day, third weekend day and fourth weekend day per month using sql query. This is getting from recurring appointment. If weekdays = 65 means it accepts only saturday and sunday. So I want first, second, third and fourth weekend days for a month using query in sql server 2008...

View 17 Replies View Related

SqlServer Weekend Job Blocked By A User Left Open Screen

Jul 20, 2005

Hello!We have a maintenance job on MSSQL server scheduled for the weekend.some times we arrive at the beginning of a week and see that a user lefthis screen open on a SQL record and the job is still waiting blocked bythat user. Please give me an idea how to overcome this.Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

T-SQL (SS2K8) :: Selecting Data By Date For Last Five Days AND Avoiding Weekend Dates

Apr 16, 2014

What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:

DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE

[Code]....

The query works with the following exceptions:

My issues are:

1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.

2. Avoid weekend dates. I need the query to display only weekdays

3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.

View 6 Replies View Related

Minus Sign ()

Jun 2, 2007

Hello:



I need to change minus brackets () with - sign in SSRS. How i can do this?



Thanks

Amit

View 11 Replies View Related

SELECT * Minus A Field

Jul 26, 2007

Hi,

I was wondering if anyone knows a command that will select all fields in a table except one?

Thanks!
Lee

View 6 Replies View Related

Minus And Intersect Functionality

Jul 20, 2005

Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth

View 1 Replies View Related

How Can I Replace The Minus-Statement

Mar 23, 2006

Hi,

I've to translate this SQL-Statement from ORACLE to SQL-Server. But I'm missing the Minus-Statement on SQL-Server.
select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
minus
select tab_name from data_dic
)
minus
select tab_name, col_name from data_dic
what can I do to run it on SQL-Server.
Thanks in advance
Raimund


View 4 Replies View Related

Oracle MINUS Equivalent In Sql Server

Apr 22, 2003

Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks

View 11 Replies View Related

SQL Server Express Edition - Plus And Minus

Jul 25, 2005

I would like to get a feed back from people who using Express edition.

What are the Plus and Minus points which you can list agenst Express Edition of MsSQL !

Please list them from your experiance rather than the documentation!

NB:- Specify : OS - Development Platforms - If any installation problems found in your Desk.

Thank you.

Thank you.

View 5 Replies View Related

Import Data Minus Duplicates

Feb 20, 2008

Hi

I am currently trying to import data from a table in 1 database into a table of the same name in another database. This in it's self is simple, however to add a twist to the proceedings there is data that exists in both tables. I just want to import the data that doesn't exist in the table I am importing into.

Please can you advise as to the best method to use

Many thanks

Paul

View 5 Replies View Related

Transact SQL :: Can Add 1 To Charindex Function But Can't Minus 1

Aug 25, 2015

The charindex can run this

LEFT([Description], CHARINDEX('(', [Description]) + 1)  as NewDesc,

But I can't run

LEFT([Description], CHARINDEX('(', [Description]) - 1)  as NewDesc,

View 5 Replies View Related

Add A Minus To Two Column Values If Criteria Met

Mar 7, 2008



Hi I was hoping you could help,


I have a query that pulls back a customers account statement, however the credits are coming back as positives as well, So I am getting wrong figures, how can I correct the code below so that VAT, Goods Value and Total are negative if the data in column "trans reference" begins with a C for example C123456789 a invoice would be 123456789



Thanks








Code Snippet
SELECT
SLCUSA as [Compny Number],
SLCUSB as [Customer Suffix],
SLDELN as [Deliber To Number],
SLCNAM as [Customer Name],
SLDATE as Date,
SLTREF as [Trans Reference],
SLGDSV as [Goods Value],
SLVATV as [VAT],
SLTOTV as Total
FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR
WHERE (SLCUSA = 2) AND (SLCUSB = 1007)
END







View 11 Replies View Related

SQL Server 2012 :: Adding Certain Rows And Minus Value With A Row

Nov 25, 2014

I got a table where i need to add certain rows and minus the valu with a row.

IDC1C2C3C4C5C6
1551557775
266201452
345222266
441727582
532556951
6022022
7182344142
8

Result in the 8 Row

Sum(id(2,3,4)-sum(id(6,7))

View 2 Replies View Related

Transact SQL :: Does Server Support MINUS Keyword

Mar 22, 2006

I have two tables CarType & Cars.  Table CarType has a column CarTypeId which is the primary key (int, identity).  Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarTypeMINUSSELECT CarTypeId FROM Cars..So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}.  (Note:  I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).I tried it in SQL Server 2005 Express.  The result is just {1, 2, 3, 4, 5}.

Is the MINUS capability supported by SQL Server?  I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server..

Here's a reference to the website where I initially found out about MINUS: [URL] ....

View 19 Replies View Related

SQL Server 2012 :: If Minus Figure Pull Back 0

Feb 6, 2015

Have the following in my SELECT Stataement

CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays
WHEN com.completion_date IS NOT NULL AND dim.DayName = 'Saturday'
THEN DATEDIFF(d, com.current_task_target_date,com.completion_date)
ELSE NULL
END AS 'DaysOverTarget'

Some of the figures coming back are minus figures. How could I get the minus figures reported to be 0.00?

Below is the full TSQL

SELECT DISTINCT com.comm_reference AS 'Referance'
,com.crt_date AS 'CreatedDate'
,com.current_task_target_date AS 'TargetDate'
,com.completion_date AS 'CompletionDate'
,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'
THEN DATEDIFF(d, com.crt_date,com.completion_date) - non.NoWorkDays

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Keep A Running Total Of OnHandQty Minus OrderQty

Aug 26, 2015

I have been tasked with writing a report that shows all open orders for an item and their quantities, along with a running total of what is left in stock. We start by building these two tables:

IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Warehouse', 'U') IS NOT NULL DROP TABLE #Warehouse;
CREATE TABLE #Orders (OrderDate DATETIME, JobNumber NVARCHAR(10), Item NVARCHAR(20), QtyOrdered NUMERIC(10, 2))
INSERT INTO #Orders SELECT '20150801', 'JOB1', 'Widget1', 5
INSERT INTO #Orders SELECT '20150802', 'JOB2', 'Widget1', 3

[code]....

View 8 Replies View Related

Using Expression Builder To Access A Dialy File Minus One Day

Jan 23, 2008

Hello,

I am using the following expression to access a daily file.

"D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

output: D:importdataperegrinedataACD_DATA_2008123.txt

I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work.

Thanks

View 3 Replies View Related

How To Create Parameter: =Today() Minus Some Number Of Days

Jan 3, 2008

SSRS 2005 on XP SP2

How does one format a date time parameter as, for example, today's date minus 7? Attempting to use:
=Today() - 7 throws the error "Operator '-' is not defined for types 'Date' and 'Integer'

Thanks in advance

View 6 Replies View Related

Data Access :: Order By And Minus In Char Fields

Apr 29, 2015

I have a test query that i have ordered by the expected result order.

select t from (
select cast('1' as nvarchar(20)) as t
union all select '1---qa'
union all select '1q'
union all select '1q1'
union all select '1qz'

[Code] ....

and the question is... why is minus character (at 3rd line in '1---qa' string) ignored by "order by" clause? That row is put after '1q1' and not after '1'.

When you replace '-' with '+' or any other special char it works as expected but not with '-'. It is just ignored no matter if there is one or more of them.

Don't mind local chars like čž, they are for testing purposes with collate (thus commented) and with bin collation that is not doing proper sorting but minus order is ok in that case.

Tested on SQL 2008 R2 and SQL express 2012, database collation is 'Croatian_CI_AS'.

View 4 Replies View Related

Stored Procedure:calculating Minus Dependent On Type With Group By

Aug 6, 2004

Hi i am trying to get the following result:
CALCULATE THE CURRENT NUMBER OF SHARES on base of number of shares bought and number of shares sold

so the logic is
int total
if transactiontype is "buy", total = total + amount bought
if transactiontype is sell, total = total-amount sold
plus must be grouped by share

base data are stored in 1 table as
share name transaction type share amount
tesco buy 200
tesco sell 50

sounds rather easy does it not?
well sql syntax must be very foreign to me as i do not manage it
I tried a few options:
1 to write a database function as above and then call it from sproc
but i need to use group by share name or id so it calculates for each share and it will not let me to use function in select with group by
2 i tried to use if within sproc but i get complain incorrect syntax (I use MSDE database accessed via Visual) - I think it suppose to be possible but it throws me out with the most simple if or = is there some special syntax?
3 I created 2 views: 1 view: all sold shares, 2nd view: all bought shares and their bought/sold quantities
as in
select share name, sum(sharequantity)
where transaction = sell
from table
group by share name
and the same for purchases
then I did union of these 2 views with boughtquantity - soldquantity what worked but it only showed me shares which were sold, if share was never sold it was not shown I guess due to union
so I am still stuck!

View 7 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

Select A Date Minus Whatever Todays Date Is?

Feb 15, 2008

At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.

The statement in my query I have to use is:

MailDate <= '2008-01-05'

I am wondering if i could make that statement automatically take off 42 days from todays date?

View 8 Replies View Related

Table1 Data Minus Table2 Data

Mar 21, 2008

Hi,

I am using SQL Server 2005 and would like to get all the rows that exist in
(Table1 - Table2) without the answer being distinct, what do i do?


Example:

Table1:
Products: A, B, C, D, D, D, E


Table2:
Products: A, C


select * from Table1
except
select * from table2


will give me: B, D, E


But i want it to give me: B, D, D, D, E


Any way that would work will be helpful. Thanks in advance.

View 11 Replies View Related

Strange Result - Minus Result -1

Mar 2, 2008

help strange result whan i do this



Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList




i try to get next unit value to next month
why i get this -1
on date




01/01/2008
1
-1

unit_date unit new_unit



01/02/2008
2
1

01/02/2008
1
4

01/01/2008
1
-1

01/02/2008
1
4

21/01/2008
1
-1

21/01/2008
1
-1

01/02/2008
1
4


TNX

View 3 Replies View Related

Moved Stock Minus In Item Table To Stock In Itemmoment Table

Sep 11, 2007

 helo all...,i want to make procedure like:examplei have table: item (itemid,itemname,stock)orderdetail(no_order,itemid,quantity)itemmoment(itemid,itemname,stock)item table itemid    itemname    stock  c1        coconut         2  p1         peanut          2orderdetail tableno_order        itemid        quantity   1                  c1                5itemmoment tableitemid    itemname    stock  c1       coconut          0  p1       peanut            0 when customer paid, his quantity in orderdetail decrease stock in item table..so stock in item table became:itemid        itemname    stock  c1            coconut         -3  p1            peanut           2it's not good, because stock may not minus...so i want to move -3 to itemmoment table..so stock in item table became:itemid        itemname    stock  c1            coconut          0  p1            peanut           2and in itemmoment table became:itemid        itemname    stock  c1             coconut        3  p1             peanut          0my store procedure like:ALTER PROCEDURE [dbo].[orders](    @no_order as integer,    @itemid AS varchar(50),    @quantity AS INT)ASBEGIN    BEGIN TRANSACTION            DECLARE @currentStock AS INT                SET @currentStock = (SELECT [Stok] FROM [item] WHERE [itemid] = @itemid)        UPDATE [item]        SET            [Stock] = @currentStock - @quantity        WHERE            [itemid] = @itemid    COMMIT TRANSACTIONENDit's only decrease stock with quantity. i want move stock minus from item to itemmoment..can anyone add code to my store procedure?plss.. helpp.thxx....

View 2 Replies View Related







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