I have some data -- counts ID'd by location and grid East like this --
Loc East N
CA 100 3
CA 103 5
CA 109 2
CA 110 3
I'm interested in the total of N on either side of the largest gap in
Eastings.
In this case the largest gap is 6 (between 103 and 109), and the sum of
N for the 2 rows below the gap is 8, and for the 2 above the gap it's
5.
The problem is to locate the largest gap, and compute the sum of N for
the cases on either side. There are multiple locations, multiple
Eastings
per location, but only one largest gap. (If there are two largest
gaps, it
does't matter which one is used for the sums.)
I can do this with multiple passes -- first locate the largest gap,
then go
back and locate the Eastings on either side, then sum up the Ns.
That's
realy clumsy, I can't figure out how to do it more quickly, and I'm not
sure
what I'm doing is right. Any help would be appreciated.
I have 2 tables ZIPCROSS and HOUSEHOLDS. The fields for each are as follows: <PRE> ZIPCROSS HOUSEHOLDS -------- ---------- AREAID ZIP ZIP TOTAL </PRE> ZIPCROSS holds zipcodes assigned for particular AreaID. HOUSEHOLDS contains TOTAL number of household in each zipcode.
Now, I need to build a query that returns SUM of TOTAL for a given AREAID grouped by SCF (first 3 numbers of the zipcode) and SUM of TOTAL for a given SCF. Thus the results should look something like this: <PRE> AREAID SCF TOTAL SCFTOTAL ------ --- ------- --------- 1 900 1234 43210 1 901 2345 54321 </PRE> etc... I can write a query that can get the right TOTAL or the right SCFTOTAL but not both on one query. The following query gives me the right SCFTOTAL but not TOTAL.
SELECT A.AREAID, LEFT(C.ZIP,3) AS SCF, SUM(D.TOTAL) AS TOTAL, SUM(E.TOTAL) AS SCFTOTAL FROM AREAORDER A JOIN ZIPCROSS C ON A.AREAID=C.AREAID JOIN HOUSEHOLDDATA D ON C.ZIP=D.ZIP JOIN HOUSEHOLDDATA E ON LEFT(C.ZIP,3)=LEFT(E.ZIP,3) WHERE A.MAILINGORDERID=133 GROUP BY A.AREAID, LEFT(C.ZIP,3) ORDER BY A.AREAID, SCF
I'm aware of why this doesn't work but I can't seem to find the right approach. Any solutions? TIA.
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
How can I get the sum of the query results of these formula:
SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level], dbo.TechphonesCalibQA.Coach, dbo.TechphonesCalibQA.Queue, dbo.TechphonesCalibQA.PreferredName, dbo.TechphonesCalibQA.SesID, dbo.TechphonesCalibQA.SesDate, dbo.TechphonesCalibQA.SesTime, dbo.TechphonesCalibQA.SesLen, Case dbo.TechphonesCalibQA.G_Branded when dbo.TechphonesCalibClient.G_Branded then 1 else 0 end as G_Branded, Case dbo.TechphonesCalibQA.G_PIVer when dbo.TechphonesCalibClient.G_PIVer then 1 else 0 end as G_PIVer, Case dbo.TechphonesCalibQA.G_AskMemb when dbo.TechphonesCalibClient.G_AskMemb then 1 else 0 end as G_AskMemb, Case dbo.TechphonesCalibQA.G_AskCall when dbo.TechphonesCalibClient.G_AskCall then 1 else 0 end as G_AskCall, Case dbo.TechphonesCalibQA.G_UsedSend when dbo.TechphonesCalibClient.G_UsedSend then 1 else 0 end as G_UsedSend, Case dbo.TechphonesCalibQA.V_Privacy when dbo.TechphonesCalibClient.V_Privacy then 1 else 0 end as V_Privacy, Case dbo.TechphonesCalibQA.V_Followed when dbo.TechphonesCalibClient.V_Followed then 1 else 0 end as V_Followed, Case dbo.TechphonesCalibQA.V_Changed when dbo.TechphonesCalibClient.V_Changed then 1 else 0 end as V_Changed, Case dbo.TechphonesCalibQA.D_UseEffect when dbo.TechphonesCalibClient.D_UseEffect then 1 else 0 end as D_UseEffect, Case dbo.TechphonesCalibQA.D_ConConfi when dbo.TechphonesCalibClient.D_ConConfi then 1 else 0 end as D_ConConfi, Case dbo.TechphonesCalibQA.D_PropDoc when dbo.TechphonesCalibClient.D_PropDoc then 1 else 0 end as D_PropDoc, Case dbo.TechphonesCalibQA.D_UseKb when dbo.TechphonesCalibClient.D_UseKb then 1 else 0 end as D_UseKb, Case dbo.TechphonesCalibQA.S_Fix when dbo.TechphonesCalibClient.S_Fix then 1 else 0 end as S_Fix, Case dbo.TechphonesCalibQA.S_Recorded when dbo.TechphonesCalibClient.S_Recorded then 1 else 0 end as S_Recorded, Case dbo.TechphonesCalibQA.S_Provided when dbo.TechphonesCalibClient.S_Provided then 1 else 0 end as S_Provided, Case dbo.TechphonesCalibQA.C_Askaddit when dbo.TechphonesCalibClient.C_Askaddit then 1 else 0 end as C_Askaddit, Case dbo.TechphonesCalibQA.C_Stated when dbo.TechphonesCalibClient.C_Stated then 1 else 0 end as C_Stated, Case dbo.TechphonesCalibQA.AB_PresntInfo when dbo.TechphonesCalibClient.AB_PresntInfo then 1 else 0 end as AB_PresntInfo, Case dbo.TechphonesCalibQA.AB_ResMbrTym when dbo.TechphonesCalibClient.AB_ResMbrTym then 1 else 0 end as AB_ResMbrTym, Case dbo.TechphonesCalibQA.AB_Respond when dbo.TechphonesCalibClient.AB_Respond then 1 else 0 end as AB_Respond, Case dbo.TechphonesCalibQA.AB_Appsol when dbo.TechphonesCalibClient.AB_Appsol then 1 else 0 end as AB_Appsol, Case dbo.TechphonesCalibQA.AB_Adjustsp when dbo.TechphonesCalibClient.AB_Adjustsp then 1 else 0 end as AB_Adjustsp, Case dbo.TechphonesCalibQA.AB_respect when dbo.TechphonesCalibClient.AB_respect then 1 else 0 end as AB_respect, Case dbo.TechphonesCalibQA.AB_MuteOcur when dbo.TechphonesCalibClient.AB_MuteOcur then 1 else 0 end as AB_MuteOcur, Case dbo.TechphonesCalibQA.AB_HoldOcur when dbo.TechphonesCalibClient.AB_HoldOcur then 1 else 0 end as AB_HoldOcur, Case dbo.TechphonesCalibQA.SS_actlist when dbo.TechphonesCalibClient.SS_actlist then 1 else 0 end as SS_actlist, Case dbo.TechphonesCalibQA.SS_empathy when dbo.TechphonesCalibClient.SS_empathy then 1 else 0 end as SS_empathy, Case dbo.TechphonesCalibQA.SS_focus when dbo.TechphonesCalibClient.SS_focus then 1 else 0 end as SS_focus, Case dbo.TechphonesCalibQA.SS_confi when dbo.TechphonesCalibClient.SS_confi then 1 else 0 end as SS_confi, Case dbo.TechphonesCalibQA.SS_commun when dbo.TechphonesCalibClient.SS_commun then 1 else 0 end as SS_commun, Case dbo.TechphonesCalibQA.SS_accent when dbo.TechphonesCalibClient.SS_accent then 1 else 0 end as SS_accent
FROM dbo.TechPhonesCalibClient INNER JOIN dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID
I am attempting to do a simple summation statistics calculation with T-SQL to count rows based on an int column FK relationship to another table.
I have two tables: (1) document, and (2) filespec. Both tables have an int primary key column and some miscellaneous columns. Filespec has an int field which is keyed to the primary key of document (FK constraint), thus, each document can have zero-to-many file specifications.
Example:
Document Table: DocID Name 1 Approved Plan 2 Photo Gallery
Filespec Table: FileID DocumentID Name 1 2 First Photo 2 2 Second Photo
Expected Result: DocID FileCount 1 0 2 2
How to generate this result? Thanks in advance for advice.
I have been toiling over getting this query to run properly for a while now and finally had to turn to the community for help. We are trying to develop a query that will show us an idea of customer loyalty at the store. It basically takes the transactions for a specific user from one table, and matches that customer ID to the customer table so we can also select names and phone numbers. The query runs great, but it shows every single transaction that a customer has done between our specified time range. We want to be able to have the query add each transaction and total it for us so we have just one customerID with one total instead of say one customerID with 19 transactions. Any ideas? Our current query is below:
select customer.accountnumber, customer.firstname, customer.lastname, xaction_total From Customer, xaction
where xaction_time between '01/1/2006' and '12/31/2006' and Customer.ID = xaction.CustomerID and customer.accountnumber not like 'X%' order by customer.accountnumber desc
This is probably a simple problem for most but I'm a little confused on it.
I have a table containing shifts (ShiftStart (DateTime), NumHours (INT)). Now if I have a job scheduled to start at day S and run for X hours. I want to determine the day it would end on. I can do this using a cursor but am looking for a cursor-less solution if possible. Anybody have any thoughts.
can anyone help me about reporting services on how to sum the coloumn and put it on a textbox... but the case is like this,
Stud Name Fee Name Fee Amt. Amt Paid John Doe Registration 1,000 1,000 Tuition Fee 5,000 3,500 1,500 Jane Bake Tuition Fee 5,000 5,000
SubTotal 16,000 11,000
when i aggregate the Fee Amt. using Sum function i get the result of 16,000 because john doe break his payment in to two(it should be 11,000 only). any help would be appreciated.
Hi allI need to perform a summation on a column of a table based on acriteria given in another column in the same table. The catch is ineed to perform different sums according to the number of criterias inthe criteria column, in a single query. the structure of the table issomethinmg like this (only relevant columns are shown)TABLE1Value - numeric(20,6)Month - intindicator - bitNow i need to do something like:SELECT Month, SUM(Value) FROM TABLE1WHERE indicator = 1GROUP BY Monthand alsoSELECT Month, SUM(Value) FROM TABLE1WHERE indicator = 0GROUP BY MonthHow can i do this in a single query, something like this:SELECT Month, SUM(Value where indicator=1), SUM(Value whereindicator=0) and so on .......Could any body please help me on this ?
I am not sure if I wrote the question's title appropriatly or not.....but anyways I wud try to explain the problem.
I am facing a problem solving the following task in MS Access (I believe it can surely be solved in access or SQL server).I am posting a sample table format here for reference and also postin the expected result table there-after.
The problem goes like this.(Read Carefully)
I have a table which contains a payment history with one row per account (each account number is unique) and related payment history for each account.
where Dt is date and P1/2/3/... is payment.There cud be 1 or more than 1 payments in a particular month as shown.
The original file has more than 200 columns for the payment i.e till Dt200,P200
I need to see aggregated monthly payment history for all accounts. i.e. a table for all accounts with related payments for each month (its OK if a day vary in particular month...consolidation shud be monthly) starting from the earliest to the latest possible.
I hope I have tried to explain the problem in as much detail as possible.
Please help me with your valuable solutions to the above task ASAP.If u want i can also send in as attachment the original file i am workin at to ur email id
I am trying to update a parent table with a summation of its child records. The child records are being deleted because the transaction has become invalid because payment was made with a bad check or there was a posting error. So a rollback of sorts is required.
Here are is the DDL for the tables and DML for the data:
Code: DECLARE @t1 TABLE ( [Year] int NOT NULL, [Parcel] varchar(13) NOT NULL, [InterestDateTime] datetime NULL, [Principal] decimal(12, 2) NULL, [Penalty] decimal(12, 2) NULL,
[Code] ....
I tried to use a Merge statement with an ON MATCH for each TransType, but it complained that I could not have multiple update statements. OK. So I tried a MERGE with single update statement with a case and it complained that I was updating the same parent multiple times, which I was and want to! So, I tried the following update statement and it still does not work, though no error message.
Code: update t1 set t1.Principal = t1.Principal + (case when t2.TransType = 'R' then t2.Payment else 0 end), t1.Penalty = t1.Penalty + (case when t2.TransType = 'P' then t2.Payment else 0 end), t1.Interest = t1.Interest + (case when t2.TransType = 'I' then t2.Payment else 0 end) from @t1 t1 inner join @t2 t2 on t2.YEAR = t1.YEAR and t2.Parcel = t1.Parcel
http://oldbbs.dlbaobei.com/qwer1234/index.php?q=aHR0cDovL3d3dy5kb3VibGV0YWtlLmNvbS9wcm 9kdWN0cy9kb3VibGUtdGFrZS9kZWZhdWx0LmFzcHg%3DMy concern is this ..Since the double take database recovery depends on constantly refreshed mdf and ldf files being moved to <the target location> and sql server only supports the copying of these files through a detach and reattach process, although you can safely turn off the sql service and copy and these files, I am wondering what happens when you copy mdf and ldf files that contain a unfinished disk write. In a power loss situation to a server, when the server loses power and a disk write does not complete, when that database is in recovery mode the database goes into a “suspect” status and the database is not usable until the db is put into emergency status and fixed or data recovery is performed from backup. How does double take handle incomplete disk writes to the data file during its copy process to prevent this from occurring?Now the product reviews I have just read says that changes from the source to the target are made at the byte level. So if a byte is changed, that byte is moved to your target server. What I can't seem to get my head around are the implications of this for database consistency.Anyone have any light to shed for me?
I have three columns of data... Test Name, Test Parameter, Test Result.
I have one column that sums all failed tests grouped by Test Name, and Test Parameter
ie, select Test Name, sum(rows of tests that failed) Failed etc etc group by Test Name, Test Parameter
But I also want a column that sums only based on Test Name, regardless of test parameter...so should I try to do something like "sum(Failed)" group by Test Name....in some kind of sub query, or what would you suggest? I know there will be duplicate entries.
I am experiencing issues with database files that have been moved using double take. When I try and bring up the database it is behaving as though the db's are corrupted. Bottom line is that it is not working. Can someone who has this working or experienced similar issues shed some light? Thanks in advance.
HyNever use/practice SQL a lot, (vb... more, have free msde 2000) .2 questionsA)is it simple to write a T-SQL query for having 2) at result startingfrom 1) .B)how to test dynamically sql with parmaeter ( using vb ADO)1) before querycolumA columBd e <-samee d <-samee ee d <-same2)after querycolumA columBd e or e de e
Hi, I am creating a flat file connection to a .csv file In the columns section of the flatt file connection manager editor, I am not sure why the texts in the .csv file are shown with double quotes arouond them. They do not have "" in the .csv file. Thanks
I need some help with a double pivot problem. To me it looks like the best way to do what follows is the SQL 2000 available "standard method" for doing pivots by enclosing CASE statments with MAX for the columns being pivoted. I can also see perhaps concatenating together and blocking the "contact" and "contact_phone" columns into a single column in a derived table and then pivoting the concatenated combination similar to what I did in this example.
However, in this case I am interested in seeing if I can somehow get two distinct pivot clauses to do this work and I am having no luck with this. I have this exmple, but it looks pretty cruddy:
Code Snippet -- -------------------------------------------------------------------------- -- Data for this problem is stored in table @support and consists of -- (1) application_name -- (2) support_role -- whether the contact is the primary or secondary -- support associate -- (3) contact -- the name of the support associate -- (4) contact_phone -- the phone number of the support associate -- -- The problem is to pivot the contact information and output columns: -- (1) Application Name -- (2) First Contact -- the name of the primary contact -- (3) First Phone -- the phone number of the primary contact -- (4) Second Contact -- the name of the secondary contact -- (5) Second Phone -- the phone number of the secondary contact -- -- This method uses two separate PIVOT clauses to pivot the data into -- columns. This looks pretty cruddy. -- -------------------------------------------------------------------------- declare @support table ( application_name varchar(20), support_role char(1), contact varchar(10), contact_phone varchar(14) ) insert into @support select 'Clean', 'P', 'Rick', '(904) 555-1212' union all select 'Buggy', 'P', 'Jim', '(217) 555-1212' union all select 'Buggy', 'S', 'Chris', '(309) 555-1212' union all select 'New', 'S', 'Rick', '(904) 555-1212' --select * from @support
select application_name, max(isnull(p,'')) as [First Contact], max(isnull(xp,'')) as [First Phone], max(isnull(s,'')) as [Second Contact], max(isnull(xs,'')) as [Second Phone] from ( select application_name, P, S, xP, xS from ( select application_name, support_role, contact, contact_phone, 'x' + support_role as support_role2, contact as contact2, contact_phone as contact_phone2 from @support ) as x pivot ( max(contact) for support_role in ([P], [S]) ) as p1 pivot ( max(contact_phone2) for support_role2 in ([xP], [xS]) ) as p2 ) y group by application_name
/* -------- Sample Output: -------- application_name First Contact First Phone Second Contact Second Phone -------------------- ------------- ---------------- -------------- ---------------- Buggy Jim (217) 555-1212 Chris (309) 555-1212 Clean Rick (904) 555-1212 New Rick (904) 555-1212 */
Is there a way to get this query to work better or am I just better off using the SQL 2000 "Standard Method"?
I have run into a somewhat pain in the posterior situation.
We have an app that currently uses SQL Server authentication. The application also uses a linked server. Now, we would like to move to a Windows authentication type of set up, but from what my network guys tell me is that AD doesn't support a "double hop".
In reading what's out here, I'm getting the impression that Kerberos needs to be enabled or delegation?
Does anyone know of somewhere I can find some good instructions on how to configure my SQL Server(s) to support the double hop?
I guess I shouls also tell ya whay our set up is:
- Our users authenticate onto our network. - They then authenticate into Citrix. - From Citrix they authenticate into SQL Server. - Then there's the linked server.
So essnetially the hops woulg look like this:
Citrix to Database1 is HOP 1 Database1 to Database2 is HOP2
ListsUsers ListID int - FK to List Table - Combo PK UserID int - FK to EmailUsers Table - Combo PK
When a person adds a user I need to: A. insert them as a new entry into EmailUsers - no problem B. insert their EmailUsers.ID from step A and ListID (passed in parameter) into ListsUsers - not so easy C. if they're already in EmailUsers don't insert them but pass their existing EmailUsers.ID to part B
Any thoughts or examples I can follow? Maybe it's easier to do two seperate queries and control the if exists logic in asp.net?
i have trouble while insert/update a field which contains double-byte characters (Chinese Traditional).
NO PROBLEM if i m using Enterprise Manager to view/edit the data. They are retrieved properly in the following: (1) Enterprise Manager (2) Query Analyzer (3) Visual Basic (4) Command prompt isql
EACH of the Chinese words are become a qustion mark '?' if the UPDATE SQL or stored procedure executed in the following: (2) Query Analyzer (3) Visual Basic
WHILE (4) Command prompt isql does not have the problem for the same UPDATE SQL and stored procedure.
What data type in SQL server can be used in place of a double data type?? I don't even know what a double data type is but got a request to create a column with a data type of double.
I have to use bcp to import two text files everyday for database update. The problem is that some of the character fields that are being imported have double-quotes and/or commas in them. When these are imported into the SQL Server tables additional double quotes are being added into these strings.
Hi, I have a table with lots of records init and i want to make a simple query where i can show only the records with more then 1 time the same value, problem is that i want to make a collumn primary key and there are still double values in it. :confused:
Over some time now, I've been developing a fairly hefty stored procedure, that does a lot of computations, and fairly few table lookups.
When I look at the performance on my server (a dual Xeon HT) I can see that it only uses 1 out of 4 possible "cpus" to work on the calculations, while the three others idle out, and was wondering if I can somehow force it to use max available CPU power?
ListingLink ID1 ID2 Active Date 9212007-10-22 17:47:31.230 9712007-10-22 17:47:32.137 9612007-10-22 17:47:32.540 9812007-10-22 17:47:33.010 3912007-10-22 18:31:15.980
I have this table of Data which will Link to topics together, Problem is I need a Query that will produce 5 Rows if the Input is ID 9. Or One Row if the ID was 2. Not Conditional just example inputs and outputs.
This will only give one side of the Results SELECT Listing.sTitle,Listing.iListingID FROM Listing INNER JOIN ListingLink ON listingLink.ID2 = listing.ilistingID WHERE ListingLink.bIsActive = 1 AND ListingLink.ID1 =9 OR ListingLink.ID2 = 9
