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 as
well as values for both of them were extracts from the same parent
table. Hope the info would substitute DDL. I need to find the "gap"
rows between these two sets.
Attempted to run a query like
select count(*)
from set2
where not exists
(select *
from set1)
did not yield what I desired. What else to try?

TIA.

View 12 Replies


ADVERTISEMENT

Need SQL For Finding Gaps Comparing Two Sets Of Pe

Feb 27, 2008

Hi all,

I have two tables - Planning and Appointments:

Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.

Id,
TypeId - the type of the planned items
BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item

In the Planning table we can have as many records per day as we need:

1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00

Appointments - contanis a list with appointments

Id,
BeginTime DateTime
EndTime DateTime

1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30

What is needed?

What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:

Free planned time:

1, 1 Jan 2008 09:30, 1 Jan 2008 10:00
2, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00

So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.

I've tried several aproaches, but I always faced performance problems.

Thanks in advance.

View 1 Replies View Related

Need SQL For Finding Gaps Comparing Two Sets Of Periods

Feb 22, 2008

Hi all,
I have two tables - Planning and Appointments:

Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.
Id,
TypeId - the type of the planned items

BeginTime DateTime - begin date and time of the planned item
EndTime DateTime - end date and time for the planned item

In the Planning table we can have as many records per day as we need:

1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00
2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00
3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00
4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00

Appointments - contanis a list with appointments
Id,

BeginTime DateTime
EndTime DateTime


1, 1 Jan 2008 09:00, 1 Jan 2008 09:30
2, 1 Jan 2008 10:00, 1 Jan 2008 11:00
3, 1 Jan 2008 11:00, 1 Jan 2008 11:30
4, 1 Jan 2008 14:00, 1 Jan 2008 15:30

What is needed?
What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:

Free planned time:
1, 1 Jan 2008 09:30, 1 Jan 2008 10:002, 1 Jan 2008 11:30, 1 Jan 2008 12:00
3, 1 Jan 2008 13:00, 1 Jan 2008 14:00
4, 1 Jan 2008 15:30, 1 Jan 2008 18:00

So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.
I've tried several aproaches, but I always faced performance problems.

Thanks in advance.

View 1 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

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

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 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

Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months

Jun 9, 2015

I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYDifference:=[YTDProjects]-[PYProjects]
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.

I tried the the DATEADD function instead but got no luck and data came out wrong!

PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))

For some reason, this also returns blank in my model:

QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])

View 2 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

Monitoring Inserted Data And Comparing Against Selected Data

Oct 22, 2006

I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....

View 2 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

Comparing SQL Data Against XLS

Apr 10, 2007

Hello,

I am looking for some "best practice" information on how to properly setup a comparison of SQL data -vs- an Excel spreadsheet. I've never performed this task before, and will likely have to do more in the future. How can I define the range of cells to be used from the xls? There are over 1000 rows in the xls, so a Select * from dbo.table where dbo.column = 'x' just isn't practical.

Any information/direction that's out there is most appreciated!

Many thanks

View 1 Replies View Related

Comparing Data - Urgent

Oct 17, 2001

Hello,


I need to do a query to compare the same field in two different databases. How can I go about doing that? Can you use an if not exists and how does that work?

I also need to compare two numbers in two different databases and then convert that number to a decimal. For example I need to compare 22940 in one column and 22942 in a different column and insert the value of 2.1 in a third column. How can that be done?

Thanks in advance,
Anita

View 2 Replies View Related

Comparing Data In Two Different Databases

Dec 20, 1999

Hi, I would like to know if it is possible to compare data in two different databases (both SQL7.0),
with same exact schema. If possible, how could we do it?

Thanks

- Sharma

View 1 Replies View Related

Comparing Data In Two Different Databases

Apr 2, 2003

Hi
Is there a way of comparing the data contained in two separate databases (on the same server)?

I have two databases: DevDB and DB and all I want to verify is that the data in both are *exactly* the same.

Is there a nifty function to do this in SQL Server?

Thanks
Sara

View 8 Replies View Related

Comparing Data - SQL Issues

May 6, 2008

Hi

I am relatively new to SQL. Basically i am trying to compare data that doesn't match properly. Firstly one set of data has spaces, secondly the first part of the data is missing.

After researching I am going to remove the spaces with the replace and only query the final 8 number - this would dow hat I want but I cannot get it to work.

Here is my code.


Code:



update aleads as a, leads as l
set a.lid = l.lid
where replace(right(a.htel,8), ' ', '') = replace(right(l.htel,8), ' ', '');




Of course this didn't work but I do not know why - or how to actually achieve this.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved