Help with a SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with a SQL Query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources