Solved

Sybase SQL Snytax - take counts for daily into month count

Posted on 2009-07-15
13
650 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
ID: 24864789
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
ID: 24865675
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
ID: 24869409
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 13

Accepted Solution

by:
alpmoon earned 350 total points
ID: 24875326
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
ID: 24875356
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
ID: 24879896
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
 
LVL 13

Expert Comment

by:alpmoon
ID: 24883645
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
ID: 24883774
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
ID: 24883780
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
ID: 24883797
> 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
ID: 24895226
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
ID: 31603978
solutions were good but the manner of data storage is the problem.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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