Help Finding The Top 3 Zipcodes Within The Top 5 Counties
Sep 21, 2005
I need to show data for the top 3 zipcodes for EACH of the top 5
counties. I feel totally blocked on how to make this work properly.
Here is my code - anything you can suggest will be greatly appreciated:
SET ROWCOUNT 5
DECLARE @tblTopMarkets TABLE (StateCD CHAR(2), CountyCD CHAR(3))
INSERT INTO @tblTopMarkets
select
S.StateCD,
S.CountyCD
FROM DAPSummary_By_County S
WHERE S.SaleMnYear > '01/01/2004'
GROUP BY S.StateCD, S.CountyCD Order By Sum(S.Nbr_MTG) DESC
-- the above works fine but next select produces only 3 rows;
-- I need 3 times 5 rows (how to effect a "loop")
SELECT TOP 3-- zips in a county
D.StateCD,
D.CountyCD,
D.Zip,
"Nbr_Mtg"= Sum(Nbr_MTG)
FROM @tblTopMarkets T
LEFT JOIN GovtFHADetails D
ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD
WHERE D.SaleMnYear > '01/01/2004'
GROUP BY D.StateCD, D.CountyCD, D.Zip
Order By Sum(Nbr_MTG) DESC
We have the need to determine surrounding counties of a particular county.
For example how can I determine, if I entered a zip code for Contra Costa and the property is also right on the line of alameda that Alameda is a neighboring county of Contra Costa
Could it be determined by the longitude and latitude of the zip code in comparison with the counties?
For zipcodes where the values is not null and less than 3 i have to add two 0's,for zipcodes where value is 4 digit i have to add one 0.this is in a case statement and my zip code format is "02814-1473" if it is ''342"then it should be "00342" if it is "1342" then it should be "01432" in some cases there are 10 digits but i have to look for the one on left hand side of the hiphen.
here is my code : SELECT distinct isnull(P.id,'') PersonID , isnull(replace(Ltrim(Rtrim(P.Firstname)),',',''),' ') FirstName , ZipCode = isnull(case when substring(Ltrim(Rtrim(PA.Zipcode)),6,1) = '-' then isnull(PA.Zipcode,'') when substring(PA.Zipcode,6,1) = ' ' then isnull(PA.Zipcode,'') when len((Ltrim(Rtrim(PA.Zipcode)))) = 3then ('0' + '0' + (PA.Zipcode)) when len((Ltrim(Rtrim(PA.Zipcode)))) = 4 then ('0' + (PA.Zipcode)) else substring(PA.Zipcode,1,5)+'-'+ substring(PA.Zipcode,6,4) end ,'') FROM vwPersonPrefAddr pa with(nolock), vwpersons p with (nolock) where p.id = pa.id
hello all,is there a quick way to convert a zipcode of type int, to a 5 characterchar value ?e.g.declare @zip intdeclare @czip char(5)select @zip = 2109select @czip = convert(char, @zip)select @czipbut with @czip = 02109 instead of 2109 ?thanks in advance
Hi All, Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?
(id_text int ,text_name varchar(10) ,id_author int)
insert into test (id_text, text_name, id_author) values (205, 'name1', 123) insert into test (id_text, text_name, id_author) values (205, 'name2', 124) insert into test (id_text, text_name, id_author) values (205, 'name2', 125) insert into test (id_text, text_name, id_author) values (206, 'name2', 124) insert into test (id_text, text_name, id_author) values (207, 'name2', 122) insert into test (id_text, text_name, id_author) values (207, 'name2', 128)
select id_text, id_author from test
and i need to get out only id_text with the first author. any solutions for sql server 2000?
I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.
I tried the following expression: =iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")
but this only works if the first day is the maximum value.
I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not Row1 Date1,falseRow2 Date2,trueRow3 Date3,falseRow4 Date4,false so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow
I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details : SELECT * FROM Members where surname = (textbox1) Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way. I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text. Is this possible? Thanks in advance Mike
I have a table TEST. I want to find the triggers which use this table test. For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST, I want to find this particular trigger. Any ideas? I am using SQL server 2000 thank you
hai I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table structure. Thanks in advance satya
I have a customer database with the following structure:
Dept ID (int) Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.
Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.
How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)
Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer.. I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.
EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.
I have a column of data of type varchar. it contains a mix of text id's and numeric id's. all i want is the highest purely numeric value The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.
I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.
The script I have written fails with overflow error. I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...
Code:
Dim str_SQL_Asset_1 daysBack = 1 tblFound = FALSE Dim xbl_Holdings_date Dim xMonth Dim xDay xbl_Holdings_date = date() If Len(Month(xbl_Holdings_date))=1 Then xMonth = "0" & Month(xbl_Holdings_date) Else xMonth = Month(xbl_Holdings_date) End If If Len(Day(xbl_Holdings_date))=1 Then xDay = "0" & Day(xbl_Holdings_date) Else xDay = Day(xbl_Holdings_date) End If
IF MM_checkCmd.EOF Then xbl_Holdings_date = (xbl_Holdings_date - daysBack) daysBack = daysBack + 1 tblFound = FALSE ELSE tblFound = TRUE MM_checkCmd.CLOSE() SET MM_checkCmd = Nothing END IF loop
The goal of this snippet is simply to identify and set the value for the existing table name. An example table name would be "xbl_Holdings_2006_12_10".
I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.
I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)
thank you
SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups FROM TERMINATION GROUP BY [TM#] HAVING Count([TM#])>1; GO
SELECT PR.WBS2, SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR' THEN Ledgerar.amount * - 1 ELSE '0' END) AS BillExt FROM PR LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3 WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001' AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND pr.wbs1 = '001-298' GROUP BY PR.WBS2
It prints out: Wbs2 BillExt 0141 0 0143 0 1217 20580
I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.
Hi, I am searching for the most easy SQL solution:
Lets say i have 500000 rows of cars in one table with 30000 different car ID's. This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?
Our products exist in batches. These batches reference a warehouse.Each warehouse carries a set of 'roles'.Users on our system also carry 'roles'. Comparing user and warehouse roles tells the system what warehouses a user can access.
So, if:
WAREHOUSE1 has roles A + B WAREHOUSE 2 has role B WAREHOUSE 3 has roles A + C and USER1 has role A USER2 has roles A + B + C USER3 has role C then USER1 will have access to WAREHOUSE1 and WAREHOUSE3 USER2 will have access to all 3 warehouses USER3 will have access to WAREHOUSE3.
What I need to do is write some SQL which allows a part number to be entered, and a list of batches for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.
I can do the *opposite* of this quite easily (ie: show users who HAVE got access):
select distinct sb.batchnumber, ur.userid from stockbatches sb inner join warehouseroles wr on sb.warehouseid = wr.warehouseid inner join user_roles ur on wr.roleid = ur.roleid where sb.partid = @part order by sb.batchnumber, ur.userid
- it's a straightforward join between the warehouse and user roles, with a 'distinct' to eliminate any duplications.
Fair enough, but it's not what I want.
Using the role data above, with the following batch info:
We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.
With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.
I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
declare @Weekday tinyint, @hour int select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19)) begin Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average' SELECT AVG(Sat_Night) FROM Weekly end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7)) begin Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average' SELECT AVG(Sun_Day) FROM Weekly end
I our firm there are 4 people who are working on a database. I need to edit all of my views. Is there any way I can find out which view was created by me?
