SQL script to convert Calendar

Michael Katz
Michael Katz used Ask the Experts™
on
I guess its really hard to explain what I am looiking at...

CalCentury  CalYear  CalMonth  CalDay1  CalDay2  CalDay3   CalDay4......CalDay31
20                 11            1            0                0             1                    1
20                 11            2            1                1             1                    1

These represent Days of the Week per month, per year, Per Century..

The 0 in each CalDay Column represent NonWorking Days while the 1 is a WorkingDay

How can I get my data to  look like this   Where 2011= Year, 01 = month ,01 = Day
Date         WorkDay
20110101     1
20110102     0

Hopefully this makes sense
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Hi.

Firstly, you can use UNPIVOT (SQL 2005+) to get the days vertically.

Something like:
;with cal_upvt(CalCentury, CalYear, CalMonth, CalDay, WorkDay) AS (
   -- unpivot your original data
   SELECT CalCentury, CalYear, CalMonth
        , CONVERT(INT, REPLACE(CalDay, 'CalDay', '')), WorkDay
   FROM Your_Calendar
   UNPIVOT(WorkDay FOR CalDay IN (CalDay1, CalDay2, CalDay3, CalDay4, CalDay31)) upvt
)
-- put together final date and display
SELECT (CalCentury*1000000+CalYear*10000+CalMonth*100+CalDay) AS [Date], WorkDay
FROM cal_upvt
;

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
OH, and if your [Date] column is ultimately to represent a *true* datetime, then just ensure that your data doesn't have any invalid options like CalMonth = 2 and CalDay31 <> null.  You can check for this by using ISDATE(CONVERT(CHAR(8), CalCentury*1000000+CalYear*10000+CalMonth*100+CalDay)) = 1 as a filter in WHERE of final select.

Author

Commented:
Getting error..
 ;WITH cal_upvt(CalCentury, CalYear, CalMonth, CalDay, WorkDay) AS (
   -- unpivot your original data
   SELECT CalCentury, CalYear, CalMonth
        , CONVERT(INT, REPLACE(CalDay, 'CalDay', '')), WorkDay
   FROM dbo.AdmCalendar
   UNPIVOT(WorkDay FOR CalDay IN (CalDay1, CalDay2, CalDay3,
   CalDay4,
   CalDay5,
   CalDay6,
   CalDay7,
   CalDay8,
   CalDay9,
   CalDay10,
   CalDay11,
   CalDay12,
   CalDay13,
   CalDay14,
   CalDay15,
   CalDay16,
   CalDay17,
   CalDay18,
   CalDay19,
   CalDay20,
   CalDay21,
   CalDay22,
   CalDay23,
   CalDay24,
   CalDay25,
   CalDay26,
   CalDay27,
   CalDay28,
   CalDay29,
   CalDay30,
   CalDay31) )

Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ')'.


11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you are missing at least 1 ) at the end.
SharathData Engineer

Commented:
On a quick look, you did not run the whole query provided by kevin. run this.
;WITH cal_upvt(CalCentury, CalYear, CalMonth, CalDay, WorkDay) AS (
   -- unpivot your original data
   SELECT CalCentury, CalYear, CalMonth
        , CONVERT(INT, REPLACE(CalDay, 'CalDay', '')), WorkDay
   FROM dbo.AdmCalendar
   UNPIVOT(WorkDay FOR CalDay IN (CalDay1, CalDay2, CalDay3,
   CalDay4,
   CalDay5,
   CalDay6,
   CalDay7,
   CalDay8,
   CalDay9,
   CalDay10,
   CalDay11,
   CalDay12,
   CalDay13,
   CalDay14,
   CalDay15,
   CalDay16,
   CalDay17,
   CalDay18,
   CalDay19,
   CalDay20,
   CalDay21,
   CalDay22,
   CalDay23,
   CalDay24,
   CalDay25,
   CalDay26,
   CalDay27,
   CalDay28,
   CalDay29,
   CalDay30,
   CalDay31)) upvt
)
-- put together final date and display
SELECT (CalCentury*1000000+CalYear*10000+CalMonth*100+CalDay) AS [Date], WorkDay
FROM cal_upvt;

Open in new window

Chief Technology Officer
Most Valuable Expert 2011
Commented:
Thanks for the follow-up, a3 & Sharath. @Mikeyman_01: hopefully, you see the fix based on their comments. I had setup test data using the 5 calDay values you provided and so just showed with calDay1-4|31; however, the remainder of the query was as it needed to be -- all you had to sub in was your table name and remainder of calDayN names.  Good luck!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial