Advanced SQL Query Problem ...
Jul 20, 2005
Dear Expert,
I have 2 tables as the following,
Table : Product
Column : ID, Code, Name
Table : Trx
Column : Trx_ID, Trx_Date, Product_ID
How can perform the following query in MSSQL or is there any other way
to perform the same query ??? For you info I can do this in Sybase and
it's work fine. Thank you.
================================================== ==========
Select Trx.Product_ID,
(Select Produc.Code
From Product
Where Product.ID = Trx.Product_ID) as Product_Code
From Trx
Where Product_Code = '0001'
================================================== ==========
Regards,
Chen Fock Siong
2004-05-27
View 4 Replies
ADVERTISEMENT
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
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
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
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
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
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
Nov 14, 2003
Ok,
I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.
Dbo.Products – This table holds product names and information.
Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID
Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.
Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID
Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.
Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are “required� (no product will be returned that does not have what is in the list).
I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.
If any of you know how to do the above with a subquery of some kind, please let me know.
Dave Larson
View 1 Replies
View Related
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
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
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
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
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
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
Oct 19, 2005
Here's one for one of you SQL genuises out there. I really appreciate any help that I can get. There are several steps to this.Step 1:I have a database that holds timesheet records for users. These records are by week. I need a query that will return the username of the people that have entered 0 time for all of their timesheets in the database.Step 2:I need a query that will delete and timesheets that have 0 time entered after the last timesheet with time entered. If they entered time in week 3 but have 0 time for week 4 and 5 and then are withdrawn at week 5 I need to remove timesheets for week 4 and 5. Note, they could have entered 0 for week 2 as long as week three has time entered.Step 3: - THIS IS THE MONSTER-Since the timesheets are by week, I need to find the date that the last time was entered. The timesheet has a start-date then suntime, montime, tuetime, wedtime etc. Sunday would equal the week start-date. So, if time was last entered on Friday, the date that I'm looking for is the start-date + 5. My problem is, How do I go about doing this? I've been trying to do it all in a query to no avail.I really appreciate any assistance that anyone can offer.Thank you,Still learning........-Scott
View 4 Replies
View Related
Oct 18, 2007
Hi
I'm new in Reporting Services and I have a task to solve. I wonder how to do it in Reporting Services.
For example I have few records:
ID
Year
Q1
Q2
Q3
Q4
1
2005
0
0
10
20
2
2006
20
20
35
35
3
2007
40
45
45
10 Now I create two parameters:
Q and Year
When I run this report I want to choose for example Year = 2006, and Q = 3
When I choose these values I need to calculate a field in report like this:
needed_sum = 0(Q1,year=2005) + 0(Q2,year=2005) + 10(Q3,year=2005) + 20(Q4,year=2005) + 20(Q1,year=2006) + 20(Q2,year=2006) + 35(Q3,year=2006)
I need to declare variables, like it is calculating on a section in other reports systems. Here I found only code for report where I can write some functions.
Anyway..
Did someone knows how to solve this problematic task??
Regards
Bashay
View 4 Replies
View Related
Aug 15, 2006
Hi, Using Visual Web Developer and SQL Express, (coding in Csharp) I want to add a
advanced search facility to my site. Problem is I do not know how to begin.
The website is a business directory and displays company names and services that they provide.
Here is where I am at....
So far I have made one query which allows users to search the database by "town" such as if they enter "London" in the text box, it returns the relevant records into a gridview.
Rather than have a seperate text box for each query, I want to provide an advanced search box option so users can define their search and query the database by various criterias, such as geography, sector, name, number of employees in companies etc.
I have seen compact advanced search boxes on many sites!.
Replies greatly appreciated.
prontonet
View 8 Replies
View Related
Mar 5, 2001
Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:
[Books]
ID
Title
[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)
[Authors]
ID
Firstname
Lastname
AuthorOrder
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Thanks for any help.
View 1 Replies
View Related
Nov 29, 2005
Dear All,
Can you suggest a good book or set of websites which have advanced SQL tutorials for SQL server? Got the basics but looking to build on my knowledge.
...cause knowledge is power...
View 3 Replies
View Related
Mar 22, 2006
Keri writes "I am still a SQL newbe, but have learned a lot in the past year. I am not creating databases, nor maintaining our SQL Server 2000. I am a report writer and use Query Analyzer for the base of my reports. I use Actuate to develop the reports. My question is, i am interested in getting a book that covers more advanced querying. I have the book The Guru's Guide to Transact-SQL, but am intested in other books to further my growth and education in SQL. Can you suggest some books that deal with queries-advanced primarily since i really am not designing or maintaining the DB. Thanks for any information you can give.
Sincerely,
Keri Taylor"
View 4 Replies
View Related
May 11, 2006
Here's a challenge for one of those database experts out there...
I want to join two tables in a Left Join format, with the first table being the one referencing the second table. Easy enough right? Well, I also want to be able to reference this second table from 3 different fields.
For example:
My tables
#1: video
id, description ...etc... award1, award2, award3
#2: award
id, description, image, etc...
As you can see, I want to be able to award more than 1 award from the "award" table to each video in the "video" table. I then want to be able to assign a variable to each field from the result of the SQL query so I can display all the information on one page for every video with every combination of possible awards.
I also, in my SQL Query, want a WHERE clause:
WHERE video.id = $vidid (a variable from a previous page so this page only displays the video I want)
My mind says surely this must be possible, but I have no idea how to go about it. Can anyone give me any hints/help/pointers/advice on this? Even if this requires two SQL queries, I may be able to work with that, but I need to know. If I'm posting at the wrong place, or nobody here knows how to do this, then I would also really appreciate being pointed in the right direction.
Thanks in advance to anyone who even reads this and thinks over it. This is for a free online fan site http://www.lostvideo.net/ that I'm helping with some redesigns.
View 2 Replies
View Related
Jul 20, 2005
I have been able to get several basic databases to function both in playingaround and functional ones on the web but they have all been pretty simple.I am now trying to develop a database for the web using Access. What I amreally needing help with is how to actually reduce the number of record setsthat I think i am going to need. Here is what I am trying to do.I am having people sign up and create teams. So you come to the site and yousee Team 1, Team 2... Team 8. (These numbers will later be replaced by realnames given by the team captains and replaced in the database, this will bedone by the captain through the web)You pick a team you want to be on, enterthe info and you are placed on that team (in the database) I have two tablesin the database TeamRoster (everyone on all of the teams) and Teams (Listthe captains and the names of their team)So when I query the database to fill the tables I am confused as to how Ishould go about the query. As I see it I need to build a RS (Record Set) foreach team to get the name of the team for the appropriate table, thenpopulate it with the players for that team (repeating region) if there areeight teams, that means 16 RS. This does not seem right.Am I making any sense?Thanks for trying to understand all of this.Houston
View 1 Replies
View Related
Aug 3, 2007
I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:
Table X
ID
-------
2,1,4,
2,5,
1,
3,1,2,4,
ect...
whereas the ID field in Table Y contains one ID in each row EX:
Table Y
ID Color
------- -------
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...
Is there a way to join these two tables together? I need the output to be...
ID Color
------ -------
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
ect...
View 8 Replies
View Related
Nov 7, 2007
When one requirement says that MS SQL Server 2005 with Advanced Services is required, what does it mean by "Advanced Services"? Doesn't the full version of SQL2005 include all the advanced services? What are advanced services? I've seen the Express version with Advanced Services but this is the full version I'm talking about.
Thank you all in advance.
View 5 Replies
View Related
Oct 5, 2006
I have a table full of Latitudes, Longitudes, address, customername, etc. , I need to grab some input(Latitude, Longitude, range) from the user. So now I have a source lat, long(user) and destination lat, long(rows in dbase). I need to take the 2 points and compute a distance from the user given lat, long to every lat, long in the database and check that distance againt the range given from the user. If the distance is below the range, I need to put that row into a temp table and return the temp table at the end of the stored proc. As of right now I am completely lost and need some guidance. I would also like to be able to add the computed distance to a table. Here is the function and stored procedure i have so far...ALTER PROCEDURE [dbo].[sp_getDistance] @srcLat numeric(18,6), @srcLong numeric(18,6), @range intASBEGIN SET NOCOUNT ON; SELECT * FROM dbo.PL_CustomerGeoCode cg WHERE dbo.fn_computeDistance(@srcLat, cg.geocodeLat, @srcLong, cg.geocodeLong) < @rangeEND CREATE FUNCTION fn_computeDistance ( -- Add the parameters for the function here @lat1 numeric(18,6), @lat2 numeric(18,6), @long1 numeric(18,6), @long2 numeric(18,6))RETURNS numeric(18,6)ASBEGIN -- Declare the return variable here DECLARE @dist numeric(18,6) IF ((@lat1 = @lat2) AND (@long1 = @long2)) SELECT @dist = 0.0 ELSE IF (((sin(@lat1)*sin(@lat2))+(cos(@lat1)*cos(@lat2)*cos(@long1-@long2)))) > 1.0 SELECT @dist = 3963.1*acos(1.0) ELSE SELECT @dist = 3963.1*acos((sin(@lat1)*sin(@lat2))+(cos(@lat1)*cos(@lat2)*cos(@long1-@long2))) -- Return the result of the function RETURN @dist Thanks, Kyle
View 1 Replies
View Related
Mar 13, 2007
Hello, I am trying to bind a sqldatasource control to the gridview.
I have selected the sqldatasource control and specified the connection string, on configure Select statement page under advanced options both the check marks
Generate INSERT, UPDATE and DELETE Statements
Use Optimistic Concurrency
are disabled for me.I have a proper SQL Server database not an express data base, how do i get to generate the InserCommand, EditCommand etc
Any help would be great .. relatively new here
thanks
View 2 Replies
View Related
Apr 19, 2007
Hi!
a) PROBLEM 1:
I have set up my main database like this (of course I'm showing an abbreviation):
company - int (identity)name - nchar
I have several companies stored in the previous table. Now I have another table with messages:
company - int (related with the company of the previous table)messageID - int (identity)contents - nchar
company and messageID are the main key of this table. I want to set the messageID column to change automatically. Since I declared it as identity it is working fine, but I was looking to start it on 0 on every new company:
Company messageID0 00 10 21 0 <- Here the company changed, so the messageID resets1 11 21 32 0 <- Again2 1
Any suggestions?
b) PROBLEM 2:I have my database stored locally on my computer. When I finished working with the database it has a lot of data for testing. I want to upload the database to my hosting provider or to my customer's. But the identity columns keep incrementing since the last value of my tests, so it's kind of annoying to see values as: 1250, 223, etc. when I expect to see 0,1, 2 and so on. Also, for receipts this is a very important issue.
How can I reset the identity fields?
Thank you very much for your attention and help.
CT
View 3 Replies
View Related
May 13, 2007
Advanced SQL generations options:
generate INSERT, UPDATE, and DELETE statements is all greyed out?
in my sql data source control.?
I have made a brand new instance with sql server management express....have I missed something?
View 4 Replies
View Related
Jan 9, 2008
Hi There,
I know this might be slighty off topic, but (apart from the CPU, RAM & DB size limits) does anyone know if Express SQL server has any performance / concurrent connection restrictions when compared to SQL 2005 Standard?
Many thanks
Ben
View 1 Replies
View Related
May 25, 2004
Hello,
(Excuse my english, i'm a french man)
I want to make a stored procedure on that model :
CREATE Procedure SGCP_GetEleveArgs
(
@Arg1 nVarChar,
@Val1 nVarChar
)
AS
SELECT *
FROM Eleve
WHERE @Arg1 = @Val1
So you understand that i want to pass my argument (@Arg1) as a field of a column.
but it gives :
WHERE 'Nom' = 'Dupont'
but i want to have :
WHERE Nom = Dupont
for it to work.
Have you an idea to solve that problem ?
Thanx
View 5 Replies
View Related
May 31, 2006
Hi,
I would like exploring Reporting Services associated with SQL Express Edition so i tried installing "SQLEXPR_ADV_.EXE" the package available on download SQL site, but after having selected Reporting Services option I've got an error explaining that component "sqlserver2005_bc.msi" is to be changed.
As this component is part of "SQLEXPR_ADV_.EXE" package is there anybody knowing an alternative solution ?
View 1 Replies
View Related
Feb 27, 2001
Has anybody used the "Use fast load" or "Table lock" options in the advanced DTS package properties ? These are supposed to be in the Advanced tab of the Data Transformation Properties dialog box. I cannot seen to find them. I want to set them in order to have the load execute in nonlogged mode. Any help would be very much appreciated.
Thanks in advance.
View 2 Replies
View Related
Mar 28, 2006
Hello, Can anyone direct me to some sample charts created with 2000 Reporting Services? Looking for the advanced capabilities and quality of the charts.
Thanks
SactoJoe
View 3 Replies
View Related
