Calculating Admits Per 1000 From Two Data Sets

Aug 27, 2013

I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.

Below are my queries from the two datasets.

MemberMonths dataset:
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership

[Code] ....

Admits dataset:

SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions

[Code] ...

View 6 Replies

Calculate Number Of Admits In Past 12 Months

Aug 5, 2013

I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .

View 6 Replies View Related

Transact SQL :: Data Type Auto Identity Is (1,1) But Its Increasing 1000 Each Time

Apr 21, 2015

In my database table has auto Identity file which is (1,1) But Its Increasing 1000 Some time 100 I don't Understand why It is happening in my every table.

View 4 Replies View Related

Power Pivot :: External Access To Data Sets In The Data Catalog?

Apr 23, 2015

I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.

Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?

View 3 Replies View Related

How To Split The Data Into Training And Validation Sets When Doing Data Mining?

Jun 15, 2007

Could I ask how to spit the data into training and validation sets when doing data mining?

Thanks

View 1 Replies View Related

Join Three Data Sets From Different Data Flows Into One Txt File

Mar 9, 2008

Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:

I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.

Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.

What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?

Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!

View 7 Replies View Related

Large Data Sets

Mar 20, 2008

Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma

View 2 Replies View Related

Query With 2 Sets Of Data

May 7, 2008

I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME

View 13 Replies View Related

Matching Two Sets Of Data

Mar 12, 2008

Hi All,

I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.

Does anyone have any suggestions how i would go about doing this?

Thanks in advance
Humate

quote:Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.

View 2 Replies View Related

Comparing Two Sets Of Data

Jul 23, 2005

I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.

View 12 Replies View Related

Two Data Sets In A Matrix

Mar 13, 2007

Hi

I have a matrix whos colunm group is filed by Dataset1,
now i want to add naother colunm group,but using the Dataset2

can I use two different dataset for a matrix,
for differnt colunm group

please help me in this regards

thanks

View 2 Replies View Related

T-SQL (SS2K8) :: Matching Two Data Sets

Apr 28, 2015

I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.

How can i write a query to do this? Examples are as below.

Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000

Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900

create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)

[Code] ....

View 5 Replies View Related

Train And Test Data Sets

Feb 8, 2007

I've seen that sometimes is better to split the table into a test dataset and a training dataset, and I'll appreciate if anyone can explain why is this...

thanks

Santiago Aceñolaza
Argentina

View 4 Replies View Related

Multiple Data Sets In One List

Mar 24, 2008

Is there a way to put more then one data set in a list.
I have a report that has three data sets with three tables. Now i want to show each report by Region, per page. So you can view the same stuff for each region seperately, instead of all together. Is there a way to do this. Where i dont have to go back in my code, and find a way to link everything together, so its in one data set .

View 3 Replies View Related

Dynamic Data Sets In Reports

Sep 3, 2007

Hi,

I'm using a matrix report where in i want to use two datasets in the same report. How can i make the dataset dynamic for a single report.

Regards

View 1 Replies View Related

Calculated Field Using Two Data Sets

May 12, 2008

Hi,

I'm trying to created a report.
Final report looks like this.

Total Loans/Lines (#)
13,283

Total Commitments ($ MM)
$1,703

Total Outstandings ($ MM)
$1,175

A
B
C
D
F

Bankruptcy
0
$0
$0
0.00%
0.00%

Charge Off
0
$0
$0
0.00%
0.00%

Source table looks like this;

Bankruptcy

0

Charge Off

0

CLTV
131

DSR
102

Exc Total
265

FICO
7

Foreclosure/Repossession

Grand Total
13283

Loan Amount
32

Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)

But it does not let me to use report expression as its not in the same scope.

Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.

Thanks

View 15 Replies View Related

Are Data Sets Memory Efficient?

Feb 8, 2008

Hi,

I have designed a contact manager with Data Grid Control bound to a Data Set.

When the application closes, data from Data Set is written to XML file and when application opens, data from XML file is loded into Data Set and is show in Data Grid control.

Contacts in my application can exceed over 1,000
So, Is Data Set capable of handling lot of data very efficiently in memory?

Please advise

View 3 Replies View Related

Problems With Data Sets In SQL Server 2005

May 25, 2006

Hello,
I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005...
The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error:
"..The data types ntext and nvarchar are incompatible in the equal to operator..."
in this line:
count = adapter.Update(dataset);
Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...

View 11 Replies View Related

Analysis :: Partitioning Sets Of Data Dynamically

Jul 17, 2015

I have a situation where i have a transactional fact table which consists of date, row type, order number and value. Simple example below

Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90

What I need to be able to do is somehow select based on a day, the total value of open orders. I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.

View 2 Replies View Related

SQL Tools :: System Data Collection Sets

Jun 19, 2015

I created Data Collection in wrong DB, how can I change the DB or return to default(as it came with clean version of SS) ?

View 3 Replies View Related

Making A Chart Run With Multiple Data Sets.

Jul 26, 2007

Hello and thanks in advance.

I was wondering if anyone has ever written a chart with multiple datasets.

I need to be able to show sales dollars inflow by order date on one line and on the other needs to be sales dollars delivered by delivery date. So the all sections Values, Category groups, and Series Groups in the chart will be from 2 different datasets.

I have tried but it will not allow aggreates in the series groups.

Any Ideas would be greatly appreciated.

Thanks, Leo

View 1 Replies View Related

Copying Large Sets Of Data In Same Database

Mar 9, 2007

I need to copy data from TableA to TableB (>5 millions rows). The two are in the same database.

What is the best way of doing this?

I was thinking about using a simple INSERT INTO ... SELECT statement. Is there a faster way to do it with SSIS?

Thanks

View 13 Replies View Related

Adding A Calculated Field By Using Two Fields From Different Data Sets

Oct 1, 2007

Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)

I tried to do that. But I got a error message.

"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."

Please can some one help me out?

View 5 Replies View Related

Data Binding To A Stored Procedure That Returns Two Result Sets

Mar 6, 2007

Hi there everyone. I have a stored procedure called “PagingTable�? that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:

strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"

The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.

'Open the recordset
rsItems.Open strSQL, conn, 0, 1

'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value

'Advance to the next recordset
Set rsItems = rsItems.NextRecordset

I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:

A) Bind the repeater control to the second resultset
B) Build a “pager�? of some sort using the values from the first resultset

View 3 Replies View Related

SQL Server 2012 :: DB2 Store Procedure Returning Two Data Sets

Oct 13, 2014

A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?

View 1 Replies View Related

SSAS Crashes - Mining Predictions For Large Data Sets

Sep 7, 2006

Hi all,

I am using SSAS 2005. The mining model works fine. But it crashes when I run the 'Mining Model Predictions' against large data sets.

I ran it against 5,000,000 records and it went fine.

But exactly same model failed for 5,100,000 records and beyound.

The message is 'Query Execution Failed' and then Visual Studio crashes.

Pl. let me know if anybody has the same experience or knows the solution.

Thanks,

Vikas

View 3 Replies View Related

Storing Vs Calculating Data

Dec 19, 2007

Hi, I'm in the process of creating a database table and was wondering if it's better to store calculated values or recalculate them each time. So for example, I am creating a table that stores articles and then a table to store the pages to the article. If a new page is added should I update the pages field in the articles table or should I calculate the number of pages for an article when it's queried? Thanks,John

View 3 Replies View Related

SQL Problem Calculating Data

Jun 14, 2006

Hi,
I'm in desperate need for some info on how to achieve the following.I have an SQL DB with the following tables, Clients and Actions.The Clients table stores client info and, the amount of "units" (15 minutes time on-site per unit) per month they have for support (varchar).The Actions table has info about an on-site call, and in that are also stored the amount of units spent on the call and the time/date of the call (datetime).Now i want to be able to see per month (select * from actions where date like etc.?) which clients did not have all the time allocated to them spent. So it's basically an add-on of all the time units in the actions table of a specific month compared to the amount of time statically set in the clients table, per client.
I don't know where to start. Should i create a view for this, do i need some sort of temp table to store info in for the query.
I'd really like some info/example to get me started here.
Thanks,Dennes

View 1 Replies View Related

Calculating Data From Multiple Tables

Aug 29, 2014

I have a table which contains readingtime (records every 4 seconds during the day) and an energy reading. I wanted to know how much energy was used in a day. This is the query that i used

SELECT date(readingtime), max(energy)-min(energy)as total FROM lights_1 WHERE readingtime between 20140407000000 and 20140409235959 Group by date(readingtime) LIMIT 0,30;

This gave me the desired results for 1 table. I have 4 tables of these lights_2, Lights_3, lights_4 and i would like to display them together and hence match up the reading time so each day has the energy requirements for all for tables with the total. When i try and join tables i get a lot of errors!

View 1 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)

And i have some data:

INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

1000 To 1,000

Aug 25, 2004

Does anyone know how to convert the number 1000 to appear as 1,000 in a SQL Statement?

View 4 Replies View Related

Converting 1000 Into 10.00

Oct 17, 2005

I have been given a Product table whoes all column types are varchar(8000)
One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed.
So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'Should I use SQL statements to convert 1000 into 10.00 or should I use some sort of programming logic to convert 1000 into 10.00.kind regards