?
Solved

SQL Where statment help

Posted on 2014-08-28
4
Medium Priority
?
249 Views
Last Modified: 2014-09-24
The last part of the WHERE needs to be different every month; the numbers behind "N##" correspond to a month.  They need to roll forward each month - the below script is setup for August, but for September, the N09 needs to change to N03; in October, the N10 would change to N04, and so on.   ' NS' doesn't change and is constant.

I've tried CASE, but can't get it to hit multiple fields.  One thought was to use a series of SQL jobs to update a temp table with the proper number series each month.  Another was to build some if/then into SSRS.

update purchase_class
set exclude_from_ranking = 'N', exclude_from_reclassification = 'N', date_last_modified = getdate(), last_maintained_by = 'ABIX_DBA'
where exclude_from_ranking = 'Y' and exclude_from_reclassification = 'Y'
and purchase_class_id in ('N11', 'N12', 'N01', 'N02', 'N09', 'N10', 'NS')
0
Comment
Question by:abarefoot
4 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40290877
>but for September, the N09 needs to change to N03; in October, the N10 would change to N04, and so on.
Explain how N09 becomes N03.  Not getting it.
0
 
LVL 1

Author Comment

by:abarefoot
ID: 40290901
This covers 6 months.  So September2014 would turn into March2015, October2014 would turn into April2015 and so on.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 40291156
Try code below.  Naturally you could substitute a variable for "GETDATE()" to make the reporting date more flexible.


and purchase_class_id in (
    SELECT 'N' + RIGHT('0' + CAST(ISNULL(NULLIF((MONTH(GETDATE()) + month_number) % 12, 0), 12) AS varchar(3)), 2)
    FROM ( SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
           SELECT 5 UNION ALL SELECT 6 ) AS month_numbers
    UNION ALL
    SELECT 'NS'
)


To test it for other months, you can adjust the month# in the query.  For example, to test September:

...(NULLIF((MONTH(GETDATE()) + 1 + month_number) % 12, 0), 12) AS ...
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 total points
ID: 40292680
Jim,
>but for September, the N09 needs to change to N03; in October, the N10 would change to N04, and so on.
Explain how N09 becomes N03.  Not getting it. <<
I found that rather confusing myself at first. He explains that the following was for August"
>>and purchase_class_id in ('N11', 'N12', 'N01', 'N02', 'N09', 'N10', 'NS')<<
which represents the six month numbers that would follow August, so the six month numbers that would follow September would be represented as  ('N10', 'N11', 'N12', 'N01', 'N02', 'N03', 'NS') having the effect of "replacing 09 with 03" At any rate, I think Scott's solution should do the trick. Here's another variation that should work -
with cte as
(select 'N' + right('00' + cast(datepart('mm',dateadd('mm',1, getdate())), as varchar(3),2) union all
 select 'N' + right('00' + cast(datepart('mm',dateadd('mm',2, getdate())), as varchar(3),2) union all
 select 'N' + right('00' + cast(datepart('mm',dateadd('mm',3, getdate())), as varchar(3),2) union all
 select 'N' + right('00' + cast(datepart('mm',dateadd('mm',4, getdate())), as varchar(3),2) union all
 select 'N' + right('00' + cast(datepart('mm',dateadd('mm',5, getdate())), as varchar(3),2) union all
 select 'N' + right('00' + cast(datepart('mm',dateadd('mm',6, getdate())), as varchar(3),2) union all
 select 'NS'
)
update ...
and purchase_class_id in
(select * from cte)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

839 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