Post reply

Need sql help

  • December 15, 2016 at 10:55 am

    #332793

    Hi

    I have a following scenario.

    File has 4000 records.4(1,2,3,4) sets of records make one final record.I am moving whole set of 4 records if any of the (1,2,3,4)record has some data errors to new tables .e.g..Q1,Q2,Q3,Q4 and inserting good records to main staging tables .This way i am having only good data to main staging tables(T1,T2,T3,T4) and bad data to error tables.For each file load with different data sources i am using the same query.

    Now the task is i have to create a new table to store no of total records,no of bad records and percentage of error records and have to capture this information for each load.

    Note ...t1,t2,t3,t4 data will vanish after each next load as these are staging tables and being used for 20 different file loads but i have to caculate the percentage of error records in each load and store .To distinguish from earlier load i am using source file name col to hold the file name.

    Thanks.

  • December 15, 2016 at 11:02 am

    #1918254

    sarwar.ali490 (12/15/2016)

    Hi

    I have a following scenario.

    File has 4000 records.4(1,2,3,4) sets of records make one final record.I am moving whole set of 4 records if any of the (1,2,3,4)record has some data errors to new tables .e.g..Q1,Q2,Q3,Q4 and inserting good records to main staging tables .This way i am having only good data to main staging tables(T1,T2,T3,T4) and bad data to error tables.For each file load with different data sources i am using the same query.

    Now the task is i have to create a new table to store no of total records,no of bad records and percentage of error records and have to capture this information for each load.

    Note ...t1,t2,t3,t4 data will vanish after each next load as these are staging tables and being used for 20 different file loads but i have to caculate the percentage of error records in each load and store .To distinguish from earlier load i am using source file name col to hold the file name.

    Thanks.

    What is your question?

  • December 15, 2016 at 11:07 am

    #1918256

    how to insert calculated values to new table

  • December 15, 2016 at 11:31 am

    #1918262

    INSERT INTO MyNewTable(ColumnList)

    SELECT ExpressionList

    FROM OldTable

    ?

  • December 15, 2016 at 12:26 pm

    #1918269

    Thanks for the reply.

    I alreaady had this kind of insert statement but here in my case i have to enter count of records from good data table T1 and bad data table Q1 and store it into third table after every load to capture the statistics with out joining other two tables.

  • December 15, 2016 at 12:53 pm

    #1918274

    Do you mean like the following?

    INSERT YourTable

    VALUES((SELECT COUNT(*) FROM T1), (SELECT COUNT(*) FROM Q1))

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 15, 2016 at 1:06 pm

    #1918276

    exactly...and now i added new column to new table and need to add the percentage of those two counts

    (count1*1.0)/(count2)...this needs to happen for every file load i do once the data gets loaded into staging tables.

    Thanks.

  • December 15, 2016 at 1:14 pm

    #1918277

    sarwar.ali490 (12/15/2016)

    exactly...and now i added new column to new table and need to add the percentage of those two counts

    (count1*1.0)/(count2)...this needs to happen for every file load i do once the data gets loaded into staging tables.

    Thanks.

    Computed Columns

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • December 16, 2016 at 6:18 am

    #1918372

    you must add tow new column (dateload,file name for each table and use

    select dateload,filename,count(*)

    from Table

    group by dateload,filename

  • December 16, 2016 at 11:35 am

    #1918466

    Thanks all .I got it

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply