 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Uncle Potato
Guest
|
 Posted: Tue Nov 09, 2004 8:46 am Post subject: SQL guru : Cut Off Day |
 |
|
I have a date field (TrDate) in an dbase table, I have been using
extract to implement the month of transaction, as in
(Extract(Year from TrDate)*100+Extract(Month from TrDate)) as YM
So in the report, I can simply use YM as a grouping expression.
Now my client want to add a cutoff, say the 4th day as the cut off,
(i.e. 1st, 2nd, 3rd day are count as previous month's entry) how do
you do it with SQL ??
Now I am saving the temp table, loop through with delphi statement to
fill in the desired YM field, but it is just taking too long.
Any suggestion ??
|
|
| Back to top |
|
 |
Bojidar Alexandrov
Guest
|
| Posted: Tue Nov 09, 2004 10:51 am Post subject: Re: SQL guru : Cut Off Day |
|
|
"Uncle Potato" <Potato (AT) aelhk (DOT) com> wrote
| Quote: | I have a date field (TrDate) in an dbase table, I have been using
extract to implement the month of transaction, as in
(Extract(Year from TrDate)*100+Extract(Month from TrDate)) as YM
|
Does the year have 100 months?
| Quote: | So in the report, I can simply use YM as a grouping expression.
Now my client want to add a cutoff, say the 4th day as the cut off,
(i.e. 1st, 2nd, 3rd day are count as previous month's entry) how do
you do it with SQL ??
|
If you substract 1, 2 or 3 days from the date and then use Extract it will
work.
Bojidar Alexandrov
|
|
| Back to top |
|
|
Uncle Potato
Guest
|
| Posted: Wed Nov 10, 2004 2:34 am Post subject: Re: SQL guru : Cut Off Day |
|
|
| Quote: | (Extract(Year from TrDate)*100+Extract(Month from TrDate)) as YM
Does the year have 100 months?
|
Does it matter??
As long as they can group together, it works fine,
i.e. 200411, 200412, 200501, ...
YM field can also be a label directly and is human friendly....
Using *12 instead of *100, you will get a number counting number of
months from year 0. It mathematically means something, but there is
no advantage at all.
| Quote: | Now my client want to add a cutoff, say the 4th day as the cut off,
(i.e. 1st, 2nd, 3rd day are count as previous month's entry) how do
you do it with SQL ??
If you substract 1, 2 or 3 days from the date and then use Extract it will
work.
Good idea... But unfortunately date field isn't the same as |
TdateTIme, SQL don't allow me to do simple math to do
subtraction........
|
|
| Back to top |
|
|
Uncle Potato
Guest
|
| Posted: Wed Nov 10, 2004 9:04 am Post subject: Re: SQL guru : Cut Off Day |
|
|
| Quote: |
Now my client want to add a cutoff, say the 4th day as the cut off,
(i.e. 1st, 2nd, 3rd day are count as previous month's entry) how do
you do it with SQL ??
If you substract 1, 2 or 3 days from the date and then use Extract it will
work.
|
I figure out the maths like this now:
work out the grouping field YM as
Year * 1200 + Month * 100 + Day - 1 - CutOff
the YM field has the last two digits as the DAY, hundreds become
number of months starting from year 0.
To group the month, I do a Int(YM / 100)
The month label would be
IntToStr( Int(YM/100/12)) +'/'+ IntToStr( (Int(YM/100) mod 12))
|
|
| Back to top |
|
|
John Herbster
Guest
|
| Posted: Wed Nov 10, 2004 11:53 am Post subject: Re: SQL guru : Cut Off Day |
|
|
"Uncle Potato" <Potato (AT) aelhk (DOT) com> wrote
| Quote: | Does it matter??
As long as they can group together, it works fine,
i.e. 200411, 200412, 200501, ...
YM field can also be a label directly and is human friendly....
Using *12 instead of *100, you will get a number counting
number of months from year 0. It mathematically means
something, but there is no advantage at all.
|
No advantage? What about for date arithmetic?
Uncle, If you are not familiar with the GetText and SetText
event methods of the dataset field, I suggest that you may
want to look at them, they allow you to easily transform
dates between machine and human readable forms.
Regards, JohnH
|
|
| Back to top |
|
|
Bojidar Alexandrov
Guest
|
| Posted: Thu Nov 11, 2004 7:29 am Post subject: Re: SQL guru : Cut Off Day |
|
|
| Quote: | Now my client want to add a cutoff, say the 4th day as the cut off,
(i.e. 1st, 2nd, 3rd day are count as previous month's entry) how do
you do it with SQL ??
If you substract 1, 2 or 3 days from the date and then use Extract it
will
work.
Good idea... But unfortunately date field isn't the same as
TdateTIme, SQL don't allow me to do simple math to do
subtraction........
|
As addition to John Herbster,
You did'nt mention what database you use, but in Interbase you can timestamp
datatype is similar to Delphi one and substracting a day is done with
substracting 1 from the date directly.
Other rdbms-es have functions to add/substract to date
Bojidar Alexandrov
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
