SQL queries this week's questions this month

SQL queries this week's questions this month

--- Calculate the number of days for difference
Select datediff (day, '2017-01-01 ', getdate ())

-- 1. The first day of a month
Select dateadd (mm, DATEDIFF (mm, 0, getdate (), 0)

-- 2. Monday of the week
Select dateadd (wk, DATEDIFF (wk, 0, getdate (), 0)

Select dateadd (wk, datediff (wk, 0, getdate (), 6)

-- 3. The first day of the year
Select dateadd (yy, DATEDIFF (yy, 0, getdate (), 0)

-- 4. The first day of the quarter
Select dateadd (qq, DATEDIFF (qq, 0, getdate (), 0)

-- 5. midnight of the day
Select dateadd (dd, DATEDIFF (dd, 0, getdate (), 0)

-- 6. Last day of last month
SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))

-- 7. Last day of last year
SELECT dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0 ))

-- 8. Last day of the month
SELECT dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 ))

-- 9. The last day of the year
SELECT dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate () + 1, 0 ))

-- 10. the first Monday of the month
Select DATEADD (wk,
DATEDIFF (wk, 0, dateadd (dd, 6-datepart (day, getdate (), getdate (), 0)

-- Query the number of registrants registered this week
Select count (*) from [user]
Where datediff (week, create_day-1, getdate () = 0

-- Number of registrants last week
Select count (*) from [user]
Where datediff (week, create_day-1, getdate () = 1


-- Number of registrants this month
Select count (*) from [user]
Where datediff (month, create_day, getdate () = 0

-- Number of registrants last month
Select count (*) from [user]
Where datediff (month, create_day, getdate () = 1

-- If efficiency is required, write the query in this way

-- Query the number of registrants registered this week
Select count (*) from [user]
Where create_day> = dateadd (day, 2-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))
And create_day <dateadd (day, 9-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))

-- Number of registrants last week
Select count (*) from [user]
Where create_day> = dateadd (day,-5-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))
And create_day <dateadd (day, 2-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))


-- Number of registrants this month
Select count (*) from [user]
Where create_day> = dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 ))
And create_day <dateadd (month, 1, dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 )))

-- Number of registrants last month
Select count (*) from [user]
Where create_day> = dateadd (month,-1, dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 )))
And create_day <dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 ))

-- This week
Select count (*) from User
Where datediff (dd, create_day, getdate () <= datepart (dw, getdate ())

-- Last week
Select count (*) from User
Where datediff (dd, create_day, (getdate ()-datepart (dw, getdate () <= 7

-- This month
Select count (*) from User
Where datepart (mm, create_day) = datepart (mm, getdate ())

-- Last month
Select count (*) from User
Where datepart (mm, create_day) = datepart (mm, getdate ()-1

-- This week
Select count (*) from [User]
Where datediff (dd, create_day, getdate () <= datepart (dw, getdate ())

-- Last week
Select count (*) from [User]
Where datediff (dd, create_day, (getdate ()-datepart (dw, getdate () <= 7

-- This month
Select count (*) from [User]
Where datepart (mm, create_day) = datepart (mm, getdate ())
-- Last month
Select count (*) from [User]
Where datepart (mm, create_day) = datepart (mm, getdate ()-1
Learning
Month (create_day) = month (getdate () this month
Month (create_day) = month (getdate ()-1 Previous month

Query all
SELECT * from feedback WHERE (DATEDIFF (d, fedtime, GETDATE () = 0) order by fedid DESC