Determine Nearest Day

Hi,
Users of an application can retrieve sales between 2 dates that they pick in
the front end (DateFrom & DateTo)
I have sales data that these values will be used to query, the data is
always summarised to the Sunday of each w.
Before my stored procedure attempts to query, it needs to work out the
nearest Sunday for each of the dates. Eg;
DateFrom = 14th December 2005
This date needs to be converted to look 'backwards' for the nearest Sunday.
It needs to find 11th December.
DateTo = 29th December 2005
This needs to be converted forwards to look to the nearest Sunday. It needs
to find 01 Jan 2006.
Is there any TSQL that can be used to work this out, or is it a case of
using a lookup table (which I do have available)
Thanks
DylanHi
DECLARE @.Today datetime
SET @.Today = '20060105'
SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7+6 , '1900')
"DylanM" <DylanM@.discussions.microsoft.com> wrote in message
news:031DC14C-596B-411C-B15D-88D17743AB7E@.microsoft.com...
> Hi,
> Users of an application can retrieve sales between 2 dates that they pick
> in
> the front end (DateFrom & DateTo)
> I have sales data that these values will be used to query, the data is
> always summarised to the Sunday of each w.
> Before my stored procedure attempts to query, it needs to work out the
> nearest Sunday for each of the dates. Eg;
> DateFrom = 14th December 2005
> This date needs to be converted to look 'backwards' for the nearest
> Sunday.
> It needs to find 11th December.
> DateTo = 29th December 2005
> This needs to be converted forwards to look to the nearest Sunday. It
> needs
> to find 01 Jan 2006.
> Is there any TSQL that can be used to work this out, or is it a case of
> using a lookup table (which I do have available)
>
> Thanks
> Dylan
>|||Excellent, thanks Uri.
And to convert backwards, I thinks it's just as follows...' (-1 instead of
+6)
SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7-1 , '1900')
Thanks again.
"Uri Dimant" wrote:

> Hi
> DECLARE @.Today datetime
> SET @.Today = '20060105'
> SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7+6 , '1900')
>
>|||This rounds it up to the nearest Sunday. It prints DateTo, the day or the
w dateto is, and then the rounded up date, and the day of the w the
rounded up day is.
I suspect you can use the modulus operator to make this perform better, but
I'm not SK.
create table salesData
(pk int not null identity primary key,
DateTo datetime,
DateFrom Datetime)
GO
insert into salesData values (getdate()-101,getdate()-103)
insert into salesData values (getdate()-105,getdate()-108)
GO
select case when DATENAME ( dw , DateTo )='Sunday' then dateto
when DATENAME ( dw , DateTo )='Monday' then dateto-1
when DATENAME ( dw , DateTo )='Tuesday' then dateto-2
when DATENAME ( dw , DateTo )='Wednesday' then dateto-3
when DATENAME ( dw , DateTo )='Thursday' then dateto-4
when DATENAME ( dw , DateTo )='Friday' then dateto-5
when DATENAME ( dw , DateTo )='Saturday' then dateto-6
end,
dateto, datename(wday,dateto),
case when DATENAME ( dw , DateTo )='Sunday' then datename(wday, dateto)
when DATENAME ( dw , DateTo )='Monday' then datename(wday, dateto-1)
when DATENAME ( dw , DateTo )='Tuesday' then datename(wday, dateto-2)
when DATENAME ( dw , DateTo )='Wednesday' then datename(wday, dateto-3)
when DATENAME ( dw , DateTo )='Thursday' then datename(wday, dateto-4)
when DATENAME ( dw , DateTo )='Friday' then datename(wday, dateto-5)
when DATENAME ( dw , DateTo )='Saturday' then datename(wday, dateto-6)
end
from salesdata
go
drop table salesdata
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DylanM" <DylanM@.discussions.microsoft.com> wrote in message
news:031DC14C-596B-411C-B15D-88D17743AB7E@.microsoft.com...
> Hi,
> Users of an application can retrieve sales between 2 dates that they pick
> in
> the front end (DateFrom & DateTo)
> I have sales data that these values will be used to query, the data is
> always summarised to the Sunday of each w.
> Before my stored procedure attempts to query, it needs to work out the
> nearest Sunday for each of the dates. Eg;
> DateFrom = 14th December 2005
> This date needs to be converted to look 'backwards' for the nearest
> Sunday.
> It needs to find 11th December.
> DateTo = 29th December 2005
> This needs to be converted forwards to look to the nearest Sunday. It
> needs
> to find 01 Jan 2006.
> Is there any TSQL that can be used to work this out, or is it a case of
> using a lookup table (which I do have available)
>
> Thanks
> Dylan
>|||To do it in one shot, try this:
SELECT DATEADD(day, DATEDIFF(day, '18991231', @.Today+3)/7*7 , '18991231')
It will find "next" Sunday, if today is Thursday, Friday, or Saturday,
it will find "This"
Sunday if today is Sunday, and it will find "last" Sunday if today is
Monday, Tuesday, or Wednesday.
Steve Kass
Drew Unviersity
DylanM wrote:

>Hi,
>Users of an application can retrieve sales between 2 dates that they pick i
n
>the front end (DateFrom & DateTo)
>I have sales data that these values will be used to query, the data is
>always summarised to the Sunday of each w.
>Before my stored procedure attempts to query, it needs to work out the
>nearest Sunday for each of the dates. Eg;
>DateFrom = 14th December 2005
>This date needs to be converted to look 'backwards' for the nearest Sunday.
>It needs to find 11th December.
>DateTo = 29th December 2005
>This needs to be converted forwards to look to the nearest Sunday. It needs
>to find 01 Jan 2006.
>Is there any TSQL that can be used to work this out, or is it a case of
>using a lookup table (which I do have available)
>
>Thanks
>Dylan
>
>sql

Detail Section printing 18 times

Hi all,

Apologies if its a stupid question but this is my very 1st report - a sales invoice.

I have a detail section - limited by parameter to sales invoice number. When it prints the detail section is repeated 18 times. Ive checked the database there is only 1 record with this invoice number.

Any idea where I should start looking? As this is my 1st report there really isn't anything fancy going on.

Thanks for any help

Beckicheck if u have created any groups|||Also check the relationships between the tables used in the report.|||Thanks for your help.

I turned out that the link I had inserted wasn't completely unique and attracted too many records.

Regards

Becki

Design idea Help for Database

we are creating a database of sales agents. Basically I have a table
of about 35,000 people, a second one with 8000 offices, and a third
table of around 400,000 transactions done by those 35,000 people in
those 8000 offices. We get new data everyday that just updates the
existing tables with the updated rosters and transactions.

I want to build a quick website where our recruiters can look up those
people and keep contact info and all that fun stuff but also pull
numbers on those people. Like

* How Many Transactions that Sales Agent did last year
* Rosters by office showing production

The goal is to click the users name and see all the percentages,
commissions and data like that which we will get by searching that
table of transactions by the agents ID.

the problems I see right away are stuff like
* If I were to pull a report showing all agents in a single office with
their number of transactions next to their name, that is a HUGE query.
It would have to search the 400k worth of records for each of the
agents on just that one report.

A suggestions I was given
I was told by a fellow programmer a better way to do this is to have an
additional table that houses stats info and have the SQL server run
automated reports everyday at say midnight where it updates that table.
This table could show stuff like
** number of transactions for each user
** avg sales price on all transactions for each user
** avg commission on transaction for each user

let me know your thoughts

thanks in advance
Monkey Girlbob1barker@.yahoo.com wrote:

> we are creating a database of sales agents. Basically I have a table
> of about 35,000 people, a second one with 8000 offices, and a third
> table of around 400,000 transactions done by those 35,000 people in
> those 8000 offices. We get new data everyday that just updates the
> existing tables with the updated rosters and transactions.
> I want to build a quick website where our recruiters can look up those
> people and keep contact info and all that fun stuff but also pull
> numbers on those people. Like
> * How Many Transactions that Sales Agent did last year
> * Rosters by office showing production
> The goal is to click the users name and see all the percentages,
> commissions and data like that which we will get by searching that
> table of transactions by the agents ID.
> the problems I see right away are stuff like
> * If I were to pull a report showing all agents in a single office with
> their number of transactions next to their name, that is a HUGE query.
> It would have to search the 400k worth of records for each of the
> agents on just that one report.
> A suggestions I was given
> I was told by a fellow programmer a better way to do this is to have an
> additional table that houses stats info and have the SQL server run
> automated reports everyday at say midnight where it updates that table.
> This table could show stuff like
> ** number of transactions for each user
> ** avg sales price on all transactions for each user
> ** avg commission on transaction for each user
> let me know your thoughts
> thanks in advance
> Monkey Girl

You didn't specify how quickly your data is growing but less than 1
million rows is a small database by most standards and probably isn't
going to be much trouble under any decent SQL Server implementation.

Pre-aggregating the data may be worthwhile but SQL Server has tools to
do that for you: Reporting Services or Analysis Services. It would be a
waste to create your own summary tables and then write the code just to
update them once a day.

> It would have to search the 400k worth of records for each of the
> agents on just that one report.

Not if you know what an index is... :-)

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--