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).
sql2.jpg
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
*************************************************************
sql2.jpg
Sybase DatabaseSQL
Last Comment
mahpog
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
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
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
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.
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)+s
is not coming back with a valid date to use.
If I can get this part corrected, i am betting my results will work.
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)+s
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
select box_loc, substring(rptdate,3,2)+'/2
(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)+s
and rptdate between substring('01/01/2008',9,2
and substring('06/30/2008',9,2
group by box_loc, rptdate
order by rptdate