I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL] CREATE TABLE [dbo].[PARAMEXAMPLE]( [Entity] [varchar](16) NULL,
I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.
I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;
fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ
FROM dbo.fnWTRalldataReport(@dt_src_date, @chr_div, @vch_portfolio_no, @vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN
dbo.fnWTRbudgetdata(@dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref
The result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.
To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).
Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.
This might seem confusing, but it is easier to read than the other thread I assure you.
We have a SQL 2008 Instance existing on active/passive cluster with 2 nodes running on Windows server 2008 R2 Ent. Edn.
Now we need to install another SQL instance on this cluster. So what are the prerequisites apart from new IP address? Do we need shared disks or can the existing disks can be utilized?
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column: thisisavalue thisisanothervalue thisisanothervalueagain shortval
replaced with xxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx xxxxxxx
My current function is replacing the data like this: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator): =CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1])) Records in time interval (numerator): =COUNTROWS(analyticJudConsist) Percent: =[countrows]/[denominatorCare]
parent | NAme | Checked | contactmethod|Check2 | Other 974198 | Employment | true | Face to Face | true | null 974224 | Other | true | Face to Face | true | skills 974224 | Other | true | Contact | true | skills
I'd like to pivot on "parent"
In a perfect world I'd like to see output like
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face, Collateral Contact | true | skills
If there are more than one name or contactmethod for the same parent then they would be strung along with commas
I want to insert the above temp table to another table with column names defined below like this
Empname, Vacation Hours, Sicks Hours EmpA, 12, 10
Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.
I'm relatively new to Reporting Services and .Net development so bear with me if I've missed something obvious. I've set up Reporting Services to run under forms authentication successfully. I want to filter the data (both within Report Builder Models and .rdl files) to the company the user belongs to.
To avoid joining the users table to all other tables on the companyID I'm wondering if it's possible to create a custom funciton which could use the UserID to lookup the companyID and then pass this through as a parameter, and have this "GetCompanyID" function available when building Models and/or reports...???
When building a model the Information Functions available are GETUSERID and GETUSERCULTURE. Is it possible to modify how these work and/or create additional functions here???
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
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'
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 am trying to use get date function, I want dates of my column in between, first of this month and first of the previous month, at this moment the expected result would be: (09/01/2015 and 10/01/2015)..
Seems like it would involve datediff and dateadd both...
I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
Recently I have come across a requirement where i need to design a table.
There are some columns in table like below with DECIMAL Datatype:
BldgLength
BldgHeight
BldgWeight
Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.
Now as per MSDN,
Precision => 1 - 9 Storage bytes => 5
so i can create column as:
BldgLengthDECIMAL(6,2) DEFAULT 0
OR
BldgLengthDECIMAL(9,2) DEFAULT 0
Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.
So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.
So my question is what should be value of DataType for above column ?
We are trying to upgrade a SQL server 2000 to a SQL 2008 R2 (SP1) server. After migrating, the developer test code, and got an error: ERROR [HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error...But this is a release for 2008, ours is already 2008 R2 SP1, so that hot fix should already be included since it is always cumulative.
I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?
Legal Name ////////////////////////////////////// Legal_Entity_Struct
S & H Farm Supply, Ltd.////////////////////////////Company F.M.Abbott Power Equipment,Co.///////////////Company Ray's Dixie Chopper, Inc.////////////////////////// Company Alexander, Justin N. ///////////////////////////////// Company Alameda Power Equipment, Inc.//////////////// Company
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000)) RETURNS VARCHAR(8000) AS BEGIN
I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.
The error is: Incorrect syntax near '.'
the query in question resembles this:
Select column1, column2 from Table1 T cross apply function(t.column4,t.column5) F where column3 = 'XXXX'
I made sure that the compatibility level is greater than 90 this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)
Can it be the .net framework? Although the machines had .net framework 3.52.
Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2
I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.
