Count Actual No. of Days

[alisaif]

Hi

Let say I have the following dates for kiosk rented in Super Market during the year.

Period Rent/Kious No.of Kiosks
---------------------------- ------------- --------------
dt1 dt2 rent totnum
1. {01/01/2012} to {31/03/2012} 2,500.00 3
2. {15/04/2012} to {30/04/2012} 500.00 2
3. {01/06/2012} to {31/07/2012} 1,600.00 5
4. {10/08/2012} to {30/09/2012} 1,400.00 5
5. {15/10/2012} to {31/12/2012} 2,000.00 3

I want to run a query to calculate the actual no. of days of Kiosk throughout the year for Sales purpose.

Please guide...

Thanks

Saif

 

[alisaif]

I want to repeat the figures as it is jumbled above

                                  Period                        Rent/Kious    No.of Kiosks 
---------------------------- ------------- --------------
        dt1             dt2       rent         totnum
1. {01/01/2012} to {31/03/2012} 2,500.00          3
2. {15/04/2012} to {30/04/2012}   500.00          2
3. {01/06/2012} to {31/07/2012} 1,600.00          5
4. {10/08/2012} to {30/09/2012} 1,400.00          5
5. {15/10/2012} to {31/12/2012} 2,000.00          3

Sorry for the inconvenience.

Saif

 

[mm0000]

From what I understood from your requirement the following should do it.

SET date BRITISH
SET STRICTDATE TO 0

CREATE cursor curTest (dt1 d, dt2 d, rent n(7,2), totnum n(3))
INSERT into curTest VALUES ({01/01/2012},{31/03/2012},2500,3)
INSERT into curTest VALUES ({15/04/2012},{30/04/2012},500,2)
INSERT into curTest VALUES ({01/06/2012} ,{31/07/2012},1600,5)
INSERT into curTest VALUES ({10/08/2012},{30/09/2012},1400,5)
INSERT into curTest VALUES ({15/10/2012},{31/12/2012},2000,3)

SELECT dt2-dt1 as dt3, rent,totnum FROM curtest INTO CURSOR curtest2


BROWSE

MM

 

[Olaf Doschke]

If you have date fields, then to calculate days simply is done by subtracting the dates.
So you can

SELECT Year(dt1) as year, SUM(dt2-dt1) As days FROM yourtable GROUP BY 1

Now you might want to multiply by totnum, then it's SUM((dt2-dt1)*totnum), and if the rent is not only per kiosk, but also per day, it's another factor, so SUM((dt2-dt1)*totnum*rent) to get the total rent.

Bye, Olaf.

 

[Olaf Doschke]

Correction: Assume dt2=dt1 still means one day of rent, you have to calculate SUM(dt2-dt1+1), SUM((dt2-dt1+1)*totnum) and SUM((dt2-dt1+1)*totnum*rent) respectively.

Bye, Olaf.

 

[alisaif]

Thanks got it, I will try it.

Saif

 

[alisaif]

Hi

Coming back to my previous query which was as follows:

Period                        Rent/Kious    No.of Kiosks 
---------------------------- ------------- --------------
        dt1             dt2       rent         totnum
1. {01/01/2012} to {31/03/2012} 2,500.00          3
2. {15/04/2012} to {30/04/2012}   500.00          2
3. {01/06/2012} to {31/07/2012} 1,600.00          5
4. {10/08/2012} to {30/09/2012} 1,400.00          5
5. {15/10/2012} to {31/12/2012} 2,000.00          3

If the user input the date between dt1(01/01/2012) to dt2(31/12/2012) it will give the total rent during the year with the total no. of Kiosks being used for that period. I want to deduct the no. of days which left between dt2 to dt1. let say :

1. {01/01/2012} to {31/03/2012}
2. {15/04/2012} to {30/04/2012}

I want 15 days {31/03/2012} to {15/04/2012} and so on till the end of the year.

How can I do so using the above mentioned postings.

Thanks

Saif

 

[Olaf Doschke]

So you want the inverse, the number of days not within a rent period?

Well, that's the inverse. You subtract the result of days WITH rental from the whole period. So you finally just have to compute filterEnddate-filterStartdate+1-result of previous query.
If that isn't what you seek, then please explain further in other words, you're hardly understandable to me.

Bye, Olaf.

 

[Olaf Doschke]

By the way, about forum use:

You should start a new thread for a new question and just point to an old thread for reference, if much of what you want to refer to is told there already. That would be fine, because you wouldn't need to reread everything up the point where your new question comes in.

To refer to another thread you simply write the word "thread" with the thread number in the text of your post. You'll find that thread number directly under the thread caption, so for this thread it's thread184-1711494. This time it's not processed to a link, because I unchecked the TGML option, but it's all you need to copy into your post to refer to a previous thread.

Bye, Olaf.

 

[alisaif]

Thanks I will keep in mind.

Saif