Solved

Sybase SQL Snytax - take counts for daily into month count

Posted on 2009-07-15
13
638 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:mahpog
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 11

Assisted Solution

by:dougaug
dougaug earned 150 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:alpmoon
Comment Utility
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
 

Author Comment

by:mahpog
Comment Utility
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
 
LVL 13

Accepted Solution

by:
alpmoon earned 350 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:alpmoon
Comment Utility
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
 

Author Comment

by:mahpog
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 13

Expert Comment

by:alpmoon
Comment Utility
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
 
LVL 11

Expert Comment

by:dougaug
Comment Utility
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
 
LVL 11

Assisted Solution

by:dougaug
dougaug earned 150 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:IncisiveOne
Comment Utility
> 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
 

Author Comment

by:mahpog
Comment Utility
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
 

Author Closing Comment

by:mahpog
Comment Utility
solutions were good but the manner of data storage is the problem.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now