Case statement with date range

[64waves]

Good afternoon, I am on a 7.1 iseries using command line STRSQL

I am trying to read an 8,0 field in YYYYMMDD format with a case statement to group them by month going across.

This is what I have so far against one month, FRCVR# is the customer number.

SELECT FRCVR# , frcdat,
case
when frcdat between 20160101 and 20160131
then count(*) else 0 end as jancount,
FROM FRTxxxx/FRIxxxx
where
frcvr# IN(50190,17993,91736,89580) and frcdat between
20160101 and 20160331
group by FRCVR#
order by FRCVR#

I saw information online regarding a DATE format to see if I could just get a grouping that way, but get message "Column or global variable DATE not found"
When I change DATE to my date field I get the message "Argument 1 of function MONTH not valid"

select frcvr#, MONTH(DATE) As "Month"

FROM FRTxxxx/FRIxxxx
where
frcvr# IN(50190,17993,91736,89580) and frcdat between
20170101 and 20170331
group by FRCVR#, MONTH(DATE)
order by FRCVR#, MONTH(DATE)


I would want to expand it out to 12 months if I can get this to work. Any help would be appreciated.

thank you

[jtaylor___]

If your column is something like NUMERIC(8,0), you're going to have to convert it before using MONTH(), which would require a DATE data type. You can use the DATE function to convert your data to a date. It looks something like this:
date('2017-02-08')

[TedHolt]

Try this:

select FRCVR#,
       sum(case when frcdat between 20160101 and 20160131    
          then 1 else 0 end) as jancount,
       sum(case when frcdat between 20160201 and 20160229    
          then 1 else 0 end) as febcount, 
       sum(case when frcdat between 20160301 and 20160331    
          then 1 else 0 end) as marcount                    
  from FRIxxxx                                          
 where frcvr# IN(50190,17993,91736,89580)
   and frcdat between 20160101 and 20160331                                 
 group by FRCVR#                                       
 order by FRCVR#

You can read more about it here:

https://www.itjungle.com/2005/10/26/fhg102605-story01/

[64waves]

Thank you for the replies, they were a big help. Going to expand on this and if I need any other assistance I will let you know.

Thank you and have a good day.

64waves

[CRinger400]

A CASE inside a SUM. Very nice Ted.

[64waves]

Ok so I built this in my program with variables, and I am running it from an SQL command line to make sure I am getting everything in my program.

The command line query it this:
SELECT FRCVR#, C.CUSTOMER_NAME , 1 as year,
sum(case when frcdat between
20170101 and 20170131 then 1 else 0 end) as jancount,
sum(case when
frcdat between 20170201 and 20170229 then 1 else 0 end) as
febcount,
sum(case when frcdat between 20170301 and 20170331 then 1
else 0 end) as marcount,
sum(case when frcdat between 20170401 and
20170430 then 1 else 0 end) as aprcount,
sum(case when frcdat
between 20170501 and 20170531 then 1 else 0 end) as maycount,
sum(case when frcdat between 20170601 and 20170630 then 1 else 0
end) as juncount
FROM FRIOxxx f, custmast c
WHERE frcdat between 20170101 and 20170208 and F.RECEIVER_NO=
C.CUSTOMER_NO GROUP BY FRCVR# ,C.CUSTOMER_NAME

and get results:
Recvr# CUSTOMER NAME YEAR JANCOUNT FEBCOUNT MARCOUNT
95,575 cust 1 1 40 0 0
71,942 cust2 1 180 0 0
17,993 cust3 1 167 0 0
91,737 cust4 1 320 60 0
31,374 cust5 1 260 180 0
68,557 cust6 1 28 0 0
******** End of data ********

the query in my program looks like this: (All the beg and end variables have been checked, they represent 20170101 and 20170131 etc.
begyear = 20170101 and yesterday = 20170208

Exec SQL
insert into INSTWORK1
SELECT FRCVR#, C.CUSTOMER_NAME , 1,
sum(case when frcdat between :JanBeg and :JanEnd then
1 else 0 end) as jancount,
sum(case when frcdat between :FebBeg and :FebEnd then
1 else 0 end) as febcount,
sum(case when frcdat between :MarBeg and :MarEnd then
1 else 0 end) as marcount,
sum(case when frcdat between :AprBeg and :AprEnd then
1 else 0 end) as aprcount,
sum(case when frcdat between :MayBeg and :MayEnd then
1 else 0 end) as maycount,
sum(case when frcdat between :JunBeg and :JunEnd then
1 else 0 end) as juncount
FROM FRIOxxxx f, custmast c
WHERE frcdat between :BegYear and :Yesterday and F.FRCVR# =
C.CUSTOMER_NO GROUP BY F.FRCVR# ,C.CUSTOMER_NAME ;

I only get one record. What gives? I was ordering by customer name, getting the last customer alphabetically, but after
I took that out it still only gives me that one.

Any help would be appreciated.

[64waves]

DUH, let's look at the job log, I got to a customer name that was longer than my destination field, so it wouldn't write any more records.

Problem solved, thanks again for all the help. Have a good day.

64waves