Creating a table with a dual primary key

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!Does it matter that the destination needs to start at 1 and be sequential? If not, just use the Identity value. Probably too simple.

If you do it in a cursor, you could use a counter and move each record one at a time and use your counter to create the line number.

You could create the line number field in your source and pre-populate it with a process to loop through and assign the line numbers just prior to dumping it to the destination.

Doing it within one SQL statement to move it from one to the other might be impossible.|||Yes, you can create primary key that consists of two columns, or composite primary key.
It is not possible to create dual promary key in SQL server. You may create multiple uniqe index/constraints. But for one talbe, there is only one primary key.|||It is not quite clear what you exactly want to do.

Do you want to add a new column for an existing table as a primary key or is it a whole new table?|||I think we are on the right track (sort of).

I my example, the Order ID's are already assigned (they basically use an Identity Key).

What I need is some sort of query (or activeX script) that will loop through each record, and assign the Order_Line_ID (By the way, I don't care how many SQL statements this takes, as long as it works).

I am querying the Order_Line table, and sorting it by the Order#, so the table will look like this at first:

LineID-Identity | Order#
1 | 12345
2 | 12345
3 | 12345
4 | 12346
5 | 12347
6 | 12347
7 | 12348

The idea is, an order may have multiple lines (you've probably ordered more than one item at a time from some site online)

The counter needs to start over at 1 when it comes to a new order. For example, if the first three records in the Order_Line table are from Order# 12345, the first three rows would be numbered 1, 2 and 3, respectively (that should make sense). Now the tricky part: If the next Order # is 12346, the counter would start at 1 again, like this.

LineID-Identity | Order# | OrderLine#
1 | 12345 | 1
2 | 12345 | 2
3 | 12345 | 3
4 | 12346 | 1
5 | 12347 | 1
6 | 12347 | 2
7 | 12348 | 1

As you can see, this is a new column, so no data is being removed. How do you do this?|||Within SQL, use a cursor and loop through a sorted list of the line items. Use a "current" and "previous" variable to hold the order ID and a counter to hold the new line number. Create the field (null at first), loop through the cursor and using the logic of comparing the last order ID to the current one, assign the line number from the counter, increment the counter, etc. Look up the cursor options you'll need to use to make the cursor editable.

Creating a table script that includes the data in the table

Hello,

I'm having a bear of a time moving my data from one database to another. Unfortunately both these databases are at webhosting providers so I don't readily have access to backup images. I might be able to get the new provider to put the backup image somewhere where I can reach it but the old provider is playing dead so I can't get to any backup image I could generate.

I've tried using Enterprise Manager for SQL2000 to move data from one to another. This grinds away for a while and eventually errors out with permission problems. I will try this again tonight as the provider has attempted to give me the needed permissions. In the meantime I'd like to try to pull the data out of the database just in case it goes belly-up. I've tried to create a script for my tables but all I get is the table structure, no data.

I don't have access to a full Enterprise Manager right now, only SQL Server Management Studio Express (someone needs to shorten that name :).

I'm a big SQL Server fan but it sure is easy to create one big SQL script for a MySQL databsae that you pull out of one db and apply to another one. Poof! Cloned copy. How do I do this with MSSQL and freely available tools? Command line is fine. Surely this is a problem that people have all the time?

Thanks,

Sander

Hi,

for a TSQL solution this could do the trick for you:

http://vyaskn.tripod.com/code.htm#inserts

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hello,

thanks for posing this solution! I succeeded in the meantime to copy the data using EM but I still want this tool to work so I'll give that a try as well.

Thanks,

Sander

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!!

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!!

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!!

Creating a 4-4-5 Time Dimension

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 script out there that will help me build a Fiscal calendar such as the one described above?

I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.

Thank you!

Phew. That doesn't sound easy - other than just looping over the weeks I don't see how it could be done. I suggest you post this on the T-SQL forum. I bet there's some people on there that would love to have a go at this.

-Jamie

|||I agree wit Jamie, the T-SQL forum is a better place for that. We have handle those cases in the past via stored procedure, but sorry I don't have a sample now. This a fairly common scenario and I am pretty sure there have to be some sample code out there.|||

Thank you for your response! I will close this one out.

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Create a backup and restore that on to the machine.
Simple and fast.
Mark Baekdal
www.dbghost.com
DB Ghost rocks!
>--Original Message--
>Hi, how to create a single script file that contains db
schema and data?
>(Moving db to another machine with sql script file) . I
have tried data
>export wizard, but it does not export data, but only
schema of db.
>
>.
>|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>