I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d =
select amount from exp_data
where period = '1111'
and exp_data.account = split_table.b
and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...
Hi! I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this? My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance!
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA :  abc,cde,efg   ColumnB : cde,xyz,abc  Â
the result in  Â
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS ( SELECT 5060,'04/30/2015','05:30', '08:30' UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30' UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30' UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
Adding more columns in a matrix report that don€™t belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I would add some ratios, that is, Units Sold/Months (sold per month) and other that is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some columns should be precalculated prior to the report so do not get into it, the real problem I don€™t see how to solve is adding one or two columns for showing these calculated column that doesn€™t depend on the column groups but they do for the rows groups€¦
Any guidance on that?
The only way I am seeing by now is to set it as two different reports, and that is not what my client wants€¦
it is my first post on this forum, please be patient if i miss any important bit of information.
i am transporting data from a legacy system into mssql 2k5 using SSIS. among those column of a dataset there are 13 columns, all necessary for operational reasons, that i need to ensure data consistance.
i believe i could do this check using the lookup data flow item, but surely there must be a way to do it in a more streamlined fashion.
since column names contain numbers to distinguish the version, eg; col01, col02, col03 .. col13.
i thought i could include the lookup within a loop and use a couple of variables to do this trick, but since i have not done it before i am asking for some sort of guidance from a guru among you folks.
please let me know if further clarification is necessary.
I have sales for a store per day, I need to add that sales for the day so say on the 4th of the month it has total sales of that day plus all previous days.
Is there a way to add two columns (with numbers) that have already been aliased? I summed the two columns separately, and also want to display a third colum that show the sum of the two already summed columns. Any help is appreciated. thanks.
I have this code and it should bring back code for the month i put in in the set statement. When i put in say may i need to add up numbers for all months prior in the total projections call em and im lost
Declare @Division as Varchar(15) Declare @Date as Varchar(7)
Set @Division = 'Del Sol' Set @Date = '05/2008'
Select [Month], Sum(Projection) as ProjectionTotal From Reporting.dbo.RetailSalesComparison_ProjectionsView PV Inner Join DelsolNet2.dbo.StoreGroup SG on PV.StoreID = OrderStoreID Where @Division = Case DivisionID When 10 then 'Del Sol' When 11 then 'Cariloha' When 12 then 'Sol Kids' else 'Notta' End and Month([Month]) = Left(@Date,2) and Year([Month]) = Right(@Date,4)
I have 21 Boolean fields that I would like to calculate a percentage from. If the boolean value is true I would like to be able to return a percentage of Outcomes available from a possible of 21. So If 7 of my boolean fileds are true then i would like to be able to calculate that 33% of Outcomes are present.
Does anyone have anything that will get me started?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Client] GO
I am familiar with the ALTER TABLE syntax that can be used to add columns to an existing table, but the columns are appended to the end of the table. Enterprise Manager allows you to insert a column in the desired place in the table, but I suspect that behind the scenes it creates a temp table with the new structure, drops the old version of the table and renames the temp table to the orginal table name (I could be wrong on this).
Is there a way to insert a new column in a specific place in a table using SQL rather than EM?
NameCity Client NoAccount No BalanceAccount No Balance SmithSydney 1234561258792 3.95 JonesMelbourne 2589641000657 9.54 BrownPerth 9876541000879 5.461000880 7.51 WhiteSydney 6548521007562 10.65
The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.
Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.
I have a LIVE SQL 2000 database. I am trying to add some new columns to a table. My question is will I need to take the database off-line to perform this operation? I have replicated it to a sample table if I can't.
HiI have a table that currently has 466 columns and about 700,000records. Adding a new DEFAULT column to this table takes a long time.It it a lot faster to recreate the table with the new columns and thencopy all of the data across.As far as I am aware when you add a DEFAULT column the followinghappens:a) The column is added with a NULL propertyb) Each row is updated to be set to the DEFAULT valuec) The column is changed to NOT NULL.However, adding the column as NOT NULL with the DEFAULT seems to take alot longer than if I do steps a) - c) separately.When I say a long time, adding just a single DEFAULT column takesaround 6 hours. Surely it should not take this long?There is a trigger on this table but disabling this does not seem tomake much difference.Can anybody give me any advice on the use of DEFAULT columns please?When should they be used, benefits, disadvantages, alternatives etc.Also should it really take as long as it is taking or is there aproblem with my setup?If I am honest I can't see why DEFAULT columns should be used as thevalues could always be inserted explicitly via the applicationThanks in Advance.Paul
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following table name: DownloadTable fileName | DownloadSize | file1 | 45 file2 | 50 file3 | 20 --------------------------------------------- second table table name: VideoTable fileName | VideoSize | file1 | 40 file2 | 60 file3 | 20 ---------------------------------------- Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following: SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me? Thanks in advance Regards,
Hi guys, I need some help again please. On my page load, I need the following done... I have a label control that must display a certain numeric value. This value must be the sum of an array of values that must be retreived from a table ("Details") with the column name "DealValue" but only where the column "ConsultantID" = txtConsultant.text I understand the select statement, but I don't know how to "run" through the table to add all the values. Can someone please help me with this and how I would construct the MSSql select statement for this. Thanks again
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
select (sum(ColA) + sum(ColB) + sum(ColC)) / 3 from tableA Where ....
I want to add the sum of the columns and then divide it by a constant. The problem I am having is there are a lot of nulls in the database that I am working on (i.e. one of the values in the ColB is Null, which makes the result of the entire expession null).
How do I get around this. I need to add the values of the columns?
i insert the names of the collumns in the design of the sql server and in some of the names it add the brackets. for example [LabsRadiologyBiochemAmylase>125mMolPLitre]. i try to remove them but when i save it, it write them again. can someone explain me how to remove the brackets???
I was wondering what is the best way to have a amount paid total from amounts entered to a specific id or column? I am using SQL 2005. I need to be able to tally the total amount paid and put that value in another column called amount paid. Any help would be great. Thanks,
I've written this procedure that is a meta data manager if you will. It looks at a vocabulary table and will create or alter tables to be in line with the meta data. It works great and has for a couple of years now. However, I just rewrote it in an effort to make it more compact, efficient, etc. Now, our system uses at least three databases. One contains shared data, the next holds only system data for the app like individual user settings, etc (atslogin is the name) then would be an actual database for client data or accounts as we call them. Clients could have one or 100 of those. Please, no comments about moving everything in to one DB as that is not always possible to to hierarchy of multiple business models.
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.
Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:
Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?
