Firehose mode: users

Showing posts with label users. Show all posts

Friday, March 30, 2012

Grouping using two stored procedures

Hi,

I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.

Each report uses a different stored procedure.

Report 1 shows emails sent:

Sender: Person in our org
Recipient: Person outside our org
Number: number of emails sent

Report 2 shows emails recived:

Sender: person outside our org
Recipient: Person in our org
Number: Number of emails received

In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).

Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.

does anyone have any idea how this may be done?

i want the report to look like this:

Person in our organisation:
----------
Sent: bob@.yahoo.co.uk 26
sally@.hotmail.com 4
peter@.msn.com 12
Subtotal: 42

Received: fred@.company.co.uk 45
vicky@.hotmail.com 10
Subtotal 55

Total 97

and so on for each person.

Many thanks if you can helpEither create it as two subreports in Crystal, or use a UNION query to create a single dataset from both SQL statements. With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".|||With the UNION query, you will probably want to add a dummy values that indicates "SENT" or "RECEIVED".

how do i do this?

at the moment, the most i can come up with is

select * from vw_sent
union
select * from vw_received

and that's where my question comes from really, how to determine what addresses in each result are @.mydomain.co.uk and then to group by those. Becuase the resultset of this query is:

Sender Recipient Number
person@.mydomain.co.uk person@.hotmail.com 5
otherperson@.mydomain.co.uk person@.hotmail.com 2
otherperson@.hotmail.com person@.mydomain.co.uk 10

so how do i add an extra field in my resultset to show which email address is in my domain?|||select 'SENT' as Direction, * from vw_sent
union
select 'RECEIVED' as Direction, * from vw_received

...though you really should enumerate your field names instead of using *, especially in an UNION query.

Grouping problems

Hello all,

I am having a problem. See, I have a small table that tracks users visits to my site. And I want to have it show all the people visiting first on the bottom of the page, people just visiting on top. I can solve that with a simple ORDER BY DateVisited clause. My problem is that I also want to group peoples IP addresses together too. If I don't, when two people visit the website around the same time, browse around, I get overlapping of IPs.

Example how it is now:
1. 127.0.0.1 /default.asp 12:15PM
2. 215.394.293.494 /default.asp 12:16PM
3. 127.0.0.1 /contact/default.asp 12:17PM

Example of how I would like it:
1. 215.394.293.494 /default.asp 12:16PM
2. 127.0.0.1 /default.asp 12:15PM
3. 127.0.0.1 /contact/default.asp 12:17PM

I can ORDER BY IpAddress first, but then my dates are wacked out and the people with lower initial IP numbers appear first, which is meaningless. Any advise?Could you add these values to your sample data and show us everything sorted the way you want to see it?

127.0.0.1 /default.asp 11:00AM the next day
127.0.0.1 /default.asp 10:00AM the same day
125.5.5.5 /default.asp 11:00AM the same day
125.5.5.5 /default.asp 11:00PM the same day
215.394.293.494 /default.asp 11:00AM the next day

Terri|||Here is my current SQL statement, notice how I only show the results for 1 day at a time. That way, I just page back and forth through the days...

"SELECT * FROM PageViews WHERE (CONVERT(datetime, CONVERT(varchar, LastVisited, 101)) = '" & dDate & "')" AND Remote_Addr <> '" & User & "' ORDER BY LastVisited,Remote_Addr;"

Variables:
dDate = Date selected
User = IP Address

Does this help? What do you want me to do next? You've helped me before Terri so I know you have a good answer for me. :)|||OK, you are only dealing with one date at a time. That helps.

Where would these fit in -- how would your 6 returned rows be sorted?

127.0.0.1 /contact/default.asp 9:00AM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /default.asp 9:15PM

Terri
PS - glad I've been able to help in the past :-)|||It would return the results like so...

127.0.0.1 /default.asp 9:15PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM

which is not the wrong thing... the problem is when two IPs visit the website at the same time... then I have something crazy like this...

127.0.0.1 /default.asp 9:15PM
123.456.0.1 /default.asp 5:05PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM

I would prefer a look like this:
127.0.0.1 /default.asp 9:15PM
127.0.0.1 /contact/default.asp 9:00PM
127.0.0.1 /contact/default.asp 9:00AM
123.456.0.1 /default.asp 5:05PM

The 124.456.0.1 is listed last only because 127.0.0.1 visited a page last at 9:15PM. Had 127.0.0.1 not visited the two pages at 9:15PM and 9:00PM in the evening, he/she would actually be listed under 123.456.0.1 like so:
123.456.0.1 /default.asp 5:05PM
127.0.0.1 /contact/default.asp 9:00AM

Does this make any sense or am I just crazy?

Thanks,
B|||Anybody have any ideas? I am really looking forward to making this work!|||I am sorry, I lost site of this.

The problem is I am now completely confused on what you are looking for, because your latest examples seem to be in reverse chronological order, which they weren't at first.

Maybe someone else can make sense of it, or maybe you can make it clearer.

Terri|||I'd want to request for a clearer explaination of what you're trying to do. I'm a bit medicine head today, but reading this thread made me dizzy. Looking at your convert in the sql alone made me cringe.

Be a little more clear as to what you need, and let's hammer this one out.|||I'm sorry that everything seems to be confusing. I will try to explain everything over, and if I still don't make sense, it's probably just me and this crazy thing I'm trying to do. But thanks for looking...

Okay, so I record stats of people visiting my site. When somebody visits, it will record an entry and display it like so:

samplecableuser.comcast.net - 68.12.94.11 - /default.asp - 1 visit - 12:22pm

I turn around and format it in HTML to look similar to this:

samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
/default.asp (1 visit) at 11:22am

(notice how the first page that the person visited is towards the bottom, so as the person surfs on the site more, it will list the last page that person visited on top)

Now, this is perfect, and if I sort the table by the LastVisited field, all is well. That is, until two people surf the website at the same time, then my page does the following:

samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm

anotherperson.bellsouth.net (230.128.43.4)
/test.asp (1 visit) at 1:55pm

samplecableuser.comcast.net (68.12.94.11)
/default.asp (1 visit) at 11:22am

Notice how they overlap? I would like to have it sorted by date, LastVisited is the field. But, I would also like to have it group the users together by IP address. so that the following above looks like this:

samplecableuser.comcast.net (68.12.94.11)
/portfolio.asp (1 visit) at 5:26pm
/contactus.asp (1 visit) at 2:24pm
/default.asp (1 visit) at 11:22am

anotherperson.bellsouth.net (230.128.43.4)
/test.asp (1 visit) at 1:55pm

Now, the samplecableuser.comcast.net person should be at the bottom, but since the last visited was at 5:26pm, he is put at the top. Is there some kind of UBound() on dates? Would that solve it? I am not very good with the GROUP BY clause because you can't really use SELECT * FROM whatever.

Any suggestions?

Thanks,
B|||Honestly, I was afraid you'd reply back so quickly. heh

Alright. I get your drift.

I think it's because you have to convert the date to a varchar. I still don't understand why you're doing that. I hope I'm not just stuck on that one.

What I'd say is group by the Remote_Addr, and sort by DateTime Desc

That should be what you want. Once you group it by the Remote_Addr, there can't be dupes since it's grouped by them anyways.

if you need help with the sql, try out www.sqlcourse.com|||Well the only reason I covert the date to varchar is because its the only way I could get the WHERE statement to pull out the records I wanted on SQL Server. If there is a better way, I would like to know. The dates get saved like so MM/DD/YYYY HH:MM:SS PM, but when I do that varchar conversion it becomes just MM/DD/YYYY, which I match with whatever todays date is.|||Another thing I am having a issue with, you say "group by remote_addr", but it wants me to include them all in the grouping or whatever. I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column 'PageViews.TrackerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And if I include that in the SQL query, it will go to the next column, and so on until I am done with all the columns, then it will display. But, is that right?|||Instead of doing the date comparison that way, use DATEDIFF.

WHERE DATEDIFF(d,LastVisited,'" & dDate & "') = 0 AND..."

Terri|||See. What Terry mentioned is much sexier than casting to varchar.

Also, in regards to having to select everything properly.. I'm wondering if you're doing this inline sql or through stored procedure.

The reason I ask this is because if you're having so much diffculty in creating one sql statement to do everything for you, you may wish to create a procedure to execute through a few different statements to obtain the desired result.|||Got the date thing fixed, but what about the recordset? I can't do stored procedures... I just need it to group by one field only. Like so:

SELECT * FROM Table
WHERE Date = This
GROUP By IP
ORDER BY Date

If that statement could work, it would be right. But, in SQL Server (2000), you can't group items together unless everything in that * is in the group statement too. WTF?

Thanks,
B

Monday, March 26, 2012

Grouping by unrelated field- SQL masters, try this!

I would like to retrieve 10(dynamic) records of table x (proucts) for
each user in table y (users). Can this be done?
I would like the end result to be something like this: (would this be
a union?)
__________________________
y.name | x.pid | x.pname
Bob | 1 | fork
Bob | 2 | spoon
... | |
Bob | 10 | potato
Jeff | 11 | pen
etc....
__________________________
But also with the number to return based off of a query, ex-
select @.pcount = count(products)
select @.ucount = count(users)
select @.pcount / @.ucount
10
And lump all this in an Stored procedure
ex-
get number of total records in x, divide by total y = z
select z records for each user in y.
You would be a master in my book if you can give me hints on this one!
Thanks,
JeffHi

It is always better to post DDL ( CREATE TABLE statements etc...) and
example data (as insert statements) with the expected results that you
require from that data. That removes most of the ambiguities and reduces
that number of assumptions that someone answers your question will have to
make.

This seems to be something similar to what you require
http://tinyurl.com/28dhn

John

"JC" <ujjc001@.charter.net> wrote in message
news:b8c0d25d.0407061959.2f9791ca@.posting.google.c om...
> I would like to retrieve 10(dynamic) records of table x (proucts) for
> each user in table y (users). Can this be done?
> I would like the end result to be something like this: (would this be
> a union?)
> __________________________
> y.name | x.pid | x.pname
> Bob | 1 | fork
> Bob | 2 | spoon
> ... | |
> Bob | 10 | potato
> Jeff | 11 | pen
> etc....
> __________________________
> But also with the number to return based off of a query, ex-
> select @.pcount = count(products)
> select @.ucount = count(users)
> select @.pcount / @.ucount
> 10
> And lump all this in an Stored procedure
> ex-
> get number of total records in x, divide by total y = z
> select z records for each user in y.
> You would be a master in my book if you can give me hints on this one!
> Thanks,
> Jeffsql

Grouping by hour, day, month, etc

I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Wednesday, March 21, 2012

Group-based authorization in Forms Authentication

Hi,
I am using the Forms Authentication Sample to authenticate users in RS. I am
trying to find a sample code to check setup groups and assign access on
Folders to groups instead of users.
Please help.
Cheers
SaiSearch the below URL for 'forms authentication groups'. I found quite a few
results:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs
--
Adrian M.
MCP
"Sai Vootla" <Sai@.discussions.microsoft.com> wrote in message
news:86332D5C-5A49-4F29-9ED0-B2ABBEDFBF6E@.microsoft.com...
> Hi,
> I am using the Forms Authentication Sample to authenticate users in RS. I
> am
> trying to find a sample code to check setup groups and assign access on
> Folders to groups instead of users.
> Please help.
> Cheers
> Saisql

Group vs Individual Users

The Books Online indicates that if a 'database user' that is actually repres
ents a NT security group creates a new database etc,
then SQL will automatically create a new 'database user' that is specificall
y signed on user.
I know from experience that the individual user, not the group, becomes the
owner of the newly created database.
Questions are
1) - What privileges and roles etc are associated with the new user.
2) - What is the login that this new user is tied to. Is it still the group
or does a login for the individual get created also.
3) - If a group that contains an individual that also has a 'database user'
record and the individual have conflicting 'database
user' setups (roles, privileges etc) which one wins. (Least restrictive, mo
st restrictive, group, individual, union, intersection
or something else)
Thanks
---
Roy Chastain
KMSystems, Inc.1. In terms of the database, the user creating the database
will be the owner of the database so they would be a member
of db_owner and mapped to dbo for the database.
2. I'm not sure what you are asking on this - the user is
always tied to their login even when they are members of
groups/roles. If the user creates a database, they will be
mapped to dbo in that database.
3. Permissions are cumulative with deny taking precedence.
The only exception would be a login that is a member of the
sysadmin server role. Sysadmins can perform any activity on
the server.
-Sue
On Mon, 21 Jun 2004 14:07:34 -0400, Roy Chastain
<roy@.kmsys.com> wrote:

>The Books Online indicates that if a 'database user' that is actually repre
sents a NT security group creates a new database etc,
>then SQL will automatically create a new 'database user' that is specifical
ly signed on user.
>I know from experience that the individual user, not the group, becomes the
owner of the newly created database.
>Questions are
>1) - What privileges and roles etc are associated with the new user.
>2) - What is the login that this new user is tied to. Is it still the grou
p or does a login for the individual get created also.
>3) - If a group that contains an individual that also has a 'database user'
record and the individual have conflicting 'database
>user' setups (roles, privileges etc) which one wins. (Least restrictive, m
ost restrictive, group, individual, union, intersection
>or something else)
>Thanks
>---
>Roy Chastain
>KMSystems, Inc.sql

Friday, February 24, 2012

Group By (part of a field)

In my tables there are fields for Make, Model, Type, Cc, Carburant, Etc...
But for a unknown reason, maybe laziness, my users fill in all data in
Model.
How can i Group on the text before the first space i like to Group.
Examples:
Mondeo Gls
Galaxy 2.0
C220 2.0 D
C220 2.5 tdi
Scenic 2.0
Scenic 2.2
320 TDS
320 TD
So i have groups like Mondeo, Galaxy, C220, Scenic and 320
How can i do this ?
Thx for your help
GL.Well, I would suggest you clean up your data, and consider making domains
(likely in related tables) for each of those columns. Frankly I am amazed
that you dont have the following data:
Mondo Gls
Galaxy_2.0
C220 2.0 D
C22 2.5 tdi
Schenic 2.0
Scenic 2.2
3200 TDS
320 TD
So I am kind of thinking that this has to be more than just user entered
data. The answer to your problem on a temporary basis (since you have
indicated that they all have an initial space as a seperator is:
declare @.table table (value varchar(30))
insert into @.table
select 'Mondeo Gls'
union all
select 'Galaxy 2.0'
union all
select 'C220 2.0 D'
union all
select 'C220 2.5 tdi'
union all
select 'Scenic 2.0'
union all
select 'Scenic 2.2'
union all
select '320 TDS'
union all
select '320 TD'
select left(value,charindex(' ', value)) as model, count(*)
from @.table
group by left(value,charindex(' ', value))
model
-- --
320 2
C220 2
Galaxy 1
Mondeo 1
Scenic 2
----
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)
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:GuZEf.233382$KY3.7519286@.phobos.telenet-ops.be...
> In my tables there are fields for Make, Model, Type, Cc, Carburant, Etc...
> But for a unknown reason, maybe laziness, my users fill in all data in
> Model.
> How can i Group on the text before the first space i like to Group.
> Examples:
> Mondeo Gls
> Galaxy 2.0
> C220 2.0 D
> C220 2.5 tdi
> Scenic 2.0
> Scenic 2.2
> 320 TDS
> 320 TD
> So i have groups like Mondeo, Galaxy, C220, Scenic and 320
> How can i do this ?
> Thx for your help
> GL.
>|||DECLARE @.SomeCar VARCHAR(100)
SET @.SomeCar = 'Scenic 2.2'
SELECT
SUBSTRING(@.SomeCar, 1,CHARINDEX(CHAR(32),@.SomeCar)-1)
YOu can group by this expression.
HTH, Jens Suessmeyer.|||Thanx for your help. I have take over some parts and this is what works fine
for me.
SELECT left(carModel,charindex(' ',carModel)) as model
From tblVehicles
GROUP BY left(carModel,charindex(' ',carModel))")|||"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:w1_Ef.233447$UW5.7562339@.phobos.telenet-ops.be...

> Thanx for your help. I have take over some parts and this is what works
> fine for me.
> SELECT left(carModel,charindex(' ',carModel)) as model
> From tblVehicles
> GROUP BY left(carModel,charindex(' ',carModel))")
Yes, but you should SERIOUSLY consider cleaning up your data, and amending
the poor design of your GUI so that your users are forced to enter values in
the correct fields.

Group age by a parameter and find out the value corresponding to that.

Hi guys...

My goal is to change the given stored procedure so that I can find out the different age gorup according to users parameter and find out sumof these values for that group:
s.TVmins, s.Notional$, COUNT(*) AS Qty, SUM(s.TVmins) AS TVMinsAmt, SUM(s.Notional$) AS NotionalAmt
For that I am planning to put another parameter @.count for the group interval and I need to group accordingly.
So my answer should look like:
if the user give the @.count value as 10:
the result should:

age group TVMins Notional

1-9 1560 125632( the sum of that particluar group)
10-19 -- --

91-100 --

I have a field DOB( Date of birth) , I have to extract age from that field first and then group them according to the parameter values and then find its corresponding sums...

<CODE>

--
ALTER PROCEDURE [dbo].[sp_PlanningData]
@.ProgrammeID numeric,
@.RegionID numeric,
@.SiteID numeric,
@.COCGroup varchar(50),
@.Provider varchar(50),
@.Schedule varchar(50),
@.StartDate datetime,
@.EndDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@.sql nvarchar(4000),
@.paramlist nvarchar(4000)

SELECT @.sql = 'SELECT
dm.DOB,
dm.Suburb,
vs.RID,
s.TVmins,
s.Notional$,
COUNT(*) AS Qty,
SUM(s.TVmins) AS TVMinsAmt,
SUM(s.Notional$) AS NotionalAmt

FROM dbo.lkpService s
INNER JOIN dbo.tmpValidServices_ALL vs ON s.Code = vs.Service
INNER JOIN dbo.tmpDemographics_ALL dm ON dm.RID = vs.RID '

IF @.COCGroup IS NOT NULL
SELECT @.sql = @.sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON vs.COC = c.pvcode '

IF @.ProgrammeID IS NOT NULL
SELECT @.sql = @.sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '

SELECT @.sql = @.sql + 'WHERE s.Schedule = @.Schedule '

IF @.StartDate IS NOT NULL
SELECT @.sql = @.sql + ' AND (vs.Complete >= @.StartDate ) '

IF @.EndDate IS NOT NULL
SELECT @.sql = @.sql + ' AND (vs.Complete <= @.EndDate ) '

IF @.ProgrammeID IS NOT NULL
SELECT @.sql = @.sql + ' AND (ag.AgencyTypeID = @.ProgrammeID)'

IF @.SiteID IS NOT NULL
SELECT @.sql = @.sql + 'AND (ag.EXACT# = @.SiteID) '

IF @.COCGroup IS NOT NULL
SELECT @.sql = @.sql + ' AND (c.pvcode = @.COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @.COCGroup)) '

IF @.Provider IS NOT NULL
SELECT @.sql = @.sql + 'AND (vs.Provider = @.Provider) '

SELECT @.sql = @.sql + 'GROUP dm.Suburb,vs.RID, s.TVmins, s.Notional$ '

SELECT @.paramlist =
' @.ProgrammeID numeric,
@.RegionID numeric,
@.SiteID numeric,
@.COCGroup varchar(50),
@.Provider varchar(50),
@.Schedule varchar(50),
@.StartDate datetime,
@.EndDate datetime '

EXEC sp_executesql @.sql,@.paramlist,@.ProgrammeID,@.RegionID,@.SiteID,@.COCGroup,@.Provider,@.Schedule,@.StartDate,@.EndDate

END
-
</CODE>

Hope this will help.. it is really urgent one.. I am trying my best to find it out..
Thanks for your help..

This will give you the range.

Create Table #Temp(Interval int, MyRange varchar(20))

DECLARE @.MyNewInterval int

DECLARE @.MyNewRange varchar(max)

DECLARE @.Interval int

SET @.INTERVAL = 10 --SET YOUR INTERVAL HERE OR PASS IT IN

SET @.MyNewInterval = 0

While @.MyNewInterval < (1000 + @.Interval)

BEGIN

INSERT INTO #Temp(Interval, MyRange) VALUES(@.MyNewInterval, @.MyNewInterval + @.Interval)

SET @.MyNewInterval = @.MyNewInterval + @.INTERVAL

END

SELECT cast(Interval as varchar(50)) + ' - ' + MyRange AS [Range]

FROM #Temp

drop table #temp

UNION your aggregates and you'll have it

Adamus

|||

Hi,

I can create this range, but how can put it into the proc so that I can get the sum values other fields..

Your help is highly appreciation,

|||

UNION your aggregates

Adamus

|||

Hi,

I could not get u, How can I do that.. could you explain a bit more please..

|||

Here's a method that I think can work for you. (it seems to work anyway)
It allows you to use the size of an interval as a parameter, so that you'll group your ages by, say 10-year groups if you pass in '10', and also lets you extract other values that you can sum, count etc...

Assuming you have a dob in the format of ssyymmdd, then this is a way to calculate the current age:

select cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age

(the cast to int is because we need an int later...)

The idea here is to use MOD as the indicator for how the ages should be grouped.
If we say that we set 10 as the range size, then for each age, the lower bound would be:

age - (age % 10)

and the higher bound would be:

age - (age % 10) + 10

To start, then, you could select the values you want to sum, along with the calculated age.

select dob,
val1,
val2,
cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
from test

Use this is a derived table and find out the low and high ranges for the selected grouping:

declare @.mod int
set @.mod = 10

select x.val1,
x.val2,
x.age - (x.age % @.mod) as lowrange,
(x.age - (x.age % @.mod)) + @.mod as highrange
from (
select dob,
val1,
val2,
cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
from test
) x

Now we can wrap this into yet another derived table and do the final grouping and sum up the values for each group.
It's not entirely necessary to wrap further out, but it keeps the code a bit more readable, since we can group by name instead of repeating the algorithms for low and high..
So the final construct should look something like this;

declare @.mod int
set @.mod = 10

select y.lowrange,
y.highrange,
sum(y.val1) as val1Sum,
sum(y.val2) as val2Sum,
count(*)
from (
select x.val1,
x.val2,
x.age - (x.age % @.mod) as lowrange,
(x.age - (x.age % @.mod)) + @.mod as highrange
from (
select dob,
val1,
val2,
cast(ceiling(datediff(day, dob, getdate()) / 365.25) as int) as age
from test
) x
) y
group by y.lowrange, y.highrange
order by y.lowrange

To change the size of the groups, just set @.mod to the desired range, 5, 8, 10, 20 or whatever.

Hope it helps you some.

=;o)
/Kenneth

|||

Bisjom wrote:

Hi,
I could not get u, How can I do that.. could you explain a bit more please..

I will give you the answer tomorrow.

Adamus

|||

Bisjom wrote:

Hi,
I could not get u, How can I do that.. could you explain a bit more please..

1. Add the one field "Range" in #temp table to your existing table with null values. (allow nulls)
2. Add all fields from your existing table to the #temp table with null values. (allow nulls)

Now you have the same fields in both tables. This is key.

3. Now create your #temp table with only the ranges and populate.
4. UNION ALL your aggregate query.
4b. In your aggregeate query, you'll have to determine which row the aggregate hypothetically will go.
5. You should now have your desired results.

Does this makes sense?

Adamus

|||

Hi Adamus,

I understand what you said. But not sure whether its a good idea to add a new field to the table in my case,

As I added in the first message, I am taking values from different tables and I am unable to add a field to any of the tables due to some security reasons.

Can I add your query into my query or DO the union all ?

I cant resolve this..I am really confused now..

|||

I couldn't quite follow all the twist and turns in the dynamic SQL stuff (not sure that the literal example is actually runnable?), but from what I gathered about the need to produce the age groupings and the two sums and count, I belive that this is about what you need to get those particular items...

You may try and see if it runs on your system.

select dm.dob,
s.TVmins,
s.Notional$,
cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age
from dbo.lkpservice s
join dbo.tmpvalidservices_all vs
on s.code = vs.service
join dbo.tmpdemographics_all dm
on dm.rid = vs.rid
where s.schedule = @.schedule
and (vs.complete >= coalesce(@.startdate, vs.complete))
and (vs.complete <= coalesce(@.enddate, vs.complete))
and (vs.provider = coalesce(@.provider, vs.provider))

Assuming that the above 'base query' is ok for the purpose, the below would (hopefully) group by the age-interval given and sum up the two values according to each group. You may need to declare additional variables @.startdate, @.enddate and @.provider though. The age-intervals returned will be only those where there are ages found in the data, there will be no empty intervals. If that is a requirement (and assuming the whole shebang actually is useful =;o), it's possible to generate empty groups with counts of zero, though that is a bit more code, and also requires a numberstable.
Another assumption is also that DOB is in the format of 'SSYYMMDD'

Anyway, try and see if the below is of any use to you.

-- with ranges contained in data (no empty intervals)
declare @.mod int
set @.mod = 10 -- set to change the range of low/high groupings

select cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10)) as AgeGroup,
sum(y.TVmins) as TVMinsAmt,
sum(y.Notional$) as NotionalAmt,
count(*) as Qty
from (
select x.TVmins,
x.Notional$,
(x.age - (x.age % @.mod)) as lowrange,
(((x.age - (x.age % @.mod)) + @.mod) - 1) as highrange
from (
select dm.dob,
s.TVmins,
s.Notional$,
cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age
from dbo.lkpservice s
join dbo.tmpvalidservices_all vs
on s.code = vs.service
join dbo.tmpdemographics_all dm
on dm.rid = vs.rid
where s.schedule = @.schedule
and (vs.complete >= coalesce(@.startdate, vs.complete))
and (vs.complete <= coalesce(@.enddate, vs.complete))
and (vs.provider = coalesce(@.provider, vs.provider))
) x
) y
group by cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10))
order by cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10))

Hope it works out for you =;o)

=;o)
/Kenneth

|||

Hi Kenneth,

Thank you very much for your help.

It seems like what I need...I will try it and let you know..

|||

I coded an answer for you:

All you have to do is pass in the interval for the sp_

You can run the #Temp3 first to see what it produces:

-==========================================================

CREATE TABLE #Temp3(Age int, BeginRange bigint)

DECLARE @.counter int
DECLARE @.counter2 int
DECLARE @.Interval int
DECLARE @.BeginRange int
DECLARE @.IntervalCounter int

SET @.Interval = 20
SET @.counter = 1
SET @.BeginRange = @.Interval
SET @.IntervalCounter = @.Interval
SET @.counter2 = 2

WHILE @.counter <> 100

BEGIN
IF @.Counter % @.Interval = 0

BEGIN

SET @.BeginRange = @.BeginRange + @.Interval

INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.BeginRange)

SET @.counter = @.counter + 1
SET @.IntervalCounter = @.Interval * @.counter2
SET @.counter2 = @.counter2 + 1

END

ELSE
BEGIN
INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.IntervalCounter)

SET @.counter = @.counter + 1
END
END

Select * from #Temp3
drop table #temp3
-==========================================================

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[sp_AggregateAge](@.Interval int)

CREATE TABLE #Temp3(Age int, BeginRange bigint)

DECLARE @.counter int

DECLARE @.counter2 int

--DECLARE @.Interval int

DECLARE @.BeginRange int

DECLARE @.IntervalCounter int

--SET @.Interval = 20

SET @.counter = 1

SET @.BeginRange = @.Interval

SET @.IntervalCounter = @.Interval

SET @.counter2 = 2

WHILE @.counter <> 100 --This assumes no one is older than 100

BEGIN

IF @.Counter % @.Interval = 0

BEGIN

SET @.BeginRange = @.BeginRange + @.Interval

INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.BeginRange)

SET @.counter = @.counter + 1

SET @.IntervalCounter = @.Interval * @.counter2

SET @.counter2 = @.counter2 + 1

END

ELSE

BEGIN

INSERT INTO #Temp3(Age, BeginRange)VALUES(@.Counter, @.IntervalCounter)

SET @.counter = @.counter + 1

END

SELECT (t3.BeginRange - @.Interval) AS BeginRange, (t3.BeginRange - 1) AS EndRange, b.Name, SUM(b.TVMins) AS TVMins, SUM(Notional) AS Notional, t3.Age

FROM #Temp3 t3 JOIN Birthday b --JOIN Your Table on Age

ON b.Age = t3.Age

GROUP BY b.Name, t3.Age, t3.BeginRange

ORDER BY t3.BeginRange

DROP TABLE #Temp3

Results:

EXEC sp_AggregateAge 20

BeginRange EndRange Name TVMins Notional Age
-- -- - -- -- --
20 39 Adam 170 10503 35
20 39 John 123 45648 36
40 59 Chris 153 123456 40
40 59 JoAnne 99 65489 53
40 59 Joe 72 5478 42
60 79 Chris 105 4652 69

Adamus

|||

Hi Kenneth,

Thank you very much for your code and it is working fine with the database.

But when I changed your code to dymanic SQL to select the null value of the parameters and check conditions( Like if Provider is not null then ....), It shows a problem.

It is giving error in the grou by line since it cant recognise the '-' character.. it is taking that as a minus operator..

group by cast(y.lowrange as varchar(10)) + '-' + cast(y.highrange as varchar(10))

Is there any way to split in to get it as range..

The modified code is as follows:

DECLARE
@.sql nvarchar(4000),
@.paramlist nvarchar(4000)

SELECT @.sql = 'select sum(y.TVmins) as TVMinsAmt, sum(y.Notional$) as NotionalAmt, count(*) as Qty, '

SELECT @.sql = @.sql + 'cast(y.lowrange as varchar(10)) '

SELECT @.sql = @.sql + ' + '

SELECT @.sql = @.sql + ' '

SELECT @.sql = @.sql + ' cast(y.highrange as varchar(10)) as AgeGroup '

SELECT @.sql = @.sql + ' from ( select x.TVmins, x.Notional$,x.Age,(x.age - (x.age % @.Interval)) as lowrange,'

SELECT @.sql = @.sql + '(((x.age - (x.age % @.Interval)) + @.Interval) - 1) as highrange'

SELECT @.sql = @.sql + ' from ( select dm.dob, s.TVmins, s.Notional$,'

SELECT @.sql = @.sql + 'cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age'

SELECT @.sql = @.sql + 'from dbo.lkpservice s '

SELECT @.sql = @.sql + 'join dbo.tmpvalidservices_all vs '

SELECT @.sql = @.sql + 'on s.code = vs.service '

SELECT @.sql = @.sql + 'join dbo.tmpdemographics_all dm '

SELECT @.sql = @.sql + 'on dm.rid = vs.rid '

SELECT @.sql = @.sql + 'where s.schedule = @.schedule '

IF @.StartDate IS NOT NULL

SELECT @.sql = @.sql + 'and (vs.complete >= coalesce(@.StartDate, vs.complete))'

IF @.EndDate IS NOT NULL

SELECT @.sql = @.sql + 'and (vs.complete <= coalesce(@.EndDate, vs.complete))'

IF @.Provider IS NOT NULL

SELECT @.sql = @.sql + 'and (vs.provider = coalesce(@.provider, vs.provider))'

SELECT @.sql = @.sql + ') x'

SELECT @.sql = @.sql + ') y '

SELECT @.sql = @.sql + 'group by cast(y.lowrange as varchar(10)) '

SELECT @.sql = @.sql + '+'

SELECT @.sql = @.sql + ' '

SELECT @.sql = @.sql + '+'

SELECT @.sql = @.sql + ' cast(y.highrange as varchar(10)) '

SELECT @.paramlist = '@.Provider varchar(50),@.Schedule varchar(50),@.StartDate datetime,@.EndDate datetime,@.Interval numeric '

EXEC sp_executesql @.sql,@.paramlist,@.Provider,@.Schedule,@.StartDate,@.EndDate,@.Interval

Thanks

|||

Hi Adamus,

Thanks for your code, Your code is working fine for the database.. But it is taking time to create temp table and I have to deploy this code to the reporting as well..

So I have a doubt whether it is a good idea to create a temp table since it is really a big one which takes a time to execute.

Thank you for your advice and waiting to get your reply...

|||

You don't really need to group by the concatenated age group, I jsut wrote that to show how to create group values like '10-19', '20-29' etc. You can keep the low and high as separate columns instead and perhaps later do the concatenation when presenting the result, if desired.

Try with the below, where you have low and high values as separate columns.
(BTW, is there any special reason you do this as dynamic SQL? For this particular query, it's not necessary. It'll run fine as is, without sp_executesql)

declare @.mod int
set @.mod = 10 -- set to change the range of low/high groupings

select y.lowrange,
y.highrange,
sum(y.TVmins) as TVMinsAmt,
sum(y.Notional$) as NotionalAmt,
count(*) as Qty
from (
select x.TVmins,
x.Notional$,
(x.age - (x.age % @.mod)) as lowrange,
(((x.age - (x.age % @.mod)) + @.mod) - 1) as highrange
from (
select dm.dob,
s.TVmins,
s.Notional$,
cast(ceiling(datediff(day, dm.dob, getdate()) / 365.25) as int) as age
from dbo.lkpservice s
join dbo.tmpvalidservices_all vs
on s.code = vs.service
join dbo.tmpdemographics_all dm
on dm.rid = vs.rid
where s.schedule = @.schedule
and (vs.complete >= coalesce(@.startdate, vs.complete))
and (vs.complete <= coalesce(@.enddate, vs.complete))
and (vs.provider = coalesce(@.provider, vs.provider))
) x
) y
group by y.lowrange, y.highrange
order by y.lowrange, y.highrange