Bulkload XML Code: experts

Showing posts with label experts. Show all posts

Tuesday, March 20, 2012

Calculate fiscal periods (T-SQL)

Hi Experts,
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each period
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
.
.
.
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanh=?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet@.80.5.182.99...
> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>> Hi Experts,
>> I would like to be able to take in a year and figure out its 12 month
>> period based on Sunday as starting day of the 1st week of that month
>> and each period has 4 weeks.
>> For example:
>> Input: 2007
>> Output:
>> P1: 12/31/2006 - 1/27/2007
>> P2: 1/28/2007 - 2/24/2007
>> P3: 2/25/2007 - 3/31/2007
>> P4: 4/1/2007 - 4/28/2007
>> P5: 4/29/2007 - 5/26/2007
>> P6: 5/27/2007 - 6/30/2007
>> P7: 7/1/2007 - 7/28/2007
>> P8: 7/29/2007 - 8/25/2007
>> .
>> .
>> .
>> P12: 11/25/2007 -12/29/2007
>>
>> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
>> of January/2007. P1 ends on 1/27/2007 because that's last day
>> (Saturday) of 4th week
>> and so on
>> My accounting dept folks call this fiscal period.
>> Anyone out there have done this or know how to do this type of
>> calculation using T-SQL.
>> I appreciate so much for any help.
>> Thanh
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet@.80.5.182.99...
>> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
>> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>> Hi Experts,
>> I would like to be able to take in a year and figure out its 12 month
>> period based on Sunday as starting day of the 1st week of that month
>> and each period has 4 weeks.
>> For example:
>> Input: 2007
>> Output:
>> P1: 12/31/2006 - 1/27/2007
>> P2: 1/28/2007 - 2/24/2007
>> P3: 2/25/2007 - 3/31/2007
>> P4: 4/1/2007 - 4/28/2007
>> P5: 4/29/2007 - 5/26/2007
>> P6: 5/27/2007 - 6/30/2007
>> P7: 7/1/2007 - 7/28/2007
>> P8: 7/29/2007 - 8/25/2007
>> .
>> .
>> .
>> P12: 11/25/2007 -12/29/2007
>>
>> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
>> of January/2007. P1 ends on 1/27/2007 because that's last day
>> (Saturday) of 4th week
>> and so on
>> My accounting dept folks call this fiscal period.
>> Anyone out there have done this or know how to do this type of
>> calculation using T-SQL.
>> I appreciate so much for any help.
>> Thanh
>>
>> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
>> need an unambiguous definition for fiscal period.
>

Calculate fiscal periods (T-SQL)

Monday, March 19, 2012

calculate cummulative or store it

hi,
i have read many time in this newsgroup where the experts say you
should not store calculated fields and it should be handled at the
frontend. But if i want to calculate cummulative (cummulative of 6-7
years)everyday,then running a query which calculates the cummulative
for past 6-7 years the query will take time to run. but if i store
that cummulative figures everyday the query is faster. The only
sacrifice i need to make is space to store this calculated values,
which i don't think should be a problem.
Please suggest which is the better approach.Praddep
Have a look at an indexed view?
Here is an example written by Steve Kass
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502162102.4cf2a1e5@.posting.google.com...
> hi,
> i have read many time in this newsgroup where the experts say you
> should not store calculated fields and it should be handled at the
> frontend. But if i want to calculate cummulative (cummulative of 6-7
> years)everyday,then running a query which calculates the cummulative
> for past 6-7 years the query will take time to run. but if i store
> that cummulative figures everyday the query is faster. The only
> sacrifice i need to make is space to store this calculated values,
> which i don't think should be a problem.
> Please suggest which is the better approach.|||Thanks Uri. But i am using Access 2000 and not SQL. So in Access i
have a predefined query which does the cummulative. But since i have
to calculate it everyday and the data is for more than 2000 rows it
takes time to do the calculation. Storing the calculated values might
improve the performance.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<O#riAaLFFHA.3928@.TK2MSFTNGP15.phx.gb
l>...
> Praddep
> Have a look at an indexed view?
> Here is an example written by Steve Kass
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
> "Pradeep" <agarwalp@.eeism.com> wrote in message
> news:364c5b9b.0502162102.4cf2a1e5@.posting.google.com...|||On 17 Feb 2005 02:46:41 -0800, Pradeep wrote:

>Thanks Uri. But i am using Access 2000 and not SQL. So in Access i
>have a predefined query which does the cummulative. But since i have
>to calculate it everyday and the data is for more than 2000 rows it
>takes time to do the calculation. Storing the calculated values might
>improve the performance.
Hi Pradeep,
That is exactly why Uri recommended you to investigate indexed views. You
can find the information in Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Under normal circumstances, you shouldn't store calculated fields. From you
r
question I assume that you want to store data aggregated from one table in
another. The problem with that is the additional housekeeping required to
keep the aggregate synchronized when the original table changes. Every
program that modifies the original table must also update the aggregate
value. Furthermore, Every program that calculates the aggregate value must
calculate it in the exact same manner. The experts are correct, because of
the additional programming required to ensure that the data remains
consistent and the possibility that a minor difference in one calculating
program can introduce database inconsistencies that are extremely difficult
to find. The best course is to first try an index, and only if that fails,
to add the aggregate field and the triggers on the original table that
maintains the aggregate.
"Pradeep" wrote:

> hi,
> i have read many time in this newsgroup where the experts say you
> should not store calculated fields and it should be handled at the
> frontend. But if i want to calculate cummulative (cummulative of 6-7
> years)everyday,then running a query which calculates the cummulative
> for past 6-7 years the query will take time to run. but if i store
> that cummulative figures everyday the query is faster. The only
> sacrifice i need to make is space to store this calculated values,
> which i don't think should be a problem.
> Please suggest which is the better approach.
>|||You're right that it wouldn't be sensible to do that everyday in an
Access query. The idea is to keep the calculations server-side. That's
why we have features such as indexed views and Analysis Services in SQL
Server.
On the other hand, if your data is only two or three thousand rows,
just putting the query in a stored-proc may well give you acceptable
results.
David Portas
SQL Server MVP
--

Thursday, February 16, 2012

Buy Enterprise or Standard Edition

Dear Experts,
Right now i am doing a development using MSDE 7.0 and VB. Soon this
development will be completed (Hopefully!!!) and i will have to recommend to
a prospective client a version of SQL Server. The application is quiet small
for about 6 to 8 users. I would appreciate if you could throw some light on
the version of SQL Server i may need.
If the client wants to buy the latest i.e. Sql Server 2005 then will there
be any conversion problems from 7.0
Thanks for Help and like always thanks for passing your expert knowledge
Regards
Manish Sawjiani
--
Three Cheers to Technet for the Help!Hi Manish
Just Check this. This might be useful to yo
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8ynn.asp
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Manish Sawjiani" wrote:
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!|||Go to www.microsoft.com/sql and read about the editions available. Note that there recently has been
released a "workgroup" edition.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
Are you asking of your code will still work? Probably, but you need to plan for test and possibly
(although unlikely) code changes between 7.0 and 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in message
news:F4CFED90-C7FC-484C-B409-C5A75F990EE8@.microsoft.com...
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Buy Enterprise or Standard Edition

Dear Experts,
Right now i am doing a development using MSDE 7.0 and VB. Soon this
development will be completed (Hopefully!!!) and i will have to recommend to
a prospective client a version of SQL Server. The application is quiet small
for about 6 to 8 users. I would appreciate if you could throw some light on
the version of SQL Server i may need.
If the client wants to buy the latest i.e. Sql Server 2005 then will there
be any conversion problems from 7.0
Thanks for Help and like always thanks for passing your expert knowledge
Regards
Manish Sawjiani
--
Three Cheers to technet for the Help!Hi Manish
Just Check this. This might be useful to you
http://msdn.microsoft.com/library/d...br />
8ynn.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Manish Sawjiani" wrote:

> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend
to
> a prospective client a version of SQL Server. The application is quiet sma
ll
> for about 6 to 8 users. I would appreciate if you could throw some light o
n
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||Go to www.microsoft.com/sql and read about the editions available. Note that
there recently has been
released a "workgroup" edition.

> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
Are you asking of your code will still work? Probably, but you need to plan
for test and possibly
(although unlikely) code changes between 7.0 and 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in messag
e
news:F4CFED90-C7FC-484C-B409-C5A75F990EE8@.microsoft.com...
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend
to
> a prospective client a version of SQL Server. The application is quiet sma
ll
> for about 6 to 8 users. I would appreciate if you could throw some light o
n
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!

Buy Enterprise or Standard Edition

Dear Experts,
Right now i am doing a development using MSDE 7.0 and VB. Soon this
development will be completed (Hopefully!!!) and i will have to recommend to
a prospective client a version of SQL Server. The application is quiet small
for about 6 to 8 users. I would appreciate if you could throw some light on
the version of SQL Server i may need.
If the client wants to buy the latest i.e. Sql Server 2005 then will there
be any conversion problems from 7.0
Thanks for Help and like always thanks for passing your expert knowledge
Regards
Manish Sawjiani
Three Cheers to Technet for the Help!
Hi Manish
Just Check this. This might be useful to you
http://msdn.microsoft.com/library/de...ar_ts_8ynn.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
"Manish Sawjiani" wrote:

> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Go to www.microsoft.com/sql and read about the editions available. Note that there recently has been
released a "workgroup" edition.

> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
Are you asking of your code will still work? Probably, but you need to plan for test and possibly
(although unlikely) code changes between 7.0 and 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in message
news:F4CFED90-C7FC-484C-B409-C5A75F990EE8@.microsoft.com...
> Dear Experts,
> Right now i am doing a development using MSDE 7.0 and VB. Soon this
> development will be completed (Hopefully!!!) and i will have to recommend to
> a prospective client a version of SQL Server. The application is quiet small
> for about 6 to 8 users. I would appreciate if you could throw some light on
> the version of SQL Server i may need.
> If the client wants to buy the latest i.e. Sql Server 2005 then will there
> be any conversion problems from 7.0
> Thanks for Help and like always thanks for passing your expert knowledge
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Tuesday, March 20, 2012

Calculate fiscal periods (T-SQL)

Calculate fiscal periods (T-SQL)

Calculate fiscal periods (T-SQL)

Monday, March 19, 2012

calculate cummulative or store it

Thursday, February 16, 2012

Buy Enterprise or Standard Edition

Buy Enterprise or Standard Edition

Buy Enterprise or Standard Edition

Bulkload XML Code

Blog Archive

About Me