Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily] ( Year, Month, Week, DayNo, Day, Trip_Date,
We are close to exceeding the Maximum Row size of 8K per row and the CIO asked me "how close are we to reaching the 8K row limit?"
I began by looking at byte requirements for the data types we are using from the systypes table then I wondered, do the indecie, Foreign Keys and Primary Keys use bytes which must be included in my Calculation for current number of bytes used per Row.
Question: How do I calculate Current total number of bytes used per row.
I have a SSRS report. I am displaying TOP 10 Stores by sales by creating a filter on group Store. It has 3 row groups (Store, brand, product).
There is no column group. In each cell, I am using sum function in expression (Sum(Fields!TotalSales.Value).
I am adding Totals by going on Group properties, but I am getting totals of all the rows not the total of all the groups within my Store level (which is my top Parent group).
Store Total sales(CW) TotalSales(LW) A 2 1 -expression used to calculated this value is sum(Fields!TotalSales.Value) B 3 2 C 7 3
I want subtotal =12 AND 6 under respective columns . ALSO, my store (A, B, C) can be expanded into Brands and brands into Products. I tried using running value but its not working as I have 3 groups within Store row parent group and I am doing a SUM on each group.
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select fs_locn, fs_accno, amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1 end ) from accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014' groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
I have to calculate the total working hours between days, there hours must get automatically round off to nearest value example:
Date :12-05-2013 time : 4:15 will change to 4.00 and if Date :13-05-2013 time: 4:25 then needs to 4.30 and sum the above total hours and results Total : 8.30 hrs.
Hi All, I have a table call case and case_status have two fields, date and status as below: date status 04/01/2006 open 04/05/2006 closed 04/10/2006 open 04/15/2006 closed Whenever i open and closed the case, one record is insert into the case_status table. Now I would need to calculate the total days of the case in storeprocedure. Anyone can help me please. Aung
I am trying to create a procedure which will calculate the total tuition This process involves 3 tables. Contract table has tuition information which is all $100 (set price). Discount table has discount type and discount percentage (ex. 0.3) on each discount type. ContractDiscount table have contract number and discount number to connect both tables
I think I need to create a loop since some contract gets more than one discount. I have to calculate and get result nee to be like this
total_tuition = (tuition - discountPer * tuition) - this has to be a loop condition
I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance 201501 201502 201503 TOTAL Number of events 8 10 10 28 Time 5 5 4 14 Ratio 8/5 10/5 10/4 28/14
hello i`m rather new in sql and i have to do something simple for u i guess. i have 3 columns a)date of birth b)current date c)final age. i have the values of the first 2 columns and i want column c to be calculated automaticaly when i enter the values of the first 2 columns. example. a)20/03/1972 b)07/02/2008 c)?? (should be 36).
if you could help me i would appriciate very much. thanks in addvance
Need calculating the count of events on each day. I have an event log table as shown below.
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime ) INSERT INTO @EventLog SELECT '100','Inplace','01/05/2015 05:00' UNION ALL SELECT '100','Removed','03/05/2015 10:00' UNION ALL SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103) hence 2 .
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.
So I need to count of all each event_id in place for each day in may.
How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.
SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK") FROM ruleResults;
I have a table of data with values for every 30 mintues per day (i.e. 48 values per record per day.) I need to find the maximum value over the range of half-hourly time periods in any particular day and then calculate the MAX value for each month and year.
Is there a way of doing it? I am using SQL Server 7.0.
For the second record, ItemsStatus=1 which means the item return to stock, at the time the running stock value calculation should be the previous row Running Stok value (=9 ) +(ItemQuantity*ItemUnitWeight)When the ItemStatus=2, that means the item is definitely out and will be never back to current stock. Is there a way to get that calculation field ?
I am trying to create a "Loan Ledger Card" in SSRS which does the calculation up to report extracting time. I was able to create it on Excel (Click to download the Excel file) as we can calculate amounts for each installment based on Row and Column name. You can test it by putting any amount (Here it should be below 62000 as it is the total balance of loan) in "Amount Paid" yellow cell and see the effect.
I have made a selection in SQL (attached query and result in screenshot). It calculates the total paid amount by now on the first row, and all due amounts in other rows. Basically, It should first deduct the interest from first row, then principle, same process for second row and so on until it covers the whole paid amount.
SELECT '0' As RN ,memrepay.lnr ,'Repayment' AS Type ,SYSDATETIME() AS NOW ,SUM(memrepay.mprinc) As PrincPaid ,SUM(memrepay.mint) As IntPaid
[Code] .....
How can I do the similar calculation of Excel file in "SQL query" or "SSRS custom code"?
table A-NewParts PartNumber Description LaunchDate a123 product a 4/1/2009
[code].....
I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.
PartNumber Description LaunchDate Yr1 Sales Total Yr1 Sales Yr2 Sales Total Yr2 Sales a123
I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd Test Inside Property2009-08-29 2010-07-31 Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
How to calculate estimated completion time of a job and what is the variance/difference in time based on previous job history. Looking for tsql query which can accomplish this.For example)...Daily a job is taking 10 mins to complete. However, today due to some reason, the job is running over an hour and still running. It could be a blocking issue or some performance issue on the server due to which the job is still running.
In such cases, using a tsql query or a stored proc which monitor these jobs every 3 mins (Configurable value), so every 3 mins , query has to check, if they are any jobs which are taking more time than its usual completion time/avg completion time in that case shoot an email using dbmail functionality i.e. sp_Senddbmail .. From there, DBA can dig further using waits or sql trace etc...