Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Counting multiple fields that have different dates

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
avoorheis
Asked:
avoorheis
  • 2
1 Solution
 
avoorheisAuthor Commented:
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
 
David ToddSenior DBACommented:
Hi,

How about some sample data ...

Regards
  David
0
 
SharathData EngineerCommented:
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
 
avoorheisAuthor Commented:
I'll try to look at it today and if not working I'll try to post some data.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now