SQl Query Case When
SQl Query Case When
SQl Query Case When
SQl Query Case When
SQl Query Case When
SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When
SQl Query Case When SQl Query Case When
SQl Query Case When
Go Back  Xtreme Visual Basic Talk > > > SQl Query Case When


Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2013, 10:32 PM
cuber_killua's Avatar
cuber_killua cuber_killua is offline
Contributor
 
Join Date: Sep 2004
Location: Philippines
Posts: 409
Default SQl Query Case When


hi,

i have here a sample of code i created. and this is the situation:

Sum(Amount) where issuedate = issue date + 61 months if TRMDWN field value is 2 or 3

Sum(Amount) where issuedate = issuedate + 62 months if TRMDWN field value is 4

Sum(Amount) where issuedate = issue date + 64 months if TRMDWN field value is 6

and include only in sum only those issuedate +6X Months is less than the current date..

i know i can use case when in sql query but im a little bit confuse how can i use it in that situation.. i use MySQL query vb.net..

Code:
str = ""
str = str & "Select sum(amort) as totalamort from parkmstr where trmdwm >=2 and "
str = str & " case trmdwm when trmdwm = '2 or 3' then date_add(isdate, INTERVAL 61 MONTH) <='" & newdate & "',"
str = str & "   case trmdwm when '4' then date_add(isdate, INTERVAL 62 MONTH) <='" & newdate & "',"
str = str & "   case trmdwm when '6' then date_add(isdate, INTERVAL 64 MONTH) <='" & newdate & "', )"
can anyone please help me with this problem.. thanks ainadvance and godbless..
Reply With Quote
  #2  
Old 07-29-2013, 03:24 AM
DrPunk's Avatar
DrPunkSQl Query Case When DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Looking at what you're after, then you're looking at doing a CASE inside a SUM. I can't confirm that the syntax here is perfect, I don't have MySQL to run anything on, but it would go along the lines of...

To find...
Code:
Sum(Amount) where issuedate = issue date + 61 months if TRMDWN field value is 2 or 3
... then the SQL is ...
Code:
SELECT SUM(CASE WHEN (trmdwm = 2 OR trmdwm = 3) AND date_add(isdate, INTERVAL 61 MONTH) >= CURDATE() THEN Amount ELSE 0 END) AS FirstSum
FROM YourDataBaseTable
The idea being that when the conditions are met it adds Amount to the Sum, otherwise it adds 0 to the Sum.

Note there's nothing between CASE and WHEN because that's not how you are using the CASE here. Also the date stuff is part of the WHEN's evaluation so should not come after the THEN..

It shouldn't be too difficult to add the other CASEs to that statement once you know that one works.
__________________
There are no computers in heaven!

Last edited by DrPunk; 07-29-2013 at 09:26 AM.
Reply With Quote
  #3  
Old 07-31-2013, 12:50 AM
cuber_killua's Avatar
cuber_killua cuber_killua is offline
Contributor
 
Join Date: Sep 2004
Location: Philippines
Posts: 409
Default

Hi,

Thanks for the help and sorry for the late reply..

i use your code here it is:

Code:
str = ""
str = str & "SELECT SUM(CASE WHEN (trmdwm = 2 OR trmdwm = 3) AND date_add(isdate, INTERVAL 61 MONTH) >= CURDATE() THEN amort "
str = str & " WHEN trmdwm = '4' AND date_add(isdate, INTERVAL 62 MONTH) >= CURDATE() THEN amort "
str = str & " WHEN trmdwm = '6' AND date_add(isdate, INTERVAL 64 MONTH) >= CURDATE() THEN amort END) AS FirstSum "
str = str & "FROM parkmstr Where balnce > 0 and year(isdate)='" & iYear & "'"
as you see, i add 2 parameters in where clause because i only want to get the total of those accounts who has an existing balance and only those with an issue year that i supply.. but it returns null.. but when i remove the year parameter in where clause it gives me the result i want but sum for all the accounts not filtered the way i want..

the same problem i encountering with my code:

Code:
str = ""
str = str & "Select sum(amort) as totalamort from parkmstr where (case when trmdwm = '2' or trmdwm='3' "
str = str & "then date_add(isdate, INTERVAL 61 MONTH) "
str = str & " when trmdwm = '4' then date_add(isdate, INTERVAL 62 MONTH) "
str = str & " when trmdwm = '6' then date_add(isdate, INTERVAL 64 MONTH) end) <='" & Format(newdate, "yyyy-MM-dd") & "' and balnce > 0 and year(isdate)='" & iYear & "'"
the result for these 2 codes is the same. when i remove year parameter it gives me a result but if i add the year it returns null.
do you know why it happens sir?..
Reply With Quote
  #4  
Old 07-31-2013, 02:29 AM
DrPunk's Avatar
DrPunkSQl Query Case When DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

The query you've made doesn't look right to me.

Each field you are trying to return is its own Sum with a Case.

Like...
Code:
SELECT SUM(CASE WHEN (trmdwm = 2 OR trmdwm = 3) AND date_add(isdate, INTERVAL 61 MONTH) <= CURDATE() THEN Amount ELSE 0 END) AS FirstSum,
SUM(WHEN trmdwm = 4 AND date_add(isdate, INTERVAL 62 MONTH) <= CURDATE() THEN Amount ELSE 0 END) AS SecondSum,
SUM(WHEN trmdwm = 6 AND date_add(isdate, INTERVAL 64 MONTH) <= CURDATE() THEN Amount ELSE 0 END) AS ThirdSum
FROM YourDataBaseTable
WHERE balnce > 0 and year(isdate)=1
I imagine Year returns an integer and therefore shouldn't be enclosed in inverted commas.
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 07-31-2013, 07:14 PM
cuber_killua's Avatar
cuber_killua cuber_killua is offline
Contributor
 
Join Date: Sep 2004
Location: Philippines
Posts: 409
Default

Thank you very much for the help sir..

i got it now.. here is the code:

Code:
str = ""
str = str & "SELECT SUM(CASE WHEN (trmdwm = 2 OR trmdwm = 3) AND date_add(isdate, INTERVAL 61 MONTH) >= '" & Format(newdate, "yyyy-MM-dd") & "' THEN amort"
str = str & " WHEN trmdwm = '4' AND date_add(isdate, INTERVAL 62 MONTH) >= '" & Format(newdate, "yyyy-MM-dd") & "' THEN amort"
str = str & " WHEN trmdwm = '6' AND date_add(isdate, INTERVAL 64 MONTH) >= '" & Format(newdate, "yyyy-MM-dd") & "' THEN amort END) AS FirstSum "
str = str & "FROM parkmstr Where balnce > 0 and year(isdate) = '" & CStr(iYear) & "'"
the reason why it gives a null value is that the query executes inside the for loop.. loop from current year to 2005 and current year to 2016 so there are years the the sum value is 0 like for example if year is 2005..

anyway, i will very much appreciate if you will give me some advise regarding that code or if there is a better way or a good practice in doing such query..

thanks so much and god bless..
Reply With Quote
  #6  
Old 08-01-2013, 02:53 AM
DrPunk's Avatar
DrPunkSQl Query Case When DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

It's all got a bit weird now. I thought your first post was asking for 3 sums returning 3 different values in one query. But the query you have written in the post above looks like it could be done without using a CASE. Everything inside the CASE could be done in the WHERE. You've just got to get the brackets and ANDs and ORs right...
Code:
SELECT SUM(amort)
FROM parkmstr
WHERE (((trmdwm = 2 OR trmdwm = 3) AND date_add(isdate, INTERVAL 61 MONTH) >= TheDate) OR 
(trmdwm = '4' AND date_add(isdate, INTERVAL 62 MONTH) >= TheDate) OR 
(trmdwm = '6' AND date_add(isdate, INTERVAL 64 MONTH) >= TheDate)) AND
balnce > 0 and year(isdate) = TheYear
That should return the same Sum.
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQl Query Case When
SQl Query Case When
SQl Query Case When SQl Query Case When
SQl Query Case When
SQl Query Case When
SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When SQl Query Case When
SQl Query Case When
SQl Query Case When
 
SQl Query Case When
SQl Query Case When
 
-->