Help needed for Select Query

  • TableName:Customer

    ---------------------------------------------------------------------------------

    CustomerIdCustomerNameLocationCityDistrictPinCode Status

    ---------------------------------------------------------------------------------

    4PavanHitechCityHydWG76871

    5KalyanCinemaStreetTuniEG12341

    7MVRaoLBNagarKkdGG1230

    8MMRaoPathapetaNzdKrishna3420

    ---------------------------------------------------------------------------------

    TableName:CustomerDetails

    ---------------------------------------------------------------------------------

    CustomerIdCustomerNameLocationCityDistrictPinCode Status

    ---------------------------------------------------------------------------------

    4Pavan1HitechCityHydWG76871

    5Kalyan1CinemaStreetTuniEG12341

    7MVRao1LBNagarKkdEG1230

    8MMRao1PathapetaNzdKrishna3420

    ---------------------------------------------------------------------------------

    TableName:TargetActivity

    ------------------------------------

    ActivityTableIdActivityTable

    ------------------------------------

    1Customer

    2CustomerDetails

    ------------------------------------

    Based on the above tables, i need to build a query to get the below output format.

    Required Output

    -----------------------------------------------------------------------------------------

    ActivityTableIdActivityTableActivityTableCountActivityTableFilterCount

    -----------------------------------------------------------------------------------------

    1Customer 42

    2CustomerDetails42

    -----------------------------------------------------------------------------------------

    Note:

    1. ActivityTableCount is the TOTAL COUNT OF THE CORRESPONDING ACTIVITY TABLE MENTIONED IN THE COLUMN.

    2. ActivityTableFilterCount is the COUNT OF RECORDS FROM THE ACTIVITY TABLE WHERE THE STATUS IS 1 (Status = 1).

    3. There are n number of tables / records in TargetActivity table. For example, 2 tables are given.

    4. I need a SELECT query only to register in our tool. Stored Procedures are not allowed.

    Please help in writing query for the required output.

    Thanks

  • This looks a lot like homework. What have you tried so far? If you really need some help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Notice any problem between the Customer table & the Customer Details table?

    As was stated earlier, what you have tried, correctly formatted DLL & insert statements are necessary to even begin.


    TableName:Customer

    ---------------------------------------------------------------------------------

    CustomerIdCustomerNameLocationCityDistrictPinCode Status

    ---------------------------------------------------------------------------------

    4PavanHitechCityHydWG76871

    5KalyanCinemaStreetTuniEG12341

    7MVRaoLBNagarKkdGG1230

    8MMRaoPathapetaNzdKrishna3420

    ---------------------------------------------------------------------------------

    TableName:CustomerDetails

    ---------------------------------------------------------------------------------

    CustomerIdCustomerNameLocationCityDistrictPinCode Status

    ---------------------------------------------------------------------------------

    4Pavan1HitechCityHydWG76871

    5Kalyan1CinemaStreetTuniEG12341

    7MVRao1LBNagarKkdEG1230

    8MMRao1PathapetaNzdKrishna3420

    ---------------------------------------------------------------------------------

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Based on the information you provided this may be what you are looking for:

    USE tempdb;

    IF OBJECT_ID('tempdb..Customer') IS NOT NULL

    DROP TABLE Customer;

    IF OBJECT_ID('tempdb..CustomerDetails') IS NOT NULL

    DROP TABLE CustomerDetails;

    IF OBJECT_ID('tempdb..TargetActivity') IS NOT NULL

    DROP TABLE TargetActivity;

    CREATE TABLE Customer

    (CustomerIdint primary key,

    CustomerNamevarchar(20) not null,

    Locationvarchar(20) not null,

    Cityvarchar(20) not null,

    Districtvarchar(10) not null,

    PinCodeint not null,

    [Status]bit not null);

    CREATE TABLE CustomerDetails

    (CustomerIdint primary key,

    CustomerNamevarchar(20) not null,

    Locationvarchar(20) not null,

    Cityvarchar(20) not null,

    Districtvarchar(10) not null,

    PinCodeint not null,

    [Status]bit not null);

    CREATE TABLE TargetActivity

    (ActivityTableId int not null,

    ActivityTable varchar(100) not null)

    INSERT INTO Customer

    SELECT 4, 'Pavan','HitechCity', 'Hyd', 'WG', 7687, 1 UNION ALL

    SELECT 5,'Kalyan','CinemaStreet','Tuni','EG', 1234, 1 UNION ALL

    SELECT 7,'MVRao','LBNagar','Kkd','GG', 123, 0 UNION ALL

    SELECT 8,'MMRao','Pathapeta','Nzd','Krishna', 342, 0;

    INSERT INTO CustomerDetails

    SELECT 4,'Pavan1', 'HitechCity','Hyd','WG',7687,1 UNION ALL

    SELECT 5,'Kalyan1', 'CinemaStreet','Tuni','EG',1234,1 UNION ALL

    SELECT 7,'MVRao1', 'LBNagar','Kkd','EG',123,0 UNION ALL

    SELECT 8,'MMRao1', 'Pathapeta','Nzd', 'Krishna',342,0;

    INSERT INTO TargetActivity

    SELECT 1, 'Customer' UNION ALL

    SELECT 2, 'CustomerDetails'

    -- Output

    SELECTc1.id AS ActivityTableId,

    c1.at AS ActivityTable,

    c1.x AS ActivityTableCount,

    c2.x AS ActivityTableFilterCount

    FROM (SELECT 1 AS id, 'Customer' AS at, COUNT(*) AS x FROM Customer) AS c1

    CROSS JOIN (SELECT COUNT(*) AS x FROM Customer WHERE [Status]=1) AS c2

    UNION ALL

    SELECT c1.id, c1.at, c1.x, c2.x

    FROM (SELECT 2 AS id, 'CustomerDetails' AS at, COUNT(*) AS x FROM CustomerDetails) AS c1

    CROSS JOIN (SELECT COUNT(*) AS x FROM CustomerDetails WHERE [Status]=1) AS c2

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Dear Alan.B,

    Wonderful. This is what i am exactly looking for.

    Thanks a lot. 🙂

    Regards,

    Pavan

  • No problem.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Very nice work Alan.

    I converted Alan's code to work Dynamically(Just in case you have more than 2 tables) as follows:

    IF OBJECT_ID('tempdb..TargetActivity') IS NOT NULL

    DROP TABLE TargetActivity;

    CREATE TABLE Customer

    (CustomerIdint primary key,

    CustomerNamevarchar(20) not null,

    Locationvarchar(20) not null,

    Cityvarchar(20) not null,

    Districtvarchar(10) not null,

    PinCodeint not null,

    [Status]bit not null);

    CREATE TABLE CustomerDetails

    (CustomerIdint primary key,

    CustomerNamevarchar(20) not null,

    Locationvarchar(20) not null,

    Cityvarchar(20) not null,

    Districtvarchar(10) not null,

    PinCodeint not null,

    [Status]bit not null);

    CREATE TABLE CustomerDetails_Current

    (CustomerIdint primary key,

    CustomerNamevarchar(20) not null,

    Locationvarchar(20) not null,

    Cityvarchar(20) not null,

    Districtvarchar(10) not null,

    PinCodeint not null,

    [Status]bit not null);

    CREATE TABLE TargetActivity

    (ActivityTableId int not null,

    ActivityTable varchar(100) not null)

    INSERT INTO Customer

    SELECT 4, 'Pavan','HitechCity', 'Hyd', 'WG', 7687, 1 UNION ALL

    SELECT 5,'Kalyan','CinemaStreet','Tuni','EG', 1234, 1 UNION ALL

    SELECT 7,'MVRao','LBNagar','Kkd','GG', 123, 0 UNION ALL

    SELECT 8,'MMRao','Pathapeta','Nzd','Krishna', 342, 0;

    INSERT INTO CustomerDetails

    SELECT 4,'Pavan1', 'HitechCity','Hyd','WG',7687,1 UNION ALL

    SELECT 5,'Kalyan1', 'CinemaStreet','Tuni','EG',1234,1 UNION ALL

    SELECT 7,'MVRao1', 'LBNagar','Kkd','EG',123,0 UNION ALL

    SELECT 8,'MMRao1', 'Pathapeta','Nzd', 'Krishna',342,0;

    INSERT INTO CustomerDetails_Current

    SELECT 4,'Pavan2', 'HitechCity','Hyd','WG',7687,1 UNION ALL

    SELECT 5,'Kalyan2', 'CinemaStreet','Tuni','EG',1234,1 UNION ALL

    SELECT 7,'MVRao2', 'LBNagar','Kkd','EG',123,0 UNION ALL

    SELECT 8,'MMRao2', 'Pathapeta','Nzd', 'Krishna',342,0;

    INSERT INTO TargetActivity

    SELECT 1, 'Customer' UNION ALL

    SELECT 2, 'CustomerDetails' UNION ALL

    SELECT 3, 'CustomerDetails_Current'

    -- Output

    Declare @sql Varchar(MAX)

    Select @sql = STUFF((Select ' UNION ALL SELECT c1.id AS ActivityTableId, c1.at AS ActivityTable, c1.x AS ActivityTableCount, c2.x AS ActivityTableFilterCount FROM (SELECT 2 AS id, ''' + ActivityTable + ''' AS at, COUNT(*) AS x FROM Customer) AS c1 CROSS JOIN (SELECT COUNT(*) AS x FROM Customer WHERE [Status]=1) AS c2' From TargetActivity FOR XML PATH('')),1,11,'')

    Execute(@sql)

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 7 posts - 1 through 6 (of 6 total)

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