SQL server questions: A tricky query

I have to write a query to generate a report over some interesting
data. It's basically scheduling which days people are working. The
data looks like this:
Employee StartDate EndDate Roster
-- -- -- --
Bob 12-Jun-06 24-Jun-06 _*___**
Mary 12-Jun-06 24-Jun-06 *_*__*_
The trick is, the roster field contains a string with a _ or *
depending on wether the person is scheduled to work that day or not,
but the first character always starts on the sunday. The startdate and
enddate can be any day of the w

.
In the example above, the 12-jun is a monday, so monday corresponds to
the second character in the roster string, so Bob's working and Mary's
not. The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here! Now, this
roster string could be 7, 10, 14 days long. The startDate -> endDate
could be the length of the roster string or less (only show a subset of
the roster data).
So! I need to write a query to feed a report to format this into
something like:
Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
Friday 16-Jun
-- --
-- --
--
Bob Mary
Bob
Mary
I could get the report out if I can write a query to get it to this:
Employee DateWorking
-- --
Bob 12-Jun
Bob 16-Jun
Mary 13-Jun
Mary 16-Jun
Any ideas?
Thanks!
DaveDave
Can I ask you , why not just doing such reports on the client side? T-SQL
is not good for such things
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w

.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||>> I have to write a query to generate a report over some interesting data.
<<
First of all, you are not using ISO-8601 format dates. You might want
to do that, since what you did post was ambigous as well as
non-standard, I hope you do not think that ORACLE is a standard.
Next, you might want to read a book on programming principles. We do
not do reports in the database in a tiered architecture. This is more
fundamental than SQL.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. I
would love to see the LOGICAL definition of that silly bar chart you
labeled "roster" in your narrative since it is pure display.
CREATE TABLE Roster
(employee_name VARCHAR(20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (employee_name, start_date));
This is usually done with a Calendar table:
SELECT R.employee_name, C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Roster AS R
ON C.cal_date BETWEEN R.start_date AND R.end_date
WHERE C.cal_date BETWEEN @.my_start_date AND @.my_end_date;
Google for other uses of the Calendar and auxiliary tables.|||Dude, you don't do things by halves... <g>
I can't think of any way to do it without iterating over each employee...
step 1:
select employee, startDate, abs(datediff(day, startDate, endDate)) as
dateIterations, Roster
into #tmp1
from theTable
step 2:
-- loop the temp table
-- for each row
-- loop the roster string from 1 to dateIterations
-- if substring(roster, index, 1) = '*'
-- insert a row for employee, dateadd(day, index, startdate)
step 3:
join Thetable to #tmp1 by employee
It sounds like you need to denormalize the results so you could also start
by creating a date table from min(startDate) to max(endDate) first with all
the dates inbetween,
then outer join to that by date to #tmp1 and theTable
[Sidenote]
This is one of the reasons why normalization is a good thing. It's
extremely hard to pull peices-parts of data from a column that contains
multiple data points...
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w

. Look at
these three bits of the description:
- "the first character always starts on the sunday"
- "The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here!"
- " this roster string could be 7, 10, 14 days long."
With a string of length 7 or 14 I can see how it can wrap around and
still start on Sunday. With a length of 10 I can not see how this is
possible.
A second point of confusion is the sample data. The date ranges give
are far longer than the strings . This makes no sense.
Anyway, I believe that once those issues have been resolved the
following will work. Note that I changed the date range from the test
data you provided so that it did not exceed the length of the string.
CREATE TABLE Numbers
(nbr int not null)
INSERT Numbers
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT
5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT
10 UNION
SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '12 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '12 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
--A view that lines up the string with the StartDate
CREATE View Madness_V
AS
SELECT Employee, StartDate, EndDate,
SUBSTRING(RTRIM(Roster) + Roster,
Datepart(w

day,StartDate),
DateDiff(day,StartDate,EndDate)) as Shifted
FROM Madness
GO
--Lets see what the view gives us
SELECT *
FROM Madness_V
--Now for the desired results
SELECT M.Employee,
DATEADD(Day,Nbr-1,StartDate) as WorkingDate
FROM Madness_V as M
JOIN Numbers as N
ON Nbr <= Datediff(day,M.StartDate,M.EndDate)
WHERE SUBSTRING(Shifted,Nbr,1) = '*'
order by 1, 2
Roy Harvey
Beacon Falls, CT
On 21 Jun 2006 06:00:05 -0700, "Dave Newman" <ddangerous@.gmail.com>
wrote:

>I have to write a query to generate a report over some interesting
>data. It's basically scheduling which days people are working. The
>data looks like this:
>
>Employee StartDate EndDate Roster
>-- -- -- --
>Bob 12-Jun-06 24-Jun-06 _*___**
>Mary 12-Jun-06 24-Jun-06 *_*__*_
>The trick is, the roster field contains a string with a _ or *
>depending on wether the person is scheduled to work that day or not,
>but the first character always starts on the sunday. The startdate and
>enddate can be any day of the w

.
>In the example above, the 12-jun is a monday, so monday corresponds to
>the second character in the roster string, so Bob's working and Mary's
>not. The roster string wraps around, so the first character of the
>roster string actually corresponds with the enddate here! Now, this
>roster string could be 7, 10, 14 days long. The startDate -> endDate
>could be the length of the roster string or less (only show a subset of
>the roster data).
>So! I need to write a query to feed a report to format this into
>something like:
>Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
>Friday 16-Jun
>-- --
>-- --
>--
>Bob Mary
> Bob
> Mary
>I could get the report out if I can write a query to get it to this:
>Employee DateWorking
>-- --
>Bob 12-Jun
>Bob 16-Jun
>Mary 13-Jun
>Mary 16-Jun
>
>Any ideas?
>Thanks!
>Dave|||I don't understand your definition of how a roster day maps to an actual
date so I can't calculate that, but you can use recursion to "unflatten"
roster string into a row for each '*' and go from there. I also don't know
what the primary key of your table is, but I assume that name+start date
will work for that.
Create table #sched
(
name NVARCHAR(20),
start DATETIME,
[end] DATETIME,
roster NVARCHAR(14)
)
--TRUNCATE TABLE #sched
INSERT INTO #sched VALUES (N'bob', '12-JUN-06', '24-JUN-06', N'_*--**')
INSERT INTO #sched VALUES (N'mary', '12-JUN-06', '24-JUN-06', N'*_*__*_')
WITH workingDays
AS
(
-- get the first roster day for every entry in table
SELECT name, CHARINDEX(N'*', roster) AS rosterDay, start, roster FROM #sched
WHERE CHARINDEX(N'*', roster, 0) > 0
UNION ALL
-- now get subsequent roster days
SELECT S.name, CHARINDEX(N'*', S.roster, W.rosterDay + 1) AS rosterDay,
S.start,
S.roster from #sched AS S
JOIN workingDays AS W ON W.name = S.name and W.start = S.start AND
CHARINDEX(N'*', S.roster, W.rosterDay + 1) > 0
)
SELECT name, rosterDay, roster, start,
DATEADD(day, rosterDay-1, start) as DateWorking
FROM workingDays
ORDER BY name, rosterDay
name rosterDay roster start Date
Working
-- -- -- -- --
--
bob 2 _*--** 2006-06-12 00:00:00.000 2006
-06-13
00:00:00.000
bob 6 _*--** 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
bob 7 _*--** 2006-06-12 00:00:00.000 2006
-06-18
00:00:00.000
mary 1 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-12
00:00:00.000
mary 3 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-14
00:00:00.000
mary 6 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
If you replace the DateWorking column with a UDF that takes as input a roste
rDay
and start and produces a working date I think you will have what you want.
Dan

> I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate
> and enddate can be any day of the w

.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset
> of the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
> Any ideas?
> Thanks!
> Dave
>|||Hi Dave,
It would have been great if you had given the ddl. Anyways, here is the
solution.
Let me know if this works..
The ddl and ur sample data first
create table t1 (Employee varchar(10), StartDate datetime,
EndDate datetime, Roster varchar(10))
insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
'_*___**')
insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
'*_*__*_')
And here is the query. You need a temp table so that I don't loop through
that roster or whatever
--I have assumed that the size of the roster is 30 characters. If yit can be
100 or 1000 increase it accordingly
select top 30 identity(int,0,1) as num into #temp from sysobjects
--query
-- This is to remove any ambiguity
set datefirst 7
select Employee, StartDate - datepart(dw,startdate) + num + 1
from t1, #temp
where
substring(Roster,num%len(roster) + 1,1) = '*'
and StartDate - datepart(dw,startdate) + num + 1 between startdate and endda
te
Let me know if this was what you wanted.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi There,
I think this may solve your problem
Try it and let me know if it helped
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '13 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '13 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
Select identity(int,1,1) myid into Numbers from sysobjects
Select Case When myid<pos Then startdate+myid-1
Else startdate+pos-datepart(dw,startdate)
end ,
Employee, Case when substring(roster,pos,1)='*' then 'Working' else
'Not' End
From
(
Select myid,
case when (datepart(dw,startdate)+myid-1)%datalength(roster)= 0 then
datalength(roster) else
(datepart(dw,startdate)+myid-1)%datalength(roster) end Pos,
Employee,
Startdate,
Enddate,
Roster
>From Madness,Numbers
where myid <= datalength(Roster)
) XY order by 2,1
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Omnibuzz wrote:
> Hi Dave,
> It would have been great if you had given the ddl. Anyways, here is the
> solution.
> Let me know if this works..
> The ddl and ur sample data first
>
> create table t1 (Employee varchar(10), StartDate datetime,
> EndDate datetime, Roster varchar(10))
> insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
> '_*___**')
> insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
> '*_*__*_')
>
> And here is the query. You need a temp table so that I don't loop through
> that roster or whatever
> --I have assumed that the size of the roster is 30 characters. If yit can
be
> 100 or 1000 increase it accordingly
> select top 30 identity(int,0,1) as num into #temp from sysobjects
>
> --query
> -- This is to remove any ambiguity
> set datefirst 7
> select Employee, StartDate - datepart(dw,startdate) + num + 1
> from t1, #temp
> where
> substring(Roster,num%len(roster) + 1,1) = '*'
> and StartDate - datepart(dw,startdate) + num + 1 between startdate and end
date
> Let me know if this was what you wanted.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/sql

Tuesday, March 20, 2012

A tricky query

No comments:

Post a Comment

SQL server questions

Blog Archive

About Me