Link to home
Create AccountLog in
Avatar of mahpog
mahpog

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
Avatar of mahpog
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
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

Avatar of 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
Avatar of 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.
Avatar of 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
Avatar of mahpog
mahpog

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account