May 13, 2012 at 10:06 am
Anyone like a good mystery? I am trying to query a vendor provided calendar structure.
Running profiler and modifying the calendar through the front-end it seems there is a record for each month/year combination and then a separate 'mask' field that perhaps represents the totality of the flagged business days for the month/year.
The inset/update statements at the end of my post represent what happens to the table as I insert and delete records using the interface.
There are also guid and id fields that are being updated in this table but I believe these are unrelated and have to do with auditing.
My hunch is that mask field defines the days of the months that are non-business days. My hope is I can understand how and reverse engineer the logic.
Interestingly a month that appears in the app with weekends displayed as nonworking doesn't have an entry in the table. When I unflag a Sunday a record is created. Here is a record inserted when I unflag a Sunday, 20110925- 2011098586764
Speculation welcome.
CREATE TABLE #Test
(
yearmonth int,
mask int
)
--Insert 20111031 through app
INSERT INTO #Test VALUES (201110,1885389187)
--Insert 20111003 through app
UPDATE #Test SET Mask = 1885389191 WHERE yearmonth = 201110
--Delete 20111031 through app
UPDATE #Test SET Mask = 811647367 WHERE yearmonth = 201110
SELECT * FROM #Test
DROP TABLE #Test
May 13, 2012 at 10:10 am
If you know the table name, why not just list the first 60 rows of data and the DDL to start with?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2012 at 6:21 pm
Jeff Moden (5/13/2012)
If you know the table name, why not just list the first 60 rows of data and the DDL to start with?
I have concerns about contractual vendor agreements.
May 14, 2012 at 8:19 am
Chrissy321 (5/13/2012)
Jeff Moden (5/13/2012)
If you know the table name, why not just list the first 60 rows of data and the DDL to start with?I have concerns about contractual vendor agreements.
There seems to be some inconsistency in the above: you're concerned about a vendor agreement, and yet - you are "reviewing" it, and won't discuss it with them. It vaguely sounds like you've already stepped over the line.
If this is something that would fall within the agreement, then ask them to describe how they use the table.
Not trying to shoot you down - just not sure what kind of help you hope for with no ability to elaborate on what's in the table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2012 at 9:38 am
I am trying to walk some kind of line here between describing an issue in general and providing exact DDL. The vendor doesn't support direct database access, they allow it but won't offer any help on understanding it.
The type of help I was hoping to receive was the possibility that someone had seen this type of design before and could provide some approach to help me understand it. Given the nature of the problem and the limited info I provided my hopes were not too high.
The more I think about this the more likely it seems that there is logic embedded in the application and I'll probably end up creating a second calendar.
May 14, 2012 at 11:30 am
I've recently seen a system that used "prime factors" to determine permissions. Maybe the vendor is using a similar idea.
CREATE TABLE #roles (
roleID INT,
roleName VARCHAR(50)
)
INSERT INTO #roles (roleID, roleName) VALUES
(2, 'All')
INSERT INTO #roles (roleID, roleName) VALUES
(3, 'Admin')
INSERT INTO #roles (roleID, roleName) VALUES
(5, 'General User')
INSERT INTO #roles (roleID, roleName) VALUES
(7, 'Specialized User')
CREATE TABLE #permissions (
applicableRoles INT,
permissionName VARCHAR(50)
)
INSERT INTO #permissions (applicableRoles, permissionName) VALUES
(2 * 3 * 5 * 7, 'General Data')
INSERT INTO #permissions (applicableRoles, permissionName) VALUES
(2, 'Administrative')
INSERT INTO #permissions (applicableRoles, permissionName) VALUES
(2 * 5, 'Specialized')
SELECT applicableRoles, permissionName
FROM #permissions
DROP TABLE #permissions
DROP TABLE #roles
Each role is assigned a prime number as an id. For the applicable roles for permissions, all roles that should have that access are multiplied together. If applicableRole mod roleID = 0 then that role has that permission. As you can see, the numbers get pretty big quickly.
When you view the calendar that has Sundays as non-working days, see if the application pulls in data from another table as well. This may give you a clue for deciphering that mask column.
May 14, 2012 at 11:35 am
Chrissy321 (5/14/2012)
I am trying to walk some kind of line here between describing an issue in general and providing exact DDL. The vendor doesn't support direct database access, they allow it but won't offer any help on understanding it.The type of help I was hoping to receive was the possibility that someone had seen this type of design before and could provide some approach to help me understand it. Given the nature of the problem and the limited info I provided my hopes were not too high.
The more I think about this the more likely it seems that there is logic embedded in the application and I'll probably end up creating a second calendar.
Well - what I CAN tell you is that the binary representation of the number AFTER you added the 10/31 date has a 1 in the 31st position. When you remove it - there's a 0 in that spot. Coincidence? Perhaps, but it doesn't look like it.
IF you still want to pursue using their table, convert the test numbers you have to binary (1's and 0's), and match up the positions to days of the month. Pretty sure this will turn out a fairly consistent pattern (where 1's are non-working days).
declare @encodeddate bigint;
select @encodeddate=1885389191
;with bobCTE as (
select ROW_NUMBER() over (order by (select null)) RN from sys.columns)
select RN, case @encodeddate & (POWER(cast(2 as bigint),RN-1)) when 0 then 'working' else 'non-working' end
from bobCTE
where RN<32
order by RN
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2012 at 2:20 pm
Brilliant Mr. Miller you are exactly right.
May 14, 2012 at 3:16 pm
How can I incorporate this into a query?
CREATE TABLE #MyCalendar
(
YearMonth char(6),
BinaryCode int
)
INSERT INTO #MyCalendar VALUES ('201205',235673648)
CREATE TABLE #MyData
(
MyDate datetime,
MyData int
)
INSERT INTO #MyData VALUES ('5/1/2012',1)
INSERT INTO #MyData VALUES ('5/5/2012',5)
INSERT INTO #MyData VALUES ('5/28/2012',28)
INSERT INTO #MyData VALUES ('5/31/2012',31)
SELECT * FROM #MyCalendar
SELECT *FROM #MyData
SELECT
*
--,here is where I need to do the business day logic but I don't know how to incorporate the row number into here
FROM #MyData D
INNER JOIN #MyCalendar C
ON CONVERT(char(4),YEAR(MyDate)) + RIGHT('0' + convert(varchar(2), month(MyDate)), 2) = C.YearMonth
DROP TABLE #MyCalendar
DROP TABLE #MyData
May 14, 2012 at 5:57 pm
Try this (based on your test data):
SELECT
*, case BinaryCode & (POWER(cast(2 as bigint),MyDaTA-1)) when 0 then 'working' else 'non-working' end
--,here is where I need to do the business day logic but I don't know how to incorporate the row number into here
FROM #MyData D
INNER JOIN #MyCalendar C
ON CONVERT(char(4),YEAR(MyDate)) + RIGHT('0' + convert(varchar(2), month(MyDate)), 2) = C.YearMonth
Row_number was only there to show you the full calendar. The query above shows you whether the days you picked are business days or not.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2012 at 8:14 am
Thanks. This is resolved and the world has been spared a bit of duplicative data entry.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply