Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 days into completed weeks only Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, September 10, 2012 5:43 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Sunday, March 02, 2014 9:08 AM Points: 143, Visits: 434
 Hi how do i easilty convert days into weeks (obviously Days/7 will give me weeks as decimal) Problem i have is I only want to count completed weeks:ie: 20 days is 2.86 weeks. need answer to be 2 and not 3 as i get in SQL.Thanks
Post #1356671
 Posted Monday, September 10, 2012 7:14 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 4:36 PM Points: 5,430, Visits: 23,053
 Is this what you need to do ?`SELECT 2.86 /1 - 2.86 % 1 SELECT 2.16 /1 - 2.16 % 1`Result for both selects is 2.000000Note that the "%" is the modulo operator - check it out in BOL it is a powerful tool to become familiar with. If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1356711
 Posted Monday, September 10, 2012 7:36 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Sunday, March 02, 2014 9:08 AM Points: 143, Visits: 434
 Thanks, sorted the issue now.
Post #1356729
 Posted Monday, September 10, 2012 7:38 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Friday, March 07, 2014 9:10 AM Points: 549, Visits: 3,113
 i do: `select FLOOR(2.86)`
Post #1356731
 Posted Monday, September 10, 2012 7:47 AM
 SSC Eights! Group: General Forum Members Last Login: Monday, March 03, 2014 11:24 AM Points: 945, Visits: 1,756
 LoosinMaMind (9/10/2012)Hi how do i easilty convert days into weeks (obviously Days/7 will give me weeks as decimal) Problem i have is I only want to count completed weeks:ie: 20 days is 2.86 weeks. need answer to be 2 and not 3 as i get in SQL.Thankswatch your data types. using integers 20/7 = 2 since SQL Server throws away the remainder when performing integer math. if you want to be absolutely certain / more precise / more explicit, i would use float or decimal with FLOOR(). For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail ShawNeed to Split some strings? Jeff Moden's DelimitedSplit8KJeff Moden's Cross tab and Pivots Part 1Jeff Moden's Cross tab and Pivots Part 2Jeremy Oursler
Post #1356739
 Posted Monday, September 10, 2012 8:04 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 4:19 PM Points: 22,243, Visits: 29,579
 LoosinMaMind (9/10/2012)Thanks, sorted the issue now. Proper forum etiquette would have you share your solution with others. It may help someone with a similar issue.
Post #1356755
 Posted Tuesday, September 11, 2012 7:22 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Since SQL is a database language, we prefer to do look ups and not calculations. I would build a calendar table with a ISO-8601 weekday-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.Put a computed column for the week: week_within_year COMPUTED AS SUBSTRING (weekday_within_year, 1, 7)Now the query is a simple group by: SELECT .. FROM Calendar AS C, Foobar AS FWHERE F.foo_date = C.cal_date GROUP BY C.week_within_year HAVING COUNT(*) = 7; There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1357388
 Posted Tuesday, September 11, 2012 7:54 AM
 Right there with Babe Group: General Forum Members Last Login: Today @ 5:51 AM Points: 733, Visits: 505
 Please check if this can help you!!!select CAST(20.0/7 AS INT) + CASE WHEN (20%7) > 3 THEN 1 ELSE 0 END Regards,Mitesh OSwal+918698619998
Post #1357426

 Permissions

 Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.