How do deduplication on Fact Table

Hi, everyone,

l've a fact table DEVICE with following structure,

DEVICE_NAME VARCHAR(50)
DEVICE_DATE DATETIME
DEVICE_NUMBER INT
Where DEVICE_NAME and DEVICE_DATE form a PRIMARY KEY

So l would like to import a text file with same information into this table.

My problem is, text file contains records which will violate my primary key constraint. In that case, l would only insert the record with DEVICE_NUMER not equal to ZERO and discard and log the others.

In case of the records violtae primary key constraints have DEVICE_NUMBER not equal to ZERO, discard both and log it.

So anyone has good suggestion on this?

In the Data Flow you can check for all of these situations. Start with a text file source.

To prevent insertion of record with zero device_number, use a Conditional Split transform. For anew output, write an expression to test for DEVICE_NUMBER == 0. Any rows that match will follow that output. Connect the following component to the default output, thus discarding those rows.

To prevent insertion of rows that already exist, use a Lookup. Set the Error Configuration, to Redirect errors. This means that when the lookup does not find a match, those "new" rows will flow to the error output, and "matched" rows will flow doen the default output. Connect the following component to the error output, again discarding the matched rows.

If you suspect you have new rows, but they can be duplicates within the file itself, use an Aggregate transform, which has the option to provide unique rows only.

How could I use row as columns?

create table t2
(
umc varchar(20),
outdate datetime,
outnumber int
)
insert t2 values (1,'2005-2-5',1)
insert t2 values (2,'2005-2-5',1)
insert t2 values (2,'2005-2-6',1)
insert t2 values (3,'2005-2-5',2)
insert t2 values (3,'2005-2-6',1)
insert t2 values (4,'2005-2-7',1)
I hope the result to be
(2005-2-5,2005-2-6,2005-2-7 is column name now)
2005-2-5 2005-2-6 2005-2-7
1 1 0 0
2 1 1 0
3 2 1 0
4 0 0 1
Can I just compose it with SELECT statement?First you have to select distinct dates into a cursor,
than select from the table left outer join each date where date from the
table = date of the column.
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||http://aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||SELECT umc,
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
FROM T2
WHERE outdate >= @.dt
AND outdate < DATEADD(DAY,3,@.dt)
GROUP BY umc
Column names in a query are fixed so dynamic SQL would be required to
change the names based on the data. That shouldn't really be a problem
though. It should be easy enough to display different column names in
your client application.
David Portas
SQL Server MVP
--|||Nadim,
Thanks so much and that's what I want, however, is it possible for you to
show me some sample codes based on my DDL?
yours, XXY
"Nadim Wakim" <nadimlb@.cyberia.net.lb>
:uOjnD2NEFHA.1392@.tk2msftngp13.phx.gbl...
> First you have to select distinct dates into a cursor,
> than select from the table left outer join each date where date from the
> table = date of the column.
>
> "XXY" <xxy02021@.NOSPAM.163.com> wrote in message
> news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||Hi David and Roji,
I do appreciated your articles and sample codes, however when the outdate
ranges much(it might be any day in a year in my table), I am afraid it is
not a good idea using datediff. Don't you think so ?
yours, XXY
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
:1108198808.232843.84220@.g14g2000cwa.googlegroups.com...
> SELECT umc,
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
> FROM T2
> WHERE outdate >= @.dt
> AND outdate < DATEADD(DAY,3,@.dt)
> GROUP BY umc
> Column names in a query are fixed so dynamic SQL would be required to
> change the names based on the data. That shouldn't really be a problem
> though. It should be easy enough to display different column names in
> your client application.
> --
> David Portas
> SQL Server MVP
> --
>|||Roji, Thanks so much!!!
I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right answer
from
exec crosstab 'select umc from t2 group by
umc','sum(outnumber)','outdate','t2'
You are the MAN!!
"XXY" <xxy02021@.NOSPAM.163.com> д?
:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||I'm reminded of the Di-Tech commercials:( :)
www.rac4sql.net
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:%23kdz4APEFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Roji, Thanks so much!!!
> I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right
> answer
> from
> exec crosstab 'select umc from t2 group by
> umc','sum(outnumber)','outdate','t2'
> You are the MAN!!
>
> "XXY" <xxy02021@.NOSPAM.163.com> д?
> :eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||I don't see a problem. The date range selection is in the WHERE clause
and is sargable. The cost of DATEDIFF should be relatively light but if
performance is a concern then you should test it out with your typical
data-set.
David Portas
SQL Server MVP
--sql

How could I get the records recusrively (nested)?

How could I get the records recusrively (nested)?
I've a table ACCT with columns as follows.
ACCT_CD varchar(20)
TYPE_CD varchar(1)
It has following data.
ACCT1 F
ACCT10 F
ACCT2 F
ACCT3 F
ACCT4 F
ACCT5 F
ACCT6 F
ACCT7 F
ACCT8 F
ACCT9 F
GRP_1 G
GRP_2 G
SGRP_1 C
SGROUP C
I've another table ACCT_REL with columns as follows.
PARENT_ACCT varchar(20)
CHILD_ACCT varchar(20)
REL_TYP varchar(1)
It has following data.
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGROUP'
SGROUP ACCT1 C
SGROUP ACCT2 C
SGROUP GRP_2 C
SGROUP SGRP_1 C
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_1'
GRP_1 ACCT3 G
GRP_1 ACCT4 G
GRP_1 ACCT5 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'GRP_2'
GRP_2 ACCT6 G
GRP_2 ACCT7 G
GRP_2 ACCT8 G
SELECT * FROM ACCT_REL WHERE PARENT_ACCT = 'SGRP_1'
SGRP_1 GRP_1 C
SGRP_1 ACCT9 C
SGRP_1 ACCT10 C
I want retrive all the child_acct for PARENT_ACCT = 'SGROUP'. If the
CHILD_ACCT has some records in the ACCT_REL table, then I would like to
get them also. It could have many levels of nesting. How could I get the
records recusrively?
E.g. In the above example, the expected result could be:
ACCT1
ACCT10
ACCT2
ACCT3
ACCT4
ACCT5
ACCT6
ACCT7
ACCT8
ACCT9
GRP_1
GRP_2
SGRP_1
Thanks,
DJ
*** Sent via Developersdex http://www.examnotes.net ***check this out... (on behalf of ML :)
http://milambda.blogspot.com/2005/0...or-monkeys.html|||Also check out CTEs (Common Table Expressions) if you are using 2005.
There is a good example posted in Omni's blog here:
http://omnibuzz-sql.blogspot.com/20...vs.ht
ml
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9CB52622-90FB-4E32-9F92-CC3456633DDD@.microsoft.com...
> check this out... (on behalf of ML :)
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>|||Get a copy of TREES & HIERARCHJIES IN SQL for several methods of
modeling this kidn of data. You do not need recursive procedural code
in the Nesteed sets model and do this in one simple query.|||
Can you please tell me how to do this?
An example would be helpful.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***|||I beleive --CELKO-- is suggesting that you change your data model to store
the data in a tree format.
Omni's post contains one method for doing this.
CTEs on SQL Server 2005 allow you to do it with a single sql statement using
your current data model, but performance may not be as good.
If you do a search on "TREES & HIERARCHIES IN SQL", or just the phrases SQL
TREES HIERARCHIES, you will find several examples, including some articles
by Joe (--CELKO--).
"DJ" <dominic_koyappillil@.yahoo.com> wrote in message
news:uQqaGahiGHA.3572@.TK2MSFTNGP04.phx.gbl...
>
> Can you please tell me how to do this?
> An example would be helpful.
> Thanks.
> *** Sent via Developersdex http://www.examnotes.net ***

How can User add order from only one company at a time

tblUser
--
UserID uniqueidentifier PK (newid)
UserName varchar(MAX)

tblCompany
-
CompanyID uniqueidentifier PK newid()
CompanyName varchar(MAX)

tblUserCart
--
CompanyID uniqueidentifier FK newid()
UserID uniqueidentifier PK
Product varchar(MAX)

Asume we got 3 tables like above. Relation ship is clear: tbluser ->tblUserCart--<tblCompany

What i want is , if user gives an order from a company , sql 2005 will decline the orders from other Companies. Naturally Orders are stored in the tblUserCart table. For example, if user Arnold gives an order of CuttingTool from Company named T3 , Arnold will not be able to give another order from other than T3. I hope im clear about situation.

Happy Coding...

Do you have an application that sits on top of the database so that the tables are populated via the application? If so, that's where I'd perform your logic, rather than trying to build some trigger-based/stored procedure solution in SQL Server.|||I agree.

I would use various SELECT functions within your application to validate weather Arnold could place another order.

So, adding an order for a particular user, query the UserCart table for that user. If an order exists for that user, then flag the app user or only show products from company T3

We could write this as a procedure, put lets put the client back into Client / Server |||So answer is , do it on application. Thanks for answers.

How Can Insert A Data In To This Table?

SALAM SIR,
CREATE TABLE Airlines_Master
( Aircode CHAR(2),
Airlines_name VARCHAR(15))
THIS IS MADE A TABLE IN SQLSERVER 2000.BUT HOW CAN INSERT DATA INTO THIS TABLE?Lookup and read the section on SELECT and INSERT statements in Books Online.|||Lookup and read the section on SELECT and INSERT statements in Books Online.
Well,Batman this post is too much.People asking questions on insert and select ....there sould be some limit..:shocked:
Joydeep|||Well,Batman this post is too much.People asking questions on insert and select ....there sould be some limit..:shocked:
JoydeepEvery time I create something "foolproof", mother nature goes out and creates a better fool. Following that same line of logic, there is no "lower limit" on how basic a question can be... Someone, somewhere, will ask anything you can imagine, and probably several things that you can't imagine too! It is just the nature of the beast, so sit back and enjoy, don't get your knickers in a twist!

-PatP

How Can I use this code for Primary Key autogeneration

Hi

DECLARE @.MyValue varchar(10)
SELECT @.MyValue = (SELECT RIGHT(YEAR(GETDATE()),1)+
REPLACE(STR(MONTH(GETDATE()),2),' ','0')+
REPLACE(STR(PlaceID,2),' ','0')+
'00001'
FROM Provinces WHERE PlaceName='Kinshasa' )
SELECT @.MyValue

I written a code like this to generate (example: 5080100001 - 5 Year, 08 Month, 01 PlaceID, remaining digit should be automatic increment for the current month) unique number for everymonth. According to my imagination it will increment automatically for each month. Now I want to assign this value to my primary key field. How it's possible in SQL Server 2000.

Thanks in advance
Jose

Here is one way.
Note: this contains undocumented/supported trick. Use at your own risk.

create table seed(i int)
insert seed values(0)
go
create proc getval
as
begin
set nocount on
declare @.i int
update seed
set @.i=i=i+1
select convert(char(4),getdate(),12)+right(1000000+@.i,6) as
end
go
create function dbo.pkgen()
returns char(10)
as
begin
return(select i from openquery(sqlserver_name,'exec tempdb..getval;commit')x)
end
go

create table t(pk char(10) primary key default dbo.pkgen(),i int)
go

insert t(i) values(10)
insert t(i) values(20)
insert t(i) values(30)
select * from t
go