I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:
1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.
Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.
Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.
The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.
I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.
2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?
I have been working on a pretty ugly stored procedure recently, while debugging I added a char(10) to the end of each line of the SQL query so I could copy it to query analyzer(QA) and debug the SQL syntax output from of the stored procedure.
It had no effect on the stored procedure working, but when I copied the query to QA it got the error below, so I removed them all and added them in one line at a time to find the problem.
--Server: Msg 170, Level 15, State 1, Line 3 --Line 3: Incorrect syntax near ','.
Below are the 2 querys, the only difference is the Char(10) between Amt6 and Amt7!
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
I’m okay with simple queries but as I’m no expert and have failed to find perhaps the correct wording to describe this method, if at all possible to do, so I have come to ask here.
What I would like to do is take a column from a query and then break down that column into separate results.
So the full query results: 36,18/09/2007 10:00:00,NULL,000102000304,NULL
The column I would like to brake down is (Unique Reference Number): 000102000304
And I would like to break it down to get the last 2 parts (0003 and 04): 0001 | 02 | 0003 | 04
Is this possible to do? If so where should I be looking or what should I be looking at?
Need query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013
Query SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime, (sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins, (sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime
Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in February and how to get decimal values in average fields.
I've never been able to get to it. I must admit my first few attempts were before I had properly configured Reporting Services. But after two fresh installs, including a fresh OS install (64 bit Vista Ultimate) I still can't get to Report Manager. The closest I've been able to come is this error message:
Error
The report server is not responding. Verify that the report server is running and can be accessed from this computer.
Home
The service is running and I'm on my local computer which I have admin rights. I'm stumped.
Hi all...I have a friend who has a problem I'm trying to help with (no...REALLY...it's a FRIEND, not ME!!! *LOL*)
I haven't run across the need...but in a nutshell...we have a table with many rows of data, one column in each row needs to be multiplied by all other same-columns in the table's other rows.
For example... MyKey int, MyFloat float(53)
I want to multiply Myfloat by all other Myfloat columns in the table.
Similar to SUM(MyFloat) but something like PRODUCT(MyFloat).
Is there a aggregate kept in a basement closet somewhere, or a way to perform this operation rather than using a cursor to do it:
An example of my table: 1 3.2 2 4.1 3 7.1
if I could do a PRODUCT(MyFloat) I would want the result to be (3.2 X 4.1 X 7.1) or 93.152
For entertainment purposes, I've been reading some articles on SQL Injection Attacks and there should be a cover charge to read these articles. (excuse the sarcasm)
Most defense is based on the use of stored procedures or read only settings on the tables.
I'm looking for practical opinions and possibly some code that would convince me personally that this is indeed a real threat.
Also, if possible, please post remedies that are solid and not open ended.
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
Hi, I have two tables called Actcodes and a another table called FundBalances...
The Act codes have the following format..
PlanId int, ClientId int, StartDate datetime, EndDate datetime, ClientActCode char(2) CodeDescription char(15) so based on what the user has selected i am getting the names of each codes
and my fund balance i have the following rows.. PlanId int Participantid int StartDAte datetime, end date datetime, FundId int Loans Act1 char(2) TotAct1 money . Act20 char(2) TotAct20 money and the data is as follows PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20 111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561 111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00 so on and so so forth
tht Act1 matches the ClientActCode in the Act.. So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
I have a table with 2 columns: Col1 Col2 stat1,stat2 AV,AD stat3 TD I need to build a query like this: SELECT * FROM table3 WHERE stat1=AV and stat2=AD and stat3=TD How do I do this?
I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.
I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.
Hello to all, I have now two tabels ( Ta and Tb). the tabel includes difference attributte. I want to build this two tables together. I used this query: select * from Ta where Ta.Id = @ID union all select * from Tb where Tb.IdOfa = @ID but it doesn't work and the following error message comes "All inquiries in an SQL application, which contain a union operator, must contain directly many expressions in their goal lists "
I am building my SELECT on the fly by use of IF statements something like: SET @searchParam = @searchParam + ' AND tblXYZ.country = ' + @searchStringCountry
Then I am trying to use @searchParam as my WHERE clause: WHERE@searchParam
How do I get the string out of @searchParam to use as my WHERE clause?
I've got a table that stores individuals, and their organization all in one table, with basic information about each (contactId, name, primary address, phone, etc.). There is also, for each line, a true/false field 'PerOrg' that lets me know if it's a person (true), or an organization (false); and a 'OrgKeyCode' field that has the contactId for the person organization.
I'm looking to make an update query that will make the primary address for all individuals set to the primary address for their corresponding organization. Here's my queries to find the items:
This gets all of the organization, their contactId and their primary Address (as long as they have one).
SELECT ContactID, FullName, PrimaryAddressType FROM tblContactInformation WHERE PrimaryAddressType IS NOT NULL AND PerOrg = '0'
This gets a list of all of the individuals that have an organization assigned.
SELECT ContactID, FullName, OrgKeyContactID, PrimaryAddressType FROM tblContactInformation WHERE PerOrg = '1' AND OrgKeyContactID IS NOT NULL
Now I need to create an update query that sets the Primary Address for individuals to the primary address for their corresponding organization. Here's what I've got worked out so far: (just the basics).
UPDATE tblContactInformation SET PrimaryAddressType = WHERE PerOrg = '1'
At issue is, I'm not sure how to pull the primary address only from the organization into the SET line. Do I need to save my first query, then somehow call it in my update query, where the contactID = the OrgKeyCode? Or something like that?
I have a table which tracks changes by user, department and date. I want to construct a query which returns two numbers, the first is all the changes by department on a certain date. The second is all changes by user on the same date. I know that the queries seperately will look like,
SELECT COUNT(User) As NumberByUser FROM Table WHERE (Date = 'DateGoesHere') AND (User= 'UserGoesHere') GROUP BY User
SELECT COUNT(Department) As NumberByDepartment FROM Table WHERE (Date = 'DateGoesHere') AND (Department = 'DepartmentGoesHere') GROUP BY Department
But how do I go about linking the two in the same query? Any help would be appreciated, thanks!
an error, incorrect syntax, was generated when the following query was tried: CREATE TABLE ASPNET2(id smallint IDENTITY(1,1) PRIMARY KEY,word nvarchar(50) NOT NULL,definition nvarchar(MAX) NOT NULL)
I wanted to use nvarchar(MAX) because something I read suggested that I couldn't do editing with the controls if a variable was set to text. Thanks. -Larry
I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:
-- insert data into proper tables with extract date added SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT SurgeryKey,'+ @extractDate+', ClinicianCode, StartTime, SessionGroup, [Description], SurgeryName, Deleted, PremisesKey FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'
EXEC master..sp_executesql @SQLString
And here's the error message that I get:
Server: Msg 241, Level 16, State 1, Line 90 Syntax error converting datetime from character string.
I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.
Hi Everyone,I'm drawing a blank here and I am hoping someone can point me in the right direction. I have a table with the following columns (some omitted)IDGUIDPageNameParentPageID I want to build a hierarchical navigation system (2-tier). The conceptual problem that I am running into was getting this information from the same table. Initially I was going to use a nested repeater but I am thinking a treeview would be better. Anyway, the problem is the query. How would I start with something like this? Let's use the following as an exampleRows(ID '1', GUID '888....', PageName 'Page A', ParentPageID '-1')(ID '2', GUID '111....', PageName 'Page B', ParentPageID '-1')(ID '3', GUID '222....', PageName 'Page C', ParentPageID '-1')(ID '4', GUID '375....', PageName 'Page 1', ParentPageID '1')(ID '5', GUID '562....', PageName 'Page 2', ParentPageID '1')(ID '6', GUID '874....', PageName 'Page 3', ParentPageID '2') (ID '7', GUID '388....', PageName 'Page 4', ParentPageID '3') So, I want to be able to build a query so that I can do the followingPage A Page 1 Page 2Page B Page 3Page C Page 4 Any help would be greatly appreciated. Thanks!
I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.
Not sure if this is the place to post this, but here goes.
I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.
Synopsis: Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.
I have 3 tables, a supplier table, a types table and a relationship table between the two..I want to build a query that put the different types in columns, and use a Boolean value to identify if the supplier supplies that type.
Hi all,I'm a newbie to SQL and I need help with investigating what ways arepossible to build an interface of some sort that wraps around a SQLquery script.I have a simple SQL query which we normally run inside Query Analyzerto update certain fields in the SQL DB based on FQDN provide by theuser. The user normally opens up the query, edit the script to replaythe xxxxx in the line "set @FQDN="xxxxx" with the node name and thenfires it off.I would like to know what are the ways to wrap a simple user interfacearound it so the user does not have to open and edit the scriptdirectly. A simple GUI or Excel type form interface that has a freeform text field for input and then a button to run the script.Obviously, I would like to see the output in some sort of output paneas well.Is this something that can be done in Query Analyzer ?Thanks in advance,Michael.
I've got a Table that has over 500,000 row in it. Now I need to convert the whole thing into Excel to import into another application. So I need to break the table into 10 different tables. How can I do that?
I hope I can get this across clearly.I have a table that needs to be broken into 3 tables.Col1 Col2 Col3 Col4 Col5 Col6 Col7Col1 and Col2 need to go into LookupTable1Col3 and Col4 into LookupTable2If Col5 is twice the width.... haha just kidding...so Col5 and Col6 go into LookupTable3There is a 4th table which is made up of foreign keys which are the PK ofLookupTable1,2,3My questions is, how to get the data from the columns of each row and add itto its respective lookuptableand sequentially step throw the table to repeat the above step until I'veprocessed each rowthanks folksT.B
Is it possible by any kind of workaround to break the 8kb limit on user-defines datatypes?
My datatype can contain an arbitrary number of double-precision points meaning that I in best case only can store 512 points (2 x 8 x 512). there's a few extra bytes used for something else, but this is roughly the maximum, which is far from what I in many cases need. I serialize the object myself to ensure that I only store what I really need.
