Help needed for Select Query

Question

Post reply

Help needed for Select Query

pavanmuppidi-1058973

SSC Rookie

Points: 35
More actions
July 8, 2013 at 10:31 am

#275877

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

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

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/

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

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/

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

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

pavanmuppidi-1058973 SSC Rookie Points: 35 More actions · Answer 4

Dear Alan.B,

Wonderful. This is what i am exactly looking for.

Thanks a lot. 🙂

Regards,

Pavan

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

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

vinu512 SSCoach Points: 15729 More actions · Answer 6

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] 😉