I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number.
Here is my SQL statement:
SELECT
dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime)
AS TimeDiff
FROM dbo.FDC_Trips
For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.
How can I get this query to work with times before and after midnight?
I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?
I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number? One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive. Does anyone know how this can be achieved?
I am representing negative number within parenthesis , but as I am working on reporting service, it is possible to make the exact alignment of the number and parenthesis.
here is one example .... when i format number with #,##,0;(#,##,0) i am getting output in a format in subsequent rows like
123,345 (145,567)
How can I make this alignment like
123,345 (145,567)
where the parenthesis will be outside the number space keeping in mind the alignment is in right .
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.
I'm working with a horrible database! The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format. I need to convert the field to a decimal (9,2) but I can't because of the negative sign.
Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.
I have a report I am creating where I need to group on whether a number is positive or negative. The number will never be zero. This number is a decimal (currency), if that makes a difference.
I have tried creating a formula field with the following expression:
Code Snippet
IIf(Fields!Num.Value > 0, 1, 0)
I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.
If you need any more information, please let me know and I will provide what I can. Thanks for your answers!
I have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
I have a 2012 report builder chart that has two series (one area chart and one bar chart) combined into one chart. The problem I'm having is the bar chart has much smaller numbers than the area chart and the scaling is messed up.
Is there any way to put the bar chart on the right axis and keep the area chart on the left axis? My goal is to increase the size of the bar chart in relation to the area chart.
Also, is there any way to make the bar chart color red if the number is negative and green if it is positive?
Aim – Calculate the number of days between the [CreatedDate] and getdate, however if stage name = ‘Live Transactions’ then Calculate the number of days between [CreatedDate] & [CloseDate]
This is my query so far
SELECT [CreatedDate] ,[StageName] ,[CloseDate] ,DATEDIFF(dd,CONVERT(datetime,[CreatedDate]),GETDATE()) as Age FROM [FDMS].[Dan].[Raz_Reporting_LCS]
I googled on the latter error number and got nothing back Error: 0xC0048006 at Set Destination File, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x016F9EA4.
The former only returns one result which does not explain my problem
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int ); insert into @t ( ID ) values (-7077); select checksum_agg( ID ) from @t; ----------- -7077 declare @t table ( ID int ); insert into @t ( ID ) values (-7077), (-8112); select checksum_agg( ID ) from @t; ----------- 1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int ); insert into @t ( ID ) values (-7077), (-7077); select checksum_agg( ID ) from @t; ----------- 0
It's not specific to -7077, for example:
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- 0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- -997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- 0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (8112), (-997777), (8112); select checksum_agg( ID ) from @t; ----------- 0
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions
Basically in the stored procedure that creates the table the query includes:
Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END, which is wrong when sale and cost is negative the formula becomes (-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....
I have the following query, I would like to know if I can replace negative values with a zero and if a posiive value appears then leave the value alone?
I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.
The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.
Is there a known bug in QA that would manifest itself as this? What is the cause for this?
Ok, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?
SQL Enterprise manager is reporting my database device size to be a negative number. Primarily, this is the case for the log files, but also occurs on a few of the .dat files.
Is there a correction? Is this a display error, or an actual size conflict/
I wrote simple script to check space used by tables:
CREATE TABLE #SpaceUsed( TableName NVARCHAR(128), NoOfRows INT, Reserved NVARCHAR(18), Data NVARCHAR(18), Index_Size NVARCHAR(18), Unused NVARCHAR(18) ) GO sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"
SELECT * FROM #SpaceUsed
SELECT CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved, CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData, CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size, CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused FROM #SpaceUsed
I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob
I'm personally in favor of using the SQL CLR where appropriate, although I'm wondering what the negative consequences of enabling SQL CLR might be? Its disabled by default within SQL Server 2005 and most likely 2008, so what was the reason behind this ... beyond the fear of the DBA enabling something he might not himself fully understand.
I´m working with chart type bar (Simple Bar) and i received four values from stored procedure, all values are negative numbers. The Reporting Service shows the values in the chart, but, doesn't show the "bars".... If one of four values will be positive, the report shows the values and the "bars" but if all values will be negative, the report shows just de values.
What do I do for the bars appear with four negative values ?
what am I doing wrong here? I can multipley * 1.25, but not -1.25. Every google entry I find is how to convert positive to negative numbers... I want to use update to multiply and lower the price by 25% (I used the function a bit to liberally to multiply by 125% and now want to bring the values down to earth)
update production.product set listprice = (listprice * -1.25) select max(listprice) from production.product
How can I remove the - after an import into a database. I want to be able to convert all negative numbers to positive, once the data has been imported into the table
Please help!!! I'm building a database that I have a form. This form allows user input the invoice amount field which is the currency field. My ultimate goal is allow user put in the postive numbers and it will store as negative amount. Please let me know if there is the way to do that. Thanks in advance. Linh
I have a DTS package that imports excel spreadsheets. The excel spreadsheet cells contain numbers based on vlookups. Now negative values appear in these cells as -999 but when loaded into the table they appear as (999). Is there anyway way to configure it to just load -999? The tricky part is I don't have control over the format of the spreadsheet cell and the destination table field is varchar. I am hoping there is some way that the DTS interprets and picks up the value as it is displayed in excel. Or do I have to stick with converting it manually by replacing '(' and ')' with nothing and appending '-'?
I am transferring the debits and the credits in to one column in another table
Type Amount C-76 D80 C90 D-30
And I am facing two problems one is the negative numbers don't transfer and also when I have debit and credit on the same line they are not transferring.
Select Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then 'D' Else 'C' End, Case When isNull(A.[Debit], 0) > 0 And IsNull(A.[Credit], 0) = 0 Then Round(A.[Debit],2) Else Round(A.[Credit],2) End FROM Tble A
