CODEWITHSUNDEEP
sqlpercentagedate-difference
Reshma
Reshma

Reputation: 1430

SQL query to calculate percentage in between two dates

I want to calculate percentage between two dates. Given below table shows values of two days count. Dates are reading from two text boxes (txtStart and txtEnd).

slNo  productName  count1  count2
....  ...........  .....   ......
1     A             50      20
2     B             30      39
3     C             20      23
4     D             15      34
5     E             18      45

My Query:

SELECT  slNo  
        productName  
        count1, count2
        cast((count1* 100.0) / (DATEDIFF(day, @start, @end) * 250)) AS  
        Percentage1, cast((count2* 100.0) / (DATEDIFF(day, @start, @end) 
        * 250)) AS Percentage2 FROM  dbo.tblPercentage

While using the above query I am getting one error. the error is : "Incorrect parameter count in the call to native function 'DATEDIFF'"

How can I get the date difference and apply into the above sql query.

count 250 per day is calculated as 100%. The above table is showing two days count. So 2*250 is consider as 100%. If 3 days count, then it will be 3*250. So help me to calculate percentage using sql query. Thank you

Upvotes: 0

Views: 5011

Answers (1)

Khurram Ali
Khurram Ali

Reputation: 1679

May be you are looking for this if i understand your problem correctly

SELECT  slNo  
        productName  
        count, 
        cast(((DATEDIFF(day,'2014-08-05','2014-06-05') * 250.0) / 250) AS decimal(9, 3)) Percentage1 
FROM  dbo.tblPercentage

Note: You can replace dates with your startdate and enddate paramters which you are getting from textboxes like this

DATEDIFF(day,@startdate,@enddate)

Upvotes: 1

Related Questions