Solved

case in a where clause

Posted on 2010-11-23
15
253 Views
Last Modified: 2012-06-27
I need to select records where fiscal year and period = variables. The problem is, I amcalculating the fiscal year and the period from a date using a case statement. How do I do this in the where?
Obviously I can't do it using the names from the case, so what do I do instead?
DECLARE @Today DATETIME
SET @Today = GETDATE()
DECLARE @fiscalYr  NUMERIC
SET @fiscalYr = 
  case
    when MONTH(@Today) >= 11 then Year(@Today) + 1 
    else  Year(@Today)
  end
DECLARE @period  NUMERIC
SET @period = 
  case
    when MONTH(@Today) >= 11 then MONTH(@Today) - 10
    else  Month(@Today)+ 2
end  
DECLARE @FYR varchar
SET @FYR = cast(@fiscalYr as varchar(4))

DECLARE @PER varchar
SET @PER = cast(@period as varchar(2))

insert into dbo.EDCOGMATUS
select
DOCSRC,  
REFNO,   
DOCDESC, 
DOCGRP,  
FSCYR,   
DOCDATE, 
DOCPER,  
AUTOREV, 
SUMM,    
ACCTTYP, 
ACCTNO,  
DETDESC,
SUM(TRAN_AMOUNT) AS AMOUNT,
DBCR,     
SUMMACCT, 
JCGRTYP,  
PANO,     
LFCHAR   
FROM
(
 SELECT 
 'je_import ' as DOCSRC,
 Substring(PA30101.PAPROJNUMBER + space(9), 1, 9)  as REFNO,
 substring('Cogsdale Labor Usage' + 
           space(40), 1,40) as DOCDESC,
 'COGLABUSED' as DOCGRP,
  /* --------fiscal year case begin---------------- */
 case
 when month(PA30100.PAPD) >= '10'
 
 then 
    CAST(YEAR(PA30100.PAPD) + 1  AS varCHAR(4))

 
  else  
   CAST(YEAR(PA30100.PAPD)  AS varCHAR(4))

 end  as FSCYR,
/* ------------------case end---------------- */
 
 Right('0' + CAST(MONTH(@Today) AS varCHAR(2)),2) + 
       Right('0' + CAST(DAY(@Today) AS varCHAR(2)),2) + 
                CAST(YEAR(@Today) AS varCHAR(4)) as DOCDATE,
 /* --------fiscal period case begin---------------- */
 case
 when month(PA30100.PAPD) >= '10'
 
 then 
   CAST(month(PA30100.PAPD) - 9  AS varCHAR(2))
 
  else  
   CAST(month(PA30100.PAPD) + 3  AS varCHAR(2))
 end  as DOCPER,
/* ------------------case end---------------- */               
 'N' as AUTOREV,
 'Y' as SUMM,
 'B' as ACCTTYP,
 '457-381-33200' as ACCTNO,
 substring(GL00100.ACTALIAS + space(30), 1, 30) as DETDESC,
 PA30103.DEBITAMT - PA30103.CRDTAMNT AS TRAN_AMOUNT,
 'C' as DBCR,
 ' ' as SUMMACCT,
 ' ' as JCGRTYP,
 space(32) as PANO,
 'Ž' as LFCHAR
 
 
 FROM  
  PA30101 PA30101
  LEFT OUTER JOIN PA30100 PA30100 
  ON PA30101.PATSNO=PA30100.PATSNO 
  AND PA30101.EMPLOYID=PA30100.EMPLOYID 
  LEFT JOIN PA01201 PA01201 
  ON PA30101.PAPROJNUMBER=PA01201.PAPROJNUMBER 
  LEFT OUTER JOIN PA30103 PA30103 
  ON PA30101.PATSNO=PA30103.PATSNO 
  INNER JOIN GL00100 GL00100 
  ON PA30103.DSTINDX=GL00100.ACTINDX

 
 WHERE  GL00100.ACTALIAS LIKE '33%' 
  AND FISCYR=@FYR
  AND DOCPER = @PER
) x
GROUP BY
DOCSRC,  
REFNO,   
DOCDESC, 
DOCGRP,  
FSCYR,   
DOCDATE, 
DOCPER,  
AUTOREV, 
SUMM,    
ACCTTYP, 
ACCTNO,  
DETDESC, 
DBCR,     
SUMMACCT, 
JCGRTYP,  
PANO,     
LFCHAR

Open in new window

0
Comment
Question by:qbjgqbjg
  • 7
  • 7
15 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34200264
For example, to get Q1 2010 (fiscal)

where dateadd(m,3,pa30100.papd) between '20100101' and '20100331'

that is easily understood, but for performance reasons,

where pa30100.papd between dateadd(m,-3,'20100101') and dateadd(m,-3,'20100331')

All you need to do to turn calendar to fiscal for Oct is add 3 months.
For example, your month case could have been

cast(month(date_add(m,3,pa30100.papd)) as varchar(2))
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200279
alternatively, you can subquery, but it won't perform as well (won't use date index)

select * from ( {original query} ) sq
where fscyr=2010 and docper=3
0
 

Author Comment

by:qbjgqbjg
ID: 34201048
The problem is the record does not contain fiscyr or period. I am calculating fiscyr and period from the date pa30100.papd. BUT I need to select records that are = to the calculated variables for the fiscal year and period.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34201116
Either of the 2 above will work
The first one uses the base date (papd) directly, for performance reasons, turn your fiscyr and period into the corresponding dates to filter on papd
The 2nd one puts your entire query into a SUBquery, which makes the fields fscyr and docper available to the OUTER query.
0
 

Author Comment

by:qbjgqbjg
ID: 34201409
If I use the sub query, what exactly goes into the subquery?
0
 

Author Comment

by:qbjgqbjg
ID: 34201512
Can I use dateadd with current date (@today) to get a start and end date for the records I want. This will always run a few days after the end of the month I want to select.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34201534
Just noticed that you already have a subquery!
Move the 2 lines
AND FISCYR=@FYR
AND DOCPER=@PER
outside the )X, e.g.

WHERE GL00100.ACTALIAS LIKE '33%'
) x
WHERE FISCYR=@FYR
  AND DOCPER=@PER
GROUP BY
....
0
 

Author Comment

by:qbjgqbjg
ID: 34201599
cast(month(date_add(m,3,pa30100.papd)) as varchar(2))
I tried this and it says 'date_add' is not a recognized built-in function name.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34201632
apologies, added an _ on the mobile, it should have been

cast(month(dateadd(m,3,pa30100.papd)) as varchar(2))
0
 

Author Comment

by:qbjgqbjg
ID: 34201649
It did not like date_add, so I changed it to dateadd. so that is fine. But
 WHERE GL00100.ACTALIAS LIKE '33%'
) x
WHERE FISCYR=@FYR
  AND DOCPER=@PER
GROUP BY
is not working. it says
Server: Msg 207, Level 16, State 1, Line 89
Invalid column name 'FISCYR'.
0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 34201652
Its 'dateadd' and not 'date_add'.
0
 

Author Comment

by:qbjgqbjg
ID: 34201688
FISCYR was just spelled wrong, so its ok. I would still like to know if I could use dateadd to build a start end date for the previous month. Starting with the current date that will be a few days into the month following the month I want to select.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34201695
I copied and pasted... didn't realise it was wrong

 WHERE GL00100.ACTALIAS LIKE '33%'
) x
WHERE FSCYR=@FYR
  AND DOCPER=@PER
GROUP BY
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 500 total points
ID: 34201723
PA30100.PAPD >= DATEADD(m,datediff(m,0,getdate())-1,0)
AND
PA30100.PAPD < DATEDIFF(d, day(getdate())-1 , getdate())
0
 

Author Closing Comment

by:qbjgqbjg
ID: 34201769
Thanks for all of your help and patience.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

772 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