• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

SQL Query DatePart Case question

Hello all,

I have the following tables that has the following:

Table:  Customer
Fields:  CustID,   BillingFrequencyID

Table:   BillingFrequency
BillingFrequencyID,   BillingFrequencyName
1                                   Monthly
2                                   Quarterly
3                                   Semi-Annually
4                                   Annually

What I need to do is I am going to get passed a parameter @PeriodEndDate.    I need to filter the list of Customers based on does that Date fall into the Billing Frequency.   Such as lets say I get passed:

12/31/14  that would mean I would want all customers where BillingFrequency IN (1, 2, 4) it falls on the last day of the month, last day of quarter and last day of the year.

If it is 1/23/14 - then no data would return

If 1/31/15 then where BillingFrequency = 1 as its just a Month End.

How can I use date parsing to get the data correct?

Thanks all
0
sbornstein2
Asked:
sbornstein2
  • 4
  • 3
1 Solution
 
LowfatspreadCommented:
like this ?
select *
from customer
where

billingfrequency in (
case when month(@periodenddate) in (1,3,5,7,8,10,12)
     then case when datepart(dd,@periodenddate)=31 then 1 end
     when month(@periodenddate) in (9,4,6,11) 
     then case when datepart(dd,@periodenddate)=30 then 1 end
     else case when year(@periodenddate)%4 = 0 
               then case when datepart(dd,@periodenddate)=29  
                         then 1 end
               else case when datepart(dd,@periodenddate)=28
                         then 1 end
               end
     end
,case when (month(@periodenddate) in (3,12) and datepart(dd,@periodenddate)=31)
        or (month(@periodenddate) in (6,9) and datepart(dd,@periodenddate)=30)
      then 2
      end
,case when month(@periodenddate)=6 and datepart(dd,@periodenddate)=30 then 3 end
,case when month(@periodenddate)=12 and datepart(dd,@periodenddate)=31 then 4 end
)
order by billingfrequency,custid

Open in new window

0
 
sbornstein2Author Commented:
There must be some easier ways to handle the end of the month days correct?  It looks like this:

DATEPART(DAY,DATEADD(DAY,1,'10/31/2014')) = 1  for example will always check if it falls on the end of a month.
0
 
sbornstein2Author Commented:
actually the IN might not be correct.   I don't want to pull for example a Customer with a Quarter End if the date is a month end date such as '2/28/15' is passed I would not want this customer even though its a month end.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
LowfatspreadCommented:
ok like this then
select *
from customer
where

billingfrequency in (
case when DATEPART(DAY,DATEADD(DAY,1,@periodenddate)) = 1
     then 1
     end
,case when DATEPART(DAY,DATEADD(DAY,1,@periodenddate)) = 1
      and month(@periodenddate) in (3,6,9,12)
      then 2
      end
,case when month(@periodenddate)=6 and datepart(dd,@periodenddate)=30 then 3 end
,case when month(@periodenddate)=12 and datepart(dd,@periodenddate)=31 then 4 end
)
order by billingfrequency,custid

Open in new window

0
 
sbornstein2Author Commented:
maybe this?

case when DATEPART(DAY,DATEADD(DAY,1,'10/31/2014')) = 1 then 1
     end,
case when DATEPART(DAY,DATEADD(DAY,1,'10/31/2014')) = 1 and
          (MONTH('10/31/2014') = 3 OR MONTH('10/31/2014') = 6
           OR MONTH('10/31/2014') = 9 OR MONTH('10/31/2014') = 12)
      then 2
      end,
case when DATEPART(DAY,DATEADD(DAY,1,'10/31/2014')) = 1 and
          (MONTH('10/31/2014') = 6 OR MONTH('10/31/2014') = 12)
      then 3
      end,
case when DATEPART(DAY,DATEADD(DAY,1,'10/31/2014')) = 1 AND MONTH('10/31/2014') = 12
      then 4
      end
0
 
sbornstein2Author Commented:
awesome thanks
0
 
LowfatspreadCommented:
I don't want to pull for example a Customer with a Quarter End if the date is a month end date such as '2/28/15' is passed I would not want this customer even though its a month end.
28th fEB ISN'T  a quarter end anyway so why would a qe customer get selected?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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