Last post Feb 12, 2019 11:03 PM by robby32
Feb 12, 2019 01:23 AM|robby32|LINK
I had this question in a previous post, there is one other thing I would like to ask .. not sure if a new post is required but I made one just in case.
The extra query was that I need the column value of the week day.
Date Mon Tues Wed Thur Fri Sat Sun FirstName LastName
28.01.2019 1 1 0.75 0.2 1 Bob Brown
I have the above row in a sql table
Now start date is 28.01.2019 so that's on Monday. Tuesday is 29.01.2019 wed 30.01.2019 thur 31.01.2019.. but fri is 01.02.2019.
How can o read those and insert into a table the firstname lastname and a column thst says what month..if no number in the day then just ignore.. so i would have
Name month day Value
Bob brown jan tue 1
Bob brown jan wed 1
Bob brown jan thur 0.75
Bob brown feb fri 0.2
Bob brown feb Sun 1
The table has 100 rows i need to do this so If i need to traverse all rows how can this be done ?
Feb 12, 2019 12:36 PM|limno|LINK
Why do you ask the same question again?
Am I missing something here?
Feb 12, 2019 12:59 PM|limno|LINK
Please post your question in DDL and insert script in the future. You can see code sample from my reply. Thanks.
CREATE TABLE mytable(id int,
Date DATE NOT NULL
INSERT INTO mytable(id,Date,Mon,Tues,Wed,Thur,Fri,Sat,Sun,FirstName,LastName)
VALUES (1,'2019-01-28',NULL,1,1,1,1,NULL,NULL,'Bob','Brown' ),
select id, FirstName,LastName
, Datename(month,(dateadd(day,n,Date)) ) [month]
, Datename(WEEKDAY,(dateadd(day,n,Date)) ) [day]
cross apply (values (0,Mon),(1,Tues),(2,Wed),(3,Thur),(4,Fri),(5,Sat),(6,Sun)) d (n,val)
Where val is not null
drop table mytable
Feb 12, 2019 11:03 PM|robby32|LINK
Thanks , yes I realised.