Grouping pblm

Hi,

I have a query which returns several data fields and one of the grouping criteria is the time.
The data format for the time field resembles this: "10.11.2005 15:45:37" .. which is 'date.month.year calltime'.
I want the query to group data by the hour, therefore i wrote the query like this: Left([AllCalls.CALLTIME],13) AS HourlyCallTime, the HourlyCallTime field shows the data in this format: 10.11.2005 15, however, the grouping is not done. It only groups properly when I do this: Left([AllCalls.CALLTIME],12) AS HourlyCallTime, but then the problem is that the HourlyCallTime field does not show the proper format, it only displays this much: '10.11.2005 1'

I hope some1 can help me out :)

ThksI speak Oracle SQL; this is, as far as I can tell, not a language I know, but perhaps this piece of advice will help you ...

In Oracle, date format you wrote as an example (10.11.2005 15:45:37) is only one (of many possible) representations of a date column. Dates are stored as a number, and it is up to the developer to choose format he wants to present data to the end user.

Now, in Oracle, you should do this: FIRST format date column to desired format, and THEN write string functions on it.

For example, it would look like this:
- First part of the solution:
SELECT TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss') FROM ...

- Second part of the solution:
SELECT SUBSTR(TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss'), 1, 13) FROM ...

I really wouldn't know is this the case in your database, but - if nothing else shows up - you could try with this.|||Hello Littlefoot,

Thks 4 the quick rep, i've been working on it but now seem to be having sum other pblm,
my query

SELECT Zones.Zone, Left([AllCalls.CALLTIME],13) AS HourlyCallTime, SCCount.Connected, ((SCCount.Connected/(UCCount.NotConnected+SCCount.Connected)*100)) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone] And (AllCalls.PREFIX)=[SCCount].[PREFIX] And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone, Left([AllCalls.CALLTIME],13), SCCount.Connected, UCCount.NotConnected, AllCalls.PREFIX;

is not grouping all the calls, it's separating the connected and not connected such that am having twice the same row of data.
When I remove the SCCount.Connected and UCCount.NotConnected, it doesn't run, comes up with "query does not include the specified xpression .."

:S|||Hm, it seems that you, actually, do not want to GROUP data, but BREAK output on the hour. I'd say that use of a GROUP BY is meaningless if there's no aggregate function (such as MAX or AVG or COUNT) in the SELECT statement.

I don't know the tool you use (do you run this query on command prompt or in a reporting tool); if it is some kind of a report builder, you might want to use master-detail blocks of data.

On command prompt, all you can do is use of an ORDER BY clause and, eventually, use of (as Oracle provides) some kind of a BREAK command which will visually break data on the screen. Something like this:SQL> break on hire_year
SQL> select to_char(hiredate, 'yyyy') hire_year, ename, hiredate
2 from emp
3 order by 1, 2;

HIRE ENAME HIREDATE
-- ---- ---
1980 SMITH 17.12.80
1981 ALLEN 20.02.81
BLAKE 01.05.81
CLARK 09.06.81
FORD 03.12.81
JAMES 03.12.81
JONES 02.04.81
KING 17.11.81
MARTIN 28.09.81
TURNER 08.09.81
WARD 22.02.81
1982 MILLER 23.01.82

12 rows selected.

SQL>|||But I do want to group the data by zones and by the hour, but the query wouldn't run unless i include the connected n notconnected as part of the grouping criteria as well which is messing it up|||I assume Connected and NotConnectedand are some times and you calculete percentage. So if you group data by Zones etc. why don't you summarize times?

SELECT
Zones.Zone,
Left([AllCalls.CALLTIME],13) AS HourlyCallTime,
sum(SCCount.Connected),
((sum(SCCount.Connected)/(sum(UCCount.NotConnected+SCCount.Connected))*100) ) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone]
And (AllCalls.PREFIX)=[SCCount].[PREFIX]
And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone,
Left([AllCalls.CALLTIME],13),
AllCalls.PREFIX;

BTW you have too many brackets there. It's MS Access generated code, isn't it? :-) Why don't you select AllCalls.PREFIX if you group by it?|||Connected and notconnected are the count result from another query, and access wont let me run the query unless I have 'SCCount.Connected' and 'UCCount.NotConnected' in the grouping criteria ..

Yes, MS Access keeps adding loads of brackets when i run the query and go bak 2 sql view :Ssql

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping by N minute intervals

I have a query that returns full date/time, hour, and minute, and other
stuff. The report needs to be grouped by various minute intervals, but I am
having difficulty getting it grouped by any interval period.
How do I group my data in 15 Minute Intervals. Is it similar to group by 30
Minute Intervals? I am making the presumption that a variable can be used to
allow the user to change the interval from the values of 15, 20, and 30
Minutes.
I am using SQL Server to fetch the data, and have access to the query code,
so if any suggestions involve something on the Query end instead of the
report end, I can do that too.Rob,
Either you have to use analysis services or create intervals using SQL in
the dataset. I had a smiliar problem with sales reports. In some of the
months we didn't have any sales for a particular product. The reports
instead of showing zero sales, they were not showing up at all. So, I
created zero sales for every product, for a certain period of time - on the
fly and sum group them with actual sales.
Hope this helps.
Regards,
Cem
"Rob 'Spike' Stevens" <RobSpikeStevens@.discussions.microsoft.com> wrote in
message news:D54A54E9-EE31-48A2-8318-2C891F1B533C@.microsoft.com...
> I have a query that returns full date/time, hour, and minute, and other
> stuff. The report needs to be grouped by various minute intervals, but I
am
> having difficulty getting it grouped by any interval period.
> How do I group my data in 15 Minute Intervals. Is it similar to group by
30
> Minute Intervals? I am making the presumption that a variable can be used
to
> allow the user to change the interval from the values of 15, 20, and 30
> Minutes.
> I am using SQL Server to fetch the data, and have access to the query
code,
> so if any suggestions involve something on the Query end instead of the
> report end, I can do that too.

Group by statement problem

I am using the T-SQL code below to pull patient information. The code returns 86 rows, however, there are only 9 distinct account numbers. Why is the group by statement not grouping these together to only display the 9 distinct accounts and associated data?

select
srm.episodes.episode_type as Visit_Type,
srm.episodes.account_number as Account_Number,
srm.episodes.medrec_no as MRN,
dbo.PtMstr.PatientFullName,
left(srm.episodes.admission_date,11) as Admit_Date,
left(srm.episodes.episode_date,11) as Disch_Date,
dbo.PtMstr.Cases as Cases,
dbo.PtMstr.TotCharges,
srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,
srm.cdmab_base_info.adm_dx_adt as Admitting_Dx
,srm.event_types.event_type_code
from srm.cdmab_base_info inner join
srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key
inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key
inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key
inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number
where srm.cdmab_base_info.abst_cmp_status <> 'Y'
and srm.episodes.episode_date is not null
and srm.event_types.event_type_code <> 'ACOD'
AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate
AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)
Group By srm.episodes.account_number,
dbo.PtMstr.TotCharges,
srm.episodes.episode_type,
srm.episodes.medrec_no,
dbo.PtMstr.PatientFullName,
srm.episodes.admission_date,
srm.episodes.episode_date,
dbo.PtMstr.Cases,
srm.cdmab_base_info.abst_cmp_status,
srm.cdmab_base_info.adm_dx_adt,
srm.event_types.event_type_code

Use the following query..

select

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

Group By

srm.episodes.account_number,

dbo.PtMstr.TotCharges,

srm.episodes.episode_type,

srm.episodes.medrec_no,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases,

srm.cdmab_base_info.abst_cmp_status,

srm.cdmab_base_info.adm_dx_adt,

srm.event_types.event_type_code

|||

I had to remove the AS portion of the group by clause to get the code to work , however, it still returns 86 rows versus the expected 9 distinct rows.

|||

How you know there is only 9 distinct record. You only the get the number of rows as per the following query..& i didn't understand your requirement on your query(there is no group by funcations used).

select Distinct

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

|||

I appreciate you help. I ordered the data by account number and saw there were 9 distinct account numbers. I also noticed the srm.event_types.event_type_code field should not have been in this query; once I removed it, the code returned the expected 9 rows of data using either of the examples you provided. Thanks again for your assistance.

|||

You are grouping several additional columns after the account number.

If you just want the nine accounts listed, you'll need to just group on that column.

Then you can apply aggregates to get sums, etc. of the other data you desire.

Group by MonthEnd

I have the following query which returns a sum of hours for each month
for a specific year.
select month(tDate) as month, sum(hours) as total from tablename
where Year(tDate) = 2005
group by month(tDate)
order by month
This works ok, however, is there a way to specify the month start date
and month end date for the grouping, much like it's possible to set the
first day of the w by setting DateFirst to 1=Monday?
With the query above, it totals hours from the 1st of the month to the
last day of the month. However, our month end date is the last Sunday
of the month and it's start date is the last Sunday of the previous
month + a day (i.e. Monday)
For example, our total hours for 2005 each month should be calculated
from the following dates:-
Jan: Mon 27 Dec 04 - Sun 30 Jan 05
Feb: Mon 31 Jan 05 - Sun 27 Feb 05
Mar: Mon 28 Feb - Sun 27 Mar
Apr: Mon 28 Mar - Sun 24 Apr
May: Mon 25 Apr - Sun 29 May
Jun: Mon 30 May - Sun 26 Jun
Jul: Mon 27 Jun - Sun 31 Jul
Aug: Mon 1 Aug - Sun 28 Aug
Sep: Mon 29 Aug - Sun 25 Sep
Oct: Mon 26 Sep - Sun 30 Oct
Nov: Mon 31 Oct - Sun 27 Nov
Dec: Mon 28 Nov - Sun 25 Dec
I suppose ideally i would need some kind of query to calculate the
monthly period based on the date and group it by that.
Any suggestions are most appreciated.
Thanks in advance.
Dan Williams.dan_williams@.newcross-nursing.com wrote:
> I have the following query which returns a sum of hours for each month
> for a specific year.
> select month(tDate) as month, sum(hours) as total from tablename
> where Year(tDate) = 2005
> group by month(tDate)
> order by month
> This works ok, however, is there a way to specify the month start date
> and month end date for the grouping, much like it's possible to set
> the first day of the w by setting DateFirst to 1=Monday?
>
I think your best course of action is to use an auxiliary calendar table, as
suggested in this article:
http://www.aspfaq.com/show.asp?id=2519
The benefit of using a calendar table as opposed to calculations is that it
will allow indexes on your date fields to be used, resulting in much
better-performing queries.
An auxiliary Numbers table can also be useful in other situations:
http://www.aspfaq.com/show.asp?id=2516
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||That's great. Think this will prove useful in a lot of my existing
code.
I mangaged to create a new calendar table with additional columns to
store a target month and target year column based on each date. I can
then do an inner join on this table based on my dates and group by the
target month.
I'm not entirely convinced of using additional memory to store a
numbers table, but if I come across any additional use of it, i'll be
sure to post. (I have since dropped the numbers table!)
Thanks for you help
Dan

GROUP By clause or DISTINCT clause

Hi, can anyone shed some light on this issue?

SELECT Status from lupStatus
with a normal query it returns the correct recordcount

SELECT Status from lupStatus GROUP BY Status
but with a GROUP By clause or DISTINCT clause it return the recordcount
= -1Which application are you using? If it is VB then it shold be client
side cursor

Madhivanan|||Madhivanan, thanks for your response, we are not using client side
cursor, but server side (the query work fine, adLockReadonly but it
doesn't work when we use adLockOptimistic) VB COM|||Jacek (jack.pedzikiewicz@.gmail.com) writes:
> Madhivanan, thanks for your response, we are not using client side
> cursor, but server side (the query work fine, adLockReadonly but it
> doesn't work when we use adLockOptimistic) VB COM

So the count we are talking about is Recordset.RecordCount?

What cursor type do you ask for? A query with GROUP BY or DISTINCT
can only result in a static or forward-only cursor, and it cannot
be updatable. A static cursor has a rowcount, a forward-only cursor
has not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Group By - Count returns no rows

Hi everyone:

I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?

SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2

This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.

Thanks for any help.

Since its just a count, you can check for @.@.ROWCOUNT after the SELECT. Or get the count into a variable which is set to 0 by default. so your variable will always have a value.

|||

Interesting problem, I too thought at first glance that it would give you 1 row with value 0, but I tried it on one of my tables with the same results.

I think the problem is that WHERE clause, since there are no instances of Field2 that satisfy the GROUP BY there can be no rows returned at all.

I think the Dinakar is right, you'll have to check @.@.rowcount or something

|||

IF EXISTS( {Your query} ) BEGIN {Your query} END ELSE BEGIN SELECT 0,NULL AS Field2 END

|||

Thanks Motley, I came to this solution too but I thought that a better one could show up, sometimes you expect something more complicated and "elegant" shows up in the way, when the simple solution is in fact the way out of it. Thanks again!