MS SQL Server :: Help With A Confusing And Advanced Query

I Am Really Confusing Please Help Me About A Single Query

Sep 1, 2005

Hi Guys,This is my Problem.A table contain following desing for handling different level of categories, bu it is dynamic int_categoryid,int_parent_categoryid,int_categorylevel,str_categoryname,bit_activethatall.I want to list data from table as following ordercategorry_parent11 category_child12 category_child13 category_child23 category_child22categorry_parent21.............................................................................................like this..ie we can insert parent category and sub category to n level dynamically without adding a new tableplease mail me for another clarification....please help meregardsAbdul

View 2 Replies View Related

Confusing Query Problem

Jun 11, 2008

I am looking to query one table where the matching information in the related table needs to be a subset of the data - but not always a complete set (and there may be added information in the related table). For example, if I have a table of Albums and I want to match songs to albums as follows:

Albums

Name CHAR_TYPE CHAR
Rock guitarists Genre Rock
Rock guitarists Instrument Guitar
Great Guitar Instrument Guitar
Rock and Blues Genre Rock
Rock and Blues Genre Blues

Songs

Name CHAR_TYPE CHAR
Purple Haze Genre Rock
Purple Haze Instrument Guitar
Purple Haze Artist Hendrix
Red House Genre Blues
Red House Instrument Guitar
Red House Artist Hendrix
Stairway to heaven Genre Rock
Stairway to heaven Instrument Guitar

For this query, the results should be

Album Song
Rock guitarists Purple Haze & Staiway to Heaven
Great Guitar All 3
Rock and Blues All 3

Everything I've tried either incudes extra songs (Rock Guitar includes Red House) OR Eliminates entire albums (everything else works, but rock and Blues produces no results

Thanks in advance for any sugestions.

View 1 Replies View Related

If Exsists

Jan 25, 2008

Hi i need a sql stored procedure

i am currently using the select statement below, but i need to use a if exsits i think.

i want

select Alo_Qty
from table
where Alo_UserID = @UserID and Alo_Stock_Code = @StockCode

but if there is no record in the table that matches then i need to set Alo_Qty = 0

any ideas what i need to do?

View 2 Replies View Related

SQL And ASP.NET Advanced Query

Jun 19, 2008

I want to query a database through a ASP.NET page and I want to set the page up like an advanced search page would look. My question is, how do I write the stored procedure and/or what value do I pass from ASP.NET to accomplish null or blank values. For instance, if someone wants to query by TRIP id and not vehicle ID then how do I write the where claue?

View 10 Replies View Related

Advanced Query

Feb 7, 2006

I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @CompanyID) AND (e.FacilityID = @FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName
so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'. I would imagine I would need a Declare @Status nvarchar(15) --- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.

View 14 Replies View Related

SQL Query Advanced Option

Apr 18, 2001

I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..

View 1 Replies View Related

Advanced Union Query

Sep 6, 2004

Hi!

I'm trying to get the following query to work:

SELECT item.name AS name, t15.f167 AS start, t15.f168 AS end, t15.f180 AS organizer
FROM item, t15
WHERE item.row_id = t15.id AND t15.f167 > getDate()
UNION ALL
SELECT item.name AS name, t30.f221 AS start, t30.f222 AS end, '<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id
+ '> KAN < / a > '
FROM item, t30
WHERE item.row_id = t30.id AND t30.f221 > getDate()
ORDER BY start DESC

The problem is the organizer field. I need to generate an url, but I get all sorts of problems with data types, ie. you can convert an int into ntext.

So the question is basically, how do I get the following to work:

'<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a >'

Vidar

View 6 Replies View Related

Advanced Sql-Shape Query - Help

Jan 12, 2006

Hi,This is my basic sql shape query:------------------------------------------------------------SHAPE {select * from tbl1}APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TOfield)------------------------------------------------------------With this query i get a RecordSet (RS1), who handle all the recordsfrom table tbl1, and a secondary RecordSet (RS2) who handle all therecords from table tbl2, who applies to the criteria that field1=1.It is possible that RS2 will be empty (zero records) since there is norecord in tbl2 who applies to that criteria.My wish is to design a query, that will collect only the records fromtbl1, that will have records from tbl2 who applies to the criteria -that RS2 won't be empty !I want to influence on the main part of the query (RS1), through thecriteria that is being used in the secondery query (RS2).I hope that my question is clear enough. thanks !

View 3 Replies View Related

Advanced SQL Query Problem ...

Jul 20, 2005

Dear Expert,I have 2 tables as the following,Table : ProductColumn : ID, Code, NameTable : TrxColumn : Trx_ID, Trx_Date, Product_IDHow can perform the following query in MSSQL or is there any other wayto perform the same query ??? For you info I can do this in Sybase andit's work fine. Thank you.================================================== ==========Select Trx.Product_ID,(Select Produc.CodeFrom ProductWhere Product.ID = Trx.Product_ID) as Product_CodeFrom TrxWhere Product_Code = '0001'================================================== ==========Regards,Chen Fock Siong2004-05-27

View 4 Replies View Related

SQL Query JOIN Needed? Too Advanced For Me

Sep 5, 2006

THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME. I am not good with SQL so please bare with me. I will give a dumbed down table for us to work with:timestamp - DATA1 - DATA2 - MAXVALUENEEDED - DATA32006/8/12 12:00 123 125 1289 121 2006/8/12 12:05 124 128 1291 1232006/8/15 12:00 130 128 1190 1242006/8/15 1:00 131 127 1196 123 The columns labeled DATA do not really matter. The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns. What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY. Example Output- 2006/8/12 124 128 1291 1232006/8/15 131 127 1196 123As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis. My current SQL Query is: SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C,
CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0)
FROM Table_1 T1
WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp)
ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days. Can anyone help me correct this? Thanks in advance for your help.

View 34 Replies View Related

Advanced Search Query In SQL Server2000

Jul 25, 2006

Hi query-experts,

I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:

Table has the fields: id, fruit, color

User enters: apple pear green

The query should return all records that contain apple AND pear AND green in any of the fields.

Now I do it like this:

SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')

this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.

Question: is there an easier way to achieve this?

(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)

SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;

View 3 Replies View Related

Search For Missing Values In Advanced Query

Jan 15, 2013

KEYIDGROUP
1 1 a
2 1 b
3 2 a
4 2 b
5 3 a
6 3 b
7 4 a
8 5 a

This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.

query should reveal answer should be missing the group b
KEYID
7 4
8 5

I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B

View 3 Replies View Related

Transact SQL :: Advanced Query With Windows Function?

Jun 16, 2015

SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date, @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:

CREATE TABLE MyTable
(
IDTable INT IDENTITY NOT NULL,
DateLog DATE NOT NULL,
CountLog INT NOT NULL
)

[code]....

In this case I need to apply a filter with the Dates:

@dateFrom ='2014-01-01',
@dateTo ='2014-04-28'

But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:

Id MonthYear Diff
1 Jan2014 5
2 Feb2014 3
3 March2014 0
4 April2014 5

With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.

[URL]

View 16 Replies View Related

Write Query To Show Results In DB View -Advanced-

Sep 10, 2007

I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,

View 2 Replies View Related

Stored Procedure Query Problem - Very Advanced. Need Assistance.

May 10, 2004

Hiya,

This is a fairly detailed problem, so this will be a long post... I do appologize. I have been agonizing over this now for over a week and cannot find a viable solution. Hopefully one of you can help.

First off, I work for a realestate company, and this query will display a list of properties based on a number of different criteria and criteria types. There are multiple tables involved:

dbo.Prop:
Property Database. Holds basic info about each property

dbo.Prop_Features:
holds all the features (such as Pool, Carpet, Drapes etc) for each property. The only information stored in this table are PropID and FeatureID (PropID being the Identity of the Prop table, FeatureID being the Identity of the Features Table)

dbo.Features
Holds information on each possable "feature" in the system.

dbo.Members
Holds basic information and criteria for each of our members.

dbo.Members_Features
This table holds the MemberID and FeatureID where members have chosen one ore more features to be used for criteria when searching for a property.

Ok, now... That said, here is my problem. The query I had written (by a professional hired thru Robert Half Technologies) takes over 30 seconds to execute. I will post a copy of that SP below. This is unacceptable. We have to process thousands of these per hour, and a 30 second process time is very bad. Can any of you give me a idea of how to better approach this problem?

In the code below, you will notice there are other tables I did not mention - they are not important. The Speed problem is surrounding a single function, which I will mention below.

CREATE PROCEDURE dbo.Member_Get_List
(
@MemberID Int,
@UpdatesOnly Bit

)
AS
Declare

@RentMin FLoat,
@RentMax Float,
@BedMin SmallInt,
@BedMax SmallInt,
@MinBaths Float,
@MinGarage Float,
@Acreage SmallInt,
@PropCount int,
@LastUpdate SmallDateTime

BEGIN
Select @Rentmin = Rentmin,
@RentMax = Rentmax,
@BedMin = BedMin,
@BedMax = BedMax,
@MinBaths = MinBaths,
@MinGarage = MinGarage,
@Acreage = Acreage,
@LastUpdate = LastUpdate

FROM
Members
WHERE
MemberID = @MemberID
END

BEGIN

SET @PropCount = (SELECT Count(*)
FROM Members_Features
WHERE MemberID = @MemberID )
END

IF @PropCount = 0
BEGIN

SELECT Top 100
P.PropID,
P.Bedrooms,
P.Baths,
P.Garage,
PT.PropName,
P.Rent,
P.Address,
P.Xstreets,
'DateAvailable' = CASE
WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!'
ELSE CONVERT(varchar(10), P.DateAvailable, 101)
End,
P.Lease,
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures)
WHEN 0 THEN ''
ELSE ', ' + P.CustomFeatures
End
+ CASE Len(P.Comments)
WHEN 0 THEN ''
ELSE ', ' + P.Comments
End
as 'Features',
P.Deposit,
Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4),
A.AreaName,
Z.County,
Z.City,
Z.State

FROM Prop P

INNER JOIN Area_Zipcode AZ

ON P.Zip = AZ.Zipcode

INNER JOIN Area_Areas A

ON AZ.AreaID = A.AreaID

INNER JOIN Members_Areas MA

ON A.AreaID = MA.AreaID

INNER JOIN Members_PropTypes MP

ON P.PropType = MP.PropType

INNER JOIN Prop_Types PT

ON P.PropType = PT.PropType

INNER JOIN LandLords L
ON P.LandLordID = L.LandLordID

INNER JOIN ZipCode Z
ON P.ZIP = Z.ZipCode

WHERE

P.Active = 1
AND
P.Rent BETWEEN @RentMin AND @RentMax
AND
P.Bedrooms BETWEEN @BedMin AND @BedMax
AND
P.Baths >= @MinBaths
AND
P.Garage >= @MinGarage
AND
P.Acreage >= @Acreage

AND
MA.MemberID = @MemberID
AND
MP.MemberID = @MemberID

AND P.ListDate >
Case @UpdatesOnly
When 0 then '01/01/1900'
When 1 then @LastUpdate
End

END

ELSE
BEGIN

DECLARE @Flag int,
@FeatureID int,
@PropID int,
@Bedrooms tinyint,
@Baths float,
@Garage float,
@DisplayText varchar(75),
@Rent float,
@Address varchar(100),
@Xstreets varchar(100),
@DateAvailable varchar(10),
@Lease tinyint,
@Features Varchar(3500),
@Deposit float,
@Phone1 varchar(12),
@AreaName Varchar(50),
@County Varchar(30),
@City varchar(30),
@State varchar(75)

CREATE TABLE #Prop
(
PropID int,
Bedrooms tinyint,
Baths float,
Garage float,
DisplayText varchar(75),
Rent float,
Address varchar(100),
Xstreets varchar(100),
DateAvailable varchar(10),
Lease tinyint,
Features Varchar(3500),
Deposit float,
Phone1 varchar(12),
AreaName Varchar(50),
County Varchar(30),
City Varchar(30),
State Varchar(75)
)

DECLARE curProp Cursor FORWARD_ONLY for

SELECT Top 100
P.PropID,
P.Bedrooms,
P.Baths,
P.Garage,
PT.PropName,
P.Rent,
P.Address,
P.Xstreets,
'DateAvailable' = CASE
WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!'
ELSE CONVERT(varchar(10),P.DateAvailable, 101)
End,
P.Lease,
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures)
WHEN 0 THEN ''
ELSE ', ' + P.CustomFeatures
End
+ CASE Len(P.Comments)
WHEN 0 THEN ''
ELSE ', ' + P.Comments
End
AS 'Features',
P.Deposit,
Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4),
A.AreaName,
Z.County,
Z.City,
Z.State

FROM Prop P

INNER JOIN Area_Zipcode AZ

ON P.Zip = AZ.Zipcode

INNER JOIN Area_Areas A

ON AZ.AreaID = A.AreaID

INNER JOIN Members_Areas MA

ON A.AreaID = MA.AreaID

INNER JOIN Members_PropTypes MP

ON P.PropType = MP.PropType

INNER JOIN Prop_Types PT

ON P.PropType = PT.PropType

INNER JOIN LandLords L
ON P.LandLordID = L.LandLordID

INNER JOIN ZipCode Z
ON P.ZIP = Z.ZipCode

WHERE

P.Active = 1
AND
P.Rent BETWEEN @RentMin AND @RentMax
AND
P.Bedrooms BETWEEN @BedMin AND @BedMax
AND
P.Baths >= @MinBaths
AND
P.Garage >= @MinGarage
AND
P.Acreage >= @Acreage
AND
MA.MemberID = @MemberID
AND
MP.MemberID = @MemberID

AND P.ListDate >
Case @UpdatesOnly
When 0 then '01/01/1900'
When 1 then @LastUpdate
End

OPEN curProp
FETCH NEXT
FROM curProp

INTO @PropID,
@Bedrooms,
@Baths,
@Garage,
@DisplayText,
@Rent,
@Address,
@Xstreets,
@DateAvailable,
@Lease,
@Features,
@Deposit,
@Phone1,
@AreaName,
@County,
@City,
@State

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Flag = 1
-- print 'PropID = ' + convert(varchar(20),@propID)
DECLARE curMembers Cursor FORWARD_ONLY FOR
SELECT MF.FeatureID

FROM Members_Features as MF

INNER JOIN Members as M

ON MF.MemberID = M.MemberID

WHERE M.MemberID = @MemberID

OPEN curMembers

FETCH NEXT FROM curMembers into @FeatureID

WHILE @@FETCH_STATUS = 0
BEGIN
--print 'FeatureID = ' + convert(varchar(20),@FeatureID)

IF (EXISTS(
SELECT *

FROM Prop_Features

WHERE FeatureID = @FeatureID

AND PropID = @PropID))

FETCH NEXT FROM curMembers INTO @FeatureID
ELSE
BEGIN
SET @Flag = 0
BREAK

END

END -- While

CLOSE curMembers
DEALLOCATE curMembers
IF (@Flag = 1)
-- PRINT 'Success!!! PropID = ' + convert(varchar(20),@PropID)
--PRINT @PropID
INSERT INTO #Prop

VALUES (
@PropID,
@Bedrooms,
@Baths,
@Garage,
@DisplayText,
@Rent,
@Address,
@Xstreets,
@DateAvailable,
@Lease,
@Features,
@Deposit,
@Phone1,
@AreaName,
@County,
@City,
@State
)

FETCH NEXT
FROM curProp

INTO @PropID,
@Bedrooms,
@Baths,
@Garage,
@DisplayText,
@Rent,
@Address,
@Xstreets,
@DateAvailable,
@Lease,
@Features,
@Deposit,
@Phone1,
@AreaName,
@County,
@City,
@State

END -- While
CLOSE curProp
DEALLOCATE curProp

SELECT * FROM #Prop
DROP TABLE #Prop
END

Okey, now. Please notice this part of that code:

dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures)
WHEN 0 THEN ''
ELSE ', ' + P.CustomFeatures
End
+ CASE Len(P.Comments)
WHEN 0 THEN ''
ELSE ', ' + P.Comments
End
AS 'Features',

This function is the cause of the speed problem, methinks. When run alone, it takes 29 seconds to process with the same MemberID that takes about 33 seconds to process completely (the whole Proc). This function simply returns a list of comma delimited features for each property, for displaying on a customer list. Here is that function:

CREATE Function dbo.Prop_Get_Feature_List_Fun (@PropID int)
RETURNS Varchar(3500)

AS
BEGIN

Declare @FeatureList Varchar(3500)

select @FeatureList = Coalesce(@FeatureList + ', ', '' ) + F.FeatureName
FROM
Prop P

INNER JOIN
Prop_Features PF

ON
P.PropID = PF.PropID

INNER JOIN
Features F

ON
PF.FeatureID = F.FeatureID

WHERE

P.PropID = @PropID

ORDER BY

F.FeatureName

Set @FeatureList = isnull(@FeatureList,'Please call for features.')

RETURN @FeatureList
END

Now, I know I probably gave lots more information then needed to solve this issue - but its better to have too much then not enough.

Any help at all in speeding up this function or describing another way to do this would be most appreciative.

Dave

View 5 Replies View Related

What Are Some Good Books On SQL Query Optimization And Other Advanced SQL Topics?

Aug 6, 2007

I graduated from college about three years ago and have been working as a programmer using MS SQL 2000 and even though I've learned a TON in these past three years I know there is tons more I can learn.

Because my company uses Microsoft SQL Server 2000 I'd like to focus on that, but anything ANSI-SQL is perfectly fine.

So... what are some good books on SQL query optimization and other advanced SQL topics?

Thanks in advance...

View 2 Replies View Related

SP's Confusing Me!

Oct 26, 2004

Argh, I'm really stuck with my SP! I'm not really used to them yet.

I have an advertiser who has multiple advertisements. I need to return a list of adverts with thier first advert.

CREATE PROCEDURE selectAdvertiserByCat
(
@category nvarchar(100)
)

AS

select
distinct advert.advertiser_id,
advert.advert_id,
advertiser.fullname
from
advert
join advertiser on advert.advertiser_id = advertiser.advertiser_id
where
category = @category
GO

I can't figure it out!

View 3 Replies View Related

Log Backups Confusing Me

Jul 20, 2004

Please help me out on log backups.

What happens when 2 log backups of the same db happen simultaneously?

Hypothetically:
One Backup Log job run by SQL Server agent(xp_SQLmaint) every 60 minutes & one Log backup run by Backup Exec every 59 minutes.

Is this a dumb idea and will it cause restore problems based on LSN's contained in the 2 separate backup sets?

Thanks
G

View 4 Replies View Related

Should My Where Clause Be Confusing Or NOT???

Oct 19, 2005

heh,heh,heh...cute title, eh? I kill me...

Anyway, The following two select statements return exactly the same results, and utilize exactly the same server resources to run (according to the generated explain execution plans).

The where clause(s) in each are designed to exclude rows that are "empty" according to the designer of the db (don't ask...*rolleyes*).

My question is this...which way would y'all code the select. I have always been "trained" to NOT use negative logic (heh,heh,heh), so originally wrote the select (which, in effect, translates to "get me everything but the empty rows") as:SELECTOSID AS OSID,
[Year] AS QtrYear,
1 AS QuarterNo,
QEPS4 AS EPS,
QSales4 AS Sales,
QInc4 AS Income,
CONVERT(varchar(10), QDate4, 101) AS EarningsReportDate
FROM dbo.RSMsi
WHERE ((QEPS4 <> 0.001 AND QEPS4 <> 0.0)
OR QSales4 <> 0.0
OR QInc4 <> 0.0
OR QDate4 <> '01/01/1900')but in retrospect, I think that's an ugly where clause, and think folks will look at it and think "WTF??? what does THAT mean?". So, I re-wrote the select as if it was looking for the empty rows, then slapped a "NOT" in front of the where clause to negate it in order to STILL say "Get me everything, but NOT the empty rows", as follows:SELECTOSID AS OSID,
[Year] AS QtrYear,
1 AS QuarterNo,
QEPS4 AS EPS,
QSales4 AS Sales,
QInc4 AS Income,
CONVERT(varchar(10), QDate4, 101) AS EarningsReportDate
FROM dbo.RSMsi
WHERE NOT ((QEPS4 = 0.001 OR QEPS4 = 0.0)
AND QSales4 = 0.0
AND QInc4 = 0.0
AND QDate4 = '01/01/1900')whatcha think y'all? It's a style issue, I know...just curious for consensus' sake. Thanks!

View 9 Replies View Related

A Confusing Join (for Me)

Jul 10, 2006

I have 5 tables, the first 4 are responsible for getting wines with all their associated info:

Areas - Regions - Wineries - Wines

The last table is an artwork table, and both wineries and wines can have artwork associated with them. How do I integrate a join on the 'Artwork' table for both wineries and wines, taking into account that sometimes (for a single record) there is art for both winery and wine, sometimes for one, sometimes there is none?

I think I understand the 'ON' statement, for instance:

Wineries LEFT JOIN Artwork
ON Wineries.winery_id = Artwork.artwork_winery_id

'and'

Wines LEFT JOIN Artwork
ON Wines.wine_id = artwork_wine_id

But how do I integrate that into the following SELECT statement?

SELECT
area_id, area_name,
region_id, region_area_id, region_name,
winery_id, winery_region_id, winery_name,
wine_id, wine_winery_id, wine_name,
artwork_id, artwork_name, artwork_wine_id, artwork_winery_id

FROM Areas INNER JOIN
Regions ON Areas.area_id = Regions.region_area_id

INNER JOIN Wineries ON
Regions.region_id = Wineries.winery_region_id

INNER JOIN Wines ON
Wineries.winery_id = Wines.wine_winery_id

ORDER BY winery_name ASC;

- ompadme

View 6 Replies View Related

Confusing Join

Jul 26, 2007

View 7 Replies View Related

Roles Confusing -Need Help

Jul 20, 2007

I have the following scenerio configured in sqlserver -

Windows group Login User Role

domainAuditConfig domainAuditConfig domainAuditConfigUser AuditConfigRole

domainLookupConfig domainLookupConfg domainLookupConfigUser LookupConfigRole

domainuser belongs to both domainAuditConfig and domainLookupConifg windows groups.

When domainuser logs in, he will have both AuditConfigRole and LookupConfigRole roles.

If I reconfigure the above using loginless user, how can the user takes on both roles at the same time?

It seems to me that the user needs to know what role he needs at the time and run the appropriate EXECUTE AS command. Is this true? I can see the benefits of using EXECUTE AS when we are testing our code. But in a production environment, how can the end user know who to EXECUTE AS?

Does it only make sense to use EXECUTE AS from within an application?

Thanks

View 8 Replies View Related

Confusing Data Error

Mar 29, 2007

I have a gridview that is very basic and list name, address, ......., and a URL.
If I have this url in the database I have no problem http://www.lubbockisd.org/earlychildhood/
yet when I place this URL http://www.lubbockisd.org/erlychildhood/stubbs.htm
It triggers this error on my localhost server and my production server.
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
I've made changes to datatypes with no success, made sure my length of data type was set long enough with no success.
Which datatype is best for a URL in sql express?
Any ideas why this happens with such a minor change?

Thanks

Tony

View 13 Replies View Related

Sth Confusing About Table Structure

Oct 27, 2007

Hi,I have a table for storing my picture albums here it is:CREATE TABLE [UserAlbum] ( [ID] [int] IDENTITY (1, 1)    NOT NULL , [UserID] [int] default(0) , [AlbumName] [nvarchar] (100) NULL , [Audience] [int] default(0) , [Date_added] [smalldatetime] NULL , CONSTRAINT PK_CmmNet_userAlbum_ID PRIMARY KEY CLUSTERED ( ID )) ON [PRIMARY]GO
it has a field called "audience" as the name defines i want to choose who will see my albumsit is integer and as follows:0 value=everyone1=friends only2=private
as i started to write a query for it i came into this problem: for exampleif i write SELECT * FROM UserAlbum where   audience=1 then only friends can see the infowhat happens to everyone with value of 0?when i set it to 0, friends should also be able to see the info because it is set for everybody
i noticed i should use "in cluase" like thisSELECT * FROM UserAlbum where   audience in ('0','1') > so in this way both everyone and also friends can see the info
or maybe i should add 3 different fields for private,public,friends
it seems to be simple but at the same time harddoes anyone have a suggestion?

View 1 Replies View Related

Problem 1/2 DataBind, 1/2 T-SQL -- So Confusing

Jun 14, 2006

Hey everyone,I've got a problem and I can't tell if it's in the stored procedure I'm calling, or it's with the Binding it to the datalist.The error is Syntax error converting datetime from character string.The code it crashes on is:Line 100: //Bind data
Line 101: dg_details.DataSource = reader;
Line 102: dg_details.DataBind();
Line 103: dg_details.Visible = true;
and my store procedure I'm calling is: ALTER PROCEDURE coglej.GetEventsForDate
@eventdate datetime,
@eventowner varchar (50)

AS

IF DATEDIFF(ww,GetDate(),@eventdate ) > 2
BEGIN
PRINT 'Getting EB event price.'
SELECT dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip,
dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM dbo.events INNER JOIN
dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE (dbo.events.event_date = CONVERT(datetime, '@eventdate')) AND (dbo.skus.owner = @eventowner) AND (dbo.skus.price_type = 'Early')

END
ELSE
BEGIN
PRINT 'Getting regular event price.'
SELECT dbo.events.event_date, dbo.locations.city, dbo.locations.state, dbo.locations.name, dbo.locations.address1, dbo.locations.address2, dbo.locations.zip,
dbo.skus.sku_id, dbo.skus.event_type, dbo.skus.price, dbo.skus.slots_available
FROM dbo.events INNER JOIN
dbo.locations ON dbo.events.location_id = dbo.locations.location_id INNER JOIN
dbo.skus ON dbo.events.sku_id = dbo.skus.sku_id
WHERE (dbo.events.event_date = CONVERT(datetime, '@eventdate')) AND (dbo.skus.owner = '@eventowner') AND (dbo.skus.price_type = 'Regular')

ENDWhat's cause the crash are the quotes around @eventdate. However, without them, it doesn't return all the rows (if any) and if I have them in, I get this error. The odd thing is that when I copy and paste one of the SELECT statements into the VWD sql window and replace the variables with the same values I'm using at runtime, everything runs fine.Anyone have any ideas?Thanks in advance,--Joel

View 7 Replies View Related

Confusing Starting Up Database ....

Jul 7, 2003

Hi,
I have a SQL2k STD with SP3 installed.
Currently, only one database db_XYZ is there. The server has been started up since 07/03/2003 16:00 pm. But in the sql server current log: "Starting up database 'db_XYZ'." info is spreading from that time (16:00 pm) to this morning 10:00am at an interval from every 1 minute to 4 minutes.

Why need to 'Starting up' this database so much times?
This reminds me that this 'Starting up' is kind of 'abused' in many other servers. Any idea?

thanks
David

View 9 Replies View Related

ActiveX, DTS, And Other Confusing Instructions

Feb 15, 2005

Hi everybody.

I've been looking on-line for about a day now and can't find what I'm looking for. I don't know much about ActiveX and even less about using it in a DTS package. If you guys could help me out be telling me what to use/give a few minimal examples I can figure the rest out. I just can't find enough info on the web to do what I want.

I'll describe what I've got, what I'm trying to do with it, and we'll see what you guys think.

What I've got:
A table that stores To, Subject, MsgText, MsgHTML, and FileAttachment locations.

What I'd Like to do:
Write out the info in the table into a text file and then copy it to the Pickup folder to be sent. Now, I know DTS has an e-mail task, but the Dynaprops/e-mail task are being flaky. The information in the E-Mail doesn't always seem to update like I'd like it.

How I was planning on doing it:
Write an ActiveX script to Create the E-mail, store it in the DB, and then Write it out to the txt file to be sent.

I think this'd be a good way to do it, but then again I could be wrong. Maybe use something else? Please help. I could use it.

Your help is more than appreciated.

Thanks!
-Me

View 2 Replies View Related

Really Confusing Server Problem!

Aug 25, 2004

Ok, i have been trying to restore the company helpdesk server since our move at the start of the year and after it was sitting doing nothing for a good six months or so.

I finally got the server going (after extensive messing about on our test box), but i'll set out the scenario for you:

Test Server: XW4000 (win2k Advanced Server + SQL Server 2k)
Main Helpdesk Server :BK_MAN5_INV (NT4 SP6 + SQL Server 2k)
Client Machines: BK_CI_03006 and BK_CI_03010

Now for the problem. Both the test server and the main helpdesk server have been configured in the exact same way, and BK_CI_03006 has no problems connecting to either server. However, BK_CI_03010 can only connect to the test server and not the main helpdesk server.

We are planning on removing the test server as soon as this is sorted, but i have no idea why there is this problem with the machine.

If anyone can provide any input on this at all, i would be really grateful.

Cheers

Alan

View 10 Replies View Related

Excel Field Is Confusing Me!

Apr 28, 2008

I have a spreadheet that has a column that is formatted by default to General and contains alpha numeric data.

1008
1008G
1008H

etc.

When I open the spreadsheet any field in the coumn that is purely numeric is right justified by default and the alpha numeric ones are left justified.

When I import the data the fields containing numeric only data come thru as NULL? But the alpha numeric comes thru OK.

The destination field in my table in varchar(20)

I have tried various formats including Text in the spreadsheet but I still get the numeric fields coming thru as NULL.

Any points please?

Thanks

View 12 Replies View Related

Confusing SQL Date Coding

Jun 22, 2007

I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:

DECODE(to_char(M.My_Table, 'MM'),
'04', 'Apr', '05', 'May', '06', 'Jun', '07',
'Jul', '08', 'Aug', '09', 'Sep', '10', 'Oct',
'11', 'Nov', '12', 'Dec', '01', 'Jan', '02',
'Feb', '03', 'Mar'),
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Q1', '05', 'Q1', '06', 'Q1',
'07', 'Q2', '08', 'Q2', '09', 'Q2',
'10', 'Q3', '11', 'Q3', '12', 'Q3',
'01', 'Q4', '02', 'Q4', '03', 'Q4'),

How does one convert something like this in MS SQL?

View 15 Replies View Related

Add A Login To A Database Role (was Very Confusing)

Apr 4, 2006

This stuff makes no sense what so ever,

In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"

There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."

I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:S

View 9 Replies View Related

Access Synchronizer Confusing And Sketchy

Jan 14, 2007

After downloading and installing this tool, I'm confused. The documentation refers to a wizard, which I can't find in the Start menu.

Development team: Where is this wizard? This is a great idea that I'm certain many people can benefit from--- please make some better documentation, and give us a product that does what the documentation says.

Michael Reinhart

View 3 Replies View Related

Help With A Confusing And Advanced Query - If Exsists?