Solved

Counting multiple fields that have different dates

Posted on 2011-02-23
4
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

759 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