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

case in a where clause

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
qbjgqbjg
Asked:
qbjgqbjg
  • 7
  • 7
2 Solutions
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
qbjgqbjgConsultantAuthor Commented:
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
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.

 
cyberkiwiCommented:
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
 
qbjgqbjgConsultantAuthor Commented:
If I use the sub query, what exactly goes into the subquery?
0
 
qbjgqbjgConsultantAuthor Commented:
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
 
cyberkiwiCommented:
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
 
qbjgqbjgConsultantAuthor Commented:
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
 
cyberkiwiCommented:
apologies, added an _ on the mobile, it should have been

cast(month(dateadd(m,3,pa30100.papd)) as varchar(2))
0
 
qbjgqbjgConsultantAuthor Commented:
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
 
expert_dharamCommented:
Its 'dateadd' and not 'date_add'.
0
 
qbjgqbjgConsultantAuthor Commented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
PA30100.PAPD >= DATEADD(m,datediff(m,0,getdate())-1,0)
AND
PA30100.PAPD < DATEDIFF(d, day(getdate())-1 , getdate())
0
 
qbjgqbjgConsultantAuthor Commented:
Thanks for all of your help and patience.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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