How to break sales into months

[alisaif]

Hi

Is it possible to break the same from {01/01/2012} to {31/12/2012} into month like;

JAN
FEB
MAR
..
..
DEC

through select sql...

The fields of the table are as follows:

1. Sino C(8)
2. Invdate D ({01/01/2012}-{31/12/2013})
3. Qntymaj N(10,2)
4. RateMaj N(10,3)
5. QntyMin N(10,2)
6. RateMin N(10,3)

I want amount=iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj) to be break into months. Please guide

Thanks

Saif

 

[Olaf Doschke]

That would be

Select sum(iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) as Total, Month(Invdate) as Month From yourtable Group By Month(Invdate)

or similar, depending an what you want, sum, min, max, avg, or whatever.

Bye, Olaf.

 

[alisaif]

Thanks for the reply!!

Taking the example of your code, I applied the following and got it.

Sele Year(invdate) As theyear, ;
month(invdate) As themonth, ;
sum(qntymaj) As cartons, ;
sum(qntymin) As units, ;
sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As mamount ;
from sijunk ;
group By theyear, themonth ;
into Curs tempSi readwrite

Thanks a lot!

Saif

 

[Olaf Doschke]

>group By theyear, themonth

There you go. Month(Invdate) didn't work out here, did it? Sorry, if that's was the case. I'm more used to T-SQL and in T-SQL you can't use the alias names of fields you generate in he query.

You could also take my example, if querying for a single year only: WHERE YEAR(Invdate) = YEAR() for the current year, for example, but indeed just grouping by month will put all january data of different years into one group, so you got that better than me.

Bye, Olaf.

 

[JRB-Bldr]

into month like;
JAN
FEB
MAR

A slight modification to the above to yield the 3 character representation of the Invoice Month as you show in your original posting you could change:
MONTH(invdate) AS TheMonth
to
UPPER(LEFT(CMONTH(invdate),3)) AS TheMonth

Good Luck,
JRB-Bldr

 

[alisaif]

Thanks a good idea! Otherwise I was doing like that:

Select tempsi
Go Top
Do While ! Eof()
   m_yur    = theyear
   m_munth  = themonth
   m_amount = amount
   Select vgraf
   Append Blank
   Replace yur With Str(m_yur)
   Do Case
      Case m_munth=1
         Replace munth With 'January',;
            amt1y1 With m_amount
      Case m_munth=2
         Replace munth With 'February',;
            amt1y1 With m_amount
      Case m_munth=3
         Replace munth With 'March',;
            amt1y1 With m_amount
      Case m_munth=4
         Replace munth With 'April',;
            amt4y1 With m_amount

Saif

 

[Olaf Doschke]

CMONTH is locale specific, so I'd rather prepare a normal array, if further processing depends on the month names or the months shorts in english, but indeed there is no need for a lengthy case statement.

LOCAL Array laMonth[12]
laMonth[1] = "January"
...
laMonth[12]= "December"

prepare your "munth" field to be able to contain the longest month name, and then the monthname simply is laMonth[themonth]. You can use that rightaway in the first select, too.

I would prefer going with the month number, introducing month names should be done as a last step in reports or exports. Data is data, and you can easier calculate with month numbers, sort by month number etc.Human readable and friendly data is for output/input layer only.

Bye, Olaf.

 

[alisaif]

Thanks I will check it too..

Saif

 

[NasibKalsi]

I agree with above posts.

what Olaf is saying for laMonth, you can do it as follows as well.
= alines(laMonth,"JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",',')
----------------------------------------------------------

Other Suggestion:
On the result table add the following fields as required
alter result_table add ICMONTH C(3) add CMONTH c(3) add ICYEAR c(4)

replace all ICMONTH with str(month(INVDATE),2), CMONTH with laMonth(month(INVDATE)), ICYEAR with str(year(INVDATE),4)

you can sort on ICYEAR + ICMONTH , etc. and print CMONTH


nasib

 

[Mike Lewis]

what Olaf is saying for laMonth, you can do it as follows as well.
= alines(laMonth,"JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",',')

That's fine if the application will always be in English. The advantage of CMONTH() is that it outputs the month in the user's language (as determined by the Foxpro resource file).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes Mike, but

1. CMONTH is always english in the IDE (even on a german Windows)
2. If your code depends on the english month names for any reason, it would break with locale specific names, and you'd not detect that in tests done in the IDE, because of 1.

As you cannot solve internationalisation of an application with the locale sepcific resource DLLs alone, I suggest and prefer to not use such functions as CMONTH. There are few of them anyway and you better incorporate that part of localisation into your overall code for i18n (internationalisation). Eg use INTL toolkit in that regard.

No. 1 is surely unknown to you and just got that way from VFP8 on, as there were no localisations of the Foxpro IDE anymore, just the runtime resource DLLs for different languages. That's also true for the VFP help. For those fluently understanding english is was a benefit, as the german translation of the help had some mistakes, which is naturally the way, if you translate something technical.

The dFPUG did a localisation of the vfp9deu.dLL (NOT vfp9rdeu.dll), also of french, czech and spanish DLLs, so you could use CMONTH in these languages during development, too. You can still order that, by the way, they offer a CD and the patch is also sanctioned by Microsoft, a totally legal patch. I fear the localisation CD only has the german localisation on it. Rainer Becker should be able to provide the others, too.

Nevertheless such small helpers as CMONTH() are best avoided in the bigger context, as it's just a snippet of the things you need to change for an application to be localised and there are much better solutions, as already mentioned, the INTL toolkit.

Bye, Olaf.