Help on how to accomplish this report? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help on how to accomplish this report?

Author

Topic

new_bees
Starting Member

27 Posts

Posted - 2008-11-11 : 10:57:10

Hi Guys,

I need help on this query. This code works, but the problem is that they are hard coded. What I want is to be able to loop through the date indefinitely for the users, items and price queries and union them. When you look at the query, the WHERE clause dateCreated is hard coded.

DECLARE @B2004 INT,
@B2005 INT,
@B2006 INT,
@B2007 INT,
@B2008 INT,
@RIQ2004 INT,
@RIQ2005 INT,
@RIQ2006 INT,
@RIQ2007 INT,
@RIQ2008 INT,
@RPAF2004 INT,
@RPAF2005 INT,
@RPAF2006 INT,
@RPAF2007 INT,
@RPAF2008 INT

--Users
SET @B2004 = (SELECT COUNT(DISTINCT UserID) as 'July 1, 2004 Users' FROM Users u WITH(NOLOCK) WHERE u.createdDate < '2004.07.01')
SET @B2005 = (SELECT COUNT(DISTINCT UserID) as 'July 1, 2005 Users' FROM Users u WITH(NOLOCK) WHERE u.createdDate < '2005.07.01')
SET @B2006 = (SELECT COUNT(DISTINCT UserID) as 'July 1, 2006 Users' FROM Users u WITH(NOLOCK) WHERE u.createdDate < '2006.07.01')
SET @B2007 = (SELECT COUNT(DISTINCT UserID) as 'July 1, 2007 Users' FROM Users u WITH(NOLOCK) WHERE u.createdDate < '2007.07.01')
SET @B2008 = (SELECT COUNT(DISTINCT UserID) as 'July 1, 2008 Users' FROM Users u WITH(NOLOCK) WHERE u.createdDate < '2008.07.01')

--Item
SET @RIQ2004 = (SELECT COUNT(*) as 'July 1, 2004 SoldItems' FROM item i WITH (NOLOCK) WHERE i.createdDate BETWEEN '2003.07.01' AND '2004.07.01')
SET @RIQ2005 = (SELECT COUNT(*) as 'July 1, 2005 SoldItems' FROM item i WITH (NOLOCK) WHERE i.createdDate BETWEEN '2004.07.01' AND '2005.07.01')
SET @RIQ2006 = (SELECT COUNT(*) as 'July 1, 2006 SoldItems' FROM item i WITH (NOLOCK) WHERE i.createdDate BETWEEN '2005.07.01' AND '2006.07.01')
SET @RIQ2007 = (SELECT COUNT(*) as 'July 1, 2007 SoldItems' FROM item i WITH (NOLOCK) WHERE i.createdDate BETWEEN '2006.07.01' AND '2007.07.01')
SET @RIQ2008 = (SELECT COUNT(*) as 'July 1, 2008 SoldItems' FROM item i WITH (NOLOCK) WHERE i.createdDate BETWEEN '2007.07.01' AND '2008.07.01')

--Price
SET @RPAF2004 = (SELECT SUM(Price) as 'July 1, 2004 Price' FROM price p WITH (NOLOCK) WHERE p.createdDate BETWEEN '2003.07.01' AND '2004.07.01')
SET @RPAF2005 = (SELECT SUM(Price) as 'July 1, 2005 Price' FROM price p WITH (NOLOCK) WHERE p.createdDate BETWEEN '2004.07.01' AND '2005.07.01')
SET @RPAF2006 = (SELECT SUM(Price) as 'July 1, 2006 Price' FROM price p WITH (NOLOCK) WHERE p.createdDate BETWEEN '2005.07.01' AND '2006.07.01')
SET @RPAF2007 = (SELECT SUM(Price) as 'July 1, 2007 Price' FROM price p WITH (NOLOCK) WHERE p.createdDate BETWEEN '2006.07.01' AND '2007.07.01')
SET @RPAF2008 = (SELECT SUM(Price) as 'July 1, 2008 Price' FROM price p WITH (NOLOCK) WHERE p.createdDate BETWEEN '2007.07.01' AND '2008.07.01')

--Temp table
DECLARE @TempTable Table
(
ID INT,
Label varchar(50),
COL2004 FLOAT,
COL2005 FLOAT,
COL2006 FLOAT,
COL2007 FLOAT,
COL2008 FLOAT
)
--Insert into temp table
INSERT INTO @TempTable (ID, Label, COL2004, COL2005, COL2006, COL2007, COL2008)
SELECT '1' AS ID, 'Users' AS Label, @B2004, @B2005,@B2006,@B2007,@B2008
UNION ALL
SELECT '2' AS ID, 'Item' AS Label, @RIQ2004,@RIQ2005,@RIQ2006,@RIQ2007,@RIQ2008
UNION ALL
SELECT '4' AS ID, 'Price' AS Label, @RPAF2004,@RPAF2005,@RPAF2006,@RPAF2007,@RPAF2008

--Temp table
DECLARE @MainTempTable Table
(
ID INT,
Label varchar(50),
COL2004 FLOAT,
COL2005 FLOAT,
COL2006 FLOAT,
COL2007 FLOAT,
COL2008 FLOAT
)
--Insert into temp table
INSERT INTO @MainTempTable (ID, Label, COL2005, COL2006, COL2007, COL2008)
SELECT ID, Label, CAST(ROUND((COL2005/COL2004 - 1) * 100,1) AS INT) AS COL2005, CAST(ROUND((COL2006/COL2005 - 1) * 100,1) AS INT) AS COL2006, CAST(ROUND((COL2007/COL2006 - 1) * 100,1) AS INT) AS COL2007, CAST(ROUND((COL2008/COL2007 - 1) * 100,1) AS INT) AS COL2008 FROM @TempTable

SELECT 2005 [Year], SUM(COL1) AS Brokers, SUM(COL2) AS 'Rolling IFP', SUM(COL3) AS 'Rolling Group', SUM(COL4) AS 'Annual Premium', SUM(COL5) AS Plans
FROM
(
SELECT (CASE WHEN ID = 1 THEN COL2005 ELSE 0 END) COL1,
(CASE WHEN ID = 2 THEN COL2005 ELSE 0 END) COL2,
(CASE WHEN ID = 3 THEN COL2005 ELSE 0 END) COL3,
(CASE WHEN ID = 4 THEN COL2005 ELSE 0 END) COL4,
(CASE WHEN ID = 5 THEN COL2005 ELSE 0 END) COL5
FROM @MainTempTable
) COL2005
UNION ALL
SELECT 2006 [Year], SUM(COL1) AS Brokers, SUM(COL2) AS 'Rolling IFP', SUM(COL3) AS 'Rolling Group', SUM(COL4) AS 'Annual Premium', SUM(COL5) AS Plans
FROM
(
SELECT (CASE WHEN ID = 1 THEN COL2006 ELSE 0 END) COL1,
(CASE WHEN ID = 2 THEN COL2006 ELSE 0 END) COL2,
(CASE WHEN ID = 3 THEN COL2006 ELSE 0 END) COL3,
(CASE WHEN ID = 4 THEN COL2006 ELSE 0 END) COL4,
(CASE WHEN ID = 5 THEN COL2006 ELSE 0 END) COL5
FROM @MainTempTable
) COL2006
UNION ALL
SELECT 2007 [Year], SUM(COL1) AS Brokers, SUM(COL2) AS 'Rolling IFP', SUM(COL3) AS 'Rolling Group', SUM(COL4) AS 'Annual Premium', SUM(COL5) AS Plans
FROM
(
SELECT (CASE WHEN ID = 1 THEN COL2006 ELSE 0 END) COL1,
(CASE WHEN ID = 2 THEN COL2007 ELSE 0 END) COL2,
(CASE WHEN ID = 3 THEN COL2007 ELSE 0 END) COL3,
(CASE WHEN ID = 4 THEN COL2007 ELSE 0 END) COL4,
(CASE WHEN ID = 5 THEN COL2007 ELSE 0 END) COL5
FROM @MainTempTable
) COL2007
UNION ALL
SELECT 2008 [Year], SUM(COL1) AS Brokers, SUM(COL2) AS 'Rolling IFP', SUM(COL3) AS 'Rolling Group', SUM(COL4) AS 'Annual Premium', SUM(COL5) AS Plans
FROM
(
SELECT (CASE WHEN ID = 1 THEN COL2008 ELSE 0 END) COL1,
(CASE WHEN ID = 2 THEN COL2008 ELSE 0 END) COL2,
(CASE WHEN ID = 3 THEN COL2008 ELSE 0 END) COL3,
(CASE WHEN ID = 4 THEN COL2008 ELSE 0 END) COL4,
(CASE WHEN ID = 5 THEN COL2008 ELSE 0 END) COL5
FROM @MainTempTable
) COL2008

Your help is very much appreciated.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-11 : 14:04:23

Here's an example that hopfully will get you to a better starting point (It will run your data with out hard coding). You are asking for someone to do a lot of work for you, and I unfortuantly do not have the time to go through all of this. Try to figure out the remaining on your own and ask a more specific question if you run into a issue.

From this point look into a pivot to arange the data as necessary, I will be happy to try to assist if you can post a more specific request.


Declare @T Table (UserID int,Item int,Price money,CreateDate datetime)
Insert Into @T(UserID ,Item ,Price,CreateDate )
select 1,1,2.00,'01/01/2004' Union all
select 1,2,3.00,'02/02/2004' Union all
select 1,3,5.00,'03/03/2006' Union all
select 1,4,2.00,'04/04/2007' Union all
select 1,5,2.00,'05/05/2007' Union all
select 1,6,9.00,'06/06/2007' Union all
select 2,1,2.00,'01/01/2005' Union all
select 2,2,3.00,'02/02/2005' Union all
select 2,3,5.00,'03/03/2006' Union all
select 2,4,2.00,'04/04/2007' Union all
select 2,4,2.00,'04/05/2007' Union all
select 2,4,2.00,'04/06/2007' Union all
select 2,5,2.00,'05/05/2007' Union all
select 2,6,9.00,'06/06/2007' Union all
select 3,1,2.00,'01/01/2006' Union all
select 3,2,3.00,'02/02/2006' Union all
select 3,3,5.00,'03/03/2006' Union all
select 3,4,2.00,'04/04/2007' Union all
select 3,5,2.00,'05/05/2007' Union all
select 3,6,9.00,'06/06/2007' Union all
select 4,1,2.00,'01/01/2006' Union all
select 4,2,3.00,'02/02/2006' Union all
select 4,3,5.00,'03/03/2006' Union all
select 4,4,2.00,'04/04/2007' Union all
select 4,5,2.00,'05/05/2007' Union all
select 4,6,9.00,'06/06/2007' 


--Users


select year(dateadd(month,6,a.CreateDate)) as MyYear,count(a.Item) as ItemCount,Sum(Price) as PriceSum,max(tmp.UserCount) as UserCount
from
@T a inner Join 
(
select year(dateadd(month,6,b.CreateDate)) as MyYear,
		(Select count(distinct bb.UserID)
		from
		@T bb 
		where 
		bb.CreateDate <= '07/01/' + convert(char(4),year(dateadd(month,6,b.CreateDate))))as UserCount
from 
@T b
group by year(dateadd(month,6,b.CreateDate))
) tmp
on year(dateadd(month,6,a.CreateDate))  = tmp.MyYear
group by year(dateadd(month,6,a.CreateDate))

/*Results
(26 row(s) affected)
MyYear      ItemCount   PriceSum              UserCount
----------- ----------- --------------------- -----------
2004        2           5.00                  1
2005        2           5.00                  2
2006        8           30.00                 4
2007        14          56.00                 4

(4 row(s) affected)
*/

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources