Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>sql

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 and summing

I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):

date prod class qty
060101 a101 1a 100
060101 a101 1a 100

I would like to have the following:

date prod class qty

060101 a101 1a 200

Any other suggestions would be greatful!!
Thanks in advance

the query to return your desired result would look something like this...

select date, prod, class, sum(qty)

from YourTable

group by date, prod, class

thus what you are saying in this query is aggregate the qty per date, prod, class. So if any of these values are different a new record is created. Thus the same product with two diff. class values would result in two records.

HTH,

Derek

|||Thanks for your help Derek!! I was putting the sum and group opposite of what you said.|||no prob dude, take it easy.

Group Format

Hi,
I want to set my group's background to different color. something like
Group1
[Group1-A] [BGColor=Blue]
[Group1-B] [BGColor=Green]
[Group1-C] [BGColor=Red]
Group2
[Group2-A] [BGColor=Blue]
[Group2-B] [BGColor=Green]
[Group2-C] [BGColor=Red]
any clue ?You can use an expression to set the group's background color. You have two
choices: iif() or swtich().
The iif() syntax is a resonable choice when there are a couple of options.
Switch() will be easier if you have a large number of options.
=iif(Fields!<FieldName.Value> = "Value1", "Green", "Blue")
=Switch(Fields!<FieldsName>.Value = "Value1", "Green",
Fields!<FieldsName>.Value = "Value2", "Blue", ...)
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ameet" <Ameet@.discussions.microsoft.com> wrote in message
news:1008088F-1FC6-4625-9C1A-EF4096D0FB0C@.microsoft.com...
> Hi,
> I want to set my group's background to different color. something like
> Group1
> [Group1-A] [BGColor=Blue]
> [Group1-B] [BGColor=Green]
> [Group1-C] [BGColor=Red]
> Group2
> [Group2-A] [BGColor=Blue]
> [Group2-B] [BGColor=Green]
> [Group2-C] [BGColor=Red]
> any clue ?

Group by problem!

I need to use Group By on a text column that contain numeric data in
different format.
For example, column "terminalID" can contain "0123" and '123' pointing to
the same terminal.
Group by treats these as 2 different groups.
I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got the
same problem since I cannot use CAST in Group By clause.
Any help is greatly appreciated!
Bill
select CAST(terminalID as INT) as terminalID
from table A
group by terminalID> I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got
> the same problem since I cannot use CAST in Group By clause.
CAST is allowed in a GROUP BY clause. The example below should work,
assuming terminalID is always numeric.
SELECT CAST(terminalID AS int) AS terminalID
FROM MyTable
GROUP BY CAST(terminalID AS int)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:O3Ud$mBBGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I need to use Group By on a text column that contain numeric data in
>different format.
> For example, column "terminalID" can contain "0123" and '123' pointing to
> the same terminal.
> Group by treats these as 2 different groups.
> I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got
> the same problem since I cannot use CAST in Group By clause.
> Any help is greatly appreciated!
> Bill
>
> select CAST(terminalID as INT) as terminalID
> from table A
> group by terminalID
>
>|||>> I need to use Group By on a text column that contain numeric data in diff
erent format. <<
Well, that is realllllly screwed up! In the RDBMS model, unlike 1950's
COBOL, there is no formatting in the database. We have abstract data
types. Look at the possible Numeric data types in any basic book on
SQL.
Gee, those are strings and not numeric at all! This is sooo basic.
1) Find the moron that did this schema and kill him. It will greatly
improve data quality.
2) Now, decide if this column is a string or a numeric; change the
table to reflect this decision; add a CHECK() constraint mto enforce
the correct format. Go thru all of your code and clean it up.
3) Read any book on SQL programming.|||Sounds like it is possible that the text column has 0s for padding when it
is created. Prior to Casting perhaps you could use a substring or trim
function to clean up the data. You might have to put the data in a temp
table are requery it so that you query the clean stuff.
Keith
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:O3Ud$mBBGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I need to use Group By on a text column that contain numeric data in
>different format.
> For example, column "terminalID" can contain "0123" and '123' pointing to
> the same terminal.
> Group by treats these as 2 different groups.
> I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got
> the same problem since I cannot use CAST in Group By clause.
> Any help is greatly appreciated!
> Bill
>
> select CAST(terminalID as INT) as terminalID
> from table A
> group by terminalID
>
>|||Thank you all.
Dan's statement below solved the problem.
Celko's comments are seriously considered
Bill
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uRKfgtBBGHA.240@.TK2MSFTNGP11.phx.gbl...
> CAST is allowed in a GROUP BY clause. The example below should work,
> assuming terminalID is always numeric.
> SELECT CAST(terminalID AS int) AS terminalID
> FROM MyTable
> GROUP BY CAST(terminalID AS int)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:O3Ud$mBBGHA.1028@.TK2MSFTNGP11.phx.gbl...
>

Group by Month

Hi All:
I have a report that has a field named CloseDate which has a date format. I
want to be able to group together all records by month. (i.e Sept, October
etc.)
If I elect to group by the CloseDate, my records are grouped by each
individal date rather than the month the date occurs.
How can I achieve this?
Thanks
Brennanin your grouping and sorting properties, edit your group on expression like
this:
=Microsoft.VisualBasic.DatePart("m",Fields!Date_Name.Value)
in the cell values for any totals be sure to use SUM(Fields!Date_Name.Value)
and you also probably want to format how your date column is displayed:
=Format(CDATE(Fields!Date_Name.Value),"MM-yyyy")
"Brennan" wrote:
> Hi All:
> I have a report that has a field named CloseDate which has a date format. I
> want to be able to group together all records by month. (i.e Sept, October
> etc.)
> If I elect to group by the CloseDate, my records are grouped by each
> individal date rather than the month the date occurs.
> How can I achieve this?
> Thanks
> Brennan

group by date only

Hi all,
I have a field that has datetime datatype. its data contain hour and minute.
how can I select it and format it as only "mm/dd/yyyy"? I don't want hour
and minute. In short I want to group by date. Something like 1/1/2002 as a
group, 1/2/2003 as a group.
Thanks,
eguyWill something like this work?
USE Northwind
select convert(char(12), OrderDate, 101) date, count(*) NumOrders
from Orders
group by convert(char(12), OrderDate, 101)
order by 1
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"*eguy" <hstandit@.yahoo.com> wrote in message
news:#cfzlW7jDHA.1004@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have a field that has datetime datatype. its data contain hour and
minute.
> how can I select it and format it as only "mm/dd/yyyy"? I don't want hour
> and minute. In short I want to group by date. Something like 1/1/2002 as a
> group, 1/2/2003 as a group.
> Thanks,
> eguy
>|||It works.
Thank you very much.
eguy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%236QJda7jDHA.2580@.TK2MSFTNGP10.phx.gbl...
> Will something like this work?
> USE Northwind
> select convert(char(12), OrderDate, 101) date, count(*) NumOrders
> from Orders
> group by convert(char(12), OrderDate, 101)
> order by 1
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "*eguy" <hstandit@.yahoo.com> wrote in message
> news:#cfzlW7jDHA.1004@.TK2MSFTNGP09.phx.gbl...
> >
> > Hi all,
> >
> > I have a field that has datetime datatype. its data contain hour and
> minute.
> > how can I select it and format it as only "mm/dd/yyyy"? I don't want
hour
> > and minute. In short I want to group by date. Something like 1/1/2002 as
a
> > group, 1/2/2003 as a group.
> > Thanks,
> >
> > eguy
> >
> >
>

Grid display

How would I take a bunch of records and make it display in a Grid Format
(actually going to go into a DataGrid) in one select statement - or can you?
If I have 12 records:
CREATE TABLE [dbo].[Rentals] (
[RentalID] [int] IDENTITY (1, 1) NOT NULL ,
[NumberOfDays] [int] NULL ,
[NumberOfRentals] [int] NULL ,
[RentalCost] [money] NULL
) ON [PRIMARY]
GO
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,1,100)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,5,400)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,10,800)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,20,1600)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,1,175)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,5,700)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,10,1400)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,20,2800)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,1,225)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,5,900)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,10,1750)
insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,20,3500)
And I want it to display like so (with or without the headings) where the
number of days is in the parenthesis:
Type: Individual: Bundle (5) Bundle (10) Bundle (20)
30 Day $100 $400 $800 $1,600
60 Day $175 $700 $1,400 $2,800
90 Day $225 $900 $1,750 $3,500
The rows are grouped by days and the columns are ordered by NumberOfDays,
NumberOfRentals.
I could read them record by record and then place them into the grid, but I
would prefer to let the Select order it for me.
Thanks,
TomAs Tom says in a message a few hours ago, thanks for the DDL. It made it
easy to help you. Generally speaking it is usually suggested to do this in
the UI, not use SQL to manipulate the dat to fit the UI. On the other hand,
if you are talking small load it is fine to do it this way:
select numberOfDays,
sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
Individual,
sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
[Bundle(5)],
sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
[Bundle(10)],
sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
[Bundle(20)]
from rentals
group by numberOfDays
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e8xikzRpFHA.1444@.tk2msftngp13.phx.gbl...
> How would I take a bunch of records and make it display in a Grid Format
> (actually going to go into a DataGrid) in one select statement - or can
> you?
> If I have 12 records:
> CREATE TABLE [dbo].[Rentals] (
> [RentalID] [int] IDENTITY (1, 1) NOT NULL ,
> [NumberOfDays] [int] NULL ,
> [NumberOfRentals] [int] NULL ,
> [RentalCost] [money] NULL
> ) ON [PRIMARY]
> GO
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,1,100)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (30,5,400)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (30,10,800)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (30,20,1600)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,1,175)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (60,5,700)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (60,10,1400)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (60,20,2800)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,1,225)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values (90,5,900)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (90,10,1750)
> insert Rentals (NumberOfDays,NumberOfRentals,RentalCost
) values
> (90,20,3500)
> And I want it to display like so (with or without the headings) where the
> number of days is in the parenthesis:
> Type: Individual: Bundle (5) Bundle (10) Bundle (20)
> 30 Day $100 $400 $800 $1,600
> 60 Day $175 $700 $1,400 $2,800
> 90 Day $225 $900 $1,750 $3,500
> The rows are grouped by days and the columns are ordered by NumberOfDays,
> NumberOfRentals.
> I could read them record by record and then place them into the grid, but
> I would prefer to let the Select order it for me.
> Thanks,
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OBWQYFTpFHA.2152@.TK2MSFTNGP14.phx.gbl...
> As Tom says in a message a few hours ago, thanks for the DDL. It made it
> easy to help you. Generally speaking it is usually suggested to do this
in
> the UI, not use SQL to manipulate the dat to fit the UI. On the other
hand,
> if you are talking small load it is fine to do it this way:
> select numberOfDays,
> sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
> Individual,
> sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
> [Bundle(5)],
> sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
> [Bundle(10)],
> sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
> [Bundle(20)]
> from rentals
> group by numberOfDays
That would work great, but is there a way to do this by separating it by the
grouping. In otherwords, I don't know that it will always be 5, 10 and 20.
It might be some other grouping so I would like to do it where I am not
doing an "= 1", "= 2" type of scenario.
My boss might change it 6 months from now and have a bundle of 15.
Thanks,
Tom
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e8xikzRpFHA.1444@.tk2msftngp13.phx.gbl...
(30,1,100)
(30,5,400)
(60,1,175)
(60,5,700)
(90,1,225)
(90,5,900)
the
(20)
NumberOfDays,
but
>|||The only way is to use dynamic sql. You would automate the select clause
from the values in the table. Personally if the change is very seldom I
would just make it something that you change whenever it changes in the
table as it will take you longer to make this change than it will to hard
code the values five or six times, including testing.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tfs@.dslextreme.com> wrote in message
news:ehlDmvUpFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OBWQYFTpFHA.2152@.TK2MSFTNGP14.phx.gbl...
> in
> hand,
> That would work great, but is there a way to do this by separating it by
> the
> grouping. In otherwords, I don't know that it will always be 5, 10 and
> 20.
> It might be some other grouping so I would like to do it where I am not
> doing an "= 1", "= 2" type of scenario.
> My boss might change it 6 months from now and have a bundle of 15.
> Thanks,
> Tom
> --
> (30,1,100)
> (30,5,400)
> (60,1,175)
> (60,5,700)
> (90,1,225)
> (90,5,900)
> the
> (20)
> NumberOfDays,
> but
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uXsuKCcpFHA.2904@.TK2MSFTNGP12.phx.gbl...
> The only way is to use dynamic sql. You would automate the select clause
> from the values in the table. Personally if the change is very seldom I
> would just make it something that you change whenever it changes in the
> table as it will take you longer to make this change than it will to hard
> code the values five or six times, including testing.
The problem is that this is one we are using and there are other companies
that will use the system that may not use the Bundles we are using so it
would not be just one change.
How would you use Dynamic Sql to do this?
This will be read into a DataGrid, and it would be easy to make the columns
visible/invisible based on the number of columns that are returned.
Thanks,
Tom
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tfs@.dslextreme.com> wrote in message
> news:ehlDmvUpFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||I also tried to add in the Rental ID to select statement and can't make it
work with the column titles. I tried using the titles from the "as column",
but got an error in the Group clause
I tried to change your statement to:
select numberOfDays,
single = case when numberOfRentals = 1 then rentalCost else 0 end,
bundle5 = case when numberOfRentals = 5 then rentalCost else 0 end,
bundle10 = case when numberOfRentals = 10 then rentalCost else 0 end,
bundle20 = case when numberOfRentals = 20 then rentalCost else 0 end,
singleID = case when numberOfRentals = 1 then rentalID else 0 end,
bundle5ID = case when numberOfRentals = 5 then rentalID else 0 end,
bundle10ID = case when numberOfRentals = 10 then rentalID else 0 end,
bundle20ID = case when numberOfRentals = 20 then rentalID else 0 end
from rentals
group by
numberOfDays,single,bundle5,bundle10,bun
dle20,singleID,bundle5ID,bundle10ID,
bundle20ID
and got:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'singleID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'bundle5ID'.
etc
I assumed you used the "sum" so you wouldn't have to list it in the "group"
clause (of course, I could be wrong here), as there is only 1 Rental Cost
for each NumberOfDays/NumberOfRentals.
Can I not use the title I set up in the select statement in the Group
clause?
thanks,
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7tHB7ypFHA.320@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uXsuKCcpFHA.2904@.TK2MSFTNGP12.phx.gbl...
> The problem is that this is one we are using and there are other companies
> that will use the system that may not use the Bundles we are using so it
> would not be just one change.
> How would you use Dynamic Sql to do this?
> This will be read into a DataGrid, and it would be easy to make the
> columns visible/invisible based on the number of columns that are
> returned.
> Thanks,
> Tom
>|||"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O2ZAZK0pFHA.3732@.TK2MSFTNGP09.phx.gbl...
>I also tried to add in the Rental ID to select statement and can't make it
>work with the column titles. I tried using the titles from the "as
>column", but got an error in the Group clause
> I tried to change your statement to:
> select numberOfDays,
> single = case when numberOfRentals = 1 then rentalCost else 0 end,
> bundle5 = case when numberOfRentals = 5 then rentalCost else 0 end,
> bundle10 = case when numberOfRentals = 10 then rentalCost else 0 end,
> bundle20 = case when numberOfRentals = 20 then rentalCost else 0 end,
> singleID = case when numberOfRentals = 1 then rentalID else 0 end,
> bundle5ID = case when numberOfRentals = 5 then rentalID else 0 end,
> bundle10ID = case when numberOfRentals = 10 then rentalID else 0 end,
> bundle20ID = case when numberOfRentals = 20 then rentalID else 0 end
> from rentals
> group by
> numberOfDays,single,bundle5,bundle10,bun
dle20,singleID,bundle5ID,bundle10I
D,bundle20ID
> and got:
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'singleID'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'bundle5ID'.
> etc
> I assumed you used the "sum" so you wouldn't have to list it in the
> "group" clause (of course, I could be wrong here), as there is only 1
> Rental Cost for each NumberOfDays/NumberOfRentals.
I was able to get it to work using your set and the sum statement. Not sure
if this is the best way, but it does work.
select numberOfDays,
sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
Individual,
sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
[Bundle(5)],
sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
[Bundle(10)],
sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
[Bundle(20)],
sum(case when numberOfRentals = 1 then rentalID else 0 end) as
IndividualID,
sum(case when numberOfRentals = 5 then rentalID else 0 end) as
[Bundle(5)ID],
sum(case when numberOfRentals = 10 then rentalID else 0 end) as
[Bundle(10)ID],
sum(case when numberOfRentals = 20 then rentalID else 0 end) as
[Bundle(20)ID]
from rentals
group by numberOfDays
thanks,
Tom
> Can I not use the title I set up in the select statement in the Group
> clause?
> thanks,
> Tom
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:u7tHB7ypFHA.320@.TK2MSFTNGP09.phx.gbl...
>|||You don't want it to be in the group, but it has to be part of an aggregate.
Hence the sum. As long as it doesn't hurt performance this is a fine way to
do it.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OGRUGV0pFHA.1464@.TK2MSFTNGP14.phx.gbl...
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O2ZAZK0pFHA.3732@.TK2MSFTNGP09.phx.gbl...
> I was able to get it to work using your set and the sum statement. Not
> sure if this is the best way, but it does work.
> select numberOfDays,
> sum(case when numberOfRentals = 1 then rentalCost else 0 end) as
> Individual,
> sum(case when numberOfRentals = 5 then rentalCost else 0 end) as
> [Bundle(5)],
> sum(case when numberOfRentals = 10 then rentalCost else 0 end) as
> [Bundle(10)],
> sum(case when numberOfRentals = 20 then rentalCost else 0 end) as
> [Bundle(20)],
> sum(case when numberOfRentals = 1 then rentalID else 0 end) as
> IndividualID,
> sum(case when numberOfRentals = 5 then rentalID else 0 end) as
> [Bundle(5)ID],
> sum(case when numberOfRentals = 10 then rentalID else 0 end) as
> [Bundle(10)ID],
> sum(case when numberOfRentals = 20 then rentalID else 0 end) as
> [Bundle(20)ID]
> from rentals
> group by numberOfDays
> thanks,
> Tom
>|||Isn't always the case?
As soon as I have it set up (as you suggested), it is necessary to make it
completely flexible (could be bundles of 17, 22, 80, etc). You just can't
win.
Tom
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uEm20w0pFHA.3064@.TK2MSFTNGP15.phx.gbl...
> You don't want it to be in the group, but it has to be part of an
> aggregate. Hence the sum. As long as it doesn't hurt performance this is
> a fine way to do it.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OGRUGV0pFHA.1464@.TK2MSFTNGP14.phx.gbl...
>