Display count of all records from 3 tablesin 1row
-
I have Table1, Table2,Table3
[ Each of the 3 tables have the same structure : col1,col2,col3,creationdate ]
I need to write a SQL-Query or Procedure which takes a daterange(ie date1, date2) and display
---Table1---Table2--Table3
----10-------12------14
[ where 10 is total count of records in table1 where creationdate lies between date1 and date2 ][ assume both dates inclusive]
[ where 12 is total count of records in table2 where creationdate lies between date1 and date2 ]
[ where 13 is total count of records in table3 where creationdate lies between date1 and date2 ]
Do i need to write a stored procedure or SQL query to implement this.
I would appreciate if someone could help me in sovling this.
{ it might sound strange, but i need this for my report generation using SQL Server Report Services}
Thanks,
Sarang Reddy
-
As this is for SQL Reporting Services, you can do this very easily by defining seperate datasets for each of the items you need.
--------------------
Colt 45 - the original point and click interface
-
While you can create three seperate datasets for each item you need, you can also use the following query.
DECLARE @Table1 TABLE (col1 VARCHAR(50),col2 VARCHAR(50),col3 VARCHAR(50),creationdate DATETIME)
DECLARE @Table2 TABLE (col1 VARCHAR(50),col2 VARCHAR(50),col3 VARCHAR(50),creationdate DATETIME)
DECLARE @Table3 TABLE (col1 VARCHAR(50),col2 VARCHAR(50),col3 VARCHAR(50),creationdate DATETIME)
DECLARE @Start_Date DATETIME, @End_Date DATETIME
SET @Start_Date = '9/1/2004'
SET @End_Date = '9/30/2004'
INSERT INTO @Table1 VALUES('1','1','1','9/1/2004')
INSERT INTO @Table1 VALUES('2','2','2','9/1/2004')
INSERT INTO @Table1 VALUES('3','3','2','9/1/2004')
INSERT INTO @Table2 VALUES('1','1','1','9/1/2004')
INSERT INTO @Table3 VALUES('1','1','1','9/1/2004')
INSERT INTO @Table3 VALUES('2','2','2','9/1/2004')
SELECT
(SELECT COUNT(creationdate) FROM @Table1 WHERE creationdate BETWEEN @Start_Date AND @End_Date) Count1,
(SELECT COUNT(creationdate) FROM @Table2 WHERE creationdate BETWEEN @Start_Date AND @End_Date) Count2,
(SELECT COUNT(creationdate) FROM @Table3 WHERE creationdate BETWEEN @Start_Date AND @End_Date) Count3
This is a working query - copied straight from query analyzer that has all it needs to run. You will want to replace the Table variables with your tables and make it into a procedure - so yours end product will look more like...
CREATE PROCEDURE spRS_Test (@Start_Date DATETIME, @End_Date DATETIME)
AS
SELECT
(SELECT COUNT(creationdate) FROM TABLE1 WHERE creationdate BETWEEN @Start_Date AND @End_Date) Count1,
(SELECT COUNT(creationdate) FROM TABLE2 WHERE creationdate BETWEEN @Start_Date AND @End_Date) Count2,
(SELECT COUNT(creationdate) FROM TABLE3 WHERE creationdate BETWEEN @Start_Date AND @End_Date) Count3
END
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply