These two tips, do not know, write is scared! It is a seemingly simple, actually doing it, you have to think about it, you can write it! Any procedure with date operation must be careful!
Let's talk about the second:
2. According to the date of birth! The so-called calculation accurate age is: the birthday is not one year old! Everyone is common, the number of intervals is counted as age! If the demand is accurate, such as: Insurance, it is rough! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course! Of course It can also be deducted to calculate the precise, the needs of the inquiry date, calculate the needs of the salary increase! I originally thought it was very simple. I also wrote a few times in the year! The masters were also shackled several times several times. Pour! Do not believe in 2002-11-27 16:16:26 Post:
"I have a field in my table. I have to calculate the age of the person according to the current date, and fill it into the corresponding field of the table?" Http://search.9cbs.net/expert/topicview1.asp?id = 1210302
Book is positive:
Declare @BIRTHDAY SmallDatetimeSet @BIRTHDAY = '1949-10-01'
- Use a date variable, in order to expand: calculate the function of the precise age at the time of the future or in the past! Declare @ SmallDateTimeSet @ = getdate ()
Select Datediff (Year, @ Birthday, getdate ()) - Case When Dated (Year, Datediff (Year, Datediff (Year, @ birthday, getdate ()), @ birthday), getdate ()) ()> = 0 Then 0 Else 1 end
T-SQL UDF: create function GetAge (@BeginDate datetime, @ EndDate datetime) returns integerbeginreturn (select datediff (year, @ BeginDate, @ EndDate) - case when datediff (day, dateadd (year, datediff (year, @ BeginDate, @ Enddate), @ begindate, @ Enddate> = 0 THEN 0 else 1 End) end
Besides the first: 1. Calculate each of the two days of Monday to Sunday, there are several! The cause is from this post: "SQL statement seeks the number of days of the day" http://community.9cbs.net/expert/ TopicView1.asp? Id = 3291510
Originally wanted to lazy google, there is no satisfaction, it seems that he will not have bottom! Thinking is different! "Ask a bit of algorithm" (a bit of a feeling, simply, and doing it) http: // www. ITPub.Net/252645.html
By the way, I have written the program to always want to write "more universal", I may answer the question!
Since @@ DateFirst can make DatePart (WeekDay, [DATE]) to make DatePart (WeekDay, [DATE])! So use DateName! Datename! In addition to the Chinese habits: Sunday counts last day last week! If it is other language version SQL Server pays the form of the language version!
Declare @B DateTimedeclare @e datetime
Set @B = '2004-07-29'set @e =' 2004-08-05 '
Select @B AS start date, @ E AS End Date, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (WeekDay, @ e) = 'Sunday' Ten @E - 1 Else @e End 1 AS Cross Week, Datediff (Week, Case When Datename (WeekDay, @ b) = 'Sunday' Then @B - 1 else @B End, Case When Datename (Weekday, @ e) = 'Sunday' Then @E - 1 else @e End) 1
- Case When Datename (Weekday, @ b) in ('Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') THEN 1 ELSE 0 Endas Monday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Wednesday', '' '' '' '' '' '' '', ',', ',' Monday ') THEN 1 ELSE 0 Endas Tuesday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Thursday', 'Friday ",' Saturday ',' Sunday ') THEN 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday", 'Tuesday') THEN 1 ELSE 0 Endas Wednesday, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (WeekDay, @ e) = 'Sunday' Then @E - 1 Else @E End 1
- Case When Datename (Weekday, @ b) in ('Friday', 'Saturday ",' Sunday ') THEN 1 ELSE 0 End- Case When Datename (WeekDay, @ e) in (' Monday ',' Tuesday", 'Wednesday') THEN 1 ELSE 0 Endas Thursday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Saturday ",' Sunday ') Then 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday ',' Tuesday ',' Wednesday", 'Thursday') THEN 1 ELSE 0 Endas Friday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Sunday') THEN 1 Else 0 end- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday ",' Thursday ', 'Friday') THEN 1 ELSE 0 Endas Six numbers, Datediff (Week, Case When Datename (weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @e - 1 else @e end) 1
- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') THEN 1 ELSE 0 END
AS Sunday
- Wow finally got it, and did not expect it: "I actually took a brain!
The rest is that people can turn him into UDF SP (View) for what they want! If you really understand my ideas, you can use any language!
Finally, make an advertisement: http://www.microshaoft.comHttp: //www.9cbs.net/develop/list_article.asp? Author = Playyuer
Send you a few giant SQL, maybe it is useful, click, help me add some popularity:
T-SQL generates two new real gift years http://www.9cbs.net/develop/read_article.asp?id=26447
T-SQL generates a simple Queen annual calendar T-SQL with the date of the date and years http://www.9cbs.net/develop/read_article.asp?id=26083
http://www.microshaoft.com ccttp://www.9cbs.net/develop/list_article.asp? Author = Playyuer
As of 2004-08-21:
A total of three axes: 1, 2 is Zou Jian
Zou Jian's idea is that the end of the end because Datediff is not affected by Set DateFirst, fixed a week from Saturday, Saturday, I think is the value of @@ DateFirst and DatePart (WeekDay, @ DT), converted into fixed fixed The number of every week from 0 (Sunday) to 6 (Saturday), according to the following rules
The result of Datediff (Week, @ dt_begin, @ dt_end) If it is 0, indicating that there is no cross-week statistics, there is no number from @dt_begin to @DT_END, there are no numbers.
If it is 1, it means a cross week, but when two weeks are not completely statistically, the number of weeks x-Saturday's week-on-week-on-week-week to @dt_end is based on the number of days X.
If it is> 1, it means a cross week, and when there is a complete week, when statistics are statistically @dt_begin to Saturday, the number of weeks of Week Sunday the number of days to @DT_END complete week complete week number
DECLARE @B DateTime, @ e DateTimeSelect @ b = '2002-8-5', @ E = '2008-7-15' - Query mode 1 (vertical) SELECT item = 'cross Week', value = Case When @B <@E THEN (Datediff (Day, @ B, @ E) 7) / 7 else (datediff (day, @ e, @ b) 7) / 7 Endunion AllSelect Aa, Case Bahen -1 Ten Case WHEN ab Between BB and BC THEN 1 Else 0 end when 0 Then Case WHEN BB <= Abten 1 Else 0 end case when bc> = Abten 1 else 0 Else B.A Case WHEN BB <= Abby 1 Else 0 END CASE WHEN BC> = Abten 1 Else 0 end endfrom (SELECT A = 'Monday', B = 1 Union All Select 'Tuesday', 2 Union All Select 'Wednesday', 3 Union All Select 'Thursday' , 4 Union All Select 'Friday', 5 Union All Select 'Saturday', 6 Union All Select 'Sunday', 0) A, (Select a = Case When @ @ e Datediff (Week, @ b, @ e ) -1 Else Datediff (Week, @ e, @ b) -1 end, b = case when @B <@E1 (@@ DateFirst Datepart (Weekday, @ b) -1)% 7 else (@@ DateFirst DATEPART (Weekday, @ e) -1)% 7 end, c = case when @B <@E1 (@@ DateFirst DatePart (WeekDay, @ E) -1)% 7 else (@@ DateFirst Datepart Weekday, @ b) -1)% 7 end) B
- Query 2 (lateral direction) Select @B AS start date, @ E AS end date, cross week number, Monday = case ahen -1 kil = 1 Then 1 else 0 End when 0 Then Case WHEN B < = 1 THEN 1 ELSE 0 END CASE WHEN C> = 1 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 1 THEN 1 ELSE 0 END CASE WHEN C> = 1 THEN 1 ELSE 0 End End, Tuesday = CASE A when -1 kil = 2 kilse 0 end when b = 2 kilse 0 end case when c> = 2 THEN 1 ELSE 0 ELSE A CASE WHEN B <= 2 THEN 1 ELSE 0 END CASE WHEN C> = 2 Then 1 Else 0 end end, Wednesday = Case a when 1 else 0 End when 0 Then Case WHEN B <= 3 THEN 1 ELSE 0 END CASE WHEN C> = 3 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 3 THEN 1 ELSE 0 END CASE WHEN C> = 3 THEN 1 ELSE 0 end end, Thursday = Case a when -1 Then Case when b = 4 THEN 1 Else 0 end when 0 thrse 0 end case when c> = 4 THEN 1 ELSE 0 ELSE A CASE WHEN B <= 4 THEN 1 ELSE 0 END Case WHEN C> = 4 THEN 1 ELSE 0 end end, Friday = Case Ahen -1 Then Case WHEN B = 5 THEN 1 ELSE 0 EN D when 0 dam case 0 end case when c> = 5 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 5 THEN 1 ELSE 0 End Case WHEN C> = 5 THEN 1 Else 0 end end, Saturday = case a when --1 kil = 6 kil = 6 Case WHEN B <= 6 THEN 1 ELSE 0 END CASE WHEN C> = 6 THEN 1 ELSE 0 END Else A Case WHEN B <= 6 THEN 1 ELSE 0 End Case WHEN C> = 6 THEN 1 ELSE 0 End End, Sunday = Case Ahen -1 Ten Case WHEN B =
0 THEN 1 ELSE 0 end when 0 Then Case WHEN B <= 0 THEN 1 ELSE 0 end case when c> = 0 THEN 1 ELSE 0 ELSE A CASE WHEN B <= 0 THEN 1 ELSE 0 End Case WHEN C > = 0 THEN 1 ELSE 0 end endfrom (SELECT cross week = case when @ @E THEN (datediff (day, @ b, @ e) 7) / 7 else (datediff (day, @ e, @ B ) 7) / 7 end, a = case when @ @ datediff (week, @ b, @ e) -1 else datediff (week, @ e, @ b) -1 end, b = case when @ B <@ e @@ datefirst datepart (weekday, @ b) -1)% 7 ELSE (@@ DateFirst DatePart (WeekDay, @ E) -1)% 7 end, c = case when @ @ @ e THEN (@@ DateFirst DatePart (WeekDay, @ e) -1)% 7 else (@@ DateFirst Datepart (WeekDay, @ b) -1)% 7 end) a - 3 is Playyuer
Select @B AS start date, @ E AS End Date, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (WeekDay, @ e) = 'Sunday' Ten @E - 1 Else @e End 1 AS cross Week
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') THEN 1 ELSE 0 Endas Monday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end ) 1- Case When Datename (Weekday, @ b) in ('Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') Then 1 Else 0 End- Case When Datename (Weekday, @ e) In ('Monday') THEN 1 ELSE 0 Endas Tuesday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Thursday', 'Friday ",' Saturday ',' Sunday ') THEN 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday", 'Tuesday') THEN 1 ELSE 0 Endas Wednesday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Friday', 'Saturday ",' Sunday ') THEN 1 ELSE 0 End- Case When Datename (WeekDay, @ e) in (' Monday ',' Tuesday", 'Wednesday') THEN 1 ELSE 0 Endas Thursday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end ) 1- Case When Datename (Weekday, @ b) in ('Saturday ",' Sunday ') Then 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday ',' Tuesday", ' Wednesday ',' Thursday ') THEN 1 ELSE 0 Endas Friday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Sunday') THEN 1 Else 0 end- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday ",' Thursday ', 'Friday') THEN 1 ELSE 0 Endas Six numbers
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') THEN 1 ELSE 0 END
AS Sunday
- =================================================================================================================================================================================================================================
Declare @B DateTime, @ e DateTimeSet @B = '2004-9-14'set @e =' 2004-9-15 '
SELECT @B AS start date, @ e as end date, datediff (week, @ b, @ e) 1 case when (@@ DateFirst Datepart (weekday, @ b))% 7 = 1 THEN 1 ELSE 0 END - Case WHEN (@@ Datefirst DatePart (Weekday, @ E))% 7 = 1 THEN 1 ELSE 0 END AS Cross Week, Datediff (Week, @ b, @ E) Case When (@@ DateFirst DatePart Weekday, @ b))% 7 case when (@@ DateFirst Datepart (weekday, @ b))% 7 = 0 THEN 7 else 0 end> 2 Then 0 else 1 end - case when (@@ DateFirst DatePart) Weekday, @ e))% 7 case when (@@ DateFirst Datepart (weekday, @ e))% 7 = 0 THEN 7 else 0 end> = 2 Then 0 else 1 End AS Mix, Datediff (Week, @ B, @ E) Case When (@@ DateFirst Datepart (Weekday, @ b))% 7 Case When (@@ DateFirst Datepart (WeekDay, @ b))% 7 = 0 THEN 7 ELSE 0 End> 3 THEN 0 Else 1 End - Case When (@@ DateFirst Datepart (WeekDay, @ E))% 7 Case When (@@ DateFirst DatePart (WeekDay, @ E))% 7 = 0 THEN 7 ELSE 0 end> = 3 Ten 0 Else 1 End AS Tuesday, Datediff (Week, @ B, @ E) Case When (@@ DateFirst Datepart (Weekday, @ b))% 7 Case When (@@ DateFirst Datepart (Weekday, @B))% 7 = 0 t Hen 7 Else 0 End> 4 THEN 0 Else 1 End - Case When (@@ Datefirst DatePart (Weekday, @ E))% 7 Case When (@@ DateFirst DatePart (WeekDay, @ E))% 7 = 0 THEN 7 else 0 end> = 4 death ELSE 1 END AS Wednesday, Datediff (Week, @ b, @ E) Case When (@@ DateFirst DatePart (Weekday, @ b))% 7 Case When (@ @datefirst DatePart (weekday, @ b))% 7 =
0 THEN 7 Else 0 End> 5 Then 0 Else 1 End - Case When (@@ DateFirst DatePart (WeekDay, @ E))% 7 Case When (@@ DateFirst DatePart (WeekDay, @ E))% 7 = 0 THEN 7 Else 0 end> = 5 THEN 0 ELSE 1 End AS Thursday, Datediff (Week, @ b, @ E) Case When (@@ DateFirst Datepart (Weekday, @ b))% 7 Case When (@@ datefirst datepart (weekday, @ b))% 7 = 0 THEN 7 else 0 end> 6 Then 0 else 1 end - case when (@@ DateFirst Datepart (WeekDay, @ e))% 7 Case When (@@ DateFirst DatePart (WeekDay, @ e))% 7 = 0 THEN 7 else 0 end> = 6 Then 0 else 1 End AS Friday, Datediff (Week, @ B, @ E) Case When (@ @datefirst datepart (weekday, @ b))% 7 case when (@@ Datefirst DatePart (weekday, @ b))% 7 = 0 THEN 7 else 0 End> 7 Then 0 else 1 end - case when (@ @Datefirst DatePart (Weekday, @ e))% 7 Case When (@@ DateFirst Datepart (WeekDay, @ e))% 7 = 0 THEN 7 ELSE 0 end> = 7 Then 0 else 1 End AS Six numbers , Datediff (Week, @ b, @ E) Case When (@@ DateFirst Datepart (Weekday, @ b))% 7 Case When (@@ DateFirst Datepart (WeekDay, @ b))% 7 = 0 THEN 7 else 0 End> 1 Then 0 Else 1 End - Case When (@@ DateFirst Datepart (Weekday, @ E))% 7 Case When (@@ DateFirst Datepart (WeekDay, @ E))% 7 = 0 THEN 7 ELSE 0 END> = 1 Then 0 Else 1 End AS Weekday
Select @B AS start date, @ E AS End Date, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (WeekDay, @ e) = 'Sunday' Ten @E - 1 Else @e End 1 AS Cross Week, Datediff (Week, Case When Datename (WeekDay, @ b) = 'Sunday' Then @B - 1 else @B End, Case When Datename (Weekday, @ e) = 'Sunday' Then @E - 1 else @e End) 1
- Case When Datename (Weekday, @ b) in ('Tuesday', 'Wednesday ",' Thursday, 'Friday', 'Saturday', 'Sunday') THEN 1 ELSE 0 Endas Monday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Wednesday', '' '' '' '' '' '' '', ',', ',' Monday ') THEN 1 ELSE 0 Endas Tuesday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Thursday', 'Friday ",' Saturday ',' Sunday ') THEN 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday", 'Tuesday') THEN 1 ELSE 0 Endas Weie Wednesday, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (WeekDay, @ e) = ' Sunday 'Ten @E - 1 Else @e End 1
- Case When Datename (Weekday, @ b) in ('Friday', 'Saturday ",' Sunday ') THEN 1 ELSE 0 End- Case When Datename (WeekDay, @ e) in (' Monday ',' Tuesday", 'Wednesday') THEN 1 ELSE 0 Endas Thursday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Saturday ",' Sunday ') Then 1 Else 0 End- Case When Datename (Weekday, @ e) in (' Monday ',' Tuesday ',' Wednesday", 'Thursday') THEN 1 ELSE 0 Endas Friday
, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (Weekday, @ e) = 'Sunday' Ten @E - 1 else @E end 1
- Case When Datename (Weekday, @ b) in ('Sunday') THEN 1 Else 0 end- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday ",' Thursday ', 'Friday') THEN 1 ELSE 0 Endas Sixth, Datediff (Week, Case When Datename (Weekday, @ b) = 'Sunday' Then @B - 1 else @B end, Case When Datename (WeekDay, @ e) = 'Sunday' Then @E - 1 Else @E End 1
- Case When Datename (Weekday, @ e) in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday') THEN 1 ELSE 0 END
AS Sunday
- Query mode 2 (lateral) SELECT @B AS start date, @ E AS end date, cross week number, Monday number = case a when -1 Then Case When 1 betWeen B and c the 1 else 0 end when 0 Then Case WHEN B <= 1 THEN 1 ELSE 0 END CASE WHEN C> = 1 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 1 THEN 1 ELSE 0 END CASE WHEN C> = 1 = 1 else 0 End End, Tuesday = Case Ahen -1 Ten Case When 2 Between B and C THEN 1 ELSE 0 End When 0 Then Case WHEN B <= 2 THEN 1 ELSE 0 End Case WHEN C> = 2 THEN 1 ELSE 0 ELSE A Case WHEN B <= 2 THEN 1 ELSE 0 END CASE WHEN C> = 2 THEN 1 Else 0 end end, Wednesday number = case a when -1 Then Case WHEN 3 Between B and C THEN 1 ELSE 0 End When 0 Then Case WHEN B <= 3 THEN 1 ELSE 0 END CASE WHEN C> = 3 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 3 TEN 1 ELSE 0 END CASE WHEN C> = 3 THEN 1 ELSE 0 End End, Thursday = Case a when -1 dam 1 else 0 end when 0 thrse winter b <= 4 THEN 1 ELSE 0 END CASE WHEN C> = 4 THEN 1 ELSE 0 ELSE A Case WHEN B <= 4 THEN 1 ELSE 0 End Case WHEN C> = 4 THEN 1 ELSE 0 END End, Friday = Case a when -1 Ten Case WHEN 5 Between B and C THEN 1 ELSE 0 end when 0 Then Case WHEN B <= 5 THEN 1 ELSE 0 End Case WHEN C> = 5 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 5 THEN 1 ELSE 0 End Case WHEN C> = 5 THEN 1 Else 0 End End, Saturday = Case A When -1 Then Case WHEN 6 Between B And C THEN 1 ELSE 0 END When 0 dam
6 THEN 1 ELSE 0 End, Sunday number = Case a when -1 Ten Case When 0 Between B and C THEN 1 ELSE 0 end when 0 Then Case WHEN B <= 0 THEN 1 ELSE 0 End Case WHEN C> = 0 THEN 1 ELSE 0 END ELSE A CASE WHEN B <= 0 THEN 1 ELSE 0 End Case WHEN C> = 0 THEN 1 ELSE 0 EndFrom (SELECT Cross Week = Case When @ @E THEN (Datediff Day, @ b, @ e) 7) / 7 else (datediff (day, @ e, @ b) 7) / 7 end, a = case when @ @e the datediff (week, @ b, @ e) -1 else datediff (Week, @ E, @ b) -1 end, b = case when @b <@E11 (@@ DateFirst DatePart (Weekday, @ b) -1)% 7 else (@@ DateFirst DatePart (Weekday, @ E) -1)% 7 end, c = case when @B <@E1 (@@ DateFirst DatePart (WeekDay, @ E) -1)% 7 else (@@ DateFirst DatePart (Weekday, @ b) -1)% 7 end) a
SELECT project = 'cross Week', value = case when @ @ @ (datediff (day, @ b, @ e) 7) / 7 else (datediff (day, @ e, @ b) 7) / 7 Endunion Allselect aa, casion bb and bc kilns 1 else 0 end when 0 thrse 0 END CASE WHEN BC> = Abten 1 else 0 end Else B.A Case WHEN BB <= Abten 1 Else 0 end case when bc> = Abten 1 else 0 end endfrom (SELECT A = 'Monday', b = 1 Union All Select 'Tuesday', 2 Union All Select 'Wednesday', 3 Union All Select 'Thursday', 4 Union All Select 'Friday', 5 Union All Select 'Saturday', 6 Union All Select 'Sunday', 0) A, (Select a = Case When @B <@ @ datediff (week, @ b, @ e) -1 else datediff (week, @ e, @ b) -1 end, b = case when @b <@ DateFirst DatePart (Weekday, @B) -1)% 7 else (@@ DateFirst DatePart (WeekDay, @ e) -1)% 7 end, c = case when @B <@E THEN (@@ DateFirst DatePart (Weekday, @ e) -1)% 7 else (@@ datefirst datepart (weekday, @ b) -1)% 7 End) B
T-SQL UDF: create function WeekdayDiff (@Weekday integer, @ BeginDate datetime, @ EndDate datetime) - @Weekday: 1 = Monday, ..., 7 = Sundayreturns integerbeginreturn (select datediff (week, @ BeginDate, @ EndDate) Case When (@@ DateFirst Datepart (Weekday, @ Begindate))% 7 Case When (@@ DateFirst DatePart (Weekday, @ Begindate))% 7 = 0 THEN 7 ELSE 0 End> @WeekDay% 7 1 THEN 0 Else 1 End - Case When (@@ DateFirst Datepart (WeekDay, @ Enddate))% 7 Case When (@@ Datefirst DatePart (WeekDay, @ Enddate))% 7 = 0 THEN 7 ELSE 0 end> = @WeekDay% 7 1 Then 0 else 1 End) endcreate function weekday (@date datetime) Returns integerbegin - Return: 1 = Monday, ..., 7 = sundayreturn (SELECT (@@ DateFirst DatePart (weekday, @ Date ))% 7 Case When (@@ DateFirst DatePart (Weekday, @ Date))% 7 <2 THEN 6 ELSE -1 END) end
