Sybase SQL Snytax - take counts for daily into month count

I have a table with records generated each day of each month. i want to gather the records generated for one month and tally into a monthly total, and do for a range specified by user to generate a trend chart with the resulting data.

my syntax is not working for the second part.  I already include a means to generate buckets by month.

See attachment for outputs and 2nd one showing desire result
1st query:
 
select count(rec_id) rec_id, entry_dt
FROM gcars_details
where convert(char(06),convert(datetime,entry_dt),12) between '#session.cstartdate#' and '#session.cenddate#'
group by entry_dt
 
***
translates:
select count(rec_id) rec_id, entry_dt
FROM gcars_details
where convert(char(06),convert(datetime,entry_dt),12) between '090101' and '090731'
group by entry_dt
 
 
 
*******
2nd query:
 
select substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy, isnull(count(entry_dt),0) cnt 
from gcars_details, asrp_rpt_helper 
where convert(datetime,entry_dt) between convert(datetime,'#session.cstartdate#') and convert(datetime,'#session.cenddate#')
and rptdate *= substring(entry_dt,9,2)+substring(entry_dt,1,2)
and rptdate between substring('#session.cstartdate#',1,4)  and substring('#session.cenddate#',1,4)
group by rptdate 
order by rptdate
 
translates :
 
select substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy, isnull(count(entry_dt),0) cnt 
from gcars_details, asrp_rpt_helper 
where convert(datetime,entry_dt) between convert(datetime,'090101') and convert(datetime,'090731')
and rptdate *= substring(entry_dt,9,2)+substring(entry_dt,1,2)
and rptdate between substring('090101',1,4)  and substring('090731',1,4)
group by rptdate 
order by rptdate

Open in new window

counts.jpg
mahpogAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
alpmoonConnect With a Mentor Commented:
I am not sure where the problem is, but I think you can use this instead without using helper table:

select substring(entry_dt,1,2)+substring(entry_dt,7,4) mmyy, isnull(count(*),0) cnt
from gcars_details
where convert(datetime,entry_dt) between convert(datetime,'090101') and convert(datetime,'090731')
group by substring(entry_dt,1,2)+substring(entry_dt,7,4)
order by 1
0
 
dougaugConnect With a Mentor Commented:
What are the columns datatypes you are using?

It seems that some "date fields" are strings... If this is true, what are the format of these dates?

Regards,
0
 
alpmoonCommented:
You should put the expression in select clause into group by. Otherwise count wouldn't work as you expected:

group by substring(rptdate,3,2)+'/20'+substring(rptdate,1,2)
order by 1
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
mahpogAuthor Commented:
The date fields are strings char(10)  in the table I am pulling from. Example, 2/17/2006

I made the group by and shown above, the 2nd query runs but comes out with same result. as priginal posted.
**********************

select substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy, isnull(count(entry_dt),0) cnt
from gcars_details, asrp_rpt_helper
where convert(datetime,entry_dt) between convert(datetime,'090101') and convert(datetime,'090731')
and rptdate *= substring(entry_dt,9,2)+substring(entry_dt,1,2)
and rptdate between substring('090101',1,4)  and substring('090731',1,4)
group by substring(rptdate,3,2)+'/20'+substring(rptdate,1,2)
order by 1

*********
1st query I am counting occurrences on an entry date. 2nd query , goal is to summarize by month.  the rptdate is being used to create a value for each month, and limit output based on date range requested.

0
 
alpmoonCommented:
By the way, I have noticed that your day and month values looks not zero filled on the left. If it is the case second query doesn't work in either way. Can you confirm that they actually have zeros like '02/06/2009'. Otherwise you should write the query very differently (by adding zeros or using datepart function).

0
 
mahpogAuthor Commented:
alpmoon,

I used your sytnax above and got better results which i attach. I now have a problem with the entry_dt getting converted properly into a recognizable date format. For example 03 has two but gets counted seperately because when the date gets generted, i have 3/30/2009 and 3/9/2009. This causes the problem. I guess I need to more editing to the entry_dt.



code based off your example (I added convert to try to fix error):
 
select substring(entry_dt,1,2)+substring(entry_dt,7,4) mmyy, isnull(count(*),0) cnt
from gcars_details
where convert(char(06),convert(datetime,entry_dt),12) between convert(datetime,'#session.cstartdate#') and convert(datetime,'#session.cenddate#')
group by substring(entry_dt,1,2)+substring(entry_dt,7,4)
order by 1

Open in new window

query-results.jpg
0
 
alpmoonCommented:
Yeah,  it looks missing zeros are the actual problem. Another option is using datepart function along with convert:

select datepart(mm, convert(date, entry_dt)) mm, datepart(yy,convert(date,entry_dt) yy, isnull(count(*),0) cnt
from gcars_details
where convert(datetime,entry_dt) between convert(datetime,'090101') and convert(datetime,'090731')
group by datepart(mm, convert(date, entry_dt)), datepart(yy,convert(date,entry_dt)
order by 2, 1

Note: For older ASE versions you should use datetime instead of date
0
 
dougaugCommented:
Hi

try to execute this query.

I hope this help, regards.

Douglas

select substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy, isnull(count(entry_dt),0) cnt 
from gcars_details, asrp_rpt_helper 
where convert(datetime,entry_dt) between convert(datetime,'#session.cstartdate#') and convert(datetime,'#session.cenddate#')
and rptdate *= substring(convert(char(4), datepart(yy, convert(datetime, @entry_dt))), 3, 2) + 
               substring(convert(char(3), 100 + datepart(mm, convert(datetime, @entry_dt))),2,2)
and rptdate between substring('#session.cstartdate#',1,4)  and substring('#session.cenddate#',1,4)
group by rptdate 
order by rptdate

Open in new window

0
 
dougaugConnect With a Mentor Commented:
Hi again,

change @entry_dt by entry_dt.

Regards,
select substring(rptdate,3,2)+'/20'+substring(rptdate,1,2) mmyy, isnull(count(entry_dt),0) cnt 
from gcars_details, asrp_rpt_helper 
where convert(datetime,entry_dt) between convert(datetime,'#session.cstartdate#') and convert(datetime,'#session.cenddate#')
and rptdate *= substring(convert(char(4), datepart(yy, convert(datetime, entry_dt))), 3, 2) + 
               substring(convert(char(3), 100 + datepart(mm, convert(datetime, entry_dt))),2,2)
and rptdate between substring('#session.cstartdate#',1,4)  and substring('#session.cenddate#',1,4)
group by rptdate 
order by rptdate

Open in new window

0
 
IncisiveOneCommented:
> The date fields are strings char(10)  in the table
> By the way, I have noticed that your day and month values looks not zero filled on the left
> I now have a problem with the entry_dt getting converted properly into a recognizable date format
1  You are breaking simple cardinal database design rules, by storing a date in anything other than a date, datetime, or smalldatetime column.   The consequences of breaking such rules will show up everywhere, as per this question.  If you had correct columns, your coding problem would be eliminated.

2  Secondly, in your manufactured "date" column, char(10), which your app controls, you have garbage.  The content of the column is only as good as the app (if it were a date datatype, the server would control it, not your app).  The old term "garbage in, garbage out" can be seen here vividly.  If you correct your data in your manufactured column; never let garbage into the database, your coding problems will be eased (the code will not have to work around the garbage).

3  More important, until then, you will never get consistent result set that the user can be confident of.  As the garbage gets cleaned up or dirtied further, without new data being added, the result set changes.  Not a "database" by any means.

4  There will be no end to "code that used to work but now doesn't".  The error is not in the code, it is in the database definition.

Cheers
0
 
mahpogAuthor Commented:
To all, thanks for the assistance. I am in agreement with all of you. The sybase table storing the date as a char is the culprit. I thought the same and attempted using datepart and convert to get around it. But with the same results as you all. I guess I need to see if I can get the originator of the table I am pulling from to do a conversion to the date field into a proper date format. Otherwise, the count is never going to function properly. Boy, thank goodness I never defined my dates as character, and I cannot image what the benefit is for doing this.


0
 
mahpogAuthor Commented:
solutions were good but the manner of data storage is the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.