?
Solved

case in a where clause

Posted on 2010-11-23
15
Medium Priority
?
261 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
[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
  • 7
  • 7
15 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

752 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