Creating calculated columns - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Creating calculated columns

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-12-19 : 09:43:26

Hello all

I need to work out the number of mails that have been opened and not opened by the recipients. The final results need to look something like this:



CycleCode Date	    Mails Sent	Mails Opened	Mails Unopened
CPW0101	  12/12/05  104	        89	        15
CPF0114	  13/12/05  295	        101	        196

CycleCode Date	   Mails Sent	Mails Opened	% of Mails Opened 
CPW0101	  12/12/05 104	        89	        86
CPF0114	  13/12/05 295	        101	        34

So far, the query is as follows:


SELECT 	  CycleCode
	, CycleInstanceDate
	, COUNT(E.EBillID)AS [EBills Sent]
	, COUNT(DISTINCT EO.EBillID)AS [EBills Opened]
	, ??????? AS [EBills unopened]
FROM Cycle AS C INNER JOIN CycleInstance AS CI ON C.CycleID = CI.CycleID 
INNER JOIN EBill AS E ON CI.CycleInstanceID = E.CycleInstanceID
INNER JOIN EBillOpened AS EO ON E.EBillID = EO.EBillID
GROUP BY CycleCode, CycleInstanceDate

However, I have no idea how to write the calculated column or how best to tackle it to obtain both of the preceding result sets. I have tried: select ([EBills Sent]-[EBills Opened]) but this does not work. Suggestions would be most appreciated.

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-12-19 : 09:54:01

I do have a solution, but there must be a simpler way to write it as a calculated column. This is what I have working:


SELECT T1.CycleCode, CONVERT(VARCHAR(14),T1.CycleInstanceDate,106)AS [Date], T1.[EBills Sent],T1.[EBills Opened],T2.[EBills Unopened]FROM
(SELECT   CycleCode
	, CycleInstanceDate
	, COUNT(E.EBillID)AS [EBills Sent]
	, COUNT(DISTINCT EO.EBillID)AS [EBills Opened]
FROM Cycle AS C INNER JOIN CycleInstance AS CI ON C.CycleID = CI.CycleID 
INNER JOIN EBill AS E ON CI.CycleInstanceID = E.CycleInstanceID
INNER JOIN EBillOpened AS EO ON E.EBillID = EO.EBillID
GROUP BY CycleCode, CycleInstanceDate)AS T1
INNER JOIN
(SELECT   CycleCode
	, CycleInstanceDate
	, COUNT(E.EBillID)- COUNT(DISTINCT EO.EBillID) AS [EBills unopened]
FROM Cycle AS C INNER JOIN CycleInstance AS CI ON C.CycleID = CI.CycleID 
INNER JOIN EBill AS E ON CI.CycleInstanceID = E.CycleInstanceID
INNER JOIN EBillOpened AS EO ON E.EBillID = EO.EBillID
GROUP BY CycleCode, CycleInstanceDate)AS T2
ON T1.CycleCode = T2.CycleCode

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-12-19 : 10:20:18

Right, I have changed the query as it was not returning the right results, but I am still sure there must be a simpler way:


SELECT CycleCode
, CONVERT(VARCHAR(14),T1.CycleInstanceDate,106)AS [Date]
, COUNT(DISTINCT SentEBillID)AS [EBills Sent]
, COUNT(DISTINCT OpenedEBillID)AS [EBills Opened]
, COUNT(DISTINCT SentEBillID)-COUNT(DISTINCT OpenedEBillID) AS [EBills Unopened]
FROM
(SELECT   CycleCode
	, CycleInstanceDate
	, E.EBillID AS [SentEBillID]
	, EO.EBillID AS [OpenedEBillID]
FROM Cycle AS C INNER JOIN CycleInstance AS CI ON C.CycleID = CI.CycleID 
INNER JOIN EBill AS E ON CI.CycleInstanceID = E.CycleInstanceID
LEFT JOIN EBillOpened AS EO ON E.EBillID = EO.EBillID)AS T1
GROUP BY CycleCode, CycleInstanceDate

Can anyone point me in the right direction for some articles on using calculated columns, derived columns, or whatever the correct terminology may be, as I am not having much success finding what I want on Google. Thankyou

Hearty head pats

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-19 : 10:36:57

Cannot understand ur table structure!!

U can try the
1. WHERE clause with UNION as appropriate
or
2. When / Case ...

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-12-19 : 11:00:06

Hello, thanks for replying. The table structures are as follows:


CREATE TABLE [dbo].[Cycle] (
	[CycleID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[CycleCode] [varchar] (12) NOT NULL ,
	[ClientCode] [varchar] (20) NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[CycleInstance] (
	[CycleInstanceID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[CycleID] [smallint] NOT NULL ,
	[CycleInstanceDate] [datetime] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[EBill] (
	[EBillID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [bigint] NOT NULL ,
	[InvoiceNumber] [varchar] (12) NOT NULL ,
	[EmailAddress] [varchar] (50) NOT NULL ,
	[CycleInstanceID] [bigint] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[Customer] (
	[CustomerID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[AccountNumber] [varchar] (7) NOT NULL ,
	[TelephoneNumber] [varchar] (15) NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

CREATE TABLE [dbo].[EBillOpened] (
	[EBillOpenedID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[EbillID] [bigint] NOT NULL ,
	[DateOpened] [smalldatetime] NOT NULL 
) ON [CPWEBilling_Reporting_data]
GO

A cycle can have multiple cycleInstances, and a cycleInstance will have multiple EBills. A customer can have multiple EBills. An EBill may or may not be opened by the customer. I want to record the number of customers that open their ebill for each cycle instance (but not the number of times it is opened - one time per customer).

I just wanted to know if their was an easy way to write the query using a calculated column and whether this would be the best approach. I have never used calculated columns before, so am not sure what is possible or what the syntax would be?!?

The full query is as follows, and gets the right results:


SELECT CycleCode
, CONVERT(VARCHAR(14),T1.CycleInstanceDate,106)AS [Date]
, COUNT(DISTINCT SentEBillID)AS [Total EBills Sent]
, COUNT(DISTINCT OpenedEBillID)AS [EBills Opened]
, ((100/COUNT(DISTINCT SentEBillID))* COUNT(DISTINCT OpenedEBillID))AS [% of EBills Opened]
, COUNT(DISTINCT SentEBillID)-COUNT(DISTINCT OpenedEBillID) AS [EBills Unopened]
, ((100/COUNT(DISTINCT SentEBillID))* (COUNT(DISTINCT SentEBillID)-COUNT(DISTINCT OpenedEBillID)))AS [% of EBills Unopened]
FROM
(SELECT   CycleCode
	, CycleInstanceDate
	, E.EBillID AS [SentEBillID]
	, EO.EBillID AS [OpenedEBillID]
FROM Cycle AS C INNER JOIN CycleInstance AS CI ON C.CycleID = CI.CycleID 
INNER JOIN EBill AS E ON CI.CycleInstanceID = E.CycleInstanceID
LEFT JOIN EBillOpened AS EO ON E.EBillID = EO.EBillID)AS T1
GROUP BY CycleCode, CycleInstanceDate

Results


CycleCode    Date           Total EBills Sent EBills Opened % of EBills Opened EBills Unopened % of EBills Unopened 
------------ -------------- ----------------- ------------- ------------------ --------------- -------------------- 
CPW0113      13 Dec 2005    5                 2             40                 3               60
CPW0105      16 Dec 2005    3                 2             66                 1               33

However, rather than writing out all the count calculations again, I was hoping that there would be some method of using the results in the columns (EBills Sent, EBills Opened) to calculate the percentages and EBills Unopened.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources