none
Add Fixed records on top of new records

    Question

  • Hello - I am using SQL Server 2012 Std. Ed.

    What is the best approach/solution to tackle this? I have this query that spits out about 886 rows. In addition, I have about 5 rows that needs to be added on top of that 886 rows. The 5 rows are added every month for billing. They are almost like fixed charges. My query is below:

    SELECT
          '0' as 'TYPE'
         ,'TBCCA' as 'Acc ID'
         ,'12882' as 'BEID'
         , '' as ' # OF UNITS'
         , Circuit_Total as 'AMT'
         ,'D' as 'D or C'
         , '' as ' FDOC yyyymmdd'
         , '' as ' LDOC yyyymmdd'
         , Address as 'Brief Comment'
         , city as 'Tax City'
         , State as 'Tax State'
         , Zip_Code as 'Tax Zip Code'
         , INV_DATE as 'Invoice Date'
    FROM [dbo].[TBC_MonthlyCircuitDetail]
    WHERE INV_DATE = '2014-06-01'
    AND [Circuit_Total] >0

    How do I solve this? Thank you in advance?


    Sanjeev Jha

    Thursday, August 14, 2014 4:45 AM

Answers

  • SELECT TYPE, "Acc Id", ...
    FROM   (SELECT section = 1, <your fixed cols here>
            FROM ....
            UNION ALL
            SELECT section = 2, '0' AS "TYPE", ....
            FROM [dbo].[TBC_MonthlyCircuitDetail]
            WHERE INV_DATE = '2014-06-01'
             AND [Circuit_Total] >0) AS u
    ORDER BY section

    Beware that the data types in the two queries will need to match, or else funny things can happen.

    Maybe you should simply have two queries?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 14, 2014 7:16 AM

All replies

  • A simple example using adventureworks, I am fixing the entries of 30-07 on the top while display

    select salesorderid,orderdate from sales.SalesOrderHeader
    where OrderDate between '20050701' and '20050731'
    Order by case when orderdate='20050731' then 0 else 1 end

     

    Satheesh
    My Blog | How to ask questions in technical forum


    Thursday, August 14, 2014 4:51 AM
  • Can you try this

    INSERT TOP(N) INTO TABLE … SELECT Cols… FROM Table1

    INSERT INTO TABLE …
    SELECT TOP (N) Cols…
    FROM Table1
    Please Mark This As Answer if it helps to solve the issue

    Thursday, August 14, 2014 4:58 AM
  • Hello - I am using SQL Server 2012 Std. Ed.

    What is the best approach/solution to tackle this? I have this query that spits out about 886 rows. In addition, I have about 5 rows that needs to be added on top of that 886 rows. The 5 rows are added every month for billing. They are almost like fixed charges. My query is below:

    SELECT
          '0' as 'TYPE'
         ,'TBCCA' as 'Acc ID'
         ,'12882' as 'BEID'
         , '' as ' # OF UNITS'
         , Circuit_Total as 'AMT'
         ,'D' as 'D or C'
         , '' as ' FDOC yyyymmdd'
         , '' as ' LDOC yyyymmdd'
         , Address as 'Brief Comment'
         , city as 'Tax City'
         , State as 'Tax State'
         , Zip_Code as 'Tax Zip Code'
         , INV_DATE as 'Invoice Date'
    FROM [dbo].[TBC_MonthlyCircuitDetail]
    WHERE INV_DATE = '2014-06-01'
    AND [Circuit_Total] >0

    How do I solve this? Thank you in advance?


    Sanjeev Jha

    Where does that 5 rows come from? Is it from same tables? or are they some static data?

    If former you can tweak where conditions to include them

    if latter then merge them using a UNION ALL


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, August 14, 2014 5:46 AM
  • SELECT TYPE, "Acc Id", ...
    FROM   (SELECT section = 1, <your fixed cols here>
            FROM ....
            UNION ALL
            SELECT section = 2, '0' AS "TYPE", ....
            FROM [dbo].[TBC_MonthlyCircuitDetail]
            WHERE INV_DATE = '2014-06-01'
             AND [Circuit_Total] >0) AS u
    ORDER BY section

    Beware that the data types in the two queries will need to match, or else funny things can happen.

    Maybe you should simply have two queries?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 14, 2014 7:16 AM
  • Hi Visakh16,

    Yes, the 5 rows are static data and I have them in an excel file. I did think about doing UNION ALL but I was not sure if it is the best way to go. Thanks for the suggestion. I appreciate your time and effort.


    Sanjeev Jha

    Thursday, August 14, 2014 6:49 PM
  • Thank you everyone for your input. I appreciate it.

    Sanjeev Jha

    Thursday, August 14, 2014 6:50 PM