x

Update Revenue Column

For the below query revenue column must be populated as per the below. So how can this be done?

If the location associated to both instances(Types are Mapics and Alvey) of project is the same, then revenue from the Mapics system should be taken as the project valuation and if the location is different then the revenue from the Alvey system should be taken as the project valuation

 SELECT ProjectName,
         Project_Location,
         [Project Number],
         EnteredDate,
         GoLiveDate,
         EST_CLOSE_DATE,
         ProjectStatus,
         [YEAR Entered],
         Revenue      
 FROM 
       ContractsMapicsMapped 
 GROUP BY ProjectName,
         Project_Location,
         [Project Number],
         EnteredDate,
         GoLiveDate,
         EST_CLOSE_DATE,
         ProjectStatus,
         [YEAR Entered],
         Revenue
more ▼

asked Jul 17 at 07:28 AM in Default

avatar image

prince1988
11 2

Can you post some example data?

Jul 18 at 08:59 AM Kev Riley ♦♦

From the below for project number A1228 the proj locations are same. So ideally if the location is same then the final output must be the row with Type Mapics and the revenue must be 0 as the revenue for Mapics Type is below output is 0. If the proj location will be different then the output must be Alvey Type and its revenue

 ProjectName    Project_Location    Project Number    EnteredDate    GoLiveDate    EST_CLOSE_DATE    ProjectStatus    YEAR Entered    Revenue    Type
 Frito-Lay Inc    Kathleen, GA    A1228    2014-05-27 23:01:59.673    2007-08-17 00:00:00.000    NULL    Closed    1905-07-08 00:00:00.000    0    MAPICS
 Frito-Lay Inc    Kathleen, GA    A1228    2014-05-27 23:01:59.673    2007-08-17 00:00:00.000    NULL    Closed    1905-07-08 00:00:00.000    583846    ALVEY
Jul 18 at 09:57 AM prince1988
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It looks like there are always 2 rows for every project number, i.e. one row for each of the 2 available types. It also appears from the question definition that there is a need to pick only one row from each pair based on whether the location is the same for both rows to consider or not. I hope I understand the question correctly. By the way, it looks like the data in the "Year Entered" column is incorrect. Perhaps the intent was to enter the January 1 2016 as the value but the script did not include the single quotes around the 2016-01-01 date value. In this case what happens is the following: 2016-01-01 is treated as numerical expression which is 2016 minus 1 minus 1 = 2014. Integer value 2014, when attempted to be converted to datetime, results in the datetime value equal to 2014 days from the zero date of January 1 1900 which is July 8 1905. This might explain why the "Year Entered" value in both rows in the sample data reads 1905-07-08.

Here is the script which should work. There are certainly other ways to achieve the same result, but please try this one, which I hope works as expected assuming that I understood the question correctly:

 select
     src.ProjectName, Project_Location, [Project Number], EnteredDate, 
     GoLiveDate, EST_CLOSE_DATE, ProjectStatus, [YEAR Entered], Revenue
     from ContractsMapicsMapped src inner join (
         select
             [Project Number], 
             case when min(Project_Location) = max(Project_Location) then 'Mapics' else 'Alvey' end [Type]
             from ContractsMapicsMapped
             group by [Project Number]
     ) grouped
         on src.[Project Number] = grouped.[Project Number] and src.[Type] = grouped.[Type];

Hope this helps.

Oleg

more ▼

answered Jul 18 at 06:23 PM

avatar image

Oleg
18.9k 3 7 28

(comments are locked)
10|1200 characters needed characters left

you could use a case statement on alvey or mapics. Also see below. could use this. USE AdventureWorks2012;
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='performance'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @SearchWord);

more ▼

answered Jul 18 at 02:14 PM

avatar image

Waqar_lionheart
101 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188

asked: Jul 17 at 07:28 AM

Seen: 43 times

Last Updated: Jul 18 at 06:23 PM

Copyright 2017 Redgate Software. Privacy Policy