T-SQL Query Help

Transact-SQL
1

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)
,Lname VARCHAR(50)
,Amount Money
,Visit INT
)

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

2

There's an ambiguity in your problem. you said, "create a duplicate row." but for James you created two rows. I assume that your sample output is incorrect and that you really wanted one new row, not two.

This query does that:

select new.ID, new.Fname, new.Lname, new.Amount, new.Visit
into InsertedMore
from InsertMore
outer apply
(
    select ID, Fname, Lname, Amount, Visit
    union
    select ID, Fname, Lname, null as Amount, Visit
    where Visit > 1
) new

select * from InsertedMore
3

Gbritton, Thank You for your help.

The result should depend on Visit. It could possible VISIT could be 5,7,8, not a fixed number. If Customer Visit 5 times. It should create 5 rows.

Could you Please help me to generate a dynamic query?

Thank you for your help.

4

Try this variation:

DECLARE @maxvisit INT;

SET @maxvisit = (
          SELECT MAX(visit)
          FROM InsertMore
          )

SELECT new.ID, new.Fname, new.Lname, CASE N
          WHEN 1
               THEN new.Amount
          END AS Amount, new.Visit
INTO InsertedMore
FROM (
     SELECT TOP (@maxvisit) ROW_NUMBER() OVER (
               ORDER BY (
                         SELECT 1
                         )
               )
     FROM sys.objects a, sys.objects b
     ) N(n)
INNER JOIN InsertMore new ON n <= Visit
1 Like
5

Awesome.... Thank You So much...

This question is answered by gbritton.

6

First, thank you VERY much for posting readily consumable test data the way you did. It makes it a whole lot easier to help someone when they do such a thing.

My very strong recommendation would be to NEVER use an rCTE that "counts". Please see the following article as to why.
Hidden RBAR: Counting with Recursive CTE's

Shifting back to the problem, if you have the "Swiss Army Knife" of T-SQL in place, the problem becomes as simple as the following.

 SELECT  ID
        ,Fname
        ,Lname
        ,Amount = CASE WHEN N = 1 THEN Amount ELSE NULL END
        ,Visit
   FROM dbo.InsertMore
  CROSS APPLY dbo.fnTally(1,Visit)
  ORDER BY ID,N
;

Here's the dbo.fnTally function. I call it "fn" because I also keep a regular dbo.Tally table active and because it's not an HR violation to tell someone, "WELL! If you used the fnTally function I told you about, you wouldn't have this performance problem!"

As normal, documentation and usage examples are included in the code.

     CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.

 As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

 Usage:
--===== Syntax example (Returns BIGINT)
 SELECT t.N
   FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;

 Notes:
 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URLs for how it works and introduction for how it replaces certain loops. 
    http://www.sqlservercentral.com/articles/T-SQL/62867/
    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
    will cause the sequence to start at 1.
 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
    number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
    that many values, you should consider using a different tool. ;-)
 7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending 
    sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
    very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally(1,@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 22 Apr 2015 - Jeff Moden
        - Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1)                                  --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)      --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c)            --10E12 or 1 Trillion rows                 
            SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
             UNION ALL 
            SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
1 Like
7

First, I'd like to recommend that you NEVER use an rCTE that counts. Please see the following article for the reasons why.
Hidden RBAR: Counting with Recursive CTE's

Getting back to the original problem, if you have the "Swiss Army Knife of T-SQL" handy, the solution to this problem becomes as simple as the following

 SELECT  ID
        ,Fname
        ,Lname
        ,Amount = CASE WHEN N = 1 THEN Amount ELSE NULL END
        ,Visit
   FROM dbo.InsertMore
  CROSS APPLY dbo.fnTally(1,Visit)
  ORDER BY ID,N
;

Here's the code to make dbo.fnTally including complete documentation and usage examples.

 CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
 Purpose:
 Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Billion.

 As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

 Usage:
--===== Syntax example (Returns BIGINT)
 SELECT t.N
   FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;

 Notes:
 1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
    Refer to the following URLs for how it works and introduction for how it replaces certain loops. 
    http://www.sqlservercentral.com/articles/T-SQL/62867/
    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
 2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
    will cause the sequence to start at 1.
 3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
 5. If @MaxN is negative or NULL, a "TOP" error will be returned.
 6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
    number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
    that many values, you should consider using a different tool. ;-)
 7. There will be a substantial reduction in performance if "N" is sorted in descending order.  If a descending 
    sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
    very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT; 
     SELECT @MaxN = 1000;
     SELECT DescendingN = @MaxN-N+1 
       FROM dbo.fnTally(1,@MaxN);

 8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

 Revision History:
 Rev 00 - Unknown     - Jeff Moden 
        - Initial creation with error handling for @MaxN.
 Rev 01 - 09 Feb 2013 - Jeff Moden 
        - Modified to start at 0 or 1.
 Rev 02 - 16 May 2013 - Jeff Moden 
        - Removed error handling for @MaxN because of exceptional cases.
 Rev 03 - 22 Apr 2015 - Jeff Moden
        - Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1)                                  --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)      --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c)            --10E12 or 1 Trillion rows                 
            SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
             UNION ALL 
            SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;

Last but certainly not least, thank you VERY much for posting readily consumable test data the way you did. It makes it SO easy to help someone when they take that bit of time to post like that. Great job!

1 Like
8

That's absolutely brilliant! Erecting a gallows in the office is becoming rather unpopular!

9

Apologies for the apparent double post. I thought I was losing my mind (well, that's happening anyway) when the first one didn't show up. Bill send me an email saying that the SPAM filter picked up on it (must have been the magic two letters) and blocked it. He released it and it's a near dupe of what I posted after that.

1 Like