May 4, 2010 at 10:13 am
Hello all!
Given the following situation:
A certain way of handling an employee's overtime is dependent on an arrangement.
This collective arrangement typically starts someplace in a year and employees subscribe to that arrangement from a certain date.
That arrangement has a number of parts which sequentially last for a set amount of weeks. After all parts have elapsed, the arrangement starts anew.
What I want to do is tally a definable value depending in which arrangement part the day has been. For example:
An employee is in an arrangement from the 4th of april, which in turn starts on the first.
The first five weeks all overtime is also tallied to a balance that lasts for the entire arrangement.
The next five weeks overtime works as always.
The last five weeks all hours not accounted for are taken from the balance.
After these the balance is set back to zero and the arrangement starts anew from the 15th of july, etc. until another arrangement is made.
Now I can take a random day in the year and calculate in which arrangement cycle it is. The first before the 15th of july, the second on and after that.
What I want to be able to tell is, which arrangement part applies to any given day anywhere in the year? I considered taking the starting date of that cycle and loop the parts until the closest starting date is found for my given day, but that smells like cursors, and there's probably a better way to do that.
Could anyone give me some helpful advice on this? It'd be much appreciated!
Thanks in advance,
Kind regards,
Oscar Janson
May 4, 2010 at 10:53 am
Most definitely, there is no c.u.r.s.o.r. required.
The solutions depend on your data structure, which is unknown to us (so far). So please help us help you.
If you would provide the table definition (DDL script) together with some sample data in a ready to use format including your expected output based on that, what you've tried so far and where you get stuck, I'm sure there will be numerous people trying to help you. If you need a guide how to post sample data please read and follow the first link in my signature.
May 5, 2010 at 6:40 am
Hi Lutz,
I've read the article linked to your topmost signaturelink, and I've come up with the following. Hope it becomes clear enough. 🙂
/* Generating and populating tables. */
CREATE TABLE #regeling(
[regeling_id] [int] IDENTITY(1,1) NOT NULL,
[regeling_code] [varchar](30) NOT NULL,
[regeling_omschrijving] [varchar](80) NULL,
[regeling_startdatum] [datetime] NOT NULL,
CONSTRAINT [PK_regeling] PRIMARY KEY CLUSTERED
(
[regeling_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #regelingsperiode(
[regelingsperiode_id] [int] IDENTITY(1,1) NOT NULL,
[regelingsperiode_nummer] [int] NOT NULL,
[regelingsperiode_regelingscode] [varchar](30) NOT NULL,
[regelingsperiode_omschrijving] [varchar](80) NULL,
[regelingsperiode_weken] [int] NOT NULL,
CONSTRAINT [PK_regelingsperiode] PRIMARY KEY CLUSTERED
(
[regelingsperiode_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO #regeling (regeling_code, regeling_omschrijving, regeling_startdatum)
SELECT 'TEST','Test','Apr 1 2010 12:00AM'
GO
INSERT INTO #regelingsperiode (regelingsperiode_nummer, regelingsperiode_omschrijving,
regelingsperiode_regelingscode, regelingsperiode_weken)
SELECT '1','Plus shift','TEST','5' UNION ALL
SELECT '2','Normal shift','TEST','5' UNION ALL
SELECT '3','Minus shift','TEST','5' UNION ALL
SELECT '4','End of cycle','TEST','0'
GO
/* Here's what I can do. The 15th of July is a new cycle start, so changing the
hardcoded date in the query to something below that will return the first of April
again in the 'new start date field' (NewStartDatum)
*/
SELECT regeling_id
,regeling_code
,regeling_omschrijving
,regeling_startdatum
,DATEADD(WEEK,
(SELECT SUM(regelingsperiode_weken) *
((CONVERT(int,'2010-07-20 00:00:00.000' - regeling_startdatum) / 7) / SUM(regelingsperiode_weken))
FROM #regelingsperiode rp
WHERE rp.regelingsperiode_regelingscode = re.regeling_code),
regeling_startdatum
) AS NewStartDatum
FROM #regeling re
/* What I want to do is extend the part of code building NewStartDatum to give me a specific regelingsperiode_id based on the currently
hardcoded date and the amount of weeks given in regelingsperiode_weken. Regelingsperiode_nummer tells me the
sequence for the cycle. Number 1 starts at the 1st of April or the 15th of July etc., number two 5 weeks after those
dates, number three another five weeks, one record in which cleanup rules are linked ends immediately, then the first
number of the next cycle starts again. The 20th of July should thus be in the first number of the second cycle, telling
me that regelingsperiode_id 1 is the linked record.
*/
SELECT [regelingsperiode_id]
,[regelingsperiode_nummer]
,[regelingsperiode_regelingscode]
,[regelingsperiode_omschrijving]
,[regelingsperiode_weken]
FROM #regelingsperiode
Thanks again!
Kind Regards,
Oscar Janson
May 5, 2010 at 7:50 am
Oscar Janson (5/5/2010)
Hi Lutz,I've read the article linked to your topmost signaturelink, and I've come up with the following. Hope it becomes clear enough. 🙂
/* Generating and populating tables. */
CREATE TABLE #regeling(
[regeling_id] [int] IDENTITY(1,1) NOT NULL,
[regeling_code] [varchar](30) NOT NULL,
[regeling_omschrijving] [varchar](80) NULL,
[regeling_startdatum] [datetime] NOT NULL,
CONSTRAINT [PK_regeling] PRIMARY KEY CLUSTERED
(
[regeling_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #regelingsperiode(
[regelingsperiode_id] [int] IDENTITY(1,1) NOT NULL,
[regelingsperiode_nummer] [int] NOT NULL,
[regelingsperiode_regelingscode] [varchar](30) NOT NULL,
[regelingsperiode_omschrijving] [varchar](80) NULL,
[regelingsperiode_weken] [int] NOT NULL,
CONSTRAINT [PK_regelingsperiode] PRIMARY KEY CLUSTERED
(
[regelingsperiode_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO #regeling (regeling_code, regeling_omschrijving, regeling_startdatum)
SELECT 'TEST','Test','Apr 1 2010 12:00AM'
GO
INSERT INTO #regelingsperiode (regelingsperiode_nummer, regelingsperiode_omschrijving,
regelingsperiode_regelingscode, regelingsperiode_weken)
SELECT '1','Plus shift','TEST','5' UNION ALL
SELECT '2','Normal shift','TEST','5' UNION ALL
SELECT '3','Minus shift','TEST','5' UNION ALL
SELECT '4','End of cycle','TEST','0'
GO
/* Here's what I can do. The 15th of July is a new cycle start, so changing the
hardcoded date in the query to something below that will return the first of April
again in the 'new start date field' (NewStartDatum)
*/
SELECT regeling_id
,regeling_code
,regeling_omschrijving
,regeling_startdatum
,DATEADD(WEEK,
(SELECT SUM(regelingsperiode_weken) *
((CONVERT(int,'2010-07-20 00:00:00.000' - regeling_startdatum) / 7) / SUM(regelingsperiode_weken))
FROM #regelingsperiode rp
WHERE rp.regelingsperiode_regelingscode = re.regeling_code),
regeling_startdatum
) AS NewStartDatum
FROM #regeling re
/* What I want to do is extend the part of code building NewStartDatum to give me a specific regelingsperiode_id based on the currently
hardcoded date and the amount of weeks given in regelingsperiode_weken. Regelingsperiode_nummer tells me the
sequence for the cycle. Number 1 starts at the 1st of April or the 15th of July etc., number two 5 weeks after those
dates, number three another five weeks, one record in which cleanup rules are linked ends immediately, then the first
number of the next cycle starts again. The 20th of July should thus be in the first number of the second cycle, telling
me that regelingsperiode_id 1 is the linked record.
*/
SELECT [regelingsperiode_id]
,[regelingsperiode_nummer]
,[regelingsperiode_regelingscode]
,[regelingsperiode_omschrijving]
,[regelingsperiode_weken]
FROM #regelingsperiode
Thanks again!
Kind Regards,
Oscar Janson
can you post required result based on your posted data ? it will be easiler for guys here ti help you sooner/correctly?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 5, 2010 at 9:20 am
Oscar, I think I am following what you are trying to accomplish. Let me know if I am correct.
There is an agreement regarding overtime. This agreement is a compilation of three separate time periods, that do not change, they are always 5 weeks, in groups of 3. So you would be able to figure out the dates of the beginning and end of each 5 week period in advance, say years into the future.
If I'm wrong, let me know, if I am correct, have you thought about using a date table and adding a column for the "omschrijving"?
May 5, 2010 at 9:58 am
There's a select query which last column gives me a date near the bottom of the script.
I want that date replaced by a 1, 2, or 3 (the 4th does not apply since it has no length in weeks) depending on the location of a given date on the cycle.
Within the first 5 weeks of a cycle, it should display 1, within the five after that 2, etc. The given date is the one I have hardcoded for now.
Change it to the 14th of July, and it should say 3, on the 15th it must be 1 again since it's the start of a new cycle.
@Wesley:
Precisely! The length and amount of time periods are customer specific, but they stay constant until an other agreement goes into effect.
I'm not sure what you mean by date table. Do you mean something like a reference table with date equals time period?
That'd be potentially 365 records per agreement per year, which would be an acceptable number, but I'm not sure if that's the way to go.
It's basically a number that could be calculated based on the information given if you know how, so it would be like storing process data.
I agree it would probably simplify the eventual query, so I'll keep it in mind to consider once I have a better understanding on what it would
take otherwise and see if I can perhaps use a temporary table for it or something. All in all quite a good idea, thanks. 🙂
The 'Omschrijving' is a description field, and is not relevant to my calculation. I'm not going to display it anywhere apart from the time period view.
Do you see any reason I might've missed why I might want to change my mind about that?
Thanks to all so far!
Kind regards,
Oscar Janson
May 5, 2010 at 10:14 am
Great! I think using a date table / dimension may work for you on this. Check out the following article:
http://www.sqlservercentral.com/scripts/Data+Warehousing/65762/
It's not really specific to Data Warehousing. It works really great for things that you have to tie specifically to dates. I have a few custom ones here. What I was thinking is this:
You create this date dimension table, it's a table that has every date in it, it's pretty big, but it is able to work quickly. You then add a new integer column and call it "Period" or something. You then populate the entire table with specific period numbers for each day. Then all you have to do is a join to your date table and grab the "Period" column. Do you understand the point I am making?
May 5, 2010 at 10:38 am
I think i might get you what you want, but i need to see a visual representation of what your end result would be.
Desired results in the form of something like tis!
ListName ListMember
-------- -----------
A 5
C 1
Please base your expeted result on the sample data provided by you! This will help us and in-turn you a lot!
Cheers!
May 7, 2010 at 2:36 am
@Wesley:
Yeah, I kind of already was thinking along those lines as soon as you mentioned the idea.. I'm playing with the idea to do that with a temporary table perhaps in a stored proc.
I'm likely to do something in that direction eventually, so again many thanks for your suggestion! 🙂
I'll try to get that resultset for you as soon as I can, right now I'm on another project that I have to get finished by today, so it'll either be the weekend or monday. Thanks for your time so far!
May 12, 2010 at 6:21 am
Back again! As promised, that resultset:
Start of the arrangement:
regeling_id regeling_code regeling_omschrijving regeling_startdatum
------------ -------------- ---------------------- --------------------
1 TEST Test Apr 1 2010 12:00AM
Contents of the arrangement:
regelingsperiode_nummer regelingsperiode_omschrijving regelingsperiode_regelingscode regelingsperiode_weken
------------------------ ------------------------------ ------------------------------- -----------------------
1 Plus shift TEST 5
2 Normal Shift TEST 5
3 Minus Shift TEST 5
4 End of Cycle TEST 0
For a given day, the regelingsperiode_nummer depends on the current date and the duration of the parts in the arrangement.
This is what my data gives so far:
regeling_id regeling_code regeling_omschrijving regeling_startdatum NewStartDatum (Hardcoded value)
------------ -------------- ---------------------- -------------------- ---------------------- -------------------
1 TEST Test Apr 1 2010 12:00AM Apr 1 2010 12:00AM Jul 13 2010 12:00AM
1 TEST Test Apr 1 2010 12:00AM Apr 1 2010 12:00AM Jul 14 2010 12:00AM
1 TEST Test Apr 1 2010 12:00AM Jul 15 2010 12:00AM Jul 15 2010 12:00AM
1 TEST Test Apr 1 2010 12:00AM Jul 15 2010 12:00AM Jul 16 2010 12:00AM
This is what it should give once it's doing what I want:
regeling_id regeling_code regeling_omschrijving regeling_startdatum Period (Hardcoded value)
------------ -------------- ---------------------- -------------------- ---------------------- -------------------
1 TEST Test Apr 1 2010 12:00AM 3 Jul 13 2010 12:00AM
1 TEST Test Apr 1 2010 12:00AM 3 Jul 14 2010 12:00AM
1 TEST Test Apr 1 2010 12:00AM 1 Jul 15 2010 12:00AM
1 TEST Test Apr 1 2010 12:00AM 1 Jul 16 2010 12:00AM
It took me some time to get it properly formatted, (the editor and the preview didn't agree) but I hope it'll clarify things.
Perhaps it's something totally different from what you mean, but with the above it might be easier to ask which data is actually meant.
Thanks in advance!
Kind regards,
Oscar Janson
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply