Column Based On Other Column (short Name Based On Name), When To Do The Transformation?
Oct 18, 2007
Hi!
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:
Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName
Create a derived column in the SSIS flow which does the same thing
Create the ProductShortName column as a computed column which uses substring on ProductName
Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated
Create a named calculation in the table in the Analysis Services project's data source view
Create a named query in the Analysis Services project's data source view
I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
I want to have an other employee table named employee_modified
Empno empname salary commission derived_column1(salary+commission) derived_column2(derived_column1 + xxxx) and so on derive other columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
The report runs but I get a "#ERROR" when I place the field on the report.
I next tried creating a new column with the SQL statement:
SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours, Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID, Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =
Case When TaskRule.ChargeableFlag = 0 Then 'Non-Bill' When TaskRule.ChargeableFlag = 1 Then 'Billable'
[Code] .....
This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error: "Invalid column name 'Negamt'."
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:
UPDATE [statistics] SET percentage = follow_count / view_count * 100 WHERE (stat_id = 15)
This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)
Table 1 --------- id | Name --------- 1 | John 2 | Debbie 3 | Kim 4 | Mary
Table 2 --------- id | Name --------- 654 | John 415 | Debbie 68 | Kim 289 | Mary
Table 2 id's values are all messed up and I need to replace them so that they look exactly like in Table one, my guess is that query will have to replace value of "Id" based on the "Name" column, I hope I made myself clear.
I have this table where there is tow primary keys ,one for Year and the other for DocNumber.
In this table the DocNumber takes a number based on each year,for example in year 2008 the DocNumber should start counting from 1 and increment by one for every record,when the year changes to 2009 the DocNumber should start again from 1 until and seed by one again.
Those columns are PKs because there must be one doc Number for each year and they can not be null .
Is there any simple way to approach this in Sql Server 2005 ,or do I have to work on trigger to do so...???
I have a column with serial numbers 1, 2,3,4,....etc...I want to have P1 for first 4 serial numbers to be displayed in a separate column beside Serial number column and for the next four serial numbers (5,6,7,8) it should be P2 and like that I want till P13 and for next again it should start with P1(I mean after P13 it should give again P1 instead of P14) and continue the same process.
Column name of Serial number is [S.No] table name for example is #temp1
I am trying to do the following. While inserting a record into a table I need one field to be created based on a stored procedure output.The stored procedure will create something like this, "XX-mm-yyyy-incremental number which resets to zero at the end of every month". All other field except the Identity field and the above mentioned storeproc generated field will be inputted manually. How can I do this? Thanks Hope my question is clear enough.
I have a report with several columns which include Status(0,1,2) and Time in hours. I want to be able to total up the time for each status. I Sum up the all of the fields fine. I'm new to reporting services so any help would be appreciated. Thanks
I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.
The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record
-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.
Everytime I to set the value of a field or parameter at runtime I get a read-only error.
I'll try to simplify the problem as much as I can.
There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.
tbl_results IDresult (primary) IDquestion (foreign, to tbl_question) result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")
Inside those tables are data (I'll put here just two questions):
Example: tbl_questions IDquestion_question 1_________Which OS do you use? 2_________Which databases do you use? tbl_answers IDanswer_IDquestion_answer 1________1_________Windows 2________1_________Linux 3________1_________Mac OS 4________2_________SQL Server 5________2_________MS Access 6________2_________MySQL 7________2_________Oracle 8________2_________Other 9________2_________No, I do not use databases tbl_results IDresult_IDquestion_results ........ 23_______1________1,2 24_______2________4,6,7 ........ Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?
Result of the query would be: IDquestion_IDanswer_answer 1_________1________Windows 1_________1________Linux 2_________4________SQL Server 2_________6________MySQL 2_________7________Oracle
Further queries based on this query should be able to make statistics (GROUP BY).
SELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId FROM SR.dbo.PayTrays WHERE WorkDate > '2012-01-01' AND WorkDate < '2012-12-31' GROUP BY EmployeeId, PiecemealType, ActivityId ORDER BY EmployeeId, PiecemealType
PiecemealType is the type of tray 1= 15 Items per tray 2= 12 Items per tray 3= 8 Items per tray 4= 6 Items per tray
I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:
/images/imagename.jpg
Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:
/images/a/aimage.jpg /images/b/bimage.jpg
I can automate the physical move of the images into the correct directory but I need SQL update query that will update each column based on the 1st letter of the image.
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
My Query: SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.
I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.
I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.
SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage FROM (SELECT ID, CD, TotalAHD, CAST ((SELECT SUM(TotalAHD) AS Expr1 FROM SumAHDforAttndPercentages AS p
I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below.
SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber", dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType", dbo.vw_tphillip_Customer_Inv.Date AS "Date", dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate", dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",
Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.
-------------------------------------- UPDATE dbo.Asgnmnt SET CustomerPending = 'Y' FROM dbo.Asgnmnt WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')
ALTER Table Asgnmnt ALTER column CustomerPending varchar(1)
UPDATE dbo.Asgnmnt SET CustomerPending = 'N' FROM dbo.Asgnmnt WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')
ALTER Table Asgnmnt ALTER column CustomerPending varchar(1) ---------------------------------------
I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?
I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example
EdgeID Desc TermType ReltTotID
1Global Edge Model w/ Fwd Earn II T 1 2Short Term Global Edge Model w Fwd Earn IIS 1 3Long Term Global Edge Model w Fwd Earn IIL 1 4Emerging Market Edge Model w Fwd Earn T 4 5Short Term EM Edge Model w Fwd Earn S 4 6Long Term EM Edge Model w Fwd Earn L 4 19SmallCap Edge Model w/ Fwd Earn T 19 20SmallCap Short Term Edge Model w/ Fwd EarnS 19 21SmallCap Long Term Edge Model w/ Fwd EarnL 19 35Global+EM Edge Model w Fwd Earn T 35
The final query result is :
EdgeID Description Short Desc PerID UnivID DefID
1Global Edge Global Developed 500622355938 4Emerging Market Emerging Markets 500632356039 19SmallCap Edge Small Cap Edge 500642364244
I would like it to be :
1Global Edge Global Developed 500622355938 2Short Term Global Developed NULL2355938 3Long TermGlobal Developed NULL2355938 4Emerging Market Emerging Markets 500632356039 5Short Term Emerging Markets NULL2356039 6Long Term Emerging Markets NULL2356039 19SmallCap Edge Small Cap Edge 500642364244 19Short Term Small Cap Edge NULL2364244 19Long Term Small Cap Edge NULL2364244
JOIN OptMod..GO_Models m ON em.EdgeModelID = m.ModelID AND m.ModelType = 'E' AND Status = 1
JOIN OptMod..GO_EdgeModelDisplayParameters emdp ON emdp.EdgeModelID = em.EdgeModelID AND emdp.ParameterName = 'NewEdge32 Screening'
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn ON emdn.EdgeModelID = em.EdgeModelID AND emdn.ParameterName = 'NewEdge32 Display Name'
LEFT JOIN OptMod..GO_ModelUniverses mu ON em.EdgeModelID = mu.ModelID
LEFT JOIN OptMod..vUniverses univ ON mu.UniverseID = univ.UniverseID
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm ON emdp_perm.EdgeModelID = em.EdgeModelID AND emdp_perm.ParameterName = 'NewEdge32 Permissions'
I don't immediately find if this is possible but hope someone can give me an answer: is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..) instancetype can be A or B if it is A then instancename must be unique but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
