| Author |
Topic |
|
th02b0
Starting Member
6 Posts |
 Posted - 2015-05-19 : 23:04:44
|
CREATE TABLE [dbo].[Shift](
[EmployeeID] [varchar](50) NULL,
[Shift] [varchar](50) NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[Day1] [bit] NULL,
[Day2] [bit] NULL,
[Day3] [bit] NULL
)
INSERT INTO Shift ([EmployeeID], [Shift], [Month], [Year], [Day1], [Day2], [Day3])
VALUES
('NV01', 'A1', 5, 2015, 1, 0, 1),
('NV01', 'A2', 5, 2015, 0, 1, 0),
('NV02', 'A1', 4, 2015, 1, 1, 1)
Shift Table:
EmployeeID-----Shift-----Month----Year---Day1----Day2----Day3
NV01-------------A1-----5--------2015----True----False---True
NV01-------------A2-----5--------2015----False---True----False
NV02-------------A1-----4--------2015----True----True----True
How to OUT PUT:
EmployeeID----Shift-------Date------------Value
NV01------------A1------2015-05-01--------True
NV01------------A1------2015-05-02--------False
NV01------------A1------2015-05-03--------True
NV02------------A1------2015-04-01--------True
NV02------------A1------2015-04-02--------True
NV02------------A1------2015-04-03--------True
NV01------------A2------2015-05-01--------False
NV01------------A2------2015-05-02--------True
NV01------------A2------2015-05-03--------False
Thanks
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-05-20 : 06:27:24
|
Try this
select [EmployeeID], [Shift], dateadd(month,[Month]-1,dateadd(year,[Year]-1900,0)), case when [Day1]=1 then 'True' else 'False' end as [Day1] from shift
union all
select [EmployeeID], [Shift], dateadd(month,[Month]-1,dateadd(year,[Year]-1900,0)), case when [Day2]=1 then 'True' else 'False' end as [Day2] from shift
union all
select [EmployeeID], [Shift], dateadd(month,[Month]-1,dateadd(year,[Year]-1900,0)), case when [Day3]=1 then 'True' else 'False' end as [Day3] from shift
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
|
|
|
Help with a SQL Query