I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
select col1,count(*) from client1..table1 group by col1 union select col1,count(*) from client2..table1 group by col1 union select col1,count(*) from client3..table1 group by col1
The results yields
33915 3405 3412
I am trying to get the following result but can't figure out how to get the total in the end.
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.
I have a field Char(10) named bank, and another one Char(15) named namebank.
I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.
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 a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone_optimized @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount. I thought to have something like:
DECLARE @Disc float SET @Disc = 0 SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100 FROM UserDiscounts WHERE UserID = 123
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty -------------------------- Package A | 15 Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
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
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
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,
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I have named this text box €™SubRepM1€™ The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year. For the first month the value is =sum(Fields! Month_1.Value)*-1, and I have named this text box 'MainRepM1' The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values but I now need to total these values together for each month in order to produce a grand total.
I have tried using the following to add the totals for Month 1 together, =subreport1.Report.SubRepM1 + MainRepM1 but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
My report looks something like this :
ID Name Oct Nov Dec Jan Feb March Total 001 alex 87.6% 104.1% 65.1% 50.2% 85.6% 002 Linda 87.4% 109.1% 68.1% 35.2% 90.8% 003 Jon 87.6% 104.1% 004 alex 87.6% 104.1% 65.1% 50.2% 85.6% 005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
The formula that I used for all of these entries is :
=IIf(IsError([total]),"",[total])
and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
I have built a query in Access that calculates the total to be charged to clients based on 3 cost columns.
I have tried to run the query in MSSQL but it will not run, so could someone help me out with the syntax in my query.
I have done about half of it myself but I am stuck on the calculation and I havent a clue of the syntax to be used.
This is my Access Query; SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings! Booking_Cost+Room_Facilities!FacilityCost AS TotalCost, [TotalCost]*17.5/100+[TotalCost] AS [Total+VAT] FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
and this is what I have managed to salvage in MSSQL format: SELECT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Rooms.CostPerNight, Room_Facilities.FacilityCost FROM Rooms INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID) INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
USE CHEC SELECT DISTINCT DAT01.[_@051] AS Branch, DAT01.[_@550] AS LoanType, convert(varchar(10), DAT01.[_@040], 110) AS Date, DAT01.[_@LOAN#] AS LoanNum FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] ON DAT01.[_@040] = [_@040] INNER JOIN [SMT_BRANCHES] ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr] WHERE DAT01.[_@040] Between '06/01/2006' And '06/30/2006' AND DAT01.[_@051] = '540' And DAT01.[_@LOAN#] Like '2%' And DAT01.[_@550] = '3' GROUP BY DAT01.[_@051], DAT01.[_@550], DAT01.[_@TP], DAT01.[_@040], DAT01.[_@LOAN#] ORDER BY DAT01.[_@051]
Where obviously DAT01.[_@LOAN#] is the LoanNum column.
I have the following query which gives me a list of names, logins, and group name along with a count of how many training modules each individual has sat. This returns 70 rows with each individuals details and totals.
However I want to be able to report instead just the total modules sat for all 70 rows instead of displaying each row individually. This is the query I am using:
SELECTpps_principals.name AS pname, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME, COUNT(PPS_SCOS.SCO_ID) AS coursecount FROMPPS_PRINCIPALS LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.STATUS like '[PCF]' AND PPS_TRANSCRIPTS.TICKET not like 'l-%' AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30' LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID AND pps_scos.name like 'MT%' LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN WHERE pps_principals.login like '%score%' and ext_user_groups.name like 'ALL SCORE PTY' AND dbo.PPS_PRINCIPALS.DISABLED IS NULL GROUP BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME HAVING COUNT(PPS_SCOS.SCO_ID) > 0 ORDER BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME
I am trying to use SUM to get the overall total but without success so far.
I need to sum the totals for each of the ratings, then group them by owneridname. the problem i am having is that the "Count(*) as total" returns a total that includes ratings that are not listed below as a criteria.
Eg. the total for all ratings for a user john would be 789. that figure would inlcude amounts for a rating eg. dead. that is not in the list below. please help.
SELECT owneridname, SUM(CASE WHEN new_ratingname = 'Hot' THEN 1 ELSE 0 END) AS Hot, SUM(CASE WHEN new_ratingname = 'warm' THEN 1 ELSE 0 END) AS Warm, SUM(CASE WHEN new_ratingname = 'cold' THEN 1 ELSE 0 END) AS Cold, SUM(CASE WHEN new_ratingname = 'cool' THEN 1 ELSE 0 END) AS Cool, SUM(CASE WHEN new_ratingname = 'new' THEN 1 ELSE 0 END) AS New, SUM(CASE WHEN new_ratingname = 'Reservation Holder' THEN 1 ELSE 0 END) AS [Reservation Holder], SUM(CASE WHEN new_ratingname = 'site visit' THEN 1 ELSE 0 END) AS [Site Visit], SUM(CASE WHEN new_ratingname IS NULL THEN 1 ELSE 0 END) AS [Not Rated], SUM(CASE WHEN new_ratingname = 'Continous Updates' THEN 1 ELSE 0 END) AS [Cont Updates], SUM(CASE WHEN new_ratingname = 'worked tasks' THEN 1 ELSE 0 END) AS [Wkd Tasks], COUNT(*) AS Total FROM FilteredContact AS filteredcontact WHERE (statuscodename = 'active') GROUP BY owneridname
HI everyone, I been looking for the answer for sometime now, thought i would post and see if anyone could help me it. This is the output of my query.
F_DivDiv_4Div_5Div_35 424600 504000 3510114
After the row 35 i want to add a row for total. I want to add the values of column Div_4 so it would be 246+0+1. The problem i'm having is that the columns are populated from using this logic.
Select rr.F_BVT_DIV_NO, (Case When rr3.F_DIVISION_NO = 4 then Max(rr3.Total_Pol_Count) else 0 end ) + (MAX(isnull(rr2.Div4,0))) Div_4
from RNEWL_RTENTN_DETL RR --join for division 4 transfers
left join(
select rr2.F_BVT_DIV_NO, count(rr2.X_POLICY_NO) As Div4 from RNEWL_RTENTN_DETL RR2 where RR2.F_BVT_DIV_NO<>RR2.F_DIVISION_NO and month(RR2.X_POLICY_XPRTN_DT)=2 and year(RR2.X_POLICY_XPRTN_DT)=2007 and rr2.F_DIVISION_NO = 4 Group By rr2.F_BVT_DIV_NO ) rr2 on rr2.F_BVT_DIV_NO = rr.F_BVT_DIV_NO
from TRNEWL_RTENTN_DETL rr3 where month(rr3.X_POLICY_XPRTN_DT)=2 and year(rr3.X_POLICY_XPRTN_DT)=2007 Group by rr3.F_DIVISION_NO) rr3 on rr3.F_DIVISION_NO = rr.F_BVT_DIV_NO group by rr.F_BVT_DIV_NO, rr3.F_DIVISION_NO order by rr.F_BVT_DIV_NO
That code is just a little piece of it, but the logic is the same throughout. Thanks for the input!
I have a SQL data source and i would like to present the total number of different records based on a "status" field. I have done total records in the past by doing this: protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; if (RecordCount == 1) { litRecordCount.Text = "1 record found"; } else { litRecordCount.Text = RecordCount.ToString() + " records found"; } } This would work, howerver, my SQLdatasource choose * records, and not based on a WHERE condition. Is there any way to total just those with a status of "Initialized" ? I tried to do it on the Gridview, but then I realized if I have paging on, it will only be on that front page. thoughts?
Is it possible to write an sql statement that will calculate a cummlative total of another field in the sql like below. The BOS column is being calculated an the sql and then the *** column is adding them up.