I have a a Group By query which is working fine aggregating records by city. Â Now I have a requirement to focus on one city and then group the other cities to 'Other'. Â Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
I have a scenario which I am not able to figure out how to do it better for quite some time.
Assume I have a few rows of data : RunningID Date WOid 1234 1/23/2007 23 1236 1/24/2007 23 1239 1/2/2007 24 1222 1/4/2007 23 1321 2/4/2007 22
My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as: select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX group by WOid
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
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?
I'm working on a fairly complex query, and to avoid exploding my brainany further, I'm going to keep this simple with an example of what I'mtrying to do...Let's say you had a table of customers and a table of ordersAnd you had data that looked like this:CustomersCustID CustName-------------------------------1 Billy2 JohnOrdersCustID Amount PaidYN-------------------------------1 $2 12 $3 01 $4 11 $2 02 $5 0And you wanted a report that looked like this:Customer TotalPaid TotalUnpaid------------------------------------------Billy $6 $2John $0 $8How would you write a query to generate this report?
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.
select max(PTR_DATE) from MPR_portfolio_transactions group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE from MPR_portfolio_transactions group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I am trying to aggrgate Values on three columns Customer , OrderDate and Product thorough SSIS. It gives me following masseges and works very very slow.
Before Aggregation I sort the Data by Customer , OrderDate & Product.
The Aggregate transformation has encountered 4085 key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 25797 key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 253973key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
The Aggregate transformation has encountered 2000037key combinations. It has to re-hash data because the number of key combinations is more than expected. The component can be configured to avoid data re-hash by adjusting the Keys, KeyScale, and AutoExtendFactor properties.
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this: Dept Project Cost_Mean Cost_Range D1 D1P1 495 135 D1 D1P2 960 70 D1 D1P3 1375 105
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
Hi, I have 3 tables: Table SLA: SLA_code, SLA_name Table industry: Industry_code, Industry_name Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq
I would like to query to get in one table: SLA_name for DESTINATION, Industry_name, Freq
Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.
How can I query to get the Freq and Industry for those destination SLAs?
I have make a relationship between: Table SLA.SLA_code with Table14.SLA_code_destination and Table_industry.Industry_code with Table14.Industry_code
But I am not sure if just a SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq FROM SLA, Industry, Table14 would select the SLA_destination and not the SLA_origin.
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€¦
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 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?
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...
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.
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!
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
we have a database hosted online on sql server 05. at work, we have a sql 2000 query analyzer on a machine, and sql 2005 management studio on a machine. both machines can connect to the database, both can pull up the data from a certain table. but there are two columns that display info in query analyzer that show up as empty on management studio. not null, just empty. any ideas why?
