Solved

case in a where clause

Posted on 2010-11-23
15
247 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now