Search Results

Search found 2898 results on 116 pages for 'sum of digits'.

Page 24/116 | < Previous Page | 20 21 22 23 24 25 26 27 28 29 30 31  | Next Page >

  • Is there a way to optimize this update query?

    - by SchlaWiener
    I have a master table called "parent" and a related table called "childs" Now I run a query against the master table to update some values with the sum from the child table like this. UPDATE master m SET quantity1 = (SELECT SUM(quantity1) FROM childs c WHERE c.master_id = m.id), quantity2 = (SELECT SUM(quantity2) FROM childs c WHERE c.master_id = m.id), count = (SELECT COUNT(*) FROM childs c WHERE c.master_id = m.id) WHERE master_id = 666; Which works as expected but is not a good style because I basically make multiple SELECT querys on the same result. Is there a way to optimize that? (Making a query first and storing the values is not an option. I tried this: UPDATE master m SET (quantity1, quantity2, count) = ( SELECT SUM(quantity1), SUM(quantity2), COUNT(*) FROM childs c WHERE c.master_id = m.id ) WHERE master_id = 666; but that doesn't work.

    Read the article

  • Excel 2003 - ADDRESS() function issues

    - by hairdresser-101
    I finally thought I had found a way to acutally use excel productively but the code that I followed does not appear to work. I'm thinking that the code is very limited and can't do what I want but I thought I'd ask to confirm - maybe it is my function that is the problem. I want to calculate the sum of a row of values for the previous month based on how many days we are into this month (i.e. It is the 20th of April so I want to sum the first 20 days of March to compare against.) =SUM(G4:ADDRESS(ROW(),7+$BR$3,4)) I basically want to SUM(G4:AA4) and have used the address function to return the cell reference AA4 by taking G4 and adding 20 to the column count. ADDRESS(ROW(),7+$BR$3,4) This successfully returns AA7 as expected HOWEVER, when I try to use the returning value in the SUM() function it throws an error... Am I not able to use this reference in my calculation?

    Read the article

  • Eunit Expected Exception

    - by dagda1
    Hi, Is there a way in Eunit to test whether an exception has been thrown under certain cicumstances? Say I have a function sum like this: sum(N, M) when N > M -> throw({"start is bigger than end", N, M}); sum(N, M) when N =:= M -> N; sum(N, M) when N =< M -> N + sum(N + 1, M). Can I test that if N is bigger than M then an exception is thrown? Cheers Paul

    Read the article

  • c++ and c# speed compared

    - by Mack
    I was worried about C#'s speed when it deals with heavy calculations, when you need to use raw CPU power. I always thought that C++ is much faster than C# when it comes to calculations. So I did some quick tests. The first test computes prime numbers < an integer n, the second test computes some pandigital numbers. The idea for second test comes from here: Pandigital Numbers C# prime computation: using System; using System.Diagnostics; class Program { static int primes(int n) { uint i, j; int countprimes = 0; for (i = 1; i <= n; i++) { bool isprime = true; for (j = 2; j <= Math.Sqrt(i); j++) if ((i % j) == 0) { isprime = false; break; } if (isprime) countprimes++; } return countprimes; } static void Main(string[] args) { int n = int.Parse(Console.ReadLine()); Stopwatch sw = new Stopwatch(); sw.Start(); int res = primes(n); sw.Stop(); Console.WriteLine("I found {0} prime numbers between 0 and {1} in {2} msecs.", res, n, sw.ElapsedMilliseconds); Console.ReadKey(); } } C++ variant: #include <iostream> #include <ctime> int primes(unsigned long n) { unsigned long i, j; int countprimes = 0; for(i = 1; i <= n; i++) { int isprime = 1; for(j = 2; j < (i^(1/2)); j++) if(!(i%j)) { isprime = 0; break; } countprimes+= isprime; } return countprimes; } int main() { int n, res; cin>>n; unsigned int start = clock(); res = primes(n); int tprime = clock() - start; cout<<"\nI found "<<res<<" prime numbers between 1 and "<<n<<" in "<<tprime<<" msecs."; return 0; } When I ran the test trying to find primes < than 100,000, C# variant finished in 0.409 seconds and C++ variant in 5.553 seconds. When I ran them for 1,000,000 C# finished in 6.039 seconds and C++ in about 337 seconds. Pandigital test in C#: using System; using System.Diagnostics; class Program { static bool IsPandigital(int n) { int digits = 0; int count = 0; int tmp; for (; n > 0; n /= 10, ++count) { if ((tmp = digits) == (digits |= 1 << (n - ((n / 10) * 10) - 1))) return false; } return digits == (1 << count) - 1; } static void Main() { int pans = 0; Stopwatch sw = new Stopwatch(); sw.Start(); for (int i = 1; i <= 123456789; i++) { if (IsPandigital(i)) { pans++; } } sw.Stop(); Console.WriteLine("{0}pcs, {1}ms", pans, sw.ElapsedMilliseconds); Console.ReadKey(); } } Pandigital test in C++: #include <iostream> #include <ctime> using namespace std; int IsPandigital(int n) { int digits = 0; int count = 0; int tmp; for (; n > 0; n /= 10, ++count) { if ((tmp = digits) == (digits |= 1 << (n - ((n / 10) * 10) - 1))) return 0; } return digits == (1 << count) - 1; } int main() { int pans = 0; unsigned int start = clock(); for (int i = 1; i <= 123456789; i++) { if (IsPandigital(i)) { pans++; } } int ptime = clock() - start; cout<<"\nPans:"<<pans<<" time:"<<ptime; return 0; } C# variant runs in 29.906 seconds and C++ in about 36.298 seconds. I didn't touch any compiler switches and bot C# and C++ programs were compiled with debug options. Before I attempted to run the test I was worried that C# will lag well behind C++, but now it seems that there is a pretty big speed difference in C# favor. Can anybody explain this? C# is jitted and C++ is compiled native so it's normal that a C++ will be faster than a C# variant. Thanks for the answers!

    Read the article

  • compute mean in python for a generator

    - by nmaxwell
    Hi, I'm doing some statistics work, I have a (large) collection of random numbers to compute the mean of, I'd like to work with generators, because I just need to compute the mean, so I don't need to store the numbers. The problem is that numpy.mean breaks if you pass it a generator. I can write a simple function to do what I want, but I'm wondering if there's a proper, built-in way to do this? It would be nice if I could say "sum(values)/len(values)", but len doesn't work for genetators, and sum already consumed values. here's an example: import numpy def my_mean(values): n = 0 Sum = 0.0 try: while True: Sum += next(values) n += 1 except StopIteration: pass return float(Sum)/n X = [k for k in range(1,7)] Y = (k for k in range(1,7)) print numpy.mean(X) print my_mean(Y) these both give the same, correct, answer, buy my_mean doesn't work for lists, and numpy.mean doesn't work for generators. I really like the idea of working with generators, but details like this seem to spoil things. thanks for any help -nick

    Read the article

  • Can someone explain how this works?

    - by Dan Howard
    Key in the first three digits of your phone number (NOT the Area code...) Multiply by 80 Add 1 Multiply by 250 Add to this the last 4 digits of your phone number Add to this the last 4 digits of your phone number again. Subtract 250 Divide number by 2 Do you recognize the answer??

    Read the article

  • Have I checked every consecutive subset of this list?

    - by Nathan
    I'm trying to solve problem 50 on Project Euler. Don't give me the answer or solve it for me, just try to answer this specific question. The goal is to find the longest sum of consecutive primes that adds to a prime below one million. I use wrote a sieve to find all the primes below n, and I have confirmed that it is correct. Next, I am going to check the sum of each subset of consecutive primes using the following method: I have a empty list sums. For each prime number, I add it to each element in sums and check the new sum, then I append the prime to sums. Here it is in python primes = allPrimesBelow(1000000) sums = [] for p in primes: for i in range(len(sums)): sums[i] += p check(sums[i]) sums.append(p) I want to know if I have called check() for every sum of two or more consecutive primes below one million The problem says that there is a prime, 953, that can be written as the sum of 21 consecutive primes, but I am not finding it.

    Read the article

  • convert string to double

    - by James123
    I have string value in that I need to convert to double in VB.Net. Conditions are like below string = "12345.00232232" if condition is 3 (2 digits after decimal and comma) display = 12,345.00 if condition is 5 (5 digits after decimal and comma) display = 12,345.00232 If Condition is 7 ( 5 digits after decimal and no comma) display = 12345.00232 How can I do that in VB.Net?

    Read the article

  • python multiprocessing.Process.Manager not producing consistent results?

    - by COpython
    I've written the following code to illustrate the problem I'm seeing. I'm trying to use a Process.Manager.list() to keep track of a list and increment random indices of that list. Each time there are 100 processes spawned, and each process increments a random index of the list by 1. Therefore, one would expect the SUM of the resulting list to be the same each time, correct? I get something between 203 and 205. from multiprocessing import Process, Manager import random class MyProc(Process): def __init__(self, A): Process.__init__(self) self.A = A def run(self): i = random.randint(0, len(self.A)-1) self.A[i] = self.A[i] + 1 if __name__ == '__main__': procs = [] M = Manager() a = M.list(range(15)) print('A: {0}'.format(a)) print('sum(A) = {0}'.format(sum(a))) for i in range(100): procs.append(MyProc(a)) map(lambda x: x.start(), procs) map(lambda x: x.join(), procs) print('A: {0}'.format(a)) print('sum(A) = {0}'.format(sum(a)))

    Read the article

  • Forcing entry of certain character types in UITextField

    - by cannyboy
    If I have a UITextField which the user is inputing a registration number, which has the format: 11-11-1111 (that is 2 digits, a dash, 2 digits, a dash, four digits) How do I force the user to enter this kind of data only... so they can't enter anything except 0-9 in the first character, and only '-' for the third character etc.

    Read the article

  • OCaml Summation

    - by Supervisor
    I'm trying to make a function in OCaml which does the summation function in math. I tried this: sum n m f = if n = 0 then 0 else if n > m then f else f + sum (n + 1) m f;; However, I get an error - "Characters 41-44: else f * sum(n + 1) m f;; Error: Unbound value sum and sum is underlined (has carrot signs pointing to it) I looked at this: Simple OCaml exercise It's the same question, but I see a lot of other things that I do not have. For example, for my n = m case, I do not have f n and then in the else case, I do not have f m. Why do you need f n if you want the function to return an integer? D: What's the problem!? Thanks in advance.

    Read the article

  • problem in using while loop in php&mysql

    - by Mac Taylor
    hey guys im using a while loop to show my latest forum topics now i need to count some fields either I'm trying to do it in one query and here is my code : $result = $db->sql_query("SELECT t.*,p.*, SUM(t.topic_approved='1') AS Amount_Of_Topics, SUM(t.topic_views) AS Amount_Of_Topic_Views, SUM(t.topic_replies) AS Amount_Of_Topic_Replies, SUM(p.post_approved ='1') AS Amount_Of_Posts FROM bb3topics t left join bb3posts p ON t.topic_id=p.topic_id ORDER BY t.topic_last_post_id DESC LIMIT 10 " ); while( $row = $db->sql_fetchrow($result) ) { problem : this code shows only one forum topic and not the rest , but if i remove sum() part from it , then it shows the rest is there anything wrong with my query code ?!

    Read the article

  • Ruby: totalling amounts

    - by Michael
    I have a Donation.rb model with an amount column that takes an integer. I want to sum all the individual donations together and show the total on the home page. In the home_controller, I'm doing @donations = Donation.all and then in the view I do <% sum = 0 %> <% @donations.each do |donation| %> <%= sum += donation.amount if donation.amount? %> <% end %> The problem is that this is printing the running sum each time a new donation is added to it. I just want the total sum at the end after they've all been added together.

    Read the article

  • Regex doesn't work properly

    - by oneofthelions
    I am trying to implement a regular expression to allow only one or two digits after a hyphen '-' and it doesn't work properly. It allows as many digits as user types after '-' Please suggest my ExtJS Ext.apply(Ext.form.VTypes, { hyphenText: "Number and hyphen", hyphenMask: /[\d\-]/, hyphenRe: /^\d+-\d{1,2}$/, hyphen: function(v){ return Ext.form.VTypes.hyphenRe.test(v); } }); //Input Field for Issue no var <portlet:namespace/>issueNoField = new Ext.form.TextField({ fieldLabel: 'Issue No', width: 120, valueField:'IssNo', vtype: 'hyphen' }); This works only to the limit that it allows digits and -. But it also has to allow only 1 to 2 digits after - at most. Is something wrong in my regex? hyphenRe: /^\d+-\d{1,2}$/,

    Read the article

  • return type while using GMP.h header file

    - by Raveesh_Kumar
    While i m using gmp.h header file. i need a fuction which takes inputs of type mpz_t and return mpz_t type too. I m very beginner of using gmp.h So, Here is snaps follows of my approached code.... mpz_t sum_upto(mpz_t max) { mpz_t sum; mpz_init(sum); mpz_init(result); for(int i=0;i<=max-1;i++) mpz_add_ui(sum,sum,pow(2,i)); return sum; } but it will show error 1 ." pow has been not used in this scope.",although i have added math.h in very beginnig of the file. 2 . sum_upto declared as function returning an array... do tell as soon as possible. i have to complete my very big project....

    Read the article

  • Regular Expression not disappearing

    - by user2439019
    I have 3 phone fields and any one is required. SO i had a custom validation class to make any one of them is required. And i am calling those class as follows [RegularExpression(@"^\(?([0-9]{3})\)?. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Entered phone format is not valid. <br/> 10 digits are required.<br/> No spaces between digits. <br/> Numbers only.")] [AtLeastOneRequired("PhoneHome", "PhoneMobile", "PhoneOffice", ErrorMessage = "Please provide either of PhoneHome or PhoneMobile or phoneOffice. ")] public string PhoneHome { get; set; } [StringLength(11, MinimumLength = 10)] [RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Entered phone format is not valid. <br/> 10 digits are required.<br/> No spaces between digits. <br/> Numbers only.")] [Display(Name = "Prompt_PhoneOffice", ResourceType = typeof(ResContactItems))] public string PhoneOffice { get; set; } [StringLength(11, MinimumLength = 10)] [RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Entered phone format is not valid. <br/> 10 digits are required.<br/> No spaces between digits. <br/> Numbers only.")] [Display(Name = "Prompt_PhoneMobile", ResourceType = typeof(ResContactItems))] public string PhoneMobile { get; set; } The problem is with only "PhoneHome " field , if i enter incorrect format, it will display error message based ont he regular expression given .But the regular expression message is not disappearing wwhen we enter the correct format too.It will disappear only in f the field is empty.Other two fields are showing properly. Is this due to the custom class i am calling.? Please help me to sort out this issue Thanks, Vidya

    Read the article

  • incompatible types in java

    - by user2975357
    Should I point out that I am a begginer at this? double averageMonthlyTemp() { double[] amt = new double[52]; int sum = 0; int index = 0; for (int i = 0; i < temp.length - 1; i = i + 7) { //where temp is an existiing //previously initialized array //of 365 elements, form 0 to 364 for (int j = 0; j < 7; j++) { sum = sum + temp[i + j]; if (j % 7 == 6) { double average = ((double) sum) / 7; amt[index] = average; index++; sum = (int) 0; } } } return amt; } When I try to compile, I get an "incompatible types" error, with the "amt" at return amt marked in red. Does somebody know why?

    Read the article

  • “Query cost (relative to the batch)” <> Query cost relative to batch

    - by Dave Ballantyne
    OK, so that is quite a contradictory title, but unfortunately it is true that a common misconception is that the query with the highest percentage relative to batch is the worst performing.  Simply put, it is a lie, or more accurately we dont understand what these figures mean. Consider the two below simple queries: SELECT * FROM Person.BusinessEntity JOIN Person.BusinessEntityAddress ON Person.BusinessEntity.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID go SELECT * FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID After executing these and looking at the plans, I see this : So, a 13% / 87% split ,  but 13% / 87% of WHAT ? CPU ? Duration ? Reads ? Writes ? or some magical weighted algorithm ?  In a Profiler trace of the two we can find the metrics we are interested in. CPU and duration are well out but what about reads (210 and 1935)? To save you doing the maths, though you are more than welcome to, that’s a 90.2% / 9.8% split.  Close, but no cigar. Lets try a different tact.  Looking at the execution plan the “Estimated Subtree cost” of query 1 is 0.29449 and query 2 its 1.96596.  Again to save you the maths that works out to 13.03% and 86.97%, round those and thats the figures we are after.  But, what is the worrying word there ? “Estimated”.  So these are not “actual”  execution costs,  but what’s the problem in comparing the estimated costs to derive a meaning of “Most Costly”.  Well, in the case of simple queries such as the above , probably not a lot.  In more complicated queries , a fair bit. By modifying the second query to also show the total number of lines on each order SELECT *,COUNT(*) OVER (PARTITION BY Sales.SalesOrderDetail.SalesOrderID) FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID The split in percentages is now 6% / 94% and the profiler metrics are : Even more of a discrepancy. Estimates can be out with actuals for a whole host of reasons,  scalar UDF’s are a particular bug bear of mine and in-fact the cost of a udf call is entirely hidden inside the execution plan.  It always estimates to 0 (well, a very small number). Take for instance the following udf Create Function dbo.udfSumSalesForCustomer(@CustomerId integer) returns money as begin Declare @Sum money Select @Sum= SUM(SalesOrderHeader.TotalDue) from Sales.SalesOrderHeader where CustomerID = @CustomerId return @Sum end If we have two statements , one that fires the udf and another that doesn't: Select CustomerID from Sales.Customer order by CustomerID go Select CustomerID,dbo.udfSumSalesForCustomer(Customer.CustomerID) from Sales.Customer order by CustomerID The costs relative to batch is a 50/50 split, but the has to be an actual cost of firing the udf. Indeed profiler shows us : No where even remotely near 50/50!!!! Moving forward to window framing functionality in SQL Server 2012 the optimizer sees ROWS and RANGE ( see here for their functional differences) as the same ‘cost’ too SELECT SalesOrderDetailID,SalesOrderId, SUM(LineTotal) OVER(PARTITION BY salesorderid ORDER BY Salesorderdetailid RANGE unbounded preceding) from Sales.SalesOrderdetail go SELECT SalesOrderDetailID,SalesOrderId, SUM(LineTotal) OVER(PARTITION BY salesorderid ORDER BY Salesorderdetailid Rows unbounded preceding) from Sales.SalesOrderdetail By now it wont be a great display to show you the Profiler trace reads a *tiny* bit different. So moral of the story, Percentage relative to batch can give a rough ‘finger in the air’ measurement, but dont rely on it as fact.

    Read the article

  • How to invalidate cache when benchmarking?

    - by Michael Buen
    I have this code, that when swapping the order of UsingAs and UsingCast, their performance also swaps. using System; using System.Diagnostics; using System.Linq; using System.IO; class Test { const int Size = 30000000; static void Main() { object[] values = new MemoryStream[Size]; UsingAs(values); UsingCast(values); Console.ReadLine(); } static void UsingCast(object[] values) { Stopwatch sw = Stopwatch.StartNew(); int sum = 0; foreach (object o in values) { if (o is MemoryStream) { var m = (MemoryStream)o; sum += (int)m.Length; } } sw.Stop(); Console.WriteLine("Cast: {0} : {1}", sum, (long)sw.ElapsedMilliseconds); } static void UsingAs(object[] values) { Stopwatch sw = Stopwatch.StartNew(); int sum = 0; foreach (object o in values) { if (o is MemoryStream) { var m = o as MemoryStream; sum += (int)m.Length; } } sw.Stop(); Console.WriteLine("As: {0} : {1}", sum, (long)sw.ElapsedMilliseconds); } } Outputs: As: 0 : 322 Cast: 0 : 281 When doing this... UsingCast(values); UsingAs(values); ...Results to this: Cast: 0 : 322 As: 0 : 281 When doing just this... UsingAs(values); ...Results to this: As: 0 : 322 When doing just this: UsingCast(values); ...Results to this: Cast: 0 : 322 Aside from running them independently, how to invalidate the cache so the second code being benchmarked won't receive the cached memory of first code? Benchmarking aside, just loved the fact that modern processors do this caching magic :-)

    Read the article

  • How to call Postgres function returning SETOF record?

    - by Peter
    I have written the following function: -- Gets stats for all markets CREATE OR REPLACE FUNCTION GetMarketStats ( ) RETURNS SETOF record AS $$ BEGIN SELECT 'R approved offer' AS Metric, SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketAPlus24, SUM(CASE WHEN M.MarketName = 'A+' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketAPlus36, SUM(CASE WHEN M.MarketName = 'A' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketA24, SUM(CASE WHEN M.MarketName = 'A' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketA36, SUM(CASE WHEN M.MarketName = 'B' AND M.Term = 24 THEN LO.Amount ELSE 0 end) AS MarketB24, SUM(CASE WHEN M.MarketName = 'B' AND M.Term = 36 THEN LO.Amount ELSE 0 end) AS MarketB36 FROM "Market" M INNER JOIN "Listing" L ON L.MarketID = M.MarketID INNER JOIN "ListingOffer" LO ON L.ListingID = LO.ListingID; END $$ LANGUAGE plpgsql; And when trying to call it like this... select * from GetMarketStats() AS (Metric VARCHAR(50),MarketAPlus24 INT,MarketAPlus36 INT,MarketA24 INT,MarketA36 INT,MarketB24 INT,MarketB36 INT); I get an error: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "getmarketstats" line 2 at SQL statement I don't understand this output. I've tried using perform too, but I thought one only had to use that if the function doesn't return anything.

    Read the article

  • subtotals in columns usind reshape2 in R

    - by user1043144
    I have spent some time now learning RESHAPE2 and plyr but I still do not get it. This time I have a problem with (a) subtotals and (b) passing different aggregate functions . Here an example using data from the excellent tutorial on the blog of mrdwab http://news.mrdwab.com/ # libraries library(plyr) library(reshape2) # get data and add few more variables book.sales = read.csv("http://news.mrdwab.com/data-booksales") book.sales$Stock = book.sales$Quantity + 10 book.sales$SubjCat[(book.sales$Subject == 'Economics') | (book.sales$Subject == 'Management') ] <- '1_EconSciences' book.sales$SubjCat[book.sales$Subject %in% c('Anthropology', 'Politics', 'Sociology') ] <- '2_SocSciences' book.sales$SubjCat[book.sales$Subject %in% c('Communication', 'Fiction', 'History', 'Research', 'Statistics') ] <- '3_other' # to get to my starting dataframe (close to the project I am working on) book.sales1 <- ddply(book.sales, c('Region', 'Representative', 'SubjCat', 'Subject', 'Publisher'), summarize, Stock = sum(Stock), Sold = sum(Quantity), Ratio = round((100 * sum(Quantity)/ sum(Stock)), digits = 1)) #melt it m.book.sales = melt(data = book.sales1, id.vars = c('Region', 'Representative', 'SubjCat', 'Subject', 'Publisher'), measured.vars = c('Stock', 'Sold', 'Ratio')) # cast it Tab1 <- dcast(data = m.book.sales, formula = Region + Representative ~ Publisher + variable, fun.aggregate = sum, margins = c('Region', 'Representative')) Now my questions : I have been able to add the subtotals in rows. But is it possible also to add margins in the columns. Say for example, Totals of Stock for one Publisher ? Sorry I meant to say example total sold for all publishers There is a problem with the columns with “ratio”. How can I get “mean” instead of “sum” for this variable ? P.S: I have seen some examples using reshape. Will you recommend to use it instead of reshape2 (which seems not to include the functionalities of two functions).

    Read the article

  • sql exception arithmetic overflow?

    - by MyHeadHurts
    In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird [ SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.HasMoreRows() +157 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197 System.Data.SqlClient.SqlDataReader.Read() +9 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282 System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19 System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222 System.Data.DataTable.Load(IDataReader reader) +14 ( @YearToGet int, @current datetime, @y int, @search datetime ) AS SET @YearToGet = 2006; WITH Years AS ( SELECT DATEPART(year, GETDATE()) [Year] UNION ALL SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet ), q_00 as ( select DIVISION , DYYYY , sum(PARTY) as asofPAX , sum(InsAmount) as asofSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int, DateAdd(year, (Years.Year - @y), @search)) and DYYYY = Years.Year group by DIVISION, DYYYY, years.year having DYYYY = years.year ), q_01 as ( select DIVISION , DYYYY , sum(PARTY) as YEPAX , sum(InsAmount) as YESales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year group by DIVISION, DYYYY , years.year having DYYYY = years.year ), q_02 as ( select DIVISION , DYYYY , sum(PARTY) as CurrentPAX , sum(InsAmount) as CurrentSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int,@current) and DYYYY = (year( getdate() )) group by DIVISION, DYYYY ) select a.DIVISION , a.DYYYY , asofPAX , asofSales , YEPAX , YESales , CurrentPAX , CurrentSales ,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales, CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION) JOIN Years as d on (b.dyyyy = d.year) where A.DYYYY <> (year( getdate() )) order by a.DIVISION, a.DYYYY ;

    Read the article

< Previous Page | 20 21 22 23 24 25 26 27 28 29 30 31  | Next Page >