October 11, 2010 at 4:27 pm
Hi all,
I want to rewrite create a query so that it accepts a numeric value we pass in, and then finds the user whose text (also containing a numeric value) is closest in value to the parameter passed in, which I can then encapsulate within an auto-generated 'Create Procedure' script.
Currently my code fulfils the purposes to find a user/users, whose texts are datestamped in the previous week from Mon-Fri, who have not texted more than 5 times, and have done so between Mon 00:00 hours and Sat 00:00 hours in the previous week. The only thing it now lacks is the variable described above.
Example - if 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.
declare @mydate datetime,@datemodified datetime
set @mydate = getdate()
--this gets us 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
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
October 11, 2010 at 5:37 pm
Example - if 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.
So, you want User 1 if the end of the column matches @attendance, otherwise the next user who's value at the end of the column is < @attendance?
Is the value of this column always going to be in the format Att#####?
If so, they you will have to split this column to get the number out and convert it to an integer: convert(INT, substring(MyColumn, 4, 5))
Then just compare :
SELECT TOP (1) (columnlist)
FROM (table/join list)
WHERE convert(INT, substring(MyColumn, 4, 5)) <= @attendance
ORDER BY convert(INT, substring(MyColumn, 4, 5))
I'd be remiss if I didn't point out that if this column contains information that needs to be split out like that, that it isn't properly designed. Specifically, it isn't even in 1NF.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 10:33 am
WayneS (10/11/2010)
Example - if 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.
So, you want User 1 if the end of the column matches @attendance, otherwise the next user who's value at the end of the column is < @attendance?
No, User2, as his Att##### value is closest to @attendance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply