Another SQL query question

[Homerboy]

I have the following QUERY:

SELECT     

COUNT(CASE WHEN CODE = 'SMINI' THEN FILENO END) AS SMINI,
COUNT(CASE WHEN CODE = 'SCALLIPA' THEN FILENO END) AS SCALLIPA,
COUNT(*) AS [TOTAL CONTACTS]

FROM         dbo.ACTIVE 
                      
WHERE     (dbo.ACTIVE.CODE IN ('SMINI', 'SCALLIPA'))

with a table structure like:

P_id	FILENO	TRANS_DATE	User Initials	TRANS_TIME	CODE	Note	Record Number
1	130645	4/28/2011	EEB	1899-12-30 15:04:02.000	EEB     	15:04 MANUALLY UPDATED BAL ON TRIP SITE         	42224947

What I really want to do though is get a COUNT of each time a SMINI or SCALLIPA occurs per given day per FILENO. Does that make sense?

So if, on FILENO 130645, on 4/28/2011 There are two rows, one with SMINI and one with SCALLIPA, that would not count as 2, it would count as 1.

I realize I am going about this fundamentally wrong right now, as I'm getting the number of CALLIPAs and SMINIs individually for each... my brain is stuck though and I can't get into the right train of thought.

 

[Jeraden]

Do you want all rows, even ones without SMINIs or SCALLIPAs? And should those count as 1?

Or should this focus on only SMINIs and SCALLIPAs, meaning the row in your table structure example would never be picked up since its an EEB?

And if there are 3 SMINIs and 2 SCALLIPAs on the same day with the same fileno, should that still only count as 1? So when would the count ever be anything other than 1?

 

[PhatoseAlpha]

Select count(*), FILE_NO, TRANS_DATE, CODE
from dbo.ACTIVE
where CODE = "SMINI" or CODE = "SCALLIPA"
group by FILE_NO, TRANS_DATE, CODE

 

[Mark R]

Misread the post.

If you want the grand total then PhatoseAlpha's code can be modified as:

Select count(*), FILE_NO, TRANS_DATE
from dbo.ACTIVE
where CODE = "SMINI" or CODE = "SCALLIPA"
group by FILE_NO, TRANS_DATE

 

[Homerboy]

That doesn't give me unique days though.

I've slept on this and thought about it more...
Let me show you how I'd like the result to read:

USER_ID	6/1/2011	6/2/2011	6/3/2011	6/4/2011	6/5/2011	6/6/2011	6/7/2011	6/8/2011	6/9/2011	6/10/2011	6/11/2011	6/12/2011	6/13/2011	6/14/2011
AAA	1	0	0	0	0	1	0	1	0	3	0	0	1	1
BBB	0	0	0	0	0	0	0	2	1	1	0	0	1	1
CCC	0	0	0	0	0	0	0	0	0	0	0	0	0	1

Column 1: is user IDs (GROUPED BY I assume)
Column 2 - whatever: are the days
The data in the grid is the count of when SMINI, SCALLIPA occurs, per user, per day. per FILENO But, if SMINI and/or SCALLIPA both exist on the same FILENO on the same day, for the same user, that's 1 "hit". I want the number of times SMINI or SCALLIPA exists per file, per user, per day.

This is impossible to explain... gaaaaaaaaaaaaaaaaah

 

[KIAman]

You want transdate as columns? You're gonna have to use temp tables to prefill the counts per day per code, or you can use a pivot table, do a pivot(count(code) for Transdate in (list your dates).

If you want more specific examples, let me know, but that should point you in the right direction.

 

[Mark R]

It's tricky to get the data into columns unless your particular version of SQL supports pivot functions. If it does, then I can't help because I've never used it.

However, try this which avoids the need for pivot commands.

SELECT
User_ID,
Count(CASE subquery.trans_date=convert(datetime,'2011-06-01',120) THEN 1 END) AS CountOn20110601,
Count(CASE subquery.trans_date=convert(datetime,'2011-06-02',120) THEN 1 END) AS CountOn20110602,
Count(CASE subquery.trans_date=convert(datetime,'2011-06-03',120) THEN 1 END) AS CountOn20110603,
... <=   You will need to add a separate statement for each column
FROM 
(
SELECT DISTINCT
trans_date, fileno, user_id
FROM
dbo.Active
WHERE
code = "SMINI" or code = "SCALLIPA"
) subquery

 

[Evadman]

IIRC, you are doing this in SQL 2005. Use the PIVOT function.