I am writing a paper for a computer programming college class and I
need to know how many companies worldwide use SQL server? How many
servers does this constitute? At what rate is SQL use growing? If you
can't provide close to exact numbers, could you point me to a source
where I could find them?
I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."
But now we have changed the concept to get the ISID (name of the user) Â and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .
After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.
if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.
Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).
Here is my sample  code:
I need to sum it up to get the total user's from both the table but I should get 1 instead of 2Â
SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*) Â from dbo.test1
where invited_by ='sam'
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.
TIA
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,
[Code] .....
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
View 4 Replies View RelatedFor some reason my Add Total is grey out, when i tried to add grand total using some expression.
I have two row & two column groups?
Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image
I'm using iff condition in my expression..Â
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.
CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')
My table contains 1000 records,
I need to know the total record count with the below paging query
SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
How to get?
Hi, can anyone help?
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.
Can anyone help, thanks
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
View 1 Replies View RelatedWe have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedI am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies View Relatedhi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
Hi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
Hi all,
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 kindly request for help.
Thanx in advance.
Ronnie
I want to be able to list all users connected/logged in a specific database and disconnect them all or a certain user.
can this be done in SQL and if so how?
We are using Navision with SQL server 2003.
What kind of methods is there out there to reduce hwo often this happens?
Hi,
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..
Please help, how can I get the total for all.
Thanks,
Farn
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?
Thanks.
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)
Any help would be great as I am completely lost.
Hello Everyone,
How can I get the total of the loan numbers:
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.
TIA
Kurt
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.
Any help gratefully received.
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
Melvin Felicien
IT Manager
DCG Properties Limited
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!
basically I need to sum the value of one group from another group.
Is this possible yet?
I have a matrix report built on a database not on the cube.
2006
Q1
Volume MS %
Sub Channel Product
X X1 10
X2 20
Subtotal 30
Y Y1 20
Y2 20
Subtotal 40
I need to calculate the Market Share values which are Volume of the product / Subtotal for example
MS% of Product X1 = Volume of X1 / Subtotal i.e 10/ 30 * 100 = 33.33 %
How can i achieve this within the report..Can somebody help/advise...
OK basically what I'm looking for is how to have two subtotals in a report along with a Grand Total.
So it would be something like this:
Revenue
Subtotal
Expense
Subtotal
Grand Total.
The grand total works fine, but I'm not sure how to add the subtotals.
Hey all,
I have following query
select Date,
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?
Hello all SQL guru's
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.
Thanks, Gary
VBS_NO
DIV_NO
ITM_NO
SLL_DLR
BOS
***%
609
9
53910
$ 36,270,045
1.24%
1.24%
609
9
53985
$ 31,542,973
1.08%
2.33%
609
9
16870
$ 29,583,159
1.01%
3.34%
609
9
24883
$ 29,532,282
1.01%
4.35%
609
9
39674
$ 27,856,172
0.96%
5.31%
609
9
11485
$ 27,598,356
0.95%
6.26%
609
9
39676
$ 26,933,126
0.92%
7.18%
609
9
33354
$ 23,056,438
0.79%
7.97%
Anyone know how to set total = 0, when the sum of that record is 0? Because when sum(total) = 0, the record is not selected anymore.
Like a stat table
Name | Total
----------------
David | 0
John | 10
Sammy | 5
Micheal | 0
Joe | 3
----------------
When displaying those record using SELECT statement, only
John | 10
Sammy | 5
Joe | 3
will be display, and i have to do a checking if the record not here, then assign 0 to them.
I wonder SQL can help to SELECT the records, so no extra work will done on the checking part.
Expert please advice.
Hi,
I have the following code
SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2
It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580
I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.
Can someone help me with this?
Thanks.
laura