Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc

Author

Topic

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-11 : 12:41:07

Hi all,

I want to create a query that accepts
Which I can then encapsulate within an auto-generated 'Create Procedure' script. The purpose is to find a user/users, whose texts are datestamped in the previous week from Mon-Fri, and come closest to number that we pass in. Constraints for this are – the user must have texted not more than 5 times and in between Mon 00:00 hours and Sat 00:00 hours in the previous week. I have written part of the query, but not optimised/cleaned up;

select cast ((replace(text,'ATT','')) as int),* from dbsrvr2.queues2.dbo.inqueue2
where text like 'ATT %' and us=83700 and service=15245
--I'm sure we'll need 'GetDate' here!!)
and datestamp>'2010-10-11 00:00:00.000'
and datestamp<'2010-10-16 00:00:00.000'

I have no sample data as it comes out jumbled here, but here's the DDL further below.

Thanks, Jim..

USE [Queues2]
GO
/****** Object: Table [dbo].[inqueue2] Script Date: 10/11/2010 15:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[inqueue2](
[msgID] [bigint] NOT NULL,
[wclID] [varchar](15) NULL,
[blockID] [varchar](38) NULL,
[phone] [char](15) NULL,
[text] [nvarchar](1024) NULL,
[us] [char](15) NULL,
[network] [int] NULL,
[origintime] [datetime] NULL,
[datestamp] [datetime] NOT NULL,
[cndate] [bigint] NOT NULL,
[processed] [tinyint] NOT NULL,
[confirmed] [tinyint] NOT NULL,
[Yy] [tinyint] NULL,
[Mm] [tinyint] NULL,
[Dd] [tinyint] NULL,
[Hh] [tinyint] NULL,
[service] [smallint] NOT NULL,
[keyword] [smallint] NOT NULL,
[origin] [tinyint] NOT NULL,
[origin2] [smallint] NOT NULL,
[keyid] [int] NOT NULL,
[OCKeyID] [int] NULL,
CONSTRAINT [PK_Inqueue2] PRIMARY KEY CLUSTERED
(
[msgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-11 : 16:54:29

declare @mydate datetime,@datemodified datetime
set @mydate = getdate()
--this gets you the first day of the week
set @datemodified = DATEADD(ww, DATEDIFF(ww,0,dateadd(dd,-1,@mydate) ), 0)

select phone
from
dbsrvr2.queues2.dbo.inqueue2
where
text like 'ATT%'
and us=83700
and service=15245
and not datepart(weekday, datestamp) in (1,7)
and datestamp between dateadd(week,-1,@datemodified) and @datemodified
group by phone
having count(*) <= 5

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-11 : 18:18:21

Vinnie, for such a poorly written request, you answered damn well!! Only thing you missed was the part where we pass in a numeric parameter, and the we want the user (or users) whose text after 'att' most closely matches the param.

So, if there user 1 has a value in the Text column of 'Att20451', and user 2's is 'Att40451', if we pass in @attendance = '30452', then the Sproc should return the 2nd user.

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-12 : 12:35:23

Bump for anyone!

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 12:38:16

Hey Jim.How are you doing?

Just it would be great if you can post some sample data & expected output.

PBUH

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-12 : 12:54:14

quote:
Originally posted by Sachin.Nand

Hey Jim.How are you doing?

Just it would be great if you can post some sample data & expected output.

PBUH

Hi Sachin,

Not sure how to present sample data here in a legible fashion, as the table is very wide, but I pretty much just want the phone column back.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 13:01:40

Just you can use the example below with your columns which you need for the query.Insert few sample records in the table with insert script with few of them matching the criteria you need.
Something like this


declare @tbl table (Id int, EmployeeId int, RegId int)
insert @tbl
select 1, 1, 15 union
select 2, 1, 16  union
select 2, 2, 16

output


employeeid  regid1      regid2
----------- ----------- -----------
1           15          16
2           16          0

PBUH

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-12 : 21:34:47

Define what qualifies a close match.
Is it the next sequential closest number, or just the smallest difference between the two, or is the match the item that has the most similiar numbers, etc?

so if you pass 45678
which of these #'s is the closest match

44678
50000
55679

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 03:35:27

Beware that "text" is a reserved word, so I recommend if that column name appears in your SQL statement that you quote it - i.e.

[text]

It does need some sample data - otherwise everyone answering will have to generate their own to test it. Only need to columns [text], datestamp and UserID - which column is that: [us], origin, keyid?? plus (say) msgID as the PK. The additional tests:

and us=83700 and service=15245

don't need to be in the test data

As described above a simple temp table and some INSERTS will do the trick:


declare @tbl table
(
    [msgID] bigint NOT NULL,
    [text] nvarchar(1024) NULL,
    [UserID] int NULL,
    [datestamp] datetime NOT NULL,
    PRIMARY KEY
    (
        [msgID] ASC
    )
)

insert @tbl
select 1, 'ATT1234', 123, '20101011' union all
select 2, 'ATT1235', 123, '20101012' union all
select 3, 'NOMATCH5678', 456, '20101010'

and then you expected output is:

UserID
123
...

i.e. the UserIDs that have more than 5 matching texts.

I agree that a definition of what makes a close match is required. Is it just the difference between the numeric part and the target number?

What about if the data following ATT is not numeric, has spaces in it, does anything like that need to tidied up? Are any of these valid?

ATT45678
ATT 45678
ATT=45678
ATT45678 From Kristen
XATT 45678
ATTX 45678

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 03:40:48

Note that


select cast ((replace(text,'ATT','')) as int)

will raise error if [text], once "ATT" is replaced with "nothing", is not valid to convert to a number.

You either need to chop off anything after the last numeric digit - so you just get the valid numeric part - or you need to exclude them as being "invalid data",w hich you could do with


where text like 'ATT %'
and [text] NOT LINE 'ATT %[^0-9]%'

that will only allow 'ATT 99999' style numbers.

If the number of digits is fixed you could do


where [text] like 'ATT [0-9][0-9][0-9][0-9]'      -- 4 digits
   or [text] like 'ATT [0-9][0-9][0-9][0-9][0-9]' -- or 5 digits
...

and then you won't have a problem with the CAST as you have only let through valid Integer values. (Dunno what your data is like though, if Joe Public sent these texts goodness knows what sort of state the data is in!!)

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-13 : 05:18:02

quote:
Originally posted by Vinnie881

Define what qualifies a close match.
Is it the next sequential closest number, or just the smallest difference between the two, or is the match the item that has the most similiar numbers, etc?

so if you pass 45678
which of these #'s is the closest match

44678
50000
55679

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Ahhhhhhh, now I see the confusion, sorry people! Perhaps I should have said "closest IN value", therefore in this case it is 44678, which is only 1,000 away. '45688' is only 20 away and if someone texts in '45678', he wins!!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-15 : 14:36:08

Based off what I can tell, this is what you want.

It will return the closest match and if two people send in the closest match that meet the criteria, it will take the first person who sent it.



declare @mydate datetime,@datemodified datetime
set @mydate = getdate()
--this gets you the first day of the week
set @datemodified = DATEADD(ww, DATEDIFF(ww,0,dateadd(dd,-1,@mydate) ), 0) 


declare @attnum int
set @attnum = 12345



select top 1 a.phone,min(a.datestamp) as datestamp
from
dbsrvr2.queues2.dbo.inqueue2 a
where
a.[TEXT] = 'ATT%'
and a.us=83700
and a.service=15245
and not datepart(weekday, a.datestamp) in (1,7)
and a.datestamp between dateadd(week,-1,@datemodified) and @datemodified
group by a.phone
having count(*) <= 5
order by ABS(@attnum - SUBSTRING(a.[text],4,99)),datestamp

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources