Comparing 2 Fields As A Measure?
Sep 1, 2000
I need to build a cube for membership over time. Active membership is the gap from the record's CreationDate to SuspendedDate, as compared to a reference date (today's active members have CreationDates before today, and SuspendedDates > today, or NULL).
Any idea how to build a cube so I can count membership over a Time dimension? The only approach I've been able to come up with is to create a table with a record for each active member on each date (brute force).
AtDhVaAnNkCsE!
View 1 Replies
ADVERTISEMENT
Apr 6, 2008
Hi all
I have a table with one nText Field , some times i need to realize whether two records have the same value in that nText Field or not , so how can i compare
these two records based on their ntext Fields.? in the other words i'm looking for some thing like the follows:
declare @a nTextdeclare @b nText
select @a=MynTextField from MyTable where PK=18select @b=MynTextField from MyTable where PK=21
if @a=@b print 'Records Are the same'else print 'Record are not the same'
Thanks in advance.
Regards.
View 3 Replies
View Related
Aug 4, 2005
I am working with the article that MAK wrote on SecurityLogs http://www.databasejournal.com/features/mssql/article.php/3515886
I have completed this, but I have made some changes to the database (for normalization to 3NF purposes). I now have problems with a query.
I am trying to "Insert a new record in a table if it does not already exist in the table". To try to clarify I perform the following query:
INSERT INTO Tmp_Event
SELECT DISTINCT EventID, EventType, EventTypeName from Tmp
Which gives me the Tmp_Event table consisting of EventID's etc. (no duplicates). What I then want to do, is compare the 'Tmp_event' table and an already existing 'Event' table. These two tables are in fact identical. I would like to insert any records from 'Tmp_Event' into 'Event' if they do not already exist in 'Event'.
This query gives me all records that do not exist in 'Event'
SELECT EventID, EventType, EventTYpeName from Tmp_Event
WHERE EventID NOT IN (SELECT EventID from Event)
How can I change this query into performing an INSERT INTO Event as well?
Hope this makes sense :)
-Silia
View 2 Replies
View Related
Jan 17, 2002
I would like to compare 2 fields.. One of which is generated by the application upon insert. The other which is today's date.
In other words I want all records with today's date.
Problem is I get no results because I'm also apparently comparing the time.
Do I use a CONVERT function on my select & GETDATE outputs?
Thanks,
Kelly
View 2 Replies
View Related
May 22, 2002
I have a table with a DateTime field that has Dates in the format of mm/dd/yyyy I want to do a simple count on the table for the previous day but keep getting 0 for the results. Here is the script I've been trying.
SELECT COUNT(*)
FROM mytbl
WHERE Collected_Date = DateAdd (dd, -1, GetDate())
It appears to me that the time portion of the DateAdd function keeps the matches that I am looking for from happening. What am I missing or am I going about this all wrong?
Thanks for any help
View 1 Replies
View Related
Nov 29, 2014
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
View 2 Replies
View Related
Jan 28, 2008
I want to do something like this.
Code SnippetSELECT * FROM [scholarship] WHERE ([schlrPrefix] + ' ' + [schlrName] AS ScholarshipName) LIKE 'Ann Buttler'
How do I something like this where it combines two fields and then use that to compare to a parameter?
View 6 Replies
View Related
Apr 9, 2008
My situation is this: I have a requirement table,in the requirement table i have a field which is allow_multiple,if it allows multiple the value is 1 and 0 if it will not allow and also I have an insert storedprocedure for inserting licenses which will be added in the Staffl table.When i insert a new license for the staff,it should check in the requirement table if the new inserted lincense does have 0 or 1 value in the allow multiple field.If it has a value of 0 the new license should not be inserted.What would be the good way to compare the existing licenses of the staff and the newluy added?
Funnyfrog
View 1 Replies
View Related
Jun 18, 2015
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
View 3 Replies
View Related
Jul 20, 2005
I have the following table structure.group1 group2 group1_result group2_result'One' 'Two' 3 2'One' 'Two' 3 1'One' 'Two' 2 5'One' 'Two' 4 1'One' 'Two' 0 5I need to sum up the number of times 'One' is greater than 'Two', andvice-versa. For example, the result I would like to achieve is asfollows.group1 group2 group1_total group2_total'One' 'Two' 3 2I'm using the following SQL statement, but I get 5 rows returned,giving me a '1' or '0' for each row.select group1, group2,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END),sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)FROM table1GROUP BY group1, group2Any help would be greatly appreciated.
View 1 Replies
View Related
May 13, 2015
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
View 4 Replies
View Related
Sep 25, 2007
Hi,
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"
and then 2 Measure to be in the row format rather than columnar format.
Example as below :
Year-Month on the column, and the measure is on the row :
2007-04
2007-05
2007-06
Amount Sales
1000
2000
3000
Unit Sales
10
20
30
Total
1010
2020
3030
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
Thanks
best regards,
Tanipar
View 1 Replies
View Related
Sep 15, 2015
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
View 4 Replies
View Related
May 3, 2015
I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.
I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:
SELECT
 [Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
 ON
COLUMNS,
[code]....
Â
Please note, that I want it in a MDX query not needing to change the cube or DWH table.
View 4 Replies
View Related
May 17, 2001
So far we are happy with MS OLAP 2000 service.
In the past we did cubes with aggregation method using summary.
MS OLAP server allows aggregate functions (Sum, Min, Max, Count, and Distinct Count).
But now the customer wants to know percentage of SUM(measure A) /SUM (measure B) in different levels and dimension combination.
Any idea?
View 1 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Jul 20, 2005
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies
View Related
Sep 14, 2004
Hi,
easy question for advanced users:
I have a dimension called "Movement rate". The members are "1" to "6".
There is a measure called "No. of Products".
What i easily like to do is showing the percentages "No. of Product" for each "Movement rate" of the amount of "No. of Products" without using the frontend-tool.
Thx in advance for any help.
Andreas
View 2 Replies
View Related
Dec 2, 2005
There is count measure in the cube. I need to create a dimension that should list the range of values based on that count values:
Exp:
0-99
100-199
200-399
...
....
..
and so on.
User wants to pick the any range level in the dimension and cube should show corresponding cell values of count measure which would fall into range of the dimension level value.
For example:
if user picks up level 100-199 then cube would filter cell values of measure that has a value between 100 and 199 within 12 month range. So, it would be year to date value of the measure. My problem is how go about creating Dimension that based on measure in the cube and dimension should have level with range of measure values.
Any hint would be appreciated greatly.
Thank You.
View 4 Replies
View Related
Dec 27, 2005
HI all friends
I have a Fact table like this
DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws
I just want make the next query with MDX
SELECT sales
FROM myFactTable
WHERE salesws > 0
Note that salesws is a measure not a dimmension
How it would be in MDX? I need a lot of help
Regards!
View 4 Replies
View Related
Apr 13, 2004
Hi,
for a calculated measure I need the total sum of a measure in different dimension. So for example I want to get in my first dimesion 1500 as my sum and in my second dimension too. Is there a way to get it?
(FirstDimension.CurrentMember.Parent, [Measures].myMeasure) doesn´t work!
Best regards,
Thorsten
View 11 Replies
View Related
May 31, 2004
Hi,
I am newbie in Analysis services. My problem is that I have to show a yearly report for credit expiry. In my fact table the measure is credit rate.
I have attributes of start_day_key and end_day_key. Now when I go through analysis services it does not let me create a measure so that I can count end_day_key for a year (end_day_key is linked to day table with daykey. It has date) and show. The thing is the measure I want is not stored (i.e: the count of end days for a year ). How do I go about it?
Should I create a calculated cell? (I tried that, but mdx does not work)
I used : Count({[Measures].[End Key]})< 366
Now this is not wha I want, I want the end_key count by matching date in day dimension for the current year? Any help is greatly appreciated.
Mdx
View 1 Replies
View Related
Jan 18, 2007
I have a delima.
I have a dataset that needs to return "Fatalities Involving drivers age 15-17". For years 2003 , 2004, 2005.
So i set up 3 different filters: Year : 2003,2004,2005
Person type: Driver
Age : 15,16,17
Now this is fine for getting a crash count and fatalities (for teen drivers). But i want the fatalities for everyone ( All Person Types) and (All Ages) ...how do i go about this. All i get right now, is the fatalities for Drivers 15-17.
Please help..im confused!
View 5 Replies
View Related
Mar 6, 2008
I need to create one or two tables in the database. There are a few ways I can design these tables, so I€™d like to measure data loading performance. My primary concern is level of IO generated by SELECT. I don€™t expect CPU utilization to have any meaningful impact, but it would be nice to know how to measure processor utilization as well.
PerformanceMonitor application is not really an option, because it measures IO and CPU at SQL Server process level.
My development box is not going to match production boxes, so I would like to measure IO in terms of amount of data loaded from disks instead of timing stored procedure execution. The same is true for stored procedure CPU utilization.
It is my understanding that SQL Server keeps table content loaded in memory as much as possible to improve performance. How do I negate effect of caching on my IO numbers?
View 5 Replies
View Related
Dec 28, 1999
I have created a table in the sql server 7.0 and I wanted to know the what is the size of the table, is there any storeprocedure or command that can be used to measure the space used by the table in the database
View 2 Replies
View Related
Dec 16, 2005
Hello
I am a newbye with Analysis Services and am desperately trying to find a way to include a calculation between one of my measures (Teus), divided by the vessel capacity, where vessel is one of my dimensions (and is therefore not depended on other dimensions...)
Any ideas how I could implement that ?? This would help a lot, thanks for your help,
Aurore Bui.
View 2 Replies
View Related
Apr 2, 2004
Hi, everyone:
Does anyone have the idea how to measue the transaction log speed? Do I need a special tool?
Thanks.
ZYT
View 3 Replies
View Related
Sep 27, 2007
Hello,
I have a Sales cube and I want to be able to display Products and the
Price at which they were purchased in a given period. I have a
Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can show Products grouped by their list price.
My Product Dimension consists of product_numbers (such as 100, 101,
102 etc...)
My Sales Fact Table consists of sales data (such as product_key,
price, net_sales, returns, etc...) at the transaction level.
I want to be able to view the data like this:
Price Net_sales Returns
Product
100 $5.99 $2005 $320
101 $3.51 $7110 $998
where Net_sales and Returns are "summed" and Price is simply a "Group
By". In other words, this report would show the net sales and returns
by product for a given price.
I'd rather not use a Price dimension since we have hundreds of
products at hundreds of different prices. Moreover, this data is
already in the Sales Fact table.
Thanks for any help provided
View 1 Replies
View Related
Aug 24, 2006
Hello,could you please advice on how to measure replication performancein Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?I've got two servers with databases installed and configured,I prepared set of data using DBGEN from TPC and I already imported theminto databases.Also, I configured the replication.Now I have to do a test with a few kind of replications methodimplemented in these RDMBS, but I don't know which tool or reports or"v$iews" should I use to measure replication performance.The replication is configured only between the same RDBMS, I meanOracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.Most of applications are great for checking performance of local DB,not for replicated/distributed.I've found description of CA Unicenter Database Performance Managementfor distributed RDMBS, and I think it could be the right one, but Ican't find any demo or trial version :(Could you please advice any place to download it, or other application,script, description, just whatever.Perhaps just any other idea how to check the replication mechanismefficiency ?Regards,Mark
View 5 Replies
View Related
