Totaling Different Table Columns Within SQL
Feb 18, 2007
Hi,
I have 3 tables which are part of my query and I need to total one column from each of them, but cannot get it to work. I had this working within Access using the nz function (below), but when trying to use this within VS to produce my ASP.net code did not work, saying NZ was not a valid function. nz(HW.HGF)+nz(HD.HGF)+nz(HL.HGF) AS FWhere HW,HD,HL are the table names and HGF is the same column name in each which I want to total.Any help would be grately appreciated.Thanks
View 3 Replies
ADVERTISEMENT
Jan 16, 2008
I need to figure out hours based on StartTime and EndTime (year does not matter) from the first table and totaled for the entire week based on the effective date in the second table.
I need it to return the follwing:
WorkHourGroup TotalWorkHours
TJOHNSO 24 Hours
Table 1 -'workhour'
WorkHourGroup DayIDStatrtTimeEndTime
TJOHNSO0NULLNULL
TJOHNSO11899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO2NULLNULL
TJOHNSO31899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO4NULLNULL
TJOHNSO51899-12-30 09:00:00.0001899-12-30 17:00:00.000
TJOHNSO6NULLNULL
Table 2 -'workhourgroup'
WorkHourGroupWorkHourDescEffective Date
SMBSMB Work Week2007-09-11 00:00:00.000
SMITHBSTANDARD2008-01-12 00:00:00.000
TJOHNSOJohnson12008-01-11 00:00:00.000
Any ideas on how to accomplish this?
Thanks,
DZ
View 2 Replies
View Related
Apr 30, 2008
I have a query that I have written that gives me a record for each "customers" total invoice. I would like to only output one line for each customer but the total for all of that customers invoices.
Like this:
Customer Total
--------------------
Customer1 1000
Customer2 1123
Customer3 1134
etc...
So I need to find a way to total up each customers' individual lines.
Here is the query:
Code:
SELECT DISTINCT
dbo.ARHEADER.[INVOICE NO] AS [INV No], dbo.ARHEADER.[INVOICE DATE] AS [INV Date], dbo.ARGLTRAN.[BATCH NO] AS [Bat. No],
dbo.ARGLTRAN.[BATCH YEAR] AS Year, dbo.ARGLTRAN.[BATCH PERIOD] AS Month, dbo.DEBTOR.NAMES AS [Customer Name],
dbo.SALESREP.NAME AS [Sales Rep], dbo.DEBTOR.CSR, dbo.ARHEADER.TOTNET
FROM dbo.ARHEADER INNER JOIN
dbo.ARGLTRAN ON dbo.ARHEADER.[INVOICE NO] = dbo.ARGLTRAN.[INVOICE NO] INNER JOIN
dbo.DEBTOR ON dbo.ARHEADER.[DEBTOR ACCT NO] = dbo.DEBTOR.[AC NO] INNER JOIN
dbo.SALESREP ON dbo.ARHEADER.[SALESREP RECNUM] = dbo.SALESREP.[DATAFLEX RECNUM ONE]
WHERE (dbo.ARGLTRAN.[BATCH TYPE] = 'DI') AND (dbo.ARGLTRAN.[BATCH YEAR] = 2008) AND (dbo.ARGLTRAN.[BATCH PERIOD] = 01)
Help is appreciated!
View 3 Replies
View Related
Jul 20, 2005
Table1:SessionID intHours int....SessionID Hours111 3222 2333 3444 2Table2:SessionID intRegistrationID int....RegistrationID SessionID888 111888 444777 111666 222666 333I want to sum the hours for each person spent in sessions.Results I'd like to see:RegistrationID hours888 5777 3666 4What is not working:Select hours, registrationIDfromtable1, table2wheretable1.SessionID = table2.sessionIDGroup by registrationID, hoursThis adds up ALL the hours and then groups them by person.Any help?John Mosey "Humping your theoretical mom since 1993"Complaints can be sent to Join Bytes!The sun rises in the east, dumbass
View 2 Replies
View Related
Sep 9, 2015
From the image above, I am totaling the column HHMMSS is SSRS with the below expression:
=CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))+sum(CInt(split(Fields!HHMMSS.Value,":")(1)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1))) mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60)
I equally tried:
=iif(Fields!HHMMSS.Value = "", "", CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(0)))
+sum(CInt(split(Fields!HHMMSS.Value,":")(1)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(1)))
mod 60+sum(CInt(split(Fields!HHMMSS.Value,":")(2)))60)
&":"& CStr(sum(CInt(split(Fields!HHMMSS.Value,":")(2))) mod 60))
and nothing works. What could be wrong with my expression?
View 7 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Sep 19, 2007
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
View 2 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
May 26, 2015
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
View 4 Replies
View Related
Jul 20, 2005
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View 2 Replies
View Related
Apr 4, 2008
I'm in the process of converting legacy DTS packages to SSIS. I need to populate a table that has more fields than the source file. In DTS I did this with an ActiveX script. How do I go about doing this within SSIS.
In the ActiveX script most of the fields were defaulted with either spaces or zeroes.
One of the Destination fields needs to be incremented by 1 for each new record inserted.
Any help would be appreciated.
Thanks,
Jeff
View 6 Replies
View Related
Dec 23, 2013
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID
column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
View 1 Replies
View Related
May 4, 2006
Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId | Property1 | Property2 | Property3 | PropertyType1 | PropertyType2 | PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.
View 3 Replies
View Related
Jan 20, 2014
I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??
View 6 Replies
View Related
Feb 12, 2007
mahesh writes "HI,
I am new to sql server.
can anybody help me
I have a table named tblqualificationmaster.
can i know the foreignkeys and the table related to this
tblqualificationmaster having foeign keys using stored procedure."
View 1 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Mar 20, 2006
I have a table A, with two ID columns. In a report both ID colums shouldbe shown with the actual value stored in a second table, BThe problem is, both IDs need to be looked up in B, but are not in thesame row.How do I do this in an efficient way? A sub select?Thanks,--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html
View 5 Replies
View Related
Feb 5, 2007
I have two tables:
Orders, with OrderID as primary key, a code for the client, and a code for the place of delivery/receipant.
Both the client and place of delivery should be linked to the table:
Relations, where each relation has it's own PrimaryID which is a auto-numbered ID. Now I want to substract my orders, with both the clientcode, and the place of delivery code linked to the relations table, so that for both the name and adress is shown.
I can link one of them by:
InnerJoin On Orders.ClientID = Relations.ClientID, but it's not possible to also link to the ReceipantsID. Is there a way to solve this?
View 7 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Jul 19, 1999
What is the easiest way to determine the number of columns in a table (in a particular database)? Also is there any way to get the column id associated with each column (is there such thing)??
NOTE, this needs to be done with only knowing the name of the table and database and nothing else.
All help is greatly appreciated.
Thanks in advance.
ziggy
View 1 Replies
View Related
Sep 13, 1999
I need to find out the number of columns per table for ALL of my tables in
my database. This way I can compare my old copy of the database to the new and make sure that any new columns are there.
Thank you,
Tracy Young
View 2 Replies
View Related
May 12, 2008
Hi,
I have a table (that i get as a result of pivots and joins in a stored procedure) that has two columns with the same name, (each time the stored procedure is called, different columns will be in this situation). For every row, only one of these columns has a value and the other has NULL.
Does anyone know how to do prevent this situation during the JOIN that creates this table? OR of a way to merge these columns once they are there ?
Thanks !!
View 2 Replies
View Related
Oct 15, 2007
i have a table with 6 million rowwhat is the affect of increasing a varchar(255) columnto varchar(500)tiaMJ
View 1 Replies
View Related
Nov 27, 2007
HI,
I have a procedure when i need to get the columns of a table and store them in a temp table and process further.
For this i am planning to use sys.columns table as follows:
SELECT OBJECT_name(c.object_id) TableName, c.name ColumnName
FROM sys.columns AS c
Can I use this table in a procedure? Do I need to have any permissions to use this table?
If so, is there any alternative to get the columns of a table so that I wont use sys.columns table.
Please advice on this
Thanks in advance
View 4 Replies
View Related
Apr 8, 2008
I have a 2 columns ID and DriverID.I need ID and DriverID column's in such a way that
what are the settings should be set?
ID DriverID
1 0001
2 0002
3 0003
4 0004
....
999 000999
View 3 Replies
View Related
Nov 15, 2005
I want to build a page that will handle all my list table... how can i get the names of the colums in specific table if i got the table name?
View 8 Replies
View Related
Jan 5, 2006
I want to get the Primary Key Columns in Arrays by sending a tablename. I am using SQL Server 2000 and I want to make a find utility in VB.net whichwill work for all the forms; I have tables with one Primary key and some tables with composite Primary keys. I used to do this in VB 6 by making a function which fills the Primary Keys inList Box (I require to fill in list box), now I need to get in array. Can some one tell me the migration of the following VB 6 Code? This was written for the MS Access, I need same for SQL Server, I cannot find Table Def and Index Object in VB.net 2003. Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName AsString) Dim lttabDef As TableDef Dim liCounter As Integer Dim liLoop As Integer Dim idxLoop As Index Dim fldLoop As Field
With ldbDatabase For Each lttabDef In .TableDefs If lttabDef.Name = lsTableName Then liCounter = lttabDef.Fields.Count For liLoop = 0 To liCounter - 1 cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name Next liLoop For Each idxLoop In lttabDef.Indexes With idxLoop lblIndexName = .Name If .Primary Then liCounter = 0 For Each fldLoop In .Fields cboPrimaryKeys.List(liCounter) = fldLoop.Name liCounter = liCounter + 1 Next fldLoop End If End With Next cboFieldLists.ListIndex = 0 If cboPrimaryKeys.ListCount > 0 Then cboPrimaryKeys.ListIndex = 0 End If Exit For End If Next End WithEnd Sub
View 2 Replies
View Related
May 26, 2006
in sql server 2000, I have a table that I have to write a script for to delete several columns in this table. I am finding that I have to use alter or drop keywords or a combination of the two but not sure because I have not done this before. I am googling this but finding all kinds of other information that I dont' need to know.
I dont have rights on this table so I cannot do this manually. I have to create the script and send it on to someone else.
If anyone can provide a good script example that I can use to delete unwanted columns it would be a great thing. Thanks.
View 1 Replies
View Related
Jan 5, 2002
At first I have to apologize that I write here. This is not right place for my problem but I could not find any better place to ask help.
I'm creating a new table and I have to copy some columns from the old one to the new. Desciption of the old table is something like that:
Table1: name char(32) not null primary key, variable1 char(32), variable2 char(32).
Now I would like to copy columns variable1 and variable2 to the new table (called Table2). I need to change the names to the id-numbers. (Don't ask me why, I just have to :) I have created Table2. Table2: id int not null primary key, newvariable1 char(32), newvariable2 char(32).
Now the problem is that how can I copy just columns variable1 and 2 without the name-column (witch is the primary key)? I just an error message:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=578, COLNO=0" is not allowed. SQLSTATE=23502
It just says that (if I understood the message) I can't leave the id-column blank. How can I generate the keys to Table2? It would be nice to get keys from 1,2,3...
Please help me. :)
View 4 Replies
View Related
Oct 16, 2004
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
View 2 Replies
View Related
Jan 11, 2006
How can I determine the number of columns in a table?
View 3 Replies
View Related
May 21, 2012
I am two table. They are backup of each other. I would like to find out what the columns of a row that need to be sync.
For example
Record with ID = 2345, Update Name (From, to) (IreneLee, Irene)
Record with ID = 2345, Update Birthday (From, to) (1975-04-24 , 1975-04-24 )
Record with ID = 4568, Update Name (From, to) (VictorChu, Victor)
I use this statement
with cte1 As
(
Select T1.ID As 'ID',
T1.Name As 'T1Name',
T2.Name As 'T2Name',
T1.Birthday As 'T1Birthday' ,
T2.Birthday As 'T2Birthday'
from T1 inner join T2 on T1.ID = T2.ID
)
Select * from cte1
(eventually I have to programatically generate the above statement)
Below is the result:
ID T1.Name T2.Name T1.Birthday T2.Birthday
--------------------------------------------------------------
1234 Richard Richard 1971-02-09 1971-02-09
2345 IreneLee Irene 1975-04-24 1975-04-24
3456 Danny Danny 1962-09-08 1962-09-08
4568 VictorChu Victor 1962-09-08 1962-09-08
And I use the below script
DECLARE @tableName varchar(max)
Set @tableName= 'T1'
Select column_name From
(
SELECT column_name
[Code] .....
I need to find out what is different between each similar column (e.g. T1.Name and T2.Name) for each row It looks like I need two loop.
This is what the logic I think I need.
For (row in Rows for JoinTable) {
For(column in Columns for ColumnTable) {
if ('T1'.column <> 'T2'.column) {
Insert ('T1.ID' , 'Update T2. ' + column name, 'T1'.column, 'T2'.column)
into #ChangesTable
} } }
View 2 Replies
View Related
