I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):
date prod class qty 060101 a101 1a 100 060101 a101 1a 100
I would like to have the following:
date prod class qty
060101 a101 1a 200
Any other suggestions would be greatful!! Thanks in advance
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
Hi everyone, I have some trouble writing a SQL stored procedure thatcan do the following:We have data in one table in numeric form, but we want to sum the datain this table based on the values of two different alpha fields. Toillustrate, let me write the following example:Table with these records:A B 1.1 2.2 Blah1 Blah1A B 2.3 5.6 Blah2 Blah2B C 7.8 9.1 Blah3 Blah3B C 4.5 1.0 Blah4 Blah4R F 1.1 4.3 Blah5 Blah5B A 3.1 2.7 Blah6 Blah6I need to write a query that will return the following result set fromthe above table:A B 3.4 7.8 Blah1 Blah1B C 12.3 10.1 Blah3 Blah3R F 1.1 4.3 Blah5 Blah5B A 3.1 2.7 Blah6 Blah6If the alphanumberic keys are the same, the sum the numeric columns upwhilst displaying one one of the records, the blah fields don't matterif one only one is displayed. Can anyone recommend the best way to dothis? I'm running MS SQL 2005.Thanks,Herman
This may seem like a simple question to be asking but I’m not the most experienced working with DTS loads and can't understand (don't know) why my load is failing.
I am trying to load in a text file comma separated into a table I have created.
As far as I can se it should be working but it gives me the error:
The number of failing rows exceeds the maximum specified. TransformCopy 'DTSTransformation_4'conversion error: General conversion failure on column pair (source column 'Col004(DBTYPE_STR), destination column 'Result1' (DBTYPE_NUMERIC)).
Help.
Can't figure out what’s going on so any ideas would be useful.
I have a table on SQL with numeric field (18,3), when create a cursoradaptor the become the a numeric field accept only int Value not allow to accept decimal. How to enable the field to accept decimal.
I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.
SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles
What I require is to group the Codes and sum the Times for each code based on the text value of the code.
Code: CODE DATE1 HOURS2 DATE2 HOURS2 AAA 01.01.2015 1:25 02.01.2015 0:15 BBB 01.01.2015 0:55 02.01.2015 0:10 CCC 01.01.2015 1:15 02.01.2015 1:15 DDD 01.01.2015 0:00 02.01.2015 1:00 The query will alway be run for a week so DATE1-DATE7 and HOURS1-HOURS7 required.
AAA BBB CCC etc can be of multiple options so these can't be hard coded. eg there could be a ZZZ or ZAC etc.
I have a table: batch_date numeric (8,0) batch_nmbr numeric (5,0) batch_ctr numeric (4,0) col4... col5... col6...
I am trying to select data using something like: select col1, col2 FROM tablename WHERE (BATCH_DATE + BATCH_NMBR + BATCH_CTR) >= (:BATCH-DATE + :BATCH-NMBR + :BATCH-CTR)
this is a mssql cursor embedded in a microfocus cobol program.
I've generated a report using Reporting Services, and some of my fields are numeric with the value zero. On generating the report this is shown as 0.00 and not 0. Is this something that is easily fixed?
If I create an index on a field in SQL Server, what will be the most efficient (fastest) field type to index a field? (This field will be a "Pointer" to a child table that will contain a list of codes, and their description.) Would a Numeric field be quicker than a VarChar field? VarChar would make it easier for a Human to decipher the raw records. (For example, if I used a numeric the code would be 42 or 47, while the VarChar could be 'savings' or 'checking'.) Basically I will have the following "Master" table: FieldType --------- IDInt NameVarChar StatusInt -or- VarChar Customer_TypeInt -or- VarChar If Customer_Type is a code that can be looked up in another table, and I index that field, would I want the "Code" to be an Int or VarChar? SQL: Select * From Master Where Customer_Type = <42> or <'savings'> My Where clause would depend on the field type. Thank you, Bryan
I have 2 varchar fields on MS 2005 table First field is date and format is 080118(YYMMDD) and second is salary field like 00002000(positive) and 00002000- (negative) how can I move them to date and numeric fields on another table....
I'm attempting to use the isnull function to convert null values in a column to a blank. It works on the alpha field but not the numeric and I was wondering what can be done for numeric field conversion.
I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.
Patient Date Albumin Adams, John 01/28/2007 4.1 Adams, John 12/30/2007 3.9 Adams, John 01/15/2007 3.2 Barker, Mark 01/18/2007 4.3 Barker, Mark 01/22/2007 4.1 Barker, Mark 01/05/2007 3.9
However, when I try to group by Albumin, Patient, it just sorts by Albumin. Patient Date Albumin Barker, Mark 01/18/2007 4.3 Adams, John 01/28/2007 4.1 Barker, Mark 01/22/2007 4.1 Adams, John 12/30/2007 3.9 Barker, Mark 01/05/2007 3.9 Adams, John 01/15/2007 3.2
What I'm looking for is this: Patient Date Albumin Barker, Mark 01/18/2007 4.3 Barker, Mark 01/22/2007 4.1 Barker, Mark 01/05/2007 3.9 Adams, John 01/28/2007 4.1 Adams, John 12/30/2007 3.9 Adams, John 01/15/2007 3.2
Is this something that can be done with grouping and sorting?
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
I'm getting the above when trying to populate a variable. The values in question are : @N = 21 @SumXY = -1303765191530058.2251000000 @SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right When I try the following ie to populate a variable with that value I get the error - SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
I am trying to check a list (MyList) against another List(SupplierList).I want sum the Qty's of UniqueID on MyList and extract the sum of thesame UniqueId's on SupplierList.BTW There are more than one instances of Unique Id on each list.The Script below is providing me with the correct answer for someproducts (UniqueId), but incorrect amounts for others.The incorrect answer is always a multiple of the correct answer.What am i doing wrong???Regards,CiaránSELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]= SupplierList.[Unique ID]GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]
I'm trying to get a sum but not doing too well. I think I need a subquery but am unsure how to phrase it.
Problem: I need to sum timesheet hours logged at work-code level to project-level (for named projects), where a project consists of 0-to-many work-codes. The 'Project' table is used for both projects and work-codes; the 'pr_code' contains the unique code (i.e. the work-code or the project-code), 'pr_master' field contains the parent. The Timesheet table will contain pr_code's for work-codes, but won't contain an entry for a work-code if no-one has logged any time to a work-code.
Intended output: For named projects QWER, QWET & QWEY:
QWER|16 QWET|2 QWEY|0
I've got the following so far which almost gets there, but appears to be summing up as it goes i.e. QWER=16, QWET=18, QWEY=18:
SELECT p1.PR_Master AS Expr1, SUM(Timesht.TS_Hours) AS Expr2 FROM Timesheet LEFT OUTER JOIN Projects ON Timesheet.PR_Code = Projects.PR_Code LEFT OUTER JOIN Projects p1 ON Timesht.PR_Code = p1.PR_Code WHERE (p1.PR_Master IN ('QWER', 'QWET', 'QWEY')) GROUP BY p1.PR_Master
This is a working 12 month intrest equation. I used this for the layout section but I am trying to take this and it gives me the correct values. But what I need to do next is have it sum those values.
I tried =SUM( whole expression but that didnt work) you can laugh at me I know but any help would be great!
=Switch(Fields!eqprecdt.Value< CDate("1 Jan 2007"),Fields!bookvalue.Value*datediff("d",Now(),#1/1/2007#)* .07/365,Fields!eqprecdt.Value> CDate("1 Jan 2007"), Fields!bookvalue.Value * datediff("d",Now(),Fields!eqprecdt.Value)* .07/365)*-1
SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice FROM Table1 WHERE BillDate BETWEEN (SELECT Min(BillDate) FROM Table1) AND (SELECT Max(BillDate) FROM Table1) GROUP BY BillDate
but this doesn’t work- summing everityng
I don’t know how to indicate in first row of query SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice a WHERE clause for every day separately.
As my name shows I am about read to pull my hair out on this and will take any help that I can get. I have a table with the following values field1,field2,field3 a | p | 1 a | n | 1 a | p | 2 b | p | 2
b | p | 2 b | n | 3
I am grouping by first column a p 1 n 1 p 2 ------------------- a 3 1
b p 2 p 2 n 3 ------------------ b 4 3 What I want to do if it have a value of p I want the value in one column if it has a value of n I want it in another column. The columns are not a problem, I use a iif statement iif( field2 = p, value, 0) iif ( field2 = n, value, 0)
the problem comes when I try to total the columns. I was trying to use the =sum(field3) in my group total. the above example is what I want to see the below example is what I get. a p 1 n 1 p 2 ------------------- a 4 4
b p 2 p 2 n 3 ------------------ b 7 7
I hope this makes some since to someone out there that can help me out. I am getting kind of thin in the hair department so I cannot afford to loose any more.
Hi,I need some help in summing each column in a gridview.id name sun mon tue wed total1 Tim 5 6 5 10 263 Sam 6 6 6 5 23The above is how the gridview looks like. In the database, I have all the fields except for total. So, I know I have to use the SUM function in SQl to get the Total. So, I am wondering how do I sum each column to get the total. I have something like this but it doesn't work:"SELECT ID, name, Sun, Mon, Tue, Wed, SUM(Sun + Mon + Tue + Wed) AS Total FROM testTable"Please helpahTan
I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type): ****************************************************************** SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1" WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999')) GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
********************************************************************** Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
Hello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.
I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.
So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.
This is my Access query: SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight, Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost, [TotalCost]*17.5/100+[TotalCost] AS [Total+VAT] FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
And this is what I have been able to salvage into MSSQL format:
SELECT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Rooms.CostPerNight, Room_Facilities.FacilityCost FROM Rooms INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID) INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
How can I total the three columns and add the tax?
Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.
We recently added a new database at the company. It has only onepurpose - to hold massive amounts a daily data generated by telephonecalls on a network.The amount of data was so large (several gigabytes a day) that the guywho set up the database creates a new table for it each day.His thinking was that if we only need to query one day's worth of datathen it would be a lot faster to query a table with one day's datathan having to query many days of data in one table.I see his reasoning. Any comments or alternatives to this schemewould be appreciated.Here's the question though...I'm writing a front end for this and waswondering if the most efficient way to query and sum data acrossmultiple tables (days) is in the form of the following statement.Suppose three days of worth of data are wanted:select sum(ET) from (select sum(vc_elapsed_time) AS ET fromswitch2030608 where init_cell_info_cell = 196 union all selectsum(vc_elapsed_time) as ET from switch2030609 whereinit_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ETfrom switch2030610 where init_cell_info_cell = 196 ) tIn my front end, based on user input, I plan to keep extending thisstatement with more union alls. Is this the best way to implement thegoal of this query?-David
