Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Columns with Day1, Day2, Day3... Day31 converted to Dates

Posted on 2011-03-23
8
Medium Priority
?
441 Views
Last Modified: 2012-05-11
My current table looks like this
WorkCentre  Century  Year   Month  Day1  Day2  Day3  Day4... Day31
A1000             20         11      3        25.1    25.2    30    15.8      14.9
A1000             20         11      4        30.1    30.2    30.2  30.1      15.8

I need to reorganize to look like this
WorkCentre Date         Available
A1000        03/01/2011   25.1
A1000        03/02/2011   25.2
A1000        03/03/2011   30

All the way to the last day of the month...

Really need help with this badly
0
Comment
Question by:Mikeyman_01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35201890
Create the new table first, with the second column being of DATETIME or SMALLDATETIME type.  Then do this in 31 separate queries:

insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-01', Day1 as Available
from myCurrentTable

insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-02', Day2 as Available
from myCurrentTable

insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-03', Day3 as Available
from myCurrentTable

-- ... etc


insert into myNewTable
select WorkCenter, convert(varchar,[Century]) + convert(varchar,[Year]) +'-'+ convert(varchar,[month]) +'-31', Day31 as Available
from myCurrentTable


0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35201967
If you want this in a single SQL statement, then try to use UNPIVOT:

select WorkCentre
     , [Date] = convert(date, convert(char(8), Century * 1000000 + [Year] * 10000 + Month * 100 + convert(int, replace([Day], 'Day', ''))), 112)
	 , Available
from your_table
unpivot (
   Available for [Day] in (Day1, Day2, Day3, Day4, Day31)
) upvt
;

Open in new window


One caveat to note is that your data needs to be clean to start with.  In other words, for April you should have NULL value for Day31 as there is not a 31st day of April.  UNPIVOT will handle the nulls correctly since you won't get a row for that in the result.

Note also, my solution is converting the date parts into a true date.  I tested on a SQL 2008 system, so went with DATE data type.  If you want the results displayed as MM/DD/YYYY, you can use CONVERT or instead of casting to a date, simply build each part of the string as above Expert showed.
0
 

Author Comment

by:Mikeyman_01
ID: 35202580
Here is my query...
 select WorkCentre
     , [Date] = convert(date, convert(char(8), CapacityCentury * 1000000 + [CapacityYear] * 10000 + CapacityMonth * 100 + convert(int, replace([Day], 'Day', ''))), 112)
       , Available
from dbo.BomCapacity
unpivot (
    Available for [Day] in (CapacityAvail1,
CapacityAvail2,
CapacityAvail3,
CapacityAvail4,
CapacityAvail5,
CapacityAvail6,
CapacityAvail7,
CapacityAvail8,
CapacityAvail9,
CapacityAvail10,
CapacityAvail11,
CapacityAvail12,
CapacityAvail13,
CapacityAvail14,
CapacityAvail15,
CapacityAvail16,
CapacityAvail17,
CapacityAvail18,
CapacityAvail19,
CapacityAvail20,
CapacityAvail21,
CapacityAvail22,
CapacityAvail23,
CapacityAvail24,
CapacityAvail25,
CapacityAvail26,
CapacityAvail27,
CapacityAvail28,
CapacityAvail29,
CapacityAvail30,
CapacityAvail31)
) upvt
;


This is the message i get

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'CapacityAvail1' to data type int.
0
Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35202692
That is because your values aren't really Day1, Day2, ... Day31.  Therefore, replacing 'Day' doesn't work.  You will need to replace 'CapacityAvail'.  Remember the point there is to get the number isolated so you can convert to an integer.
select WorkCentre
     , [Date] = convert(date, convert(char(8), CapacityCentury * 1000000 + [CapacityYear] * 10000 + CapacityMonth * 100 + convert(int, replace([Day], 'CapacityAvail', ''))), 112)
       , Available
from dbo.BomCapacity
unpivot (
    Available for [Day] in (CapacityAvail1, 
    CapacityAvail2,
    CapacityAvail3,
    CapacityAvail4,
    CapacityAvail5,
    CapacityAvail6,
    CapacityAvail7,
    CapacityAvail8,
    CapacityAvail9,
    CapacityAvail10,
    CapacityAvail11,
    CapacityAvail12,
    CapacityAvail13,
    CapacityAvail14,
    CapacityAvail15,
    CapacityAvail16,
    CapacityAvail17,
    CapacityAvail18,
    CapacityAvail19,
    CapacityAvail20,
    CapacityAvail21,
    CapacityAvail22,
    CapacityAvail23,
    CapacityAvail24,
    CapacityAvail25,
    CapacityAvail26,
    CapacityAvail27,
    CapacityAvail28,
    CapacityAvail29,
    CapacityAvail30,
    CapacityAvail31)
) upvt
;

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 35231890
mwvisa1,

I am still getting the error.. What I am I not doing correctly??..

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
0
 

Author Comment

by:Mikeyman_01
ID: 35232388
knightEknight,

This Table you showed me doesnt appear to be run everyday...Did I miss something?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 35234473
@Mikeyman_01:

That would suggest you have values that don't equate to a proper date.  It is probably what I was talking about with dates like 4/31 that don't really exist.  Let's try like this with a test first.

+Get all the data without converting to actual DATE
+Using the below as a view, derived table or common table expression, select only those rows where ISDATE([DATE]) = 1.

select WorkCentre
     , [Date] = convert(char(8), CapacityCentury * 1000000 + [CapacityYear] * 10000 + CapacityMonth * 100 + convert(int, replace([Day], 'CapacityAvail', '')))
     , Available
from dbo.BomCapacity
unpivot (
    Available for [Day] in (CapacityAvail1, 
    CapacityAvail2,
    CapacityAvail3,
    CapacityAvail4,
    CapacityAvail5,
    CapacityAvail6,
    CapacityAvail7,
    CapacityAvail8,
    CapacityAvail9,
    CapacityAvail10,
    CapacityAvail11,
    CapacityAvail12,
    CapacityAvail13,
    CapacityAvail14,
    CapacityAvail15,
    CapacityAvail16,
    CapacityAvail17,
    CapacityAvail18,
    CapacityAvail19,
    CapacityAvail20,
    CapacityAvail21,
    CapacityAvail22,
    CapacityAvail23,
    CapacityAvail24,
    CapacityAvail25,
    CapacityAvail26,
    CapacityAvail27,
    CapacityAvail28,
    CapacityAvail29,
    CapacityAvail30,
    CapacityAvail31)
) upvt
;

Open in new window

0
 

Author Comment

by:Mikeyman_01
ID: 35237298
mwvisa1,

Excellent Job!! Thank you very much...

You helped me tremendously..

Michael
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question