T-SQL Query Help

  • 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

  • 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

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

  • 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

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

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

  • A tally table is simply a simple table that contains a single column of indexed integers.

    Jeff Moden has a great article on the topic... The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

  • 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

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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