Query to calculate the allowance

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query to calculate the allowance

Author

Topic

sara_23apr
Starting Member

8 Posts

Posted - 2008-12-29 : 06:42:25

Dear Friends
I have been struggling to write a Query for the following requirement. I have written and rewritten number of queries . I am unable to get a bug free and efficient query.
I need to calculate the Allowance and update the related fields (Day Allowance, Night Allowance). Please help.

I need to calculate the Day Allowance and Night Allowance for the Driver of a vehicle whose journey details are given below

Day Allowance = 4 $ ,Night Allowance = 10$

Table columns
TripMasterId, DtlsId, FromDateTime, ToDateTime, DayAllowanceAmt , NightAllowanceAmt,

1, 1, 1-jan-08 9am, 1-jan-08 1 pm ,4, 0

1, 3, 1-jan-08 2am, 1-jan-08 4am, 0, 10

1, 2, 1-jan-08 10pm, 1-jan-08 11 pm, 0, 0

1, 4, 2-jan-08 4 am , 2-jan-08 11pm, 4, 10

1, 5, 2-jan-08-11.30pm, 4-jan-08 10am, 8, 20

For each day , the day allowance will be given if the driver has travelled between 6 am and 10 pm
For each day the the Night allowance will be given if the driver has travelled between (12am and 6am) OR (10pm and 12Midnight)

Explanation For the DtlsId5 (2-jan-08-11.30pm TO 4-jan-08 10am) . record
Jan2 -- 0 Night Allowance + 0 Day Allowance ( Night allowance cannot be applied as it has been applied in the DtlsId4)
Jan3 – 1 Night Allowance + 1 Day Allowance
Jan4 - 1 Night Allowance + 1 Day Allowance

Regards
Sara

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-29 : 06:51:15

Whats the datatype for FromDateTime, ToDateTime columns please .

sara_23apr
Starting Member

8 Posts

Posted - 2008-12-29 : 07:37:21

Thanks for the reply

The data type is SmallDateTime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:50:33

i didnt understand the explanation. why you didnt consider the night allowance for driver on Jan3 for travel from 10pm to 12 midnight? shouldnt it be 2 Night Allowance + 1 Day Allowance

sara_23apr
Starting Member

8 Posts

Posted - 2008-12-29 : 19:54:17

For each day there can be only one Day allowance and one night allowance
e.g. Jan1 he can have have max one day allowance and one night allowance.

[b]For each day , the day allowance will be given if the driver has travelled between 6 am and 10 pm
For each day the the Night allowance will be given if the driver has travelled between (12am and 6am) OR (10pm and 12Midnight)[/b]

Thanks for your time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 01:56:12

[code]
UPDATE r
SET r.DayAllowance=r1.DayAllowance,
r.NightAllowance= r1.NightAllowance
FROM Table r
INNER JOIN
(SELECT t.TripMasterId,
t.DtlsId,
t.FromDateTime,
t.ToDateTime,
SUM(CASE WHEN (
(DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)=DATEADD(dd,DATEDIFF(dd,0,FromDateTime),0)
OR DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)=DATEADD(dd,DATEDIFF(dd,0,ToDateTime),0))
AND (DATEPART(hh,DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)) BETWEEN 0 AND 6 OR DATEPART(hh,DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)) BETWEEN 10 AND 23))
OR (DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0) BETWEEN DATEADD(dd,DATEDIFF(dd,0,FromDateTime),0) AND DATEADD(dd,DATEDIFF(dd,0,ToDateTime),0)) THEN 20
ELSE 0 END) AS NightAllowance,
SUM(CASE WHEN (
(DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)=DATEADD(dd,DATEDIFF(dd,0,FromDateTime),0)
OR DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)=DATEADD(dd,DATEDIFF(dd,0,ToDateTime),0))
AND (DATEPART(hh,DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)) BETWEEN 6 AND 22 )
OR (DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0) BETWEEN DATEADD(dd,DATEDIFF(dd,0,FromDateTime),0) AND DATEADD(dd,DATEDIFF(dd,0,ToDateTime),0)) THEN 4
ELSE 0 END) AS DayAllowance
FROM Table t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,DATEDIFF(dd,0,FromDateTime)+ v.number,0)<=ToDateTime
GROUP BY t.TripMasterId,
t.DtlsId,
t.FromDateTime,
t.ToDateTime)r1
ON r1.TripMasterId=r.TripMasterId AND
r1.DtlsId = r.DtlsId AND
r1.FromDateTime=r.FromDateTime AND
r1.ToDateTime=r.ToDateTime
[/code]

sara_23apr
Starting Member

8 Posts

Posted - 2008-12-30 : 05:08:00

Thank you visakh16 for the reply.
But i am unableto follow the code kindly can you give few comments as to how the logic works

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 05:10:52

i'm basically splitting up each record of table day wise. then grouping on each period (start-end) and summing up allowance counts for each day based on your rule. this should give you the total allowance amounts for each period (start-end). then i'm updating this value to your main table fields.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 05:13:43

and master..spt_values is just an internal count table of sql server which ised to split records to daywise detail between specified periods.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources