Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculate Margin - Group 1

Status
Not open for further replies.

wexas45

MIS
Mar 11, 2010
9
GB
Hi, hope every one is doing well. I am new to sql queries.
I need to calculate 'MARGIN', which is 'GrossProfit'/[price]*100 and then display the reuslt in the query. Can someone please help me !

SELECT
[Booking Product],
YEAR([Booking Conf Date]) AS 'BookingYear',
COUNT(DISTINCT [Booking Ref]) AS TotalBookings,
CONVERT (DECIMAL(17,2),(SUM(GBPCost))) AS Cost,
CONVERT (DECIMAL(17,2),(SUM(price))) AS Price,
CONVERT (DECIMAL(17,2),(SUM([Price]-[GBPCost]))) AS 'GrossProfit',
([Booked Adults]+[Booked Children]+[Booked Infants])AS 'PAX',




FROM
[A]

WHERE
BookingStatus = 'CONFIRMED'

GROUP BY
[GBPCost],[Price],[Booking Product],[Booking Conf Date],[Booked Adults],[Booked Children],[Booked Infants];

 
Code:
select *, [Gross Profit] / NULLIF(Price,0) * 100.0 as Margin from
(SELECT         [Booking Product],    
YEAR([Booking Conf Date]) AS 'BookingYear', 
       COUNT(DISTINCT [Booking Ref]) AS TotalBookings,     CONVERT (DECIMAL(17,2),(SUM(GBPCost))) AS Cost, 
    CONVERT (DECIMAL(17,2),(SUM(price))) AS Price,    CONVERT (DECIMAL(17,2),(SUM([Price]-[GBPCost]))) AS 'GrossProfit',   
 ([Booked Adults]+[Booked Children]+[Booked Infants])AS 'PAX'
            FROM             [A]
WHERE    BookingStatus = 'CONFIRMED'       
  GROUP BY     [GBPCost],[Price],[Booking Product],[Booking Conf Date],[Booked Adults],[Booked Children],[Booked Infants]) X;

PluralSight Learning Library
 
Hello Markos, thanks for your reply but i am getting this erros

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Gross Profit'.

Gross Profit is not the column from the table but it's a calculated one.

Appreciate your time

Regards
WEXAS
 
I understand that, that's why I used a derived table with your original select and added extra field in the outer select.

Did you use my suggestion as I wrote it or did you alter it in some way?

PluralSight Learning Library
 
Hi, Question in the code you have written X at the end. What is the purpose of that and one more question some of the results are showing value as null how can i display 0.00 ?

Thanks in advance
 
1. I used X as an alias for derived table (because I'm lazy I like to use X here). You can give it more meaningful alias - the syntax requires the alias to be used

2. Take a look at COALESCE and ISNULL functions in BOL.

PluralSight Learning Library
 
Hello

I would like to return the sum but I am returning individual records with "1"

Code:
select 
(count (distinct( DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear) ))
from dirstat_record_bk1yr
where
	dirstatcoverage=94
	and DirstatPolState = 37

group by 
	DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear
having sum(XdirstatWritPrem) > 0 and
	sum(XdirstatWritPrem) < 25000

this returns 9 rows containg the "1"

what I want is 1 record containing the 9 (the sum)

Thanks in advance
Dave

 
I haven't re-read the whole thread, so this is based on your last query
Code:
select count(*) as TotalCount from (select 
(count (distinct( DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear) ))
from dirstat_record_bk1yr
where
    dirstatcoverage=94
    and DirstatPolState = 37

group by 
    DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear
having sum(XdirstatWritPrem) > 0 and
    sum(XdirstatWritPrem) < 25000 ) Z

PluralSight Learning Library
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top