Creating DB from scripts: basically

Showing posts with label basically. Show all posts

Thursday, March 29, 2012

Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Sunday, March 11, 2012

Creating a normalized database

Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!4 easy steps

CREATE the client table (ClientID, ClientName, Address...)

SELECT DISTINCT Client info from the case table into the new Client Table

Build Relationship between the 2 tables (on ClientID)

DELETE the redundant columns from case table|||You can use a ClientCase table with a many to many relationship between your Clients and your Cases tables.

Clients ClientCase Case
------ ------ ------
CliNumber --> CliNumber
CaseNumber <-- CaseNumber
Cliname CaseLeadAtty
CliAddress CaseSecondAtty

etc.|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?|||... unless one case number can have multiple clients?if a case only and forever belongs to only one client, then yeah, you don't need the many-to-many relationship table

however, note that you can implement a one-to-many relationship using a many-to-many relationship table -- just make sure (in your app logic) that you never store more than one client per case!!

then, when the day comes, and the case rolls in which requires two clients, you're all set!!

:) :)|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?

How about a class-action lawsuit?|||4 easy steps

CREATE the client table (ClientID, ClientName, Address...)

SELECT DISTINCT Client info from the case table into the new Client Table

Build Relationship between the 2 tables (on ClientID)

DELETE the redundant columns from case table
Thank you! That worked like a charm. I still have a few duplicates but it beats going through all of them manually. :)

Creating a Maintenance Plan via the Command Line

Hello All,

I've searched high and low for documentation on this to no avail.

Basically my goal is to create a maintenance plan in SQLSERVER2005 via the command line. I need to create this plan in a way that it can be seen in the list of Maintenance Plans in the Management Studio Interface. I went into the SSIS designer and created my plan. I now have a DTSX file. I tried the dtutil.exe utility, however i never saw my maintenance plan in the list of plans.

I ran dtutil.exe and did /FILE to /SQL but i don't see the plan listed or in a way a user could modify it, which is of the utmost importance to my clients.

How do i get my file to turn into a real running Maintenance Plan that is seen in the list of Maintenance Plans via the Management Studio Interface and is editable by clients?

Other things to keep in mind, i'm attempting to create these via Installshield MSI installer. So i need to do it via command line, or file system-wise. No interface or user interaction.

Please Adivse.

Hi Brendan,

You can use the stored procedures sp_add_maintenance_plan, sp_add_maintenance_plan_db and sp_add_maintenance_plan_job to create maintenance plans, add the databases to it and associate the jobs with the same. Refer documentation on these from Books Online.

Thanks,
Kuntal

|||You have to take help of SQLCMD in this case where using the statements referred above as a Stored procedure, this will do when you want to deploy as the package.|||These three SP's i understand. But how do i reference the file? Or how do i give the plan the 'meat' of what it needs to do? From what i can tell i went to the designer created my dtsx file, and that code in that file need to be input somewhere... can that be done through a sproc as well?|||Anyone?|||

BOL states:

The sqlmaint utility performs a specified set of maintenance operations on one or more databases. Use sqlmaint to run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes. All database maintenance activities generate a report that can be sent to a designated text file, HTML file, or e-mail account. sqlmaint executes database maintenance plans created with previous versions of SQL Server. To run SQL Server 2005 maintenance plans from the command prompt, use the dtexec utility utility.

|||

Yeah i wish i could run it like that, but i need it to run in the exact fashion as if it were created in the GUI. Maybe i should explain this better...

I need a programatic way, either through sprocs or command line, to create a maintenance plan, that will show up in the GUI, under maintenance plans, with my name, and have all the steps i specify in the design screen.

Above i see that i run these;

sp_add_maintenance_plan : adds my plan. ok great.

sp_add_maintenance_plan_db : adds a databse that i want to run the plan against, ok great.

sp_add_maintenance_plan_job : ok, adds a job to run my plan. great.

But where does the 'meat' , the steps i designed in the SSIS designer go? all i want to do is add a new maintenance plan that shows in the GUI and has all my steps in the plan. No the steps in the job, the steps in the plan.

I'm sorry, for some reason i cannot wrap my head around this. Thanks for your patience. But i'm not a SQLSERVER admin, i'm an install guy. Any further help would be greatly appreciated.

-b

|||

You can create the SSIS package to perform this maintenace task and you can deploy that SSIS package to multiple servers, http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

Drop me an email using my site (contact us) below and I can talk you through the steps.

|||Emailed...|||Anybody have a solution to this? I'm sure it's quite simple, there is just no direct example or i'm not doing this correctly. I'm getting rather desperate here... any help would certainly be appreciated.|||

Brendan

Sorry I didn't get any email from you, could you please send it to smaster@.sqloogle.co.uk.

Brendan Stewart wrote:
Emailed...

Thursday, March 8, 2012

Creating a Maintenance Plan via the Command Line

Hello All,

I've searched high and low for documentation on this to no avail.

I ran dtutil.exe and did /FILE to /SQL but i don't see the plan listed or in a way a user could modify it, which is of the utmost importance to my clients.

How do i get my file to turn into a real running Maintenance Plan that is seen in the list of Maintenance Plans via the Management Studio Interface and is editable by clients?

Other things to keep in mind, i'm attempting to create these via Installshield MSI installer. So i need to do it via command line, or file system-wise. No interface or user interaction.

Please Adivse.

Hi Brendan,

Thanks,
Kuntal

BOL states:

|||

Yeah i wish i could run it like that, but i need it to run in the exact fashion as if it were created in the GUI. Maybe i should explain this better...

Above i see that i run these;

sp_add_maintenance_plan : adds my plan. ok great.

sp_add_maintenance_plan_db : adds a databse that i want to run the plan against, ok great.

sp_add_maintenance_plan_job : ok, adds a job to run my plan. great.

I'm sorry, for some reason i cannot wrap my head around this. Thanks for your patience. But i'm not a SQLSERVER admin, i'm an install guy. Any further help would be greatly appreciated.

-b

|||

Drop me an email using my site (contact us) below and I can talk you through the steps.

Brendan

Sorry I didn't get any email from you, could you please send it to smaster@.sqloogle.co.uk.

Brendan Stewart wrote:
Emailed...

Wednesday, March 7, 2012

Creating a fork in the road

Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?

Question the second: After I aggregate my records (down one of the paths), I need to store some columns as xml. Is there a tool for this?

Thanks for all your help!

Jim Work

Jim Work wrote:
Question the first: So my records are going along nicely, but I need them to split up (basically, I need to create a copy of the record and send one copy down one path, and another copy down another path). Any ideas how to do that?

Use the multicast transformation.|||For "forking" the data like that, you can use the Multicast component in the data flow tab.|||Oh, wow! Thanks a bunch!

Jim Work

Friday, February 24, 2012

Creating a 4-4-5 Time Period table

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

check the sample code as below:

decalre @.dt_startdate datetime

set @.dt_startdate = '2007-01-01'

select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),

dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),

dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),

'next_startdate'=dateadd(day,86@.dt_startdate)

use this method for build date list.

take 'next_startdate' replace the @.dt_startdate.

hoping this can help you.

Best Regrads,

Hunt.

|||

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

|||

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iDaysInFiscalYear smallint

set @.dtFiscalYEarStart = 'January 1, 2007'

set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart)

set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd)

declare @.Numbers table(Num int, dtTemp smalldatetime)

insert into @.Numbers select 0, @.dtFiscalYEarStart

declare @.i tinyint

set @.i = 0

while @.i < 9

begin

insert into @.Numbers select Num + power(2,@.i)

, Dateadd(d, power(2,@.i), dtTemp) from @.Numbers

set @.i = @.i + 1

end

delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd

select dtTemp

, Num + 1 as FiscalDay

, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10

when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11

else 12

end

as [FiscalMonth]

, case

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3

else 4

end

as FiscalQuarter

, datepart(dy,dtTemp) as CalendarDayOfYear

, Datepart(wk,dtTemp) as CalendarWeekOfYear

, Datepart(m,dtTemp) as CalendarMonth

, Datepart(q,dtTemp) as CalendarQuarter

from @.Numbers

order by Dateadd(d, Num, @.dtFiscalYearStart)

|||

Oh rusag2! I Bow To Thee!!!

I truly apologize for not looking at your post for this long. I have not figured out how to get alerts in my emal when a post is entered.

Your code is really amazing. I ran it and am now comparing it with a physical copy of a fiscal calendar I got from Finance. We seem to be off by a day. You see, at DBL we end our weeks on a Sat; the first month therefore ends on the 27th instead of the 28th, and although the 28th happens to be a Sunday it is a big deal for us as we process e-commerce orders on Sundays. Further, our year will end on the 29th and 2008 will being on the 30th.

As I would not know where to being modifying your code to accomplish the above I would sincerely appreciate some direction from you.

Thanks so much for taking the time to write the code!!! I would love to use it but need to make the changes I have indicated above.

Thanks again!

|||

Near the very top, there is a "FiscalYearStart" variable. Currently, it's set to January 1, 2007. That's a Monday...which, following a 4, 4, 5 rule, (which is weeks) then if the fiscal year starts on January 1, 2007...well then the week ends on Sunday.

Try changing the value of that variable to "December 31, 2006" (that's a sunday). That way, the last day of the week will be saturday.

|||

Thanks!

I almost get what I want when I start the date on December 31, 2006. I need to check with Finance if it is correct though.

The other issue is that December 2007 should end on the 29th, and fiscal 2008 should start on December 30th. How can I get your code to do this.

Early on you mentioned that the last quarter needed to be 93 days; can we not have that be the case because at DBL we actually end our fiscal year on the 29th.

|||

Ok, the first answer was a bit...over the top.

Try this. Explicitly define the StartOfFiscalYear and EndOfFiscalYear dates:

Code Snippet

--A few variables:

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iTemp int

This is the table we'll populate and return at the end

declare @.tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate smalldatetime

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @.dtFiscalYearStart = 'December 31, 2006'

set @.dtFiscalYearEnd = 'December 29, 2007'

set @.iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @.tb where CalendarDate >= @.dtFiscalYearEnd)

begin

insert into @.tb (CalendarDate, FiscalWeek)

select dateadd(dd, @.iTemp, @.dtFiscalYearStart), (@.iTemp / 7) + 1

set @.iTEmp = @.iTemp + 1

end

update @.tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 5

update @.tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 9

update @.tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek < 14

update @.tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 18

update @.tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 22

update @.tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek < 27

update @.tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 31

update @.tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 35

update @.tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek < 40

update @.tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 44

update @.tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek < 48

update @.tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek > 47

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

--select FiscalMonth,count(distinct fiscalWeek) from @.tb group by FiscalMonth

select * from @.tb

|||

Well rusag2, life is just about the get more interesting.

I just had a conversation with Finance and they confirmed that the Fiscal Calendar is not going to have 365 days all the time. Further, the calendar method that has been in use for awhile is called the Retail Calendar, which is what I need to take a look at.

Basically, every so often the last quarter of the year becomes a 4-4-6 to "catch-up" for a wekk lost in previous years.

I have to get to the bottom of this and so am going to do some research on how the Retail Calendar can be programmed. Apparently all the retail stores have this programmed so I am hoping there is something out there.

I will keep you posted.

Thanks a bunch again for your efforts!!

desibull

|||

rusag2:

Can you modify your code to accept the start dt, end dt, and the number of weeks for the last quarter as variables and then just cutoff the year when you reach that last day?

Let me know if I am pushing my luck! Your code is almost there and I really would like it to work.

|||

Use my most recently posted code. You explicity specify the Fiscal Year Start Date and Fiscal Year End Date.

Then, I build the year, one week at a time, going 4-4-5 for each quarter until the last when I go 4-4-<Whatever Is Left>.

|||

I did and it is almost working like a charm except that for some reason the script is creating an extra day at the end. Any clues why?

For example, I provided the following values: Start Dt: December 31, 2006 End Dt: December 29, 2007. I got a record for 12/30/07, which I should not. It should be part of 08.

|||

This is a quesion of "Through" vs. "To"

Just adjust your end date.

Or you may change this:

CalendarDate >= @.dtFiscalYearEnd

to this:

CalendarDate > @.dtFiscalYearEnd

|||

Actually, setting it to >= worked. Somehow it got changed to >.

Your code now is fully functional.

I cannot thank you enough, rusag2. When I have some spare time I would like to go through your code and understand what you have done. Many of the functions you have used are new to me. It is one slick code though!!

Thanks!

|||

Spoke too soon! Your second code while it produced the correct end date does not set the Fiscal month correctly. Your first code is working correctly. I made a similar change to the first one and it works.

Regardless, you are a genius!!

Sunday, February 19, 2012

Creating a 4-4-5 Time Period table

Hello:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension.

Is there any T-SQL script out there that will help me build a Fiscal calendar such as the one described above?

Thank you!

Hi desibull,

this only way i though it use "dateadd" function for your issue.

check the sample code as below:

decalre @.dt_startdate datetime

set @.dt_startdate = '2007-01-01'

select dateadd(day,0,@.dt_startdate),dateadd(day,27,@.dt_startdate),

dateadd(day,28,@.dt_startdate),dateadd(day,55,@.dt_startdate),
dateadd(day,56,@.dt_startdate),dateadd(day,85,@.dt_startdate),
'next_startdate'=dateadd(day,86@.dt_startdate)
use this method for build date list.
take 'next_startdate' replace the @.dt_startdate.

hoping this can help you.

Best Regrads,
Hunt.

|||

Excel is a quick and easy way to create date/time dimensions. You can then import them into sql server using ssis.

|||

The funky thing here is that 445, 445, 445, 445 (for quarters) leaves a couple days at the end of a year. It's three 91-day quarters and 1 93-day quarter.

Here's the query i came up with. Nothing succedes like brute force!

You can set any date for @.dtFiscalYearStart and this query will work...that's the only variable. no tables needed for this...just run it.

Code Snippet

declare @.dtFiscalYearStart smalldatetime

, @.dtFiscalYearEnd smalldatetime

, @.iDaysInFiscalYear smallint
set @.dtFiscalYEarStart = 'January 1, 2007'

set @.dtFiscalYearEnd = dateadd(yyyy, 1, @.dtFiscalYEarStart)

set @.iDaysInFiscalYear = datediff(d, @.DtFiscalYearStart, @.dtFiscalYearEnd)

declare @.Numbers table(Num int, dtTemp smalldatetime)

insert into @.Numbers select 0, @.dtFiscalYEarStart

declare @.i tinyint
set @.i = 0

while @.i < 9
begin

insert into @.Numbers select Num + power(2,@.i)

, Dateadd(d, power(2,@.i), dtTemp) from @.Numbers
set @.i = @.i + 1
end
delete from @.Numbers where dtTemp >= @.dtFiscalYearEnd

select dtTemp
, Num + 1 as FiscalDay
, Dense_Rank() over (Partition by Num % (7) order by dtTemp) as FiscalWeek

, case
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 1 and 4 then 1
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 5 and 8 then 2
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 9 and 13 then 3
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 14 and 17 then 4
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 18 and 21 then 5
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 22 and 26 then 6
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 27 and 30 then 7
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 31 and 34 then 8
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 35 and 39 then 9
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 40 and 43 then 10
when Dense_Rank() over (Partition by Num % (7) order by dtTemp) between 44 and 47 then 11
else 12
end
as [FiscalMonth]
, case

when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*1) then 1
when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*2) then 2
when datediff(d, @.dtFiscalYEarStart, dtTEmp) < (91*3) then 3
else 4
end

as FiscalQuarter
, datepart(dy,dtTemp) as CalendarDayOfYear
, Datepart(wk,dtTemp) as CalendarWeekOfYear
, Datepart(m,dtTemp) as CalendarMonth
, Datepart(q,dtTemp) as CalendarQuarter

from @.Numbers

order by Dateadd(d, Num, @.dtFiscalYearStart)