Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
select
a.InvoiceID,
a.CustomerCode,
a.CustName,
a.dimension,
a.dimension2_,
a.dimension3_ ,
a.SALES_AMOUNT,
a.COST_AMOUNT,
a.GROSS_PROFIT,
case
when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
else GROSS_PROFIT/LineAmountMST * 100
END MARGIN_PER
from
(
select
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT
--case
-- when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
-- when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
-- when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
-- else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100
--END MARGIN_PER
FROM
salesdim
where
invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
) a
where
a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc
select * from (
select
a.InvoiceID,
a.CustomerCode,
a.CustName,
a.dimension,
a.dimension2_,
a.dimension3_ ,
a.SALES_AMOUNT,
a.COST_AMOUNT,
a.GROSS_PROFIT,
case
when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
else GROSS_PROFIT/LineAmountMST * 100
END MARGIN_PER
from
(
select
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT
--case
-- when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
-- when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
-- when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
-- else sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100
--END MARGIN_PER
FROM
salesdim
where
invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
) a ) t
where
t.MARGIN_PER between @marginFrom and @marginTo
order by t.MARGIN_PER desc
-- a table variable to hold the values while they get eliminated by the margin calculation
Declare @Vartable1 table (
InvoiceID int IDENTITY(1,1) Primary Key,
CustomerCode WhateverDataType,
CustName WhateverDataType,
dimension WhateverDataType,
dimension2_ WhateverDataType,
dimension3_ WhateverDataType,
LineAmountMST WhateverDataType,
linecostAmount WhateverDataType,
lineamountmst WhateverDataType,
linecostamount WhateverDataType
--fill the table variable
insert into @Vartable1 (
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
LineAmountMST,
linecostAmount,
lineamountmst,
linecostamount)
select InvoiceID,CustomerCode,CustName,dimension,
dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount
FROM salesdim
where invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
--now get the final summing done outside the table variable
--and do the summing for MARGIN_PER inside a temptable, and eliminate the ones you don't need final summed
select InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT, MARGIN_PER
from (
select InvoiceID,CustomerCode,CustName,dimension,
dimension2_,dimension3_, LineAmountMST, linecostAmount,lineamountmst, linecostamount,
case
when SUM(lineamountmst)=0 and sum(linecostamount)> 0 then -100
when SUM(lineamountmst)=0 and sum(linecostamount)< 0 then 100
when SUM(lineamountmst)=0 and sum(linecostamount)= 0 then 0
else
sum(LineAmountMST- linecostAmount)/sum(LineAmountMST)*100 END MARGIN_PER
from @Vartable1
where MARGIN_PER between @marginFrom and @marginTo
)
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
order by a.MARGIN_PER desc
select
InvoiceID,
CustomerCode,
CustName,
dimension,
dimension2_,
dimension3_,
sum(LineAmountMST) SALES_AMOUNT,
SUM(linecostAmount) COST_AMOUNT,
sum(LineAmountMST- linecostAmount) GROSS_PROFIT
INTO #temp
FROM
salesdim
where
invoicedate between @startdate and @enddate
and itembuyergroupid = @itembuyergroupid
and itemgroupid = @itemgroupid
and salesoriginid = @salesoriginid
and CustomerCode = @Cust
and dimension = @Dept
and dimension2_ = @CC
and dimension3_ = @Purpose
GROUP BY InvoiceID,CustomerCode,CustName,dimension,dimension2_,dimension3_
select
a.InvoiceID,
a.CustomerCode,
a.CustName,
a.dimension,
a.dimension2_,
a.dimension3_ ,
a.SALES_AMOUNT,
a.COST_AMOUNT,
a.GROSS_PROFIT,
case
when SALES_AMOUNT =0 and COST_AMOUNT > 0 then -100
when SALES_AMOUNT =0 and COST_AMOUNT < 0 then 100
when SALES_AMOUNT =0 and COST_AMOUNT = 0 then 0
else GROSS_PROFIT/LineAmountMST * 100
END MARGIN_PER
from
#temp a
where
a.MARGIN_PER between @marginFrom and @marginTo
order by a.MARGIN_PER desc
DROP TABLE #temp
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.