asked on

Correcting Sql Syntax to tally counts by month for a location

I have a table with a field called box location. I have 4 different box locations. I want to simply the code, so I want to code the sql to do one location at a time.
So, I am attaching my code below.
------
I want to do a count for box location = SMCBUYR from 01/01/08 through 06/30/08.
If there is no count for January, I want 0 displayed on my result set for January. (the reason for the second table called asrp_rpt_helper). Asrp_rpt_helper has YYMM dates ( 0801,0802,0803,0804...)

------
My current result set shows jan through june but no counts!
(attaching results)
-----
finally my table has 6 records for SMCBUYR for the month of APRIL.

(attaching below).

select  substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy,
 (CASE WHEN box_loc = 'SMCBUYR' THEN 1 ELSE 0 END) AS 'SMCBUYR' 
	from a_smc, asrp_rpt_helper 
	where convert(datetime,date_rcvd) between convert(datetime,'080101') and convert(datetime,'080630')
	and rptdate *= substring(date_rcvd,9,2)+substring(date_rcvd,1,2)
	and rptdate between substring('080101',1,4)  and substring('080630',1,4)
	group by box_loc, rptdate 
	order by rptdate
 
*************************************************************

Open in new window

sql1.jpg
sql2.jpg

mahpog

ASKER

Cleaned up by syntax some more.

select box_loc, substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy,
(CASE WHEN box_loc = 'SMCBUYR' THEN 1 ELSE 0 END) AS 'SMCBUYR'
      from a_smc, asrp_rpt_helper
      where date_rcvd between '01/01/2008' and '06/30/2008'
      and rptdate *= substring(date_rcvd,9,2)+substring(date_rcvd,1,2)
      and rptdate between substring('01/01/2008',9,2)+substring('01/01/2008',1,2)
and substring('06/30/2008',9,2)+substring('06/30/2008',1,2)
      group by box_loc, rptdate
      order by rptdate

ee_rlee

hi, try this

select  substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy
        ,SUM(CASE WHEN box_loc = 'SMCBUYR' THEN 1 ELSE 0 END) AS 'SMCBUYR' 
from asrp_rpt_helper LEFT JOIN a_smc on rptdate = substring(date_rcvd,9,2)+substring(date_rcvd,1,2)
where rptdate between substring('080101',1,4)  and substring('080630',1,4)
group by rptdate 
order by rptdate

Open in new window

mahpog

ASKER

ee_RLEE,

no PRIZE yet. I ran your sql and I got 0 in the results set again. I should at least have 6 count for the month of april 2008. I definitely appreciate your time.

sql3.bmp

mahpog

ASKER

In my logic do I need to convert the stored date in my sybase sql table since I use smalldatetime? I do not think so, but I thought the coding would work.

mahpog

ASKER

I was reviewing my last code logic....

and I did a select on these two lines of code (with original (9,2) and (1,2) and I got the following result:
00       Ap
00       Ap
.....
my date_rcvd is stored as a smalldatetime in my sybase table, when I changed the code to 10,2 and 1,3 I get the following:

select substring(date_rcvd,10,2) as year,
substring(date_rcvd,1,3) as month
from a_smc

08       Apr
08       Apr
....

How do I convert my date_rcvd stored in smalldatetime so that I can get 0801, 0802,....

I am starting to think that is why I keep getting 0 back for the totals because
from asrp_rpt_helper LEFT JOIN a_smc on rptdate = substring(date_rcvd,9,2)+substring(date_rcvd,1,2)
is not coming back with a valid date to use.

If I can get this part corrected, i am betting my results will work.

ASKER CERTIFIED SOLUTION

mahpog

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial