May 13, 2015 at 10:29 am
Here is my Sample Data (Below SQL For Create Sample Data)
ID,Fname,Lname,Amount,Visit
1,Smith,D,125,1
2,James,C,145,3
3,Norman,S,121,1
4,Sam,P,111,2
Here is End result Looks like.
ID,Fname,Lname,Amount,Visit
1,Smith,D,125,1
2,James,C,145,3
2,James,C,,3
2,James,C,,3
3,Norman,S,121,1
4,Sam,P,111,2
4,Sam,P,,2
What I want to check Visit column If Visit is more then 1 then create a duplicate row. First row with all Information i.e (4,Sam,P,111,2). However, Second row should be without Amount (4,Sam,P,,2) Because we don't want to calculate the same amount twice.
Please help how I can accomplish this one in SQL or SSIS.
Here is the SQL
Create Table InsertMore
(
ID Int
,Fname VARCHAR(50)
,LnameVARCHAR(50)
,AmountMoney
,VisitINT
)
insert into InsertMore
Select 1,'Smith','D',125,1
union
Select 2,'James','C',145,3
union
Select 3,'Norman','S',121,1
union
Select 4,'Sam','P',111,2
Select * from InsertMore
May 13, 2015 at 10:42 am
Second row should be without Amount (4,Sam,P,,2) Because we don't want to calculate the same amount twice.
am a little confused on what you intend to do with your requested result set?
care to explain?
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2015 at 10:46 am
The proposed data structure is not pleasant, from a relational standpoint. You should never have repeated occurrences of people's names, for example. And rows 3 and 4 of your desired output are exact duplicates. Why would you ever want to do this?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 13, 2015 at 10:49 am
The names should not be in that table, only the IDs.
Also, you need some type of amount code so that amounts for that visit can be distinguished from one another.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 13, 2015 at 10:52 am
Sorry for the confusion.
Let me try to explain what I am trying accomplish.
Here is my source data that I am receiving from customer every day in .txt
ID,Fname,Lname,Amount,Visit
1,Smith,D,125,1
2,James,C,145,3
3,Norman,S,121,1
4,Sam,P,111,2
In above data, it is showing how many visit customers did and pay (e.g Smith visit once and he paid $125/ James visit 3 times and he paid $145)
Now what I want or want to insert in my SQL DB
ID,Fname,Lname,Amount,Visit
1,Smith,D,125,1
2,James,C,145,3
2,James,C,,3
2,James,C,,3
3,Norman,S,121,1
4,Sam,P,111,2
4,Sam,P,,2
If want to create my source before I insert into my SQL, if there is only one visit don't do anything just create only one line. If visit more than one create a duplicate row without $ amount. e.g (James visit 3 times Create three rows in destination.However, only one time $145 amount)
Make sense?
May 13, 2015 at 10:55 am
tooba111 (5/13/2015)
Sorry for the confusion.Let me try to explain what I am trying accomplish.
Here is my source data that I am receiving from customer every day in .txt
ID,Fname,Lname,Amount,Visit
1,Smith,D,125,1
2,James,C,145,3
3,Norman,S,121,1
4,Sam,P,111,2
In above data, it is showing how many visit customers did and pay (e.g Smith visit once and he paid $125/ James visit 3 times and he paid $145)
Now what I want or want to insert in my SQL DB
ID,Fname,Lname,Amount,Visit
1,Smith,D,125,1
2,James,C,145,3
2,James,C,,3
2,James,C,,3
3,Norman,S,121,1
4,Sam,P,111,2
4,Sam,P,,2
If want to create my source before I insert into my SQL, if there is only one visit don't do anything just create only one line. If visit more than one create a duplicate row without $ amount. e.g (James visit 3 times Create three rows in destination.However, only one time $145 amount)
Make sense?
I understand what you are trying to do....but why...doesnt make any sense when you already have summarised data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2015 at 10:59 am
It's a good question why. Couple of things
1) This is Ad hoc request
2) The system we are using is calculating per visit and a lot of other logics.
I agree most of you guys data doesn't make sense or RDB shouldn't like this. However, sometimes we have to deal whatever we have.
Any help to build SQL Query would be a big help.
Thank You.
May 13, 2015 at 11:01 am
As long as this is only for reporting purposes, because as previously stated, it makes no sense to store so many repeated values.
You just need to query your table joining it to a tally or numbers table using a non-equi join (or triangular join). Here's an example.
SELECT ID,
Fname,
Lname,
CASE WHEN tally = 1 THEN Amount END AS Amount,
Visit
FROM InsertMore im
JOIN Tally t ON im.Visit >= t.tally
You can read about Tally tables in here: http://www.sqlservercentral.com/articles/T-SQL/62867/
May 13, 2015 at 11:13 am
Luis I really apreciate your help. I am sure I am missing something. After I running Sample Query I am getting Error "Invalid Object Name dbo.Tally"
Can You Please guide me what I am doing wrong?
Thank You.
May 13, 2015 at 11:36 am
May 13, 2015 at 11:36 am
tooba111 (5/13/2015)
Luis I really apreciate your help. I am sure I am missing something. After I running Sample Query I am getting Error "Invalid Object Name dbo.Tally"Can You Please guide me what I am doing wrong?
Thank You.
as Luis pointed to in his reply.....read the following ...there are many ways of creating a tally/numbers table...but this article will give you a good understanding of how they work
http://www.sqlservercentral.com/articles/T-SQL/62867/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2015 at 11:43 am
tooba111 (5/13/2015)
Luis I really apreciate your help. I am sure I am missing something. After I running Sample Query I am getting Error "Invalid Object Name dbo.Tally"Can You Please guide me what I am doing wrong?
Thank You.
The problem was that you copied the code without analysing it. This time, this was an error, but be careful when doing that because you could really get nasty code over the internet.
You need to create a Tally table or something that works as one (view or function). You might need to adapt the code to comply to your own personal tally table.
May 14, 2015 at 7:59 am
Thanks ALL for the input and help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply