Assistance Developing Query

Jul 23, 2005

Good Day;

I would appreciate assistance developing a query that I haven't been
able to develop without using a second table. I wish to count the
number of records that are still open on the first of each month.
Each record has an open date and a close date or the close date is
null i.e., the record is not yet closed. I've previously beaten this
by building a table, simply a list of the dates for the first of each
month for the next ten years or so, and then selecting values based
upon a date selected from that table. However I'd be happier if I
could do it without the second table. I'd be prepared to accept the
Min(Date) for each month as being the first of the month.

I've included some DDL statements to build and populate the table if
that helps. Since the selection is rather small and all the open
dates are very close together I think the result will be simply a
decreasing count from the month the first record is opened till today.

A pseudo code select statement might look like

Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
FROM DetailT
Where DateReceived > DateOfInterest or DateReceived is Null and
DateOpened < DateOfInterest
Group by Min(DateOpened)
Order by Min(DateOpened)

I hope I've explained it sufficiently well.

CREATE TABLE [dbo].[DetailT] (
[Autonum] [int] IDENTITY (1, 1) NOT NULL ,
[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOpened] [smalldatetime] NOT NULL ,
[DateReceived] [smalldatetime] NULL ,

)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('D01197', '06/04/2005 10:13')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('J01202', '06/04/2005 10:31')
Insert into DetailT (QDNumber, DateOpened)
VALUES('G01193', '06/04/2005 10:32')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01164', '06/04/2005 10:35')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01162', '06/04/2005 10:48')
Insert into DetailT (QDNumber, DateOpened)
VALUES('F01124', '06/04/2005 10:59')
Insert into DetailT (QDNumber, DateOpened)
VALUES('H01147', '06/04/2005 11:01')
Insert into DetailT (QDNumber, DateOpened)
VALUES('S15068', '06/04/2005 11:10')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('C03394', '06/04/2005 11:51')

If you made it this far thank you for your patience. Any help would be
appreciated.

Thank you.

Bill

View 5 Replies


ADVERTISEMENT

Help Developing SQL Query

Jun 22, 2001

I need to create a query that will pull all of the records that are statused as 'OUT' from my RECORDS table and then get the last (chronologically) 'OUT' record from my IN/OUT table for each record it pulled from RECORDS.

I can get the records out of RECORDS without any problems but I don't know how to set up a query that will pull the last chronological 'OUT' record from my IN/OUT table. Is there a way to do this in SQL?

Thank you in advance for any help.

View 1 Replies View Related

SQL Query Assistance

Jan 14, 2008

I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!

View 2 Replies View Related

Need Query Assistance

Jul 28, 2005

First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...

View 1 Replies View Related

Query Assistance

Nov 30, 2005

Hi,

I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:


ClaimNumber LineNumber SequenceNumber
abc123 1 1
abc123 2 2
abc123 3 3
def321 5 1
def321 6 2
ghi456 2 1
jkl789 3 1
jkl789 4 2


So...
SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable


Is there any way to do this?


Thanks,
Dennis

View 4 Replies View Related

Query Assistance

Mar 5, 2007

I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.

sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,

Any help here would be hotness!

Thanks!

View 10 Replies View Related

Query Assistance

Mar 8, 2006

I am trying to pull some "notes" from a sql database.....the notes thatare put into the database come via the web and the user is entering itfor a certain task. they are stored in their own table and field andget assigned and incremental ID #.I want to be able to pull up the latest entry to the task, not all ofthe notes just the latest one.. The entry does get a timestamp in thefield so I am thinking I might be able to look at that fieldsomehow.... Right now my query shows all notes / entries for the task.I am an intermediate sql query guy so I hopefully expained enough toget assistance.Let me know if you need to know more.

View 2 Replies View Related

Assistance With Query

Nov 12, 2007

I am trying to get a running total. I need the query to reset the running total for each year/id. Below is the sample query. Any help would be greatly appreciated.

Code:
CREATE TABLE #valueset (k1 int, date datetime, groupByThis nvarchar(50), c1 int)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13024, '09/14/2007', '2007', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/15/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '10/13/2006', '2006', 1)
INSERT #valueset (k1, date, groupbythis, c1) VALUES (13025, '09/14/2007', '2007', 2)

SELECT v.k1, v.date, v.groupByThis, v.c1, RunningTotal=SUM(a.c1)
FROM ( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) v

CROSS JOIN

( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank
FROM #valueset ) a
WHERE a.Rank <= v.Rank
AND a.groupByThis = v.groupByThis
GROUP BY v.k1, v.date, v.groupByThis, v.c1
ORDER BY v.groupByThis, v.date

Drop Table #valueset


Expected Results:
k1 date groupbythis c1 RunningTotal
13025 2006-09-15 00:00:00.000 2006 1 1
13025 2006-10-13 00:00:00.000 2006 1 2
13024 2007-09-14 00:00:00.000 2007 1 1
13025 2007-09-14 00:00:00.000 2007 2 2
13025 2007-11-09 00:00:00.000 2007 1 3

I am almost there - any help would be great. I need to reset the running total after every "groupbythis" for every "k1"

Thanks.

View 1 Replies View Related

Query Assistance.

Jun 25, 2007

I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.

Source table.








Address
ID
Workstation

Test-a
1
WS1

Test-b
2
WS1

Test-a
5
WS2

Test-d
3
WS2

Test-b
7
WS2

I am trying to write a query that will display this result into Excel.











Address

Duplicate

WS1

WS2

Test-a


Yes


1


5

Test-b


Yes


2


7

Test-d


No








Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation.
I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.

Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.

I appreciate any help I can get on this.

View 7 Replies View Related

Need Some Query Assistance

Mar 10, 2008



Hello,

I have two tables, USER and ROTATION. What I would like to display is the Maximum Start_Date and Maximum End_Date from the ROTATION Table along with the First_Name and Last_Name that is associated with that max entry.

When I just create a select statement that asks for the max value of the Start_Date and End_Date, I get the value I see. However when I try to add the First_Name and Last_Name to the mix, I get the Max Start and End Date Values of all the people in the User Table. I only want one result returned. I'm probably missing something very simple here. Any assistance would be appreciated.

The SQL code I am currently using:




Code SnippetSELECT [USER].FIRST_NAME, [USER].LAST_NAME, MAX(ROTATION.ONCALL_START_DATE) AS Expr1, MAX(ROTATION.ONCALL_END_DATE) AS Expr2
FROM ([USER] INNER JOIN
ROTATION ON [USER].USER_ID = ROTATION.USER_ID)
GROUP BY [USER].FIRST_NAME, [USER].LAST_NAME






Example of my desired result:

First_Name Last_Name OnCall_Start_Date OnCall_End_Date
John Doe 8/10/08 8/17/08

John Doe should be the only result returned because he would have the highest OnCall_Start and OnCall_End in the ROTATION Table.

View 4 Replies View Related

Need Assistance With A Select Query.

Feb 27, 2006

I am new to SQL and aftering reading my SQL For Dummies book, I still am unsure how to accomplish this task.

The table I need to query contains resident census information. There are multiple rows for each resident. I need to determine if the resident is still active, so I only need to read the last row for each resident to make this determination.

What method can I use to read only the last row for the resident. Here is what I tried previously, but it returns multiple rows for each resident.

select a.firm_id_code as FacAddOnNum,
f.shortname as FacName,
left(a.resident_code,6) as ResidentCode,
(r.res_first_name + ' ' + r.res_last_name) as ResidentName,
a.LastDate,
a.cens_trans_type

from (select firm_id_code,
resident_code,
max(dt_cens_trans) as LastDate,
cens_trans_type
from arrescensus
where firm_id_code = @FacId
group by firm_id_code, resident_code, dt_cens_trans, cens_trans_type) as a
join corp_info.dbo.facilityinfo as f on (a.firm_id_code = f.addonnum)
join arresidents as r on ((r.firm_id_code = a.firm_id_code) and (r.resident_code = a.resident_code))


Any help with this is greatly appreciated!

View 6 Replies View Related

Some Assistance With Query Needed -_-

Oct 12, 2004

I've got a website with dynamic content, each page (subject) got an ID. On every page there can be a number of links. These are either links to internal other pages on that website or external links.

For the internal links the only thing I need is the ID and Title of that page. Those can be found in the Tbl_subjects. As for external links I need ID, Title and URL which can be found in the Tbl_ext_links.

I've got a table named Tbl_linkboxes with:
- a Subject ID which means that this link belongs on this subject page.
- Link ID which is either an ID from Tbl_subjects or Tbl_ext_links
- External a boolean column to indicate if the Link ID refers to the Subject table or the External links table

There's basically 2 questions:
1) How to make this work? I've got a query below as feeble attempt
2) Should I really really really consider to use 2 columns for IDs and removing the External boolean. And simply setting one of those fields in the columns to >0 while the other is 0.

Okay, here's my attempt

PHP Code:




 SELECT    s.Sub_id, s.Link_id
    (l.external IS FALSE, (SELECT Title FROM Tbl_subjects), (SELECT Title,URL FROM Tbl_ext_links)
FROM    Tbl_subjects s
WHERE    s.Sub_id = <some id> 






Not sure if I should work with IIF here to make it work or something else. I'm almost tempted to kick the boolean column overboard and introduce a JOINT on both columns then, one for external link ids and other for internal page ids.

Amazing how long one can stare at a query and not being able to get it right

View 14 Replies View Related

Update Query Assistance

Mar 7, 2007

I'm attempting to write and update query. So far I have written the following:
update vwDISTCITY_TAXCODE
set tax_code='04'
where DIST_CITY='04'AND year_id=2007 AND frozen_id=0 AND p_id=93549 AND total_taxes=isnull

The last part of the query "total_taxes=isnull" is where the problem lies. Essentially I want to say if all of the other things are true and there is a null value in the total_taxes column, then I I want to set the tax_code to '04'. However how would I phrase the last part correctly?

Thanks!

-Steve H.

View 4 Replies View Related

Assistance Building A Query...

Jun 15, 2006

I am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.

View 4 Replies View Related

Monster Query Assistance

Mar 5, 2007

I realize this query is inherently incorrect, but my issue is mainly
syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong.
I want to ensure that a.order_id is not in the settlement table. So I
was thinking something along the lines of "WHEN a.order_id not in
(select order_id from settlement)" which I know will cause a slower
response time, but I'm willing to deal with it. In any case, that
syntax doesn't appear to work.

sum(
CASE
WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and (
CASE
WHEN a.order_id <> b.order_id THEN
a.transaction_date
ELSE
b.delivery_date
END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN
a.amount
END) earn_amount_rtp_curr,

Any help here would be hotness!

Thanks!

View 9 Replies View Related

Query Assistance Combining Columns Into One

Oct 5, 2006

I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC  

View 10 Replies View Related

SQL Query Assistance. MAX Causing Issue

May 23, 2007

I got some help on here before with building my query. I thought this was working fine but it turns out when there are multiple records for a column type, it only grabs the first one. I need to get all records. Is there an alternative to MAX? I needed to structure it like this because I needed to return each row as a column and this was the way suggessted before.
My query:SELECT TOP (100) PERCENT PRODUCT_NUMBER, PRODUCT_NAME,
MAX(CASE WHEN ColumnName = 'Federal Specification Number' THEN TheValue ELSE NULL END) AS [Federal Specification Number]FROM (SELECT dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID AS ProductID, dbo.SHARED_FEATURE_VALUES.FEATURE_TEXT_VALUE AS TheValue,
dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE AS ColumnName, dbo.PRODUCTS.PRODUCT_NUMBER, dbo.PRODUCTS.PRODUCT_NAME
FROM dbo.PRODUCT_FEATURE_VALUES INNER JOINdbo.SHARED_FEATURE_TYPES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_TYPE_ID = dbo.SHARED_FEATURE_TYPES.FEATURE_TYPE_ID INNER JOINdbo.SHARED_FEATURE_VALUES ON
dbo.PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = dbo.SHARED_FEATURE_VALUES.FEATURE_VALUE_ID INNER JOINdbo.PRODUCTS ON dbo.PRODUCT_FEATURE_VALUES.PRODUCT_ID = dbo.PRODUCTS.PRODUCT_ID
UNIONSELECT dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID AS ProductID, ISNULL(dbo.EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE,
dbo.EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUE) AS TheValue, dbo.EXTENDED_ATTRIBUTES.COLUMN_NAME AS ColumnName, PRODUCTS_1.PRODUCT_NUMBER, PRODUCTS_1.PRODUCT_NAME
FROM dbo.EXTENDED_ATTRIBUTE_VALUES INNER JOINdbo.EXTENDED_ATTRIBUTES ON
dbo.EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = dbo.EXTENDED_ATTRIBUTES.EXT_ATT_ID INNER JOIN
dbo.PRODUCTS AS PRODUCTS_1 ON dbo.EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID = PRODUCTS_1.PRODUCT_ID) AS t1
WHERE PRODUCT_NUMBER = '02083'
GROUP BY PRODUCT_NUMBER, PRODUCT_NAME
ORDER BY PRODUCT_NUMBER
 
This returns:
Product_Number    Product_Name                                 Federal Specification Number 
02083                   Di-Electric Grease, 10.5 Wt Oz          FDZ-CFR-21-178.3570
There is another record for Federal Specification Number I need to return as well. If I change to MIN, it gets the other record. Anyway I can get both?

View 15 Replies View Related

Page 2 - Some Assistance With Query Needed -_-

Oct 26, 2004

Code:

select *
from linkboxes l
left outer
join subjects s
on l.sub_link_id
= s.sub_id
left outer
join external_links e
on l.ext_link_id
= e.ext_id
where l.sub_id = subid

View 4 Replies View Related

Query Optimization Assistance W/ Joins

Jul 20, 2005

I have a couple of tables that look like this (not excactly but closeenough):[Contact]id intfname varchar(50)lname varchar(50)[ContactPhoneNumber]id intnumber varchar(15)ext varchar(6)contact_id intpriority int (indicates primary, secondary... numbers)type int (indicates type of number: fax, cell, land line)I'm looking for a more optimized method of displaying this informationin this format:fname, primary business phoneUsing a derived column like this works, but seems to be slow with manyrecords, despite tuning indexes:SELECT c.fname AS [First Name],( SELECT TOP 1numberFROM ContactPhoneNumber cpnWHERE cpn.type = 1AND cpn.contact_id = c.idORDER BY cpn.priority) AS NumberFROM Contact cI can get the same results using a join, and it's a lot faster. But I'mnot sure how to select only the primary phone number this way...basically the first phone number whose priority is either NULL or 1.Any suggestions?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View 5 Replies View Related

Ole Db Source Query Assistance Requested

Sep 15, 2006

my query is below:

"SELECT * FROM " + @[User:: TableName] +
" WHERE OrderDate = " + "'" + @[User::dTrxnDate] + "'"

needless to say, the ole db source editor is giving me syntax errors. the data type of TableName is string. the data type of dTrxnDate is DateTime.

can someone please help me resolve the syntax errors?

thanks in advance.

View 8 Replies View Related

Assistance With Lookup Style Query

Sep 28, 2007

I have two tables that are pretty standard I think. Table a has product descriptions and one of those fields is a price. I have a second table that contains fees based on the price. so table B looks like this.

min max fee
0 19.99 2.50
20.00 49.99 3.50
50.00 1000.00 5.50

the max ends up around a million just to be sure we cover all prices. my problem is this I need a very efficient query to
poll all the values from A and the correct value from B. All the attempts I have made are not working. I also have to make sure this query is extremely efficient as it is executed several times a minute. If there is a better way in general to structure this I am all ears. I wanted to avoid placing the fees in the product table as the fees are updated often, but if its the only way to get this to work, then that is where I will go.

Thanks everyone

View 7 Replies View Related

Assistance Requested Building View Query

Apr 4, 2008

The View SQL below takes 1:50 minutes to execute. I am looking for a way to improve processing time and keep the view select statements as simple as possible.

One part of the view that could be restructured is the lookup for the current term (TERM_TBL - bolded). When I hard code the current term my execution time drops to about 13 seconds

There are three possible values for the current term; one for each of our three acedemic careers (UGRD, CONT, & EXED). Could this information be looked up once and then each enrollment selected based upon the appropriate academic career current term value? If so, how would it be restructured? Currently, the lookup is done for every enrollment record.

There are no common fields between the NAMES table and the TERMS_TBL.





Code Snippet

SELECT A.EMPLID, A.LAST_NAME_SRCH, A.FIRST_NAME, A.MIDDLE_NAME, A.LAST_NAME

FROM NAMES A

WHERE A.EMPLID IN (


SELECT DISTINCT B.EMPLID

FROM STDNT_ENRL B

WHERE B.INSTITUTION = 'AAAAA'

AND B.STRM >= (

SELECT DISTINCT T.STRM
FROM TERM_TBL T
WHERE T.INSTITUTION = B.INSTITUTION
AND T.ACAD_CAREER = B.ACAD_CAREER
AND T.TERM_BEGIN_DT <= GETDATE()
AND T.TERM_END_DT >= GETDATE())

AND A.EMPLID NOT IN (

SELECT DISTINCT C.EMPLID
FROM SRVC_IND_DATA C
WHERE C.INSTITUTION = 'AAAAA'
AND C.SRVC_IND_CD = 'DPL' )

AND A.NAME_TYPE = 'PRI'

AND A.EFFDT = (

SELECT MAX(D.EFFDT)

FROM NAMES D

WHERE D.EMPLID = A.EMPLID

AND D.NAME_TYPE = 'PRI'

AND D.EFFDT <= GETDATE() )


Any suggestions will be gladly accepted. Keep in mind that the new and much improved must be legal within the context of a view select statement.


Thanks,
Steve


View 3 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

Assistance With Insertering Query Results Into A Single Table Row.

Feb 14, 2008

I have a table that was created as follows
create table call_summary (

row_date smalldatetime,
[700] int,
[730] int,
[800] int,
[900] int)


I have a query that returns the following
date interval (int) calls (int)
2008-01-07 00:00:00 700 0
2008-01-07 00:00:00 730 0
2008-01-07 00:00:00 800 0
2008-01-07 00:00:00 830 9
2008-01-07 00:00:00 900 8

I am looking for a way to get my table mentioned above to look like this
row_date [700] [730] [800] [830] [900]
2008-1-7 0 0 0 9 8

does anyone have any slick ideas on how I can accomplish this task?

View 3 Replies View Related

Developing WAP Using ASP.NET And Web Services

Sep 25, 2004

Hi all,

I would like to develop WAP using ASP.NET which can connect to the Database( MS SQL Server ). Is it possible to do it? I try to find the tutorial but I cannot find it.

Another thing I want to ask is can I connect the WAP to the web services that I create using ASP.NET, anyone can tell me how to do it.

Thanks, Regards.

RED

View 1 Replies View Related

Developing New Task

Oct 6, 2006

Hi

Can i inherit a existing task to a new custom class to add functionality to custom class.

for eg: Inherit lookup transfor which is already present in SSIS to add some funstionality into it.

Thanks

Vipul

View 1 Replies View Related

Developing A Database System

Jul 16, 2013

company is developing a database system containing information about cities, towns, and villages in the country. The number of cities, towns, and villages are in thousands and the database program should allow users to search a particular place by name. Users should also be able to search all places that match a particular value or range of values for attributes such as location or population size. This particular feature is known as a range query.

If the database is meant to support range queries that can return many cities that match the query specification, the entire operation may be allowed to take longer. To meet this requirement, it will be necessary to support operations that process range queries efficiently by processing all cities in the range as a batch, rather than as a series of operations on individual cities.Different indexing methods can be used to perform this operation. What will be your choice if:

a) database is created once and has no change over a period of time

b) database is created once and database is changed over a period of time

View 2 Replies View Related

Developing A New Plug-in Algorithm

Feb 7, 2008

Hi,

i'm making my master thesis about a new plug-in algorithm, with the LVQ Algorithm.
I make the tutorial with the pair_wise_linear_regression algorithm and i have some doubts. i was searching for the code of the algorithm in the files of the tutorial and i didn't saw it. I have my new algorithm programmed in C++ ready to attach him, but i don't know where to put him, in which file i have to put him to start to define the COM interfaces? And in which file is the code of the pair_wise_linear_regression algorithm in the SRC paste of the tutorial?

Thanks

View 3 Replies View Related

Sdk For Developing Sql Server Front-end

Feb 1, 2006

I am in the planning stages of a front-end to access sql server databases. The project involves analysis of stored data as well as storing results of the analysis. The classes for the analytical part is fairly complex so I would like to speed up the data manipulation part of the project. I am already familiar programming with ADO but I was hoping for a more complete solution. Is there a sdk or example code (third-party OK) that could facilitate this?

The client is not especially fond of .NET and would prefer to use VB6. However, with the right solution, C# or VB.NET would be acceptable languages.

View 1 Replies View Related

Developing New Task In SSIS

Oct 6, 2006

Hi All:

For making a custom Task in SSIS, is it possible to reuse the existing code base? For e.g. If i need to append some functionalities to LookUp Transform. Can I inherit the lookUp transform class?

Thanks,

Vipul

View 1 Replies View Related

Developing A SQL Server DB (noob Question)

Jul 31, 2007

I'm beginning development on a medium/ sized in-house web based system.  Well moving from asp/mysql to asp.net/sql server.  Re-doing DB design as well.My question is does it make sense for me to develop the SQL DB in Visual Studio 2005 pro, and then later move it to a real SQL server?  Or basically what is the best and/or most practical way to do this development.  Any other IDE's out there?Thanks 

View 3 Replies View Related

I Have Been Developing In Visual Studio 2005...

Sep 7, 2007

how do i view the databases that i have made in my web sites in MICROSOFT SQL SERVER STUDIO EXPRESS? can anyone help?

View 2 Replies View Related

Developing Database For Mobile Phone

Mar 9, 2008

Hi,


I am developing a Translator for a mobile phone and I am using MS SQL server 2005 for creating my database.. I am having a problem in creating a relationship between my tables.. I have the following tables:
English(engcode,engword) - engcode is primary key
Esperanto (espcode, espword) - espcode is promary key
EngEsp (engcode,espcode)..

The problem is that the icon "relationship" is disable in sql 2005 and i am nt able 2 create the relationship..

Any Suggestions??? Please help me..

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved