Solved

Counting multiple fields that have different dates

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

18 Experts available now in Live!

Get 1:1 Help Now