Beginner- Needs sql multi-query help 1

[ljsmith91]

I am a novice and need some help with this sql query. I have a sql table that contains records of tape media written to, the date written to and whether the media is onsite/offsite and the date it was discovered offsite. It has other fields but these 4 are what is of importance. Sample info in the table is:

Media DATE_Written Offsite Discovered_OFFSITE_DATE
tape01 01-03-2009 N NULL
tape01 01-04-2009 N NULL
tape01 01-05-2009 N NULL
tape99 01-03-2009 Y 01-06-2009
tape99 01-04-2009 Y 01-06-2009
tape99 01-05-2009 Y 01-06-2009
tape33 01-04-2009 Y 01-08-2009
tape33 01-05-2009 Y 01-08-2009
tape99 01-21-2009 Y 01-25-2009
tape99 01-22-2009 Y 01-25-2009

I need to query this table and get a count of media written in month of Jan 2009 and another distinct count for every time a media was sent offsite.

So, in the above sample, 10 medias are written to but the offsite media count would be only 3 medias(not 6). tape99 was written to on 5 different days and sent offsite 2 different dates. I believe there must be some sub-query using that discovery date of counting distinct media moving offsite but I have no idea how to do it.

Any help/ direction would be great. Thanks.

ljs

 

[RiverGuy]

Is there what you are looking for?

SELECT 
Media,
COUNT(*) AS TotalWrites,
SUM(
	CASE
		WHEN Offsite = 'Y' THEN 1
		ELSE 0
	END) AS OffSiteWrites
FROM YourTable
WHERE DATE_Written >= '20090101' AND DATE_Written < '20090201'
GROUP BY Media

 

[ljsmith91]

Hi RiverGuy,

Thanks for the query suggestion. However, I need the totals for the month rather than by media. So I need Output that looks like this:

Total_Write_Count Total_Offsite_Count
10 4

The key is that I want to count the same media as offsite if the discovered_offsite date is different or distinct for same media name. So, in my sample table, my total_offsite_count would end up as 4 and not 6. Is there a query to get at that ?

Thanks again.

ljs

 

[ljsmith91]

Anyone have a way to query this table and extract media totals when the media and Discovered_offsite_Date are distinct? I'm in over my head. Thanks for any direction/ guidance.

ljs

 

[RyanEK]

Hi,

How about this?

select count(*) as Total_Write_Acount,
(select count(*) from (
  select distinct media, discovered_offsite_date
  from @temp where offsite = 'Y') as t) as Total_Offsite_Count 
from 
@temp 
where DATE_Written >= '20090101' AND DATE_Written < '20090201'

Ryan

 

[ljsmith91]

RyanEK,

Thanks. I think that may be what i am looking for. However, by using "distinct media" in the inner query, aren't you limiting the times when a media is listed multiple times with different discovered_offsite_dates? I want to eliminate dupe media ONLY when the discovered_offsite_date is the same. How is this accomplised? -ljs

 

[RyanEK]

From your table above, what value are you expecting for Total_Offsite_Count? You said initially 3 but changed it to 4.

I'm not sure what you mean in your last paragraph but if you just want a count of how many time "any" media was sent offsite, change the sql to:

...
select distinct discovered_offsite_date
...

Either way, I don't see how your table above can return any value other than 3?

Ryan

 

[ljsmith91]

RyanEK,

Yes the 4 was a mistake on my part. I am looking for the query to return the value of 3 not 4. Am my question that I admit was confusing let me rephrase. If I use "distinct media", wouldn't tape99 in my sample only count as 1 rather than 2 ? I am looking to count it as 2.

Larry

 

[Geoka]

Larry,

Why don't you try the inner query alone specified by Ryan and see the output matches your requirement. The solution provided by Ryan will returns 10 as total count and 3 as offsite count. As long as discovered_offsite_date used in the subquery, the DISTINCT operator will check that field also before eliminating the duplicates.

select distinct media, discovered_offsite_date
  from @temp where offsite = 'Y'