# how to get the data based on exact date i selected..... where i store the data week by week [Answered]RSS

## 4 replies

Last post Feb 10, 2015 09:00 AM by limno

• malina

Member

7 Points

46 Posts

### how to get the data based on exact date i selected..... where i store the data week by week

Feb 10, 2015 01:25 AM|malina|LINK

Hi

i have a table where i store data week by week

eg:

week                                  mon tues wed thur  fri  sat   sun  tot
2015-02-01 00:00:00.000    2      5      0      2     0    4     0    13
2015-02-08 00:00:00.000    3      4      0      1      0    4     0    12

so,now my requirement is if i select a date like
'2015-02-05' then i should get
tot as 2+5+0+2 = 9

how can write a sql for that, Can anyone help me....

Thank you

• prakher_prak...

Member

421 Points

123 Posts

### Re: how to get the data based on exact date i selected..... where i store the data week by week

Feb 10, 2015 03:35 AM|prakher_prakher|LINK

DECLARE @InputDate  VARCHAR(20)

SET @InputDate = '2015-02-05'

SELECT  Mon + Tues + wed + thur as [Tot]  FROM  TABLE WHERE CONVERT(Varchar(10), Week, 121)   = @InputDate

Prakher Srivastava
Microsoft Certified Technology Specialist

(Mark as Answer on the post that helps you)
• malina

Member

7 Points

46 Posts

### Re: how to get the data based on exact date i selected..... where i store the data week by week

Feb 10, 2015 03:54 AM|malina|LINK

Hi ,

but actually here my data is stored week by week meaning only weekendings.....

my input might be any day  in mid of week or start of the week ...

i should calculate based on exact date which i select....

thanks..

• gimimex

Contributor

5450 Points

1123 Posts

### Re: how to get the data based on exact date i selected..... where i store the data week by week

Feb 10, 2015 06:33 AM|gimimex|LINK

Hi,

Try something like this:

where [week] = DATEADD(WEEK, DATEDIFF(WEEK, 6, '2015-02-05'), 6)

Hope this helps.

• limno

All-Star

122238 Points

9703 Posts

Moderator

### Re: how to get the data based on exact date i selected..... where i store the data week by week

Feb 10, 2015 09:00 AM|limno|LINK

You table needs to be normalized if you are serious about your application. You can get what you need with TSQL but it becomes harder to work with.

Here is a sample for your:

create table test (weekCol datetime, mon int, tues int, wed int, thur int,  fri int, sat int, sun int)
insert into test values('2015-02-01 00:00:00.000',2,5,0,2,0,4,0) ,
('2015-02-08 00:00:00.000',3,4,0,1,0,4,0)

declare @dt datetime='2015-02-05'
;with mycte as (
Select weekCol,dateadd(day,n, weekCol) weekdaycol, d.val from test
cross apply (Values (1,mon),(2,tues),(3,wed),(4,thur),(5,fri),(6,sat),(0,sun))  d(n,val)
)

Select weekCol, sum(val) as tot
FROM mycte