Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
Course Of The Month
11 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sum - Case when
Posted on 2008-10-22
The results shown in the Excel file : text.xls
Have a table tbl_testingfreight :
The result is the first tab of the Excel sheet (Actual Data)
After running the below script I get the results showed in the second tab (Result based on the query)
I am trying to do a SQL case when , but not able to see the result as needed
The final result needs tto be as shown in the tab (Results required)
The conditions are:
when the VndrNum = 359958 and OpCo = 177 for that SUVCVndrNum, ItemNum -------- need to sum up the [Freight Cost / Case ($)] jus t as highlighted.
How can i apply this condion in the below query:
Have a table tbl_testingfreight :
The result is the first tab of the Excel sheet (Actual Data)
After running the below script I get the results showed in the second tab (Result based on the query)
I am trying to do a SQL case when , but not able to see the result as needed
The final result needs tto be as shown in the tab (Results required)
The conditions are:
when the VndrNum = 359958 and OpCo = 177 for that SUVCVndrNum, ItemNum -------- need to sum up the [Freight Cost / Case ($)] jus t as highlighted.
How can i apply this condion in the below query:
select TOP 100 PERCENT
d.OpCo
,d.SUVCVndrNum
,d.VndrNum
,ItemNum
,sum(Cases) [Case Volume]
,sum(FOB) [COGS ($)]
,sum(isnull(FrtPerCase, 0)*Cases) [Freight Cost ($)]
,sum(isnull(LEIPerCase, 0)*Cases) [LEI ($)]
,max(isnull(FrtPerCase, 0)) [Freight Cost / Case ($)]
,max(isnull(LEIPerCase, 0)) [LEI ($) / Case]
from
tbl_testingfreight d
left outer join test.dbo.[OpCo Locations] o
on d.OpCo = o.OpCo
cross join (select distinct sourcinginitiative
from [freightPO-OPCO]) si
where
SourcedCases > 0
group by
d.OpCo
,d.SUVCVndrNum
,d.VndrNum
,ItemNum
order by
d.OpCo
,ItemNum
[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
-
5
3
3
11 Comments
Active 7 days ago
Hi,
I'm having difficulty following your logic.
But think that my attached query might be close.
Plese apply the table alias's consistently. They are applied to only half the columns. It helps with code review and maintenance if they are all applied.
Cheers
David
I'm having difficulty following your logic.
But think that my attached query might be close.
Plese apply the table alias's consistently. They are applied to only half the columns. It helps with code review and maintenance if they are all applied.
Cheers
David
select TOP 100 PERCENT
d.OpCo
,d.SUVCVndrNum
,d.VndrNum
,ItemNum
,sum(Cases) [Case Volume]
,sum(FOB) [COGS ($)]
,sum(isnull(FrtPerCase, 0)*Cases) [Freight Cost ($)]
,sum(isnull(LEIPerCase, 0)*Cases) [LEI ($)]
,max(isnull(FrtPerCase, 0)) [Freight Cost / Case ($)]
,max(isnull(LEIPerCase, 0)) [LEI ($) / Case]
, case
when VndrNum = 359958 and OpCo = 177 then
(
select sum(isnull(FrtPerCase, 0)*Cases)
from
tbl_testingfreight di
left outer join test.dbo.[OpCo Locations] o
on d.OpCo = o.OpCo
cross join (select distinct sourcinginitiative
from [freightPO-OPCO]) si
where
SourcedCases > 0
and di.SUVCVndrNum = d.SUVCVndrNum
and di.ItemNum = d.ItemNum
)
else null
end
from
tbl_testingfreight d
left outer join test.dbo.[OpCo Locations] o
on d.OpCo = o.OpCo
cross join (select distinct sourcinginitiative
from [freightPO-OPCO]) si
where
SourcedCases > 0
group by
d.OpCo
,d.SUVCVndrNum
,d.VndrNum
,ItemNum
order by
d.OpCo
,ItemNum
Active today
Based on your XLS, it seems like you would be better off making a view out of your original query and then do something like this:
SELECT *,
CASE VndrNum WHEN '359958' THEN ISNULL((SELECT [Freight Cost / Case ($)] FROM yourView v2 WHERE v2.VndrNum = v1.SUVCVndrNum AND v2.ItemNum = v1.ItemNum AND v2.OpCo = 177), 0.0)
ELSE 0.0 END AS [Addtl Freight Cost / Case ($)]
FROM yourView v1
WHERE OpCo <> 177
Then add up [Addtl Freight Cost / Case ($)] AND [Freight Cost / Case ($)] in spreadsheet or in query. Just simplified here, but if you type out columns in select you can just place this case statement after the original freight/case column as a + and add right then and there.
SELECT *,
CASE VndrNum WHEN '359958' THEN ISNULL((SELECT [Freight Cost / Case ($)] FROM yourView v2 WHERE v2.VndrNum = v1.SUVCVndrNum AND v2.ItemNum = v1.ItemNum AND v2.OpCo = 177), 0.0)
ELSE 0.0 END AS [Addtl Freight Cost / Case ($)]
FROM yourView v1
WHERE OpCo <> 177
Then add up [Addtl Freight Cost / Case ($)] AND [Freight Cost / Case ($)] in spreadsheet or in query. Just simplified here, but if you type out columns in select you can just place this case statement after the original freight/case column as a + and add right then and there.
i have the code working but i see duplicates , something were i did the grouping, please help...
I took the d.SUVCVndrNum d.VndrNum off
I took the d.SUVCVndrNum d.VndrNum off
select TOP 100 PERCENT
d.OpCo
,max(o.OpCoName) OpCoName
,ItemNum
,max(Description) Description
,sum(Cases) [Case Volume]
,sum(Weight) [Weight Volume]
,sum(FOB) [COGS ($)]
,sum(isnull(FrtPerCase, 0)*Cases) [Freight Cost ($)]
,sum(isnull(LEIPerCase, 0)*Cases) [LEI ($)]
, (max(isnull(FrtPerCase, 0)) ) + (case when VndrNum = '359958' then
isnull((select max(FrtPerCase)
from SourcingFreightDetail_VndrNum di
where
di.SourcedCases > 0
and di.SUVCVndrNum = d.SUVCVndrNum
and di.ItemNum = d.ItemNum
and di.opco='177'
group by di.SUVCVndrNum,di.ItemNum ,di.opco),0)
else 0
end )[Freight Cost / Case ($)]
,max(isnull(LEIPerCase, 0)) [LEI ($) / Case]
,max(si.SourcingInitiative) SourcingInitiative
from
SourcingFreightDetail_VndrNum d
left outer join test.dbo.[OpCo Locations] o
on d.OpCo = o.OpCo
cross join (select distinct sourcinginitiative
from [freightPO-OPCO]) si
where
d.SourcedCases > 0 and
d.opco <>'177'
group by
d.OpCo
,ItemNum
,d.SUVCVndrNum
,d.VndrNum
order by
d.OpCo
,ItemNum
Active 7 days ago
Hi,
You have removed d.SUVCVndrNum and d.VndrNum from the column list, but not from the group by. Suggest that you take them out of the group by and that should remove your duplicates.
Cheers
David
You have removed d.SUVCVndrNum and d.VndrNum from the column list, but not from the group by. Suggest that you take them out of the group by and that should remove your duplicates.
Cheers
David
Active 7 days ago
Hi,
Okay, then you are going to see apparent duplicates. Since they are in the group by, removing them from the field list like you have shouldn't alter the results as such.
That is, I don't understand how removing them suddenly makes the query work.
Cheers
David
Okay, then you are going to see apparent duplicates. Since they are in the group by, removing them from the field list like you have shouldn't alter the results as such.
That is, I don't understand how removing them suddenly makes the query work.
Cheers
David
the error i get is:
Column 'd.SUVCVndrNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'd.VndrNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'd.SUVCVndrNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'd.VndrNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
how can i show one record, without having to add those 2 fields with total values,
I tried all ways either showing error or duplicates
I tried all ways either showing error or duplicates
Active today
Have you completely tried my suggestion -- http:22782044?
It appears you are using pieces of it, but an important part of what I suggested was to make your original query into a view. This is important as the subquery must exactly match the original query and you need to do the calculations / joins based on the already grouped and aliased data like it appears in your spreadsheet.
If you are unwilling/able to use that approach, please advise and will go on from there but will probably need to be slightly adjusted as my solution was based on that fact.
Regards,
Kevin
It appears you are using pieces of it, but an important part of what I suggested was to make your original query into a view. This is important as the subquery must exactly match the original query and you need to do the calculations / joins based on the already grouped and aliased data like it appears in your spreadsheet.
If you are unwilling/able to use that approach, please advise and will go on from there but will probably need to be slightly adjusted as my solution was based on that fact.
Regards,
Kevin
mwvisa1: Actually I tried your suggention ,
I took off the d.SUVCVndrNum and d.VndrNum and thats when i get the error.
So i created the given script into a view and then sumed up the values in another view...
I guess that could be the best approach
I took off the d.SUVCVndrNum and d.VndrNum and thats when i get the error.
So i created the given script into a view and then sumed up the values in another view...
I guess that could be the best approach
Featured Post
Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 1 hour left to enroll
631 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.