September 9, 2014 at 4:34 pm
I have attached an excel spreadsheet with sample data and results I am looking for. I'm trying to create a column with a running difference in counts. So for each event_id I want the difference in counts of member_id that have event = open. Any help greatly appreciated.
September 10, 2014 at 8:15 am
If I understand your requirement correctly, you would need something like this:
;With ID_TotalCount(Int_ID, Int_IDTotalCount)
AS
(
SELECTInt_ID, COUNT(Int_ID)
FROMdbo.tblData
GROUP BYInt_ID
),
ID_OpenCount(Int_ID, Int_IDOpenCount)
AS
(
SELECTInt_ID, COUNT(Int_ID)
FROMdbo.tblData
WHEREEvent = 'Open'
GROUP BYInt_ID
)
SELECTDISTINCT td.Int_ID
,ISNULL(Int_IDOpenCount, 0) AS Opened
,ISNULL(Int_IDTotalCount - Int_IDOpenCount, -1) AS Diff
FROMID_TotalCount tc
JOINID_OpenCount oc
ONtc.Int_ID = oc.Int_ID
RIGHT JOINdbo.tblData td
ONtd.Int_ID = oc.Int_ID
Btw - not sure if your columns are actually named Event, Date etc. but if they are, you may want to revisit your naming convention and eliminate key/reserved words usage.
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply