I have to set a condition in where clause on the basis of financial year

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

please help how can i set a condition on the basis of financial year

Select top 25 O.OCSEmployeeCode as UserID, OCSBillNo as BillNo, convert(varchar(10),OCSBillDate,103) as BillDate, Year(OCSBillDate) as mYear, DATEPART(mm,OCSBillDate) as mMonth
from  HMS_OCSBill O where OCSCancelStatus Is Null And OCSDiscontinueFlag Is Null and O.OCSEmployeeCode='GAA6725' 
AND (
CASE WHEN DATEPART(mm,getdate())<4 THEN 
((Year(OCSBillDate)=year(getdate())-1 AND DATEPART(mm,OCSBillDate)>=4)) OR ((Year(OCSBillDate)=year(getdate()) AND DATEPART(mm,OCSBillDate)<=3))
ELSE
((Year(OCSBillDate)=year(getdate()) AND DATEPART(mm,OCSBillDate)>=4)) END)
 ORDER BY Year(OCSBillDate) DESC;

Its giving me error message that incorect syntex near "=".

What I have tried:

Please help how can i resolve it..

Posted 13-Jul-18 23:47pm

Member 10807962

Updated 14-Jul-18 5:43am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-07-14T00:19:00

Solution 1

Try this:

SQL

SELECT TOP 25 O.OCSEmployeeCode AS UserID, 
       OCSBillNo AS BillNo, 
       CONVERT(VARCHAR(10),OCSBillDate,103) AS BillDate, 
       YEAR(OCSBillDate) AS mYear, 
       DATEPART(mm,OCSBillDate) AS mMonth
FROM  HMS_OCSBill O 
WHERE OCSCancelStatus IS NULL 
  AND OCSDiscontinueFlag IS NULL 
  AND O.OCSEmployeeCode='GAA6725' 
  AND 1 = CASE WHEN DATEPART(mm,getdate())<4 
                AND ((YEAR(OCSBillDate)=YEAR(GETDATE())-1 AND DATEPART(mm,OCSBillDate)>=4)) 
                OR  ((YEAR(OCSBillDate)=YEAR(GETDATE()) AND DATEPART(mm,OCSBillDate)<=3)) 
                OR  ((YEAR(OCSBillDate)=YEAR(GETDATE()) AND DATEPART(mm,OCSBillDate)>=4)) 
               THEN 1
               ELSE 0
          END
ORDER BY YEAR(OCSBillDate) DESC;

Posted 14-Jul-18 0:19am

OriginalGriff

Comments

Member 10807962 14-Jul-18 8:28am

Hi Friend,

It not working, it's giving previous financial years details too.

basically i have to execute two conditions based on the current month-

when system month is less then 4 (April) then first block will execute else next block.

Hide Copy Code
CASE WHEN DATEPART(mm,getdate())<4 THEN
((Year(OCSBillDate)=year(getdate())-1 AND DATEPART(mm,OCSBillDate)>=4)) OR ((Year(OCSBillDate)=year(getdate()) AND DATEPART(mm,OCSBillDate)<=3))
ELSE
((Year(OCSBillDate)=year(getdate()) AND DATEPART(mm,OCSBillDate)>=4)) END)
ORDER BY Year(OCSBillDate) DESC;

OriginalGriff 14-Jul-18 8:39am

Look at what I did, and form your WHERE condition the same way. You can't do it the way you want to, so you need to get your AND and OR right.

Just posting the same - faulty - code doesn't help anyone.
And I can;t test conditions - I don't have access to your data...

Member 10807962 14-Jul-18 8:29am

Select top 100 O.OCSEmployeeCode as UserID, OCSBillNo as BillNo, convert(varchar(10),OCSBillDate,103) as BillDate, Year(OCSBillDate) as mYear, DATEPART(mm,OCSBillDate) as mMonth, FinYr=
case WHEN (DATEPART(mm,OCSBillDate) BETWEEN 4 AND 12)
THEN CONVERT(VARCHAR(4),Year(OCSBillDate))+'-'+(CONVERT(VARCHAR(4),Year(OCSBillDate)+1))
WHEN (DATEPART(mm,OCSBillDate) BETWEEN 1 AND 3)
THEN (CONVERT(VARCHAR(4),Year(OCSBillDate)-1))+'-'+CONVERT(VARCHAR(4),Year(OCSBillDate)) END
from HMS_OCSBill O where OCSCancelStatus Is Null And OCSDiscontinueFlag Is Null
and O.OCSEmployeeCode='GAA6725' and FinYr=
(CASE WHEN DATEPART(mm,getdate())<4
THEN CONVERT(VARCHAR(4),Year(getdate())-1) + '-' + CONVERT(VARCHAR(4),Year(getdate()))
ELSE CONVERT(VARCHAR(4),Year(getdate())) + '-' + CONVERT(VARCHAR(4),Year(getdate())+1)
END)
ORDER BY Year(OCSBillDate) DESC;

Patrice T · Answer 2 · 2018-07-14T05:43:00

Even without your problem, your query is badly constructed and will be slooow on a production server storing a few years of history.
You are using getdate() in the when clause, the problem is that the function is volatile, its value changes any time you call it.
This mean that the server can't optimize the filter and the workload depend on the number of records in the table. The fact that you are butchering date fields is another reason to not optimize, because it is beyond the understanding of the server.

The other way to do it is to compute a StartDate and EndDate of records you want before the select.
And then :

SQL

when OCSBillDate between @StartDate and @EndDate

This way, the SQL server knows how to take advantage of an index, and the workload will depend on the number of records matching the constraint.