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 would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
I want to filter out the duplicate rows based on three columns. I got this quick query from Microsoft site to filter out the duplicate rows, but I am getting the result that filters out the non-duplicate one too. Below is the query
;With Temp as ( SELECT row_number() over (partition by [id],[p_date], order by [id],[p_date],) as Row,
[code]...
In the above case id is null, but in some rows id is not null . The above is obviously not duplicate.
I have a table that holds a number of offers made to a orginization for placements at a lecture. what im wanting to do is have each of the rows for a orginization repeated so that the names of people attending can be put into the database.
the result im looking to get is something like this where the name of the attendess would be inputed in an application. id | orginization | lecture | nameofattende 1 | orga | lec1 | j. blog 2 | orga | lec1 | s. smith 3 | orga | lec1 | h. samual 4 | orga | lec1 | j. sams 5 | orga | lec1 | b.j. james 6 | orgb | lec1 | m. curry 7 | orgb | lec1 | k. murry 8 | orgb | lec1 | g. hansen
> SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans FROM mytab WHERE mrno = 'MR1514' and tranno = 1111 ORDER BY no_of_trans tranno medrecno createdon createdat nooftrans
I have two tables that I am pulling data from to insert into a third table.
Table 1: Organization Address Date Name 404 St. 12/31/1999
Table 2: Organization Software Type Quantity Name SW1 5 Name SW2 6 Name SW3 7 Name SW4 8
My query looks something like this:
INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity) SELECT Table 1.Name, Table 1.Address, Table 1.Date, (Select Table 2.Quantity Where Table 2.Software Type = 'SW1'), (Select Table 2.Quantity Where Table 2.Software Type = 'SW2'), (Select Table 2.Quantity Where Table 2.Software Type = 'SW3') FROM Table 1, Table 2 WHERE Table 1.Organization = Table 2. Organization
Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE FROM @SPLITROW s INNER JOIN master.dbo.spt_values t ON t.type='P' AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK) WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK) WHERE P.PrsnPIISSN_Chr IN ( SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + '''' FROM MassProcessing_Rules PR WHERE PR.intRuleID = 10 )
I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far. Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end). I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.
I added one crummy column to my table. I updated the stored procedure and added the thing to the aspx page which is my form for adding an article. I have a strong feeling that something is foul over here...Why is it that Visual Studio will not allow me to capitalize the word get and when I delete the () after ShortDesc they immediatlye reappear and the get statement is set to get. Every other one of them, and there are 9 others use GET and there is no () after the public property variable name. Does anyone know the reason for this? Public Property Author As System.String GETReturn _Author End Get Set(ByVal Value As System.String) _Author= ValueEnd Set End PropertyPublic Property ShortDesc() As System.String GetReturn _ShortDesc End GetSet(ByVal value As System.String) End Set End Property
When I view the report on the web, everything is fine until I exported the report to Excel. Additional column and row without heading appear in the exported Excel report. I have no idea, where do these row and column come from as I don't have these additional row and column in the designer. These additional row and column contain the subtotal.
How do I get rid of these additional row and column?
Need to know a mode whereby somehow I can every time insert an additional column in a table while bulk inserting data to an existing table from a new flat file thus identifying the file from which, or the time when, the data was inserted in an existing table.
Hi! This is my trigger and I'd like to insert the date of today in Column DeletedDate. This trigger is in tblA. tblA and tblB both had the same number of columns and same fields, but I just added another column to tblB called deletedDate and i'd like to insert the date along with the other data. Thanks!!! Insert into tblB SELECT* FROM Deleted
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch. 4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
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'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
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".
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
Type Name Value x M1 5 x M2 10 x M3 20 y M1 10 y M2 15 y M3 30
Now, i need to add four more rows to the table
Type Name Value x M1 5 x M2 10 x M3 20 y M1 10 y M2 15 y M3 35 z1 Total 15 (xM1+XM2) z1 Diff 5 (xM3-xM1+XM2) z2 Total 25 (yM1+yM2) z2 Diff 10 (yM3-yM1+yM2)
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.
