Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location
location shows the dif cities and the sql query will show cities will a count of people per city
how do you do this?
I am having a problem transforming a data set to the structure I need. I have data in the following format.
Id Visit1 Visit2 Visit3 Visit4
1 2Mar2010 27Mar2010 24Apr2010 8Jul2010
2 2Apr2010 3May2010 4Jun2010 11Jul2010
I need to transform it into a table which gives a count of the number of subjects at each visit at the end of each month.
For example, in March there will be 1 subject at visit 2. In April, there will be a total of 2 subjects, 1 at visit 1 and 1 at visit 3. In May there will be 2 subjects, 1 at visit 3 and 1 at visit 2, etc.
Here is the table I was hoping to produce.
Date.....Subjects.......Visit1.....Visit2.....Visit3.....Visit4
March....1.............................1
April......2...................1.........................1
May.......2.............................1..............1
Jun.......2............................................2
Jul.......2...........................................................2
I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?
View 6 Replies View RelatedWe have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
I am trying to add to a query to produce a report in Apex. Currently I have this
SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,
[Code] ....
I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?
join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
[Code]....
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),
Aim –To produce a unique line by month for the FDMSAccountNo
I have created the following query (see below) which produces the following result set
FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000
SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate
[Code] ....
Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08
[Code] ....
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.
I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...
exec sp_QueryInvalidForNotification "exec myTestSproc 99"
Does such a sproc exist? Or does anyone know how to write one?
Basically what i have is a ddl that gets filled with column data "BidCity" in the C# code behind. with this code.
All im interested in is how to add a value("with SQL") that doesnt exist in the database and have it populated into the ddl with all the other info in the "BidCity" column. read more below.....
Is this even possible with SQL?
private void DoCitiesInit()
{
//CONNECTION STRING
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
DataSet dsCities = new DataSet();
SqlDataAdapter daCities = new SqlDataAdapter("SELECT DISTINCT Bids.BidCity, Bids.BidStatusID FROM Bids WHERE Bids.BidState = '" + ddlStates.SelectedValue + "' AND Bids.BidStatusID = 1 ORDER BY Bids.BidCity ", conn);
daCities.Fill(dsCities, "Bids");
ddlCities.DataTextField = "BidCity";
ddlCities.DataValueField = "BidCity";
ddlCities.DataSource = dsCities;
ddlCities.DataBind();
}
I would like to add a text field value "(All Cities)" to my ddl with the other data that gets populated into the ddl, and have that selection have a value(999 or whatever) without having to put this in the database. My reason behind this madness is that if i put this data into a row in the same table it will get populated into the gridview with the rest of my code.
Basically i am working with a database i myself did not design and i cannot change the database as it is being used for a windows application and i am using this table to populate for a website for end users. if i dont make sense please let me know. Im used to it.
Thanks
I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?
View 6 Replies View RelatedHi,
I need to make the format by pulling the number from a table from sql server 7.0 and show the result in a table and have the following format $120,534.45 is that possible.
Thanks
Ali
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
View 3 Replies View RelatedI have a percentage cell within a matrix. I want this cell to be formatted to 1 decimal place, so I use the format of ##0.#% (After trying P1 etc). The cell appears formatted ok within the report viewer:
100%
12.9%
However after exporting to Excel the following occurs:
100.% - There is no 0 after the decimal point (but there is a decimal point . )
12.9% - Works ok when the the trailing digit is not a 0
Any ideas?
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
I want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
Hi,
I've SQL Server 2005 Dev Edition and Windows Server 2003 ENT SP2.
in the task manager i see that the server use 5GB out of 6GB,but when i arrange the process to see whom takes all the memory i see that the sqlservr.exe takes 150MB(he is the biggest).
when i open Perfmon and look on the sqlservr.exe memory use, i see that he takes the 4.5G.
i've a problem that the server use alot of cpu time to run users queries,i see that pages/sec counter is very big average between 600-800.
is there any problem with my memory?
why the memory reading from the task manager is wrong?
if my pages/sec counter is so big do i have a memory leaks + pressure?
THX
This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
Hi...I want one listbox showing cities but I dont want to list a city more than one time....
I know that DISTINCT maybe could work...But I dont get it to work correctly....
The code:
<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>
<asp:SqlDataSource ID="DataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT [City] FROM [Location]">
</asp:SqlDataSource>
I got the message:
The text data type cannot be selected as DISTINCT because it is not comparable
The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
Greetings:
I read with great interest a post on getting hte top 75 per cent by month, but still get through this problem. I have a table containing addresses, city names and state names, Each row has a unique address. I want to get the first ten addresses for each city in each state. I've tried something like the following which didn't work. Suggestions on how to solve the (simple) problem would be appreciated.
Select top (10) city,state,address
from address_list
alan
Quick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
Hello I am new to SQL Server 2005 and am designing my first database.
In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.
Why would you not put it in a separate table with state/province or a separate table all togeather?
Thanks for your help.
In the database, most of our cities are stored in all upper case. For reporting purposes, I need to have them returned as upper/lower. I used the below function, which works great for one word cities. However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita.
Upper(left(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY,1))+lower(substring(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY, 2, LEN(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY)))As ADDRESSCITY
This returns back: ‘Rancho santa margarita’; I need it to return ‘Rancho Santa Margarita’. Is this possible to do at the query level?
Hi,
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
Thanks
Kate
I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:
Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco
---------------- etc----------------
I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:
ZIP | City name
111 belmont
114 San Francisco
How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.
The select statement is below:
CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int
AS
SELECT distinct o.City AS City, o.zip_code, o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist
FROM zipcodes z,
zipcodes o,
zipcodes a
WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
GO
Thanks,
Christian
I am searching for the Sql Server Script of all country State and City with the Following type..
For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)
For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)
For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).
I need this type of Script with Column name.
I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.
Two things I don't like about query:
1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)
Sample DDL: 2 tables
create table dim_lead
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),
lead_id int,
[Code] .....
I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.
Lets say that my table looks like this:
idorderIdUserItem
122joepotatoes
223SteveApples
324SteveBananas
438DaveCarrots
And the query looks like this:
SELECT orderID FROM sales WHERE orderID IN ('22','51','38')
You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?
i have one table completed tabletask_id task_due_datetime task_completed_on 1 03/21/2008 03/25/20082 03/12/2008 03/10/20083 03/10/2008 03/18/2008 i want the output:task_id on time late 1 0 1 2 0 0 3 0 1 how i get this output in sql query without the use of cursor...
View 5 Replies View Related