Solved

Counting multiple fields that have different dates

Posted on 2011-02-23
4
245 Views
Last Modified: 2012-05-11
I have a data set that has 2 date values, an opened date and a closed date. I have a fairly long query that filters on some groupings and counts when the records were opened (also, one of the groupings groups the opened dates by month).
I need to do the same query, but, count/group by the closed date. Would it be possible to do something like a case for the opened and closed dates so they could be counted in the same query, or, do I have to repeat the whole query and just group by the closed date in the second query?
(also, if you happen to spot any other areas of code improvement, please suggest away)
SELECT	WebID.OEM, dbo.udf_FormatDate(Install.StartDate, 'mmm-yy') as Dated, 
		CASE CW.CategoryShort	--a few can be filtered by this, others need more (below)
				When 'Website' Then 'Website'
				When 'imr Website' Then 'Website'
				When 'Procare' Then 'Procare'
				When 'PowerSearchComplete' Then 'SearchAll'
	When 'Search' Then 'SearchAll'
	When 'SearchOther' Then 'SearchAll'	
	When 'Other' Then 'Other'
		Else	--these need addional filters
			CASE When CW.CategoryShort = 'DAP' and CW.PromoNameShort is not null Then 'DAP-New'
				 When CW.CategoryLong = 'DAP Used' Then 'DAP-Used'
			Else 'AllOthers'
			End
		End as Category, 
		count(Install.sfWebID) as Enrollments
FROM	tblsfInstalledBase  as Install INNER JOIN
		tblcwInstalledBase as CW ON Install.ProductCode = CW.ProductCode INNER JOIN
		tblsfWebID as WebID ON Install.sfWebID = WebID.sfWebID INNER JOIN
		tblsfAccount as Accnt ON Accnt.sfAccntID = WebID.sfAccntID
WHERE	CW.CategoryLong <> 'Promo' AND Install.StartDate > '1/1/2010' AND --PromoNameShort is null AND(this is always null for non DAP products)
		NOT (Accnt.AccntName like 'Cobalt%' or Accnt.AccntName like '%test%'
		or WebID.WebID like 'asm-%' or WebID.WebID like 'prospector%' or WebID.WebID like 'export%'
		or WebID.WebID like 'cobalt-%')
Group By WebID.OEM, dbo.udf_FormatDate(Install.StartDate, 'mmm-yy'), 
		CASE CW.CategoryShort	
				When 'Website' Then 'Website'
				When 'imr Website' Then 'Website'
				When 'Procare' Then 'Procare'
				When 'PowerSearchComplete' Then 'SearchAll'
				When 'Search' Then 'SearchAll'
				When 'SearchOther' Then 'SearchAll'
				When 'Other' Then 'Other'
		Else
			CASE When CW.CategoryShort = 'DAP' and CW.PromoNameShort is not null Then 'DAP-New'
				 When CW.CategoryLong = 'DAP Used' Then 'DAP-Used'
			Else 'AllOthers'
			End
		End
ORDER BY WebID.OEM, Dated

Open in new window

0
Comment
Question by:avoorheis
  • 2
4 Comments
 

Author Comment

by:avoorheis
ID: 34962152
so the result would look something like: (also note that I stated opened/closed dates, but, code actually uses Enrolled and would use Canceled for the dates
OEM    Dated      Category       Enrolled        Canceled
Name1  May-10    Other            4                       3
etc
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34967550
Hi,

How about some sample data ...

Regards
  David
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34968044
Can you check this?
SELECT DISTINCT OEM, 
                  StartDate, 
                  EndDate, 
                  Category, 
                  COUNT(sfWebID) 
                    OVER(PARTITION BY OEM,StartDate,Category ) Enrolled, 
                  COUNT(sfWebID) 
                    OVER(PARTITION BY OEM,EndDate,Category ) Closed 
    FROM (SELECT WebID.OEM, 
                 dbo.UDF_FORMATDATE(Install.StartDate,'mmm-yy') AS StartDate, 
                 dbo.UDF_FORMATDATE(Install.EndDate,'mmm-yy')   AS EndDate, 
                 CASE CW.CategoryShort  --a few can be filtered by this, others need more (below)  
                   WHEN 'Website' THEN 'Website' 
                   WHEN 'imr Website' THEN 'Website' 
                   WHEN 'Procare' THEN 'Procare' 
                   WHEN 'PowerSearchComplete' THEN 'SearchAll' 
                   WHEN 'Search' THEN 'SearchAll' 
                   WHEN 'SearchOther' THEN 'SearchAll' 
                   WHEN 'Other' THEN 'Other' 
                   ELSE  --these need addional filters  
                  CASE 
                          WHEN CW.CategoryShort = 'DAP' 
                               AND CW.PromoNameShort IS NOT NULL THEN 'DAP-New' 
                          WHEN CW.CategoryLong = 'DAP Used' THEN 'DAP-Used' 
                          ELSE 'AllOthers' 
                        END 
                 END AS Category, 
                 COUNT(Install.sfWebID)                         AS Enrollments 
            FROM tblsfInstalledBase AS Install 
                 INNER JOIN tblcwInstalledBase AS CW 
                   ON Install.ProductCode = CW.ProductCode 
                 INNER JOIN tblsfWebID AS WebID 
                   ON Install.sfWebID = WebID.sfWebID 
                 INNER JOIN tblsfAccount AS Accnt 
                   ON Accnt.sfAccntID = WebID.sfAccntID 
           WHERE CW.CategoryLong <> 'Promo' 
                 AND Install.StartDate > '1/1/2010' 
                 AND --PromoNameShort is null AND(this is always null for non DAP products)  
                  NOT (Accnt.AccntName LIKE 'Cobalt%' 
                        OR Accnt.AccntName LIKE '%test%' 
                        OR WebID.WebID LIKE 'asm-%' 
                        OR WebID.WebID LIKE 'prospector%' 
                        OR WebID.WebID LIKE 'export%' 
                        OR WebID.WebID LIKE 'cobalt-%')) t1 
ORDER BY OEM, 
         StartDate

Open in new window

0
 

Author Comment

by:avoorheis
ID: 34971044
I'll try to look at it today and if not working I'll try to post some data.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now