July 8, 2013 at 10:31 am
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
July 8, 2013 at 11:02 am
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/
July 8, 2013 at 11:23 am
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/
July 8, 2013 at 1:27 pm
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
-- Itzik Ben-Gan 2001
July 15, 2013 at 10:42 am
Dear Alan.B,
Wonderful. This is what i am exactly looking for.
Thanks a lot. 🙂
Regards,
Pavan
July 15, 2013 at 11:05 am
No problem.
-- Itzik Ben-Gan 2001
July 17, 2013 at 12:36 am
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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply