Hi,
I have a query with DISTINCT option. If i use DISTINCT option with aggregate function like SUM will it eliminate duplicate values and then sum the values of a column and what is the syntax for it.
EX I have a column for budget
BUDGET
100
350
275
350
100
so if i use DISTINCT SUM(BUDGET) will it eliminate duplicate values and sum the total.
Im reading some guys code and I see the following code
"select distinct(State) from listOFschools;select * from listOFschools"
He is trying to query out states and the schools that are within the state. What exactly is the distinct() function do. and if anyone can tell me, what exactly is he asking to do in this statement
I have a query that returns employee names and reservations they updated for a set date. Here's a simplified version:
Select Employee.Name, Reservation.ResID
From Employee INNER JOIN Reservation
ON Employee.EmpID = Reservation.EmpID
Where Reservation.DateModified BETWEEN '200510010000' AND '200510050000'
The problem is each employee can update the same reservation several times but I only want it to display that record once. The distinct statement doesn't work because I want the EmpID to be listed more than once. Thanks.
I need to return a single, unique record for each director. In my example here, I'm getting back 5 records for Bossidy because he has 5 records in the Directorships table each with a unique CompID.
SELECT TDirectors.IDDir, TDirectors.DirFName, TDirectors.DirLName, TDirectors.DirLName + ', ' + TDirectors.DirFName AS DirFullName, TDirectors.ExecutiveTitle, TDirectors.DirGender, TDirectors.DirAge, TDirRace.DirRace,
TDirectors.PrincipalCompany AS CompanyName, TDirectorships.CompID
FROM TDirRace RIGHT OUTER JOIN
TDirectors ON TDirRace.IDDir = TDirectors.IDDir RIGHT OUTER JOIN
TDirectorships ON TDirectors.IDDir = TDirectorships.IDDir
WHERE (TDirectors.DirLName='Bossidy')
I thought I could do this but it doesn't work.
SELECT DISTINCT(TDirectors.IDDir)...
Thanks
Hi!
I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:
Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34
Incorrect syntax near the keyword 'option'.
Here is the function:
create FUNCTION [dbo].[ExpandedCTE]
(
@p_id int
)
RETURNS TABLE
AS
RETURN
(
with tbl_cte (id, tbl_id, lvl)
as
(
select
id, tbl_id, 0 lvl
from
tbl
where
id = @p_id
union all
select
t.id, t.tbl_id, lvl + 1
from
tbl_cte
inner join tbl t
on rnr.tbl_id = tbl_cte.id
)
select
id, tbl_id, lvl
from
tbl_cte
option (maxrecursion 0)
)
Please help!
Alexander.
P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.
I need to know how many widgets are located at each factory.
I have a table called "Widgets". The pertinent column(s) are:
Factory UID
By using only this table I can group the results by the FactoryUID to get the answer. However, this table does not tell me the factory name.
I have a table called "Factories". The pertinent column(s) are:
FactoryUID
FactoryName
I can join these two tables by the FactoryUID. But I don't know how to write this query so that my results will look like the following table:
FactoryName Widgets
Factory1 100
Factory2 200
Factory3 300
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(Â
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(Â
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef
HQ 123 1 Jim 4444 ABCDE
HQ 123 2 Bob 5555 ABCDE
HQ 123 3 Jim 6666 ABCDE
HQ 123 4 Bob 7777 ABCDE
HQ 124 1 Jenny 8888 FGHIJ
HQ 124 2 Jenny 9999 FGHIJ
HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE
Jim 4444/
6666
Bob 5555
7777
--BookingRef FGHIJ
Jenny 8888/
9999/
3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedHi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View 2 Replies View RelatedHello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.
Here's an example of what I'm hoping to accomplish:
*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)
===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555
==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
I have a table name “StringResources� which contains resources for different cultures. Right now, whenever admin adds any new resource, it immediately available to end user. Now the new requirement came up. We want admin to add resource first and when he is ready with all the resources for particular culture then only the resources should be available to end user.
Important: StringResource table has SQLCacheDependecy set. So any change for particular culture will invalidate the cache. This how the select statement looks.SELECT dbo.StringResources.resourceType, dbo.StringResources.cultureCode, dbo.StringResources.resourceKey, dbo.StringResources.resourceValue FROM dbo.StringResources
WHERE dbo.StringResources.cultureCode=@cultureCode
Which would be the best option below:
1> Add new Boolean column “Published� and show only resources which are published to end user.
Advantage: No need of extra table.
Disadvantage: This will invalidate the cache every time resource is added even if it’s not published.
Other option
2> Add new temporary table with same structure. When admin add new resource, add it to this temp table and when publish move resources to String Resources table.
Advantage: Admin will have separate working space. It will invalidate cache only when resources are published.
Disadvantage: Need extra table.
How can I get an All option into the cascading prompt?
I want to view data for all states in USA. I find I can't proceed to load report unless I have filled in a value for all prompts. Any help?
Thanks
I have a small doubt.
If we enable AWE option we can have advantage of available physical memory.we can have more memory by using max server memory property.
then why this AWE option comes in to picture.
I read bol but iam not able to understand what exactly happens.
Could any one tell me why this AWE option if we have max server memory property.
Hi,
What is equivalent to OPTION (RECOMPILE) in SQl Server 2000.
Create table #Employee
(
EmpId int IDENTITY,EmpName varchar(30)
)
insert into #Employee(EmpName )
select EmpName from AllEmployees
OPTION (RECOMPILE)
I have a vacation request app I'm designing, and it has a VacationData Table with TotalVac, UsedVac, VacLeft, VacationCarriedOver, and VacCompleted.
I need to take the VacLeft and divide by 2 and place that data in the following two spots, one in the VCO and add it to the TotalVac, which is pulled from another table w/hire date and other info.
I only need to run this on Jan 1 of every year, any suggestions?
I was running a query and it didn't execute stating you have run out of locks
How could I set the locks
thanks
Sejal
I am trying to alter a table that has an identity field to make the column have the not for replication option. I just can't seem to get the syntax down for the alter table command. PLEASE HELP. I know it can't be that hard!!!
View 2 Replies View RelatedIs there a Stored Procedure or something else that will tell me if a DB is set to Full or Simple recovery?
Thanks,
Ken Nicholson
Im trying to transfer data from SQL tbl to .xls file through a DTS package.
My problem is that DTS transfers all the data from the source tbl to .xls
file.
But i would like to have the selective data transfer based on some query like ;
select * from tbl where field ='val'
instead of complete data transfer.
Thanks in advance.
Regards
Siva
70-229 certification exam measures your ability to design and implement database solutions by using Microsoft SQL Server 2000 Enterprise Edition. Candidates for this exam work in a medium to enterprise computing environment that uses SQL Server 2000 Enterprise Edition. Candidates have at least one year of experience implementing relational databases. I don't have experience on database. Haven't experience on SQL server administration. Suggest me good one resources in low price. What are best option available in market?
View 2 Replies View RelatedFirst off, I apologize for not knowing what I'm talking about and being long winded. I'm trying to determine if SQL Server Express is an option for a client of mine. Their needs are beginning to go beyond what I'm comfortable with in Access, so I'm looking into the option of upgrading to a SQL Server Express back-end with a VB front-end.
Access doesn't require any setup beyond "File, new", so I know nothing about the background work required to get a database running on a platform like SQL Server.
My first concern is what kind of network admin rights do I need to install & use SSE..
It's unlikely that her IT group will just hand us the keys to any of their servers, so all we really have available is what we can put on her network drives. Am I right in assuming that using a SQL Server database would involve more network privileges than just dropping a file on the network and pointing my front end app at it?
With that said, I think I remember seeing something on one of the MSDN pages about setting up a database to run off of a CD-Rom. Could I somehow use this capability to get what I need?
Hello all
I would like to add a couple more fields that the users can query by, more options how would I add in the same stored proecedure, First Name, Date, and IR#?? so they can have the ability to run it by one field or two fields or all the above?? how would I incorporate those in the stored procedure??
CREATE PROCEDURE [SearchByDateExcls]
(@StartDate datetime,
@EndDate datetime)
AS SELECT [IR Number], Date, Inspector, Violation, [Violation Type], Loss, [Loss Type]
FROM dbo.Revised_MainTable
GROUP BY [IR Number], Date, Inspector, Violation, [Violation Type], Loss, [Loss Type]
HAVING (Date BETWEEN @StartDate AND @EndDate)
is this correct
CREATE PROCEDURE [SearchByDateExcls]
(@StartDateServed datetime,
@EndDateServed datetime, @Enter_LastName nvarchar(25), @Enter_Duration nvarchar(10))
AS SELECT IR#, [Date Served], [Reason For Exclusion], Duration, [First Name], [Last Name]
FROM [dbo].[Extended Exclusions]
GROUP BY IR#, [Date Served], [Reason For Exclusion] , Duration, [First Name], [Last Name]
HAVING (Date BETWEEN @StartDateServed AND @EndDateServed, Enter_LastName, @Enter_Duration)