Solved

Counting multiple fields that have different dates

Posted on 2011-02-23
4
246 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

828 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