Dividing Bytes By 1 Million To Get Megabytes In Select
Feb 13, 2005
Hello!
I have a table that contains a field containing the total bytes for a file. I am displaying the information in a datagrid but need to display the information in MB. If I divide by 1,000,000 in my select statement as such:SELECT cs_fileSize / 1000000 AS MB, cs_fileSize
I have a requirement to delete 1 Million records from a table having 10 Million data and it's being queried on 24/7 basis (don't have a downtime). how can I achieve that?
I have four sum statements in a procedure and I want to add two more fields that divide thismonthdeclines / thismonthsales, and prevmonthdeclines / premonthsales. I have tried a few ways and I just cant get the syntax right. Can someone help..
sum(case when O.orderdate between @prevMonthStart and @prevMonthEnd then D.orderlinetotal else 0 end ) as PrevMonthSales,
sum(case when O.orderdate between @thisMonthStart and @thisMonthEnd then D.orderlinetotal else 0 end ) as ThisMonthSales,
sum(case when O.orderdate between @prevMonthStart and @prevMonthEnd then case when O.orderstatus = 'CC Declined' then O.ordertotal end else 0 end) as PrevMonthDeclines,
sum(case when O.orderdate between @thisMonthStart and @thisMonthEnd then case when O.orderstatus = 'CC Declined' then O.ordertotal end else 0 end) as ThisMonthDeclines
from exigo_data_sync.Orders O INNER JOIN exigo_data_sync.OrderDetail D ON O.OrderID = D.OrderID
I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:
select count(*)[No. of Tickets Overdue], case when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due' end [Ticket Status] from whd.priority_type pt inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6' group by pt.due_hours order by pt.due_hours desc COMPUTE SUM(count(*))
select count(*)[Count2] from whd.job_ticket jt where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6' --COMPUTE [No. of Tickets Overdue]/[Count2]
I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated. Thanks in advance.
I am trying to take my result set and dividing up the results into 4 equal groups
I have included my code and a partial result set. All of your input and help is appreciated as I am new at this
select accounts.id, sum(trans.amount) as 'income' from jom.dbo.accounts join jom.dbo.trans on trans.accounts_id = accounts.id where trans.amount > 0 group by accounts.id
Inside each of these tables, there are two columms with the same name for both -> V, T. The V columm of the
table PDE#HD#Consumption#RP_Consumption_Gas gives me the cas consumed and the V table of
PDE#HD#SpeedCurves#Productivity the productivity. The V1/V2 graph must be ploted in a 24 hours period. What
happens is that if I do it for 24 houres, I mean, 1 day, the graph is ploted completly wrong in time! If I
do the same thing, but using only one columm, not the division by another, it works! Why this is happening ?
What is wrong with the following script ?? I need to know the answer for this as fast as possible! If you
need more details, I will give.
The script:
DayForm = Request.Form("D1")
Dim hoje Dim dia
If DayForm = "" OR DayForm = 0 Then hoje = true
If hoje Then _ Set rs = conn.Execute("SELECT
convert(varchar,PDE#HD#Consumption#RP_Consumption_Gas.T,108),(PDE#HD#Consumption #RP_Consumption_Gas.V/PDE#HD#SpeedCurves#Productivity.V) As Consumo FROM
PDE#HD#Consumption#RP_Consumption_Gas INNER JOIN PDE#HD#SpeedCurves#Productivity ON
PDE#HD#Consumption#RP_Consumption_Gas.T = PDE#HD#SpeedCurves#Productivity.T WHERE
PDE#HD#Consumption#RP_Consumption_Gas.T > DATEADD(dd, -1, GetDate()) ORDER BY
PDE#HD#Consumption#RP_Consumption_Gas.T") _ Else _ Set rs = conn.Execute("SELECT
convert(varchar,PDE#HD#Consumption#RP_Consumption_Gas.T,108),(PDE#HD#Consumption #RP_Consumption_Gas.V/PDE#HD#SpeedCurves#Productivity.V) As Consumo FROM
PDE#HD#Consumption#RP_Consumption_Gas INNER JOIN PDE#HD#SpeedCurves#Productivity ON
PDE#HD#Consumption#RP_Consumption_Gas.T = PDE#HD#SpeedCurves#Productivity.T WHERE
Just a quick question, how do I divide the results of these two queriestogether ?(select count (*) as ontime from schedule where actualarrivaldate <=estimatearrivaldate)(select count (*) as total from schedule)Thanks for any helpRobert
Hi 1: I€™m trying to get a percentage value (Left to Target) but I€™m getting €œ#Error€? in the Preview. This is what I have: =IIF(Fields!SalesTarget.Value Is Nothing , 0, ((Fields!Rev.Value)-(Fields!SalesTarget.Value))/(Fields!SalesTarget.Value)) How can this not work? I looked at a previous thread in here but I couldn€™t get anything from there L Kind Regards
SQL Server (TSQL) how to divide a number by either 2,3,4, or 6 and spreading it across that number of rows depending on what we are dividing the number by where it comes back to the original number...That is confusing I know so let me break it down...
Bill for 143.23 that will be paid out through 2 months...When you divide that by 2, you come back with 71.62, but if you multiply that number by 2, you come back with 143.24, not the amount for the bill...the end result has to be 71.62 for month1 and 71.61 for month2...Basically when there is a remainder, that has to be applied to the first month...
143.23: Month1 = 71.62 Month2 = 71.61
Another example...Same amount but have to divide by 6
I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.
For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:
Month 1: 50$
Month 2: 100$
Month 3: 100$
Month 4: 100$
Month 5: 100$
Month 6: 50$
I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.
I have two fields - both defined as money. When I divide them, SQL Server truncates the result after the 4th decimal point. So SQL Server says: 370.45 / 3,391,517.85 = 0.0001 I want to achieve: 370.45 / 3,391,517.85 = 0.00010922837... etc. The field the result is going into is defined as decimal(20,18)
I've tried using "cast(1stmoneyfield as decimal(20,18)) / cast(2ndmoneyfield as decimal(20,18)) as dividednumber", but SQL Server reports back errors about null values and Arithmetic overflow and terminates.
I'm at a loss as to how to solve the problem. Any suggestions please?
The issue I'm having is that the values I need to divide by are in fact, a result set from the CASE statement. It's been a long time since I've done anything like this.
I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A) 2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B) 2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100 SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security] FROM HS140_Rpt_Tmp_ForSummary s WHERE s.[Security] = 'Yes' GROUP BY Market
One of my programmers brought an error to me that I need to get some clarification on ...
Its says: The total row size (24301) for table xxxx eceeds the max number of bytes per row(8060). Rows that exceed the max number of bytes will not be added.
I used to write the data in a database throught the component. Few days it worked fine and suddenly one day it showed the database is suspect and the file size is 0 bytes but actually is should have written lots of data.
But i have the datafile which shows zero bytes and when i try to attach it it gives an error:
Server: Msg 823, Level 24, State 6, Line 1 I/O error 38(Reached the end of the file.) detected during read at offset 0000000000000000 in file 'D:EorderdatabasePritamdbeorderpritamdb_Data.mdf'.
Connection Broken
Can anyone tell y is this. And it would be really if anyone can tell how to recover the data. The only thing which i have now is the data file which shows zero bytes.
I tried to ALTER TABLE calendar NOCHECK CONSTRAINT ALL and I got this error:
Warning: The table 'messages' has been created but its maximum row size (8321) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I'm not too certain what this error means so I manually deleted the records in this table. I got the same error.
My database has chinese characters in. Eventually I need to only get the first 50 bytes of the data field, but somehow, I use len('==data==',50), it would catch 50 chinese characters which make 100 bytes ... can anyone help me? Thank you very much no matter what the result is ;)
Hi all, I have a table with approx 75 million rows of names and addrersses in it that I am trtying to update...so far the update is running 5 hours and with no end in sight...a liitle background is that this is running on a quad zion 500 with 3 gb ram ands one 145 gb drive (boooo) without improving the hardware needs can i improve the performance...I have indexed all the where fields that i read on and only update the table but once or twice a month, but I do daily selects by zip or county (all indexed) i even have a composite key on phone and zip...
i have heard of horizontal partioning but i always thought that was reserved for archiving old transactional data that rarely gets read on....
when i performed a trace there are plenty of reads but no writes...is this normal during an update like this...
i have been running this proc for the past 7 HOURS!!!....any help is appreciated, since all i have is time at this point....
THANKS!!!!
--Set rowcount to 100000 to limit number of updates --performed in each batch to 100K rows. Set rowcount 100000
--Declare variable for row count Declare @rc int Set @rc=100000
While @rc=100000 Begin
Begin Transaction
--Use tablockx and holdlock to obtain and hold --an immediate exclusive table lock. This unusually --speeds the update because only one lock is needed. Update [2000] With (tablockx, holdlock) set [source] = '2000'
--Get number of rows updated --Process will continue until less than 10000 Select @rc=@@rowcount
Hi guys,I'm currently trying to insert image into my SQL db. I have tried a number of methods that were posted online, and so far with no luck.My current code reads: Dim conn As New Data.SqlClient.SqlConnection() conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString conn.Open() Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn) cmd.CommandType = Data.CommandType.StoredProcedure Dim sImageName As New Data.SqlClient.SqlParameter("@sImageName", Data.SqlDbType.VarChar, 50) sImageName.Value = sImageName Dim sImageType As New Data.SqlClient.SqlParameter("@sImageType", Data.SqlDbType.VarChar, 50) sImageType.Value = fileType Dim sImageData As New Data.SqlClient.SqlParameter("@sImageData", Data.SqlDbType.Image, uploadedFile.Length) sImageData.Value = uploadedFile cmd.Parameters.Add(sImageName) cmd.Parameters.Add(sImageType) cmd.Parameters.Add(sImageData) Dim reader1 As Data.SqlClient.SqlDataReader reader1 = cmd.ExecuteReaderRunning through debug, everything runs up until the last line, where an error is caught saying : Failed to convert parameter value from a SqlParameter to a String I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it. Any help greatly appreciated!!
if SQL SERVER 2000 only allow 8060 bytes per row, then how can it store images or CLOB data? Is there a way that would let us change the maximum number of bytes per row? Any help would be greatly appreciated. Thanks.
I'm wondering if there's a way to use the string function replicate() to produce an output greater than 8000 bytes. From the documentation REPLICATE returns a varchar of max 8000 but I'm told there's a way to stop this restriction.
My mentor has been giving me tasks to try and accomplish and for this one I'm to create a stored procedure with no limitations, however I'm stuck at this replicate() problem.
We are trying to get a rough estimate of the size of the warehouse in terms of number of bytes. Now I understand that when I say char(2) datatype requires 2 bytes of memory. If this is correct then how many bytes does the following data type need -
I have a field that is stored in bytes in sql2008 R2. I need a simple script that will convert this field to MB & round up. I'm not able to find anything but complicated functions a.
Hi i want to know how much bytes will sql server take to store asingle alphabet like "a". i need to know similarly for all data types(including images).here i am doing a application where i need topredict the amount of space required in sql server to store the userfed dynamic data.Give me a handy solution.Regardsvisu
does anyone know when you sync (for the first time), if the snapshot is sent to the remote/client side compressed or uncompressed? i'm not talking about the resulting .sdf file size. i'm talking about the actual bytes that are tranfered or the wire.
I have a varchar(900) which means that I can use 900bytes, so if I am not wrong if the character is unicode, y only can use 450 because each character need two bytes.I have a databease with a column that use the intercalation general_latin_CI_AI, but I don't know if this intercalation use 1byte per character or use 2bytes per character.How can I know how many bytes need a character of a varchar column?
