Query Help

Im trying to write a query that will return the next daily sub number and
Volume number for our submissions. the volume number is easy as it is
incremented each time, but the Daily sub number needs to be reset each day.
Each time a submission is made the numbers are incememted
ive come up with this so far but am struggling with the daily Sub numbers
select
Right('000000' + cast((VolumeNumber + 1) as varchar(6)), 6) As Volume,
Right('000' + cast((DailySerial + 1) as varchar(6)), 6) as DailySub
from dbo.BureauSubRecord
this result would be
Volume DailySub
-- --
000001 0001
Then the table would be updater to read
Volume DailySub
-- --
000002 0002 etc
However the first sub of the following day should read
Volume DailySub
-- --
000003 0001
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[BureauSubRecord]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[BureauSubRecord]
GO
CREATE TABLE [dbo].[BureauSubRecord] (
[BureauId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VolumeNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DailySerial] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastSub] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [BureauSubRecord]
([BureauId],[VolumeNumber],[DailySerial]
,[LastSub])VALUES('12345','000000','000'
,'Jan 1 2005 12:00:00:000AM')
SET NOCOUNT OFFHi
I am not sure about your DDL as it does not seem to match your description
The following is untested but you may want something like:
SELECT B.[BureauId], B.[VolumeNumber],
(SELECT COUNT(*) FROM dbo.BureauSubRecord S WHERE
S.[BureauId] = B.[BureauId]
AND S.[VolumeNumber] = B.[VolumeNumber]
AND S.[LastSub] < B.[LastSub]
AND CONVERT(CHAR(8), S.[LastSub], 112 ) = CONVERT(CHAR(8), B.[LastSub], 112
) ) + 1 AS [DailySerial],
B.[LastSub]
FROM dbo.BureauSubRecord B
John
"Peter Newman" wrote:

> Im trying to write a query that will return the next daily sub number and
> Volume number for our submissions. the volume number is easy as it is
> incremented each time, but the Daily sub number needs to be reset each da
y.
> Each time a submission is made the numbers are incememted
> ive come up with this so far but am struggling with the daily Sub numbers
> select
> Right('000000' + cast((VolumeNumber + 1) as varchar(6)), 6) As Volume,
> Right('000' + cast((DailySerial + 1) as varchar(6)), 6) as DailySub
> from dbo.BureauSubRecord
> this result would be
> Volume DailySub
> -- --
> 000001 0001
>
> Then the table would be updater to read
> Volume DailySub
> -- --
> 000002 0002 etc
> However the first sub of the following day should read
> Volume DailySub
> -- --
> 000003 0001
>
>
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[BureauSubRecord]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[BureauSubRecord]
> GO
> CREATE TABLE [dbo].[BureauSubRecord] (
> [BureauId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VolumeNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DailySerial] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastSub] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [BureauSubRecord]
> ([BureauId],[VolumeNumber],[DailySerial]
,[LastSub])VALUES('12345','000000','000'
,'Jan 1 2005 12:00:00:000AM')
> SET NOCOUNT OFF
>