Reg. Discount Calculation in Existing Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reg. Discount Calculation in Existing Query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-06-08 : 11:39:53

Dear Friends,

I have two table with below column

Table 1 : CC_BoatAllocation
ID |BoatId| FromDate | ToDate | Available | Price | Discount | Total Price

Table 2 : CC_BoatBooking
ID | BoatId |FromDate | ToDate

I am using below query to check the availability of booking

SELECT c.id as id,c.fromDate as fromDate, c.ToDate as ToDate,
CASE WHEN b.BoatId IS NULL THEN 'YES' ELSE 'NO' END AS Available,c.Price
FROM ( SELECT id,BoatId,Price,FromDate,FromDate +(6) AS ToDate
FROM ( SELECT id,BoatId,Price,ToDate,
CASE WHEN DATEPART(DW,DATEADD(day,Number,FromDate))%(7) = 0
THEN DATEADD(day,Number,FromDate) END AS FromDate FROM CC_BoatAllocation ,
(SELECT number FROM MASTER..SPT_VALUES WHERE TYPE = 'P') n
WHERE BoatId = '1' AND Deleted=0 AND DATEADD(day,Number,FromDate) <= ToDate )t
WHERE FromDate IS NOT NULL )c LEFT JOIN CC_BoatBooking b ON c.BoatId = b.BoatId
AND b.Status=1 AND c.fromDate = b.fromDate AND c.ToDate = b.ToDate
AND c.id=b.AllocationId where c.ToDate>=getdate() and c.FromDate>=getDate()

For this query my output is

id fromDate ToDate Available Price
-------------------- ----------------------
11 2009-06-13 00:00:00.000 2009-06-19 00:00:00.000 YES 600
11 2009-06-20 00:00:00.000 2009-06-26 00:00:00.000 YES 600
11 2009-06-27 00:00:00.000 2009-07-03 00:00:00.000 YES 600
11 2009-07-04 00:00:00.000 2009-07-10 00:00:00.000 YES 600
19 2009-07-11 00:00:00.000 2009-07-17 00:00:00.000 YES 700

But i want to modify the Output,

I want to display the Discount price, it means if i have 10% discount means i have to display the discount as 60 for the price of 600, and then i want to display the total price as 600 - 60 = 540.

Please find my expected output
id fromDate ToDate Available Price Discount Total Price
-------------------- ----------------------
11 2009-06-13 00:00:00.000 2009-06-19 00:00:00.000 YES 600 10% 540
11 2009-06-20 00:00:00.000 2009-06-26 00:00:00.000 YES 600 10% 540
11 2009-06-27 00:00:00.000 2009-07-03 00:00:00.000 YES 600 10% 540
11 2009-07-04 00:00:00.000 2009-07-10 00:00:00.000 YES 600 10% 540
19 2009-07-11 00:00:00.000 2009-07-17 00:00:00.000 YES 700 10% 640

Please help me to get my expected output

Please modify my query, Thanks in advace

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-06-08 : 12:48:51

Dear Friends,

Visakh or any other experts please help me, it will be much helpful for me..

Thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-06-08 : 23:58:29

Friends,

I am still waiting for your help.

thanks in advance

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-06-09 : 05:09:23

Whether my query can be able to modifiable or i have to try some other way to get my expected output,

Please advice / suggest me

Thanks in advance

prav3575
Starting Member

5 Posts

Posted - 2009-06-09 : 06:58:53

I did not understand your written query , but for the provided information you can write very simple inner join for your expected output

select a.id,b.fromdate,b.todate,a.available,a.price,convert(varchar,a.discount)+'%' discount,a.price -(a.price*a.discount)/100 discount
from cc_boatallocation a inner join cc_boatbooking b
on a.id=b.id

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-09 : 07:01:44

Maybe there will be some more help if the code is well formatted:

SELECT c.id       AS id, 
       c.fromdate AS fromdate, 
       c.todate   AS todate, 
       CASE 
         WHEN b.boatid IS NULL 
         THEN 'YES' 
         ELSE 'NO' 
       END AS available, 
       c.price 
FROM   (SELECT id, 
               boatid, 
               price, 
               fromdate, 
               fromdate + (6) AS todate 
        FROM   (SELECT id, 
                       boatid, 
                       price, 
                       todate, 
                       CASE 
                         WHEN Datepart(dw,Dateadd(DAY,NUMBER,fromdate))%(7) = 0 
                         THEN Dateadd(DAY,NUMBER,fromdate) 
                       END AS fromdate 
                FROM   cc_boatallocation, 
                       (SELECT NUMBER 
                        FROM   MASTER..spt_values 
                        WHERE  TYPE = 'P') n 
                WHERE  boatid = '1' 
                       AND deleted = 0 
                       AND Dateadd(DAY,NUMBER,fromdate) <= todate) t 
        WHERE  fromdate IS NOT NULL) c 
       LEFT JOIN cc_boatbooking b 
         ON c.boatid = b.boatid 
            AND b.status = 1 
            AND c.fromdate = b.fromdate 
            AND c.todate = b.todate 
            AND c.id = b.allocationid 
WHERE  c.todate >= Getdate() 
       AND c.fromdate >= Getdate()

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-09 : 08:54:53

Not tested but should work for you:

SELECT c.id       AS id, 
       c.fromdate AS fromdate, 
       c.todate   AS todate, 
       CASE 
         WHEN b.boatid IS NULL 
         THEN 'YES' 
         ELSE 'NO' 
       END AS available, 
       c.price, 
       convert(varchar(3),c.discount)+'%' as discount,
       round(c.price - (c.price * c.discount)/100.0,2) as TotalPrice
FROM   (SELECT id, 
               boatid, 
               price, 
               discount,
               fromdate, 
               fromdate + (6) AS todate 
        FROM   (SELECT id, 
                       boatid, 
                       price, 
                       todate, 
                       discount,
                       CASE 
                         WHEN Datepart(dw,Dateadd(DAY,NUMBER,fromdate))%(7) = 0 
                         THEN Dateadd(DAY,NUMBER,fromdate) 
                       END AS fromdate 
                FROM   cc_boatallocation, 
                       (SELECT NUMBER 
                        FROM   MASTER..spt_values 
                        WHERE  TYPE = 'P') n 
                WHERE  boatid = '1' 
                       AND deleted = 0 
                       AND Dateadd(DAY,NUMBER,fromdate) <= todate) t 
        WHERE  fromdate IS NOT NULL) c 
       LEFT JOIN cc_boatbooking b 
         ON c.boatid = b.boatid 
            AND b.status = 1 
            AND c.fromdate = b.fromdate 
            AND c.todate = b.todate 
            AND c.id = b.allocationid 
WHERE  c.todate >= Getdate() 
       AND c.fromdate >= Getdate()

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-06-09 : 13:06:19

Dear Webfred,

Thank you for your message,

Yes i got the expected output,

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-09 : 13:56:49

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-06-13 : 08:18:38

Hello WebFred,

Thank you for your help , i need to modify my query with EuropeExchangeRate,

I have europe exchange rate in my table
table name cc_exchangeRate
column name : exchange
Value : 7

I will have always one value for the money exchange, i want to calculate that and display in the Price and Total Price, please help me to modify the above query

we have to subtract the cc_exchangerate exchange value 7 with price and Total price and we have to display the price in the BELOW format, the below value is sample display format and i need to display the rounded number without decimal point.

EXPECTED OUTPUT

id fromDate ToDate Available Price Discount TotalPrice
-------------------- ----------------------
11 2009-06-13 00:00:00.000 2009-06-19 00:00:00.000 YES 600 HRK [89 EUR ] 10% 540 HRK [87 EUR ]
11 2009-06-20 00:00:00.000 2009-06-26 00:00:00.000 YES 600 HRK [89 EUR ] 10% 540 HRK [87 EUR ]
11 2009-06-27 00:00:00.000 2009-07-03 00:00:00.000 YES 600 HRK [89 EUR ] 10% 540 HRK [87 EUR ]
11 2009-07-04 00:00:00.000 2009-07-10 00:00:00.000 YES 600 HRK [89 EUR ] 10% 540 HRK [87 EUR ]
19 2009-07-11 00:00:00.000 2009-07-17 00:00:00.000 YES 700 HRK [91 EUR ] 10% 640 HRK [90 EUR ]

Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-13 : 13:12:56

Can you show me a select to get only the wanted exchange ratio out of table cc_exchangeRate?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources