O**K
发帖数: 11 | 1 select Name, Date
from (
select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff
from TableName as T1, TableName as T2
where T1.Name = T2.Name
and T1.Date < T2.Date
group by T1.Name, T1.Date
order by min(T2.Date - T1.Date) ) as A
union
select Name, Date + DateDiff as Date
from (
select top 1 T1.Name, T1.Date, min(T2.Date - T1.Date) as DateDiff
from TableName as T1, TableName as T2
where T1.Name = T2.Name
and T1.Date < T2.Date
group by T1.Name, T1.Date
order by min(T2.Date - T1.Date) ) |
|
e***t
发帖数: 1 | 2 Use DateDiff function for the first one:
datediff(hh, date1, date2)
use decimal or numeric for the second one, you can declare it as
decimal(10,2) |
|
m*********y
发帖数: 389 | 3 Here are some questions I copied from online.. Obviously LouZhu is a lazy
ass... these questions are everywhere... :-)
SQL Interview questions
Below is a list of questions in this blog post so you can test your
knowledge without saying answers. If you would like to see questions and
answers please scrool down.
Question: What type of joins have you used?
Question: How can you combine two tables/views together? For instance one
table contains 100 rows and the other one contains 200 rows, have exac... 阅读全帖 |
|
t****n
发帖数: 10724 | 4 借idtknow的思路，改进一下。比较不同行的日期，而不是同一行的日期。希望没有虫子
SELECT userID, StartDT, EndDT,
ROW_NUMBER() OVER (partition by userID order by StartDT) rk,
DATEDIFF(dd, StartDT, EndDT) diff
into #ttt
FROM #tbl
SELECT a.userID, a.rk, a.StartDT as A_Start, a.EndDT as A_End, b.StartDT as
B_Start, b.EndDT as B_End
into #shift
FROM #ttt a
LEFT JOIN #ttt b on a.userID = b.userID and a.rk + 1 = b.rk
order by a.userID, a.rk
select userID, min(A_Start)as StartDate, max(case when B_End IS null or
Cat= 'Y' then A_End else B_E... 阅读全帖 |
|
d******o
发帖数: 2489 | 5 很多方法吧，从registry、系统文件里边，不过都会被破解。
如果你用C#，试一下：
// adding trial period for the application
// set for 30 days on initial launch
Option Explicit
Private Const TRIAL_PERIOD_DAYS As Integer = 30
Private Function TrialPeriodDaysLeft(DaysTrial As Integer) As Integer
Dim DateStart As Date
DateStart = GetSetting(App.Title, "Trial Period", "Date Start", 0)
If DateStart = 0 Then
SaveSetting App.Title, "Trial Period", "Date Start", Date
Else
TrialPeriodDaysLeft = DateDiff("d", DateStart, Date) > DaysTri... 阅读全帖 |
|
c**g
发帖数: 274 | 6 I have no idea if this is the best. When I work on sql, I always have
a reference on my desk. This kind of interview quesion silly
(my personal view). A good memory of manual is not that useful in
real work.
in t-sql
update A
set Value = B.Value
from A, B
where datediff(mi, A.dateTime, B.dateTime) <= 1 |
|
|
C****n
发帖数: 2324 | 8 Yea, this is one time thing.
You mean to write a program t od this? It's really over kill. A CPA won't
understand what you are talk about, and she won't know what to do.
a SQL is the simplest solution cause she can just copy and paste and got wgat
she wants.
Here is the SQL I worked out, no joins. :-)
select ProductID,
Max(IIF(Date > "1999-09-30" and Date <="2000-09-30", DateDiff("D",Date,
"1999-09-30") * 100000 + price, 0)) Mod 100000 as LatestPriceOf2000,
Max(IIF(Date > "2000-09-30" and Date < |
|
z***y
发帖数: 7151 | 9 select t.[text] as query_last3hours from sys.dm_exec_query_stats s
cross apply sys.dm_exec_sql_text (s.sql_handle) t
where datediff(hh, last_execution_time , getdate())<1 |
|
g***l
发帖数: 18555 | 10 这个是最简单的
select phonenum,
DateDiff(dd, '12/31/2009',phonedate)
as DayNumber,
1 as PhoneCount
into PhoneCountTemp
from table
select phonenum,DayNumber, sum(phonecount) as PhoneCount
from PhoneCountTemp
group by phonenum, daynumber
order by phonenum, daynumber |
|
g***l
发帖数: 18555 | 11 我改了，如果把2/1/2010当第一天，就是DATEDIFF(DD,'1/31/2010',PHONEDATE) |
|
n********6
发帖数: 1511 | 12 LOGIC:
Today = DateDiff("d", "01/01/1900", Date())
CurrentWeekDay = (Today Mod 7) + 1
FirstWeekDay = DateAdd("d", Today-CurrentWeekDay, "01/01/1900")
LastWeekDay = DateAdd("d", Today+6-CurrentWeekDay, "01/01/1900")
SELECT * FROM TABLEA
WHERE TRANSACTIONDATE >= FIRSTWEEKDAY
AND TRANSACTIONDATE <= LASTWEEKDAY
/* works in asp */
Any simple method? |
|
|
k*****n
发帖数: 361 | 14 我知道datediff的，但是条件是除去date range在FR里的 |
|
c*********c
发帖数: 113 | 15 一张table里有【start time】和 【end time】，类型是date
需要计算时间间隔，以小时计，datediff貌似不行，纠结半天了。。。 |
|
s**********o
发帖数: 14359 | 16 SELECT DATEDIFF(hour,'2008-06-05','2008-08-05') |
|
i*****w
发帖数: 75 | 17 Try this:
DECLARE @tbl Table (userID int , StartDT datetime, EndDT datetime)
INSERT INTO @tbl (userID, StartDT, EndDT)
Select 1, '12/02/2011', '01/16/2012'
UNION ALL
Select 1, '03/04/2012', '03/24/2012'
UNION ALL
Select 1, '04/05/2012', '04/26/2012'
UNION ALL
Select 1, '05/14/2012', '06/07/2012'
UNION ALL
Select 2, '03/05/2012', '03/30/2012'
UNION ALL
Select 2, '08/04/2012', '09/15/2012'
UNION ALL
Select 2, '04/01/2012', '04/30/2012'
UNION ALL
Select 3, ... 阅读全帖 |
|
o***i
发帖数: 603 | 18 你开始的DATEDIFF(dd, startDT, EndDT) diff这里
感觉不用cte不成的 |
|
s**********o
发帖数: 14359 | 19 而且是ORDER好的吧，其实30天肯定要DATEDIFF了，否则没法控制2月的28，9天 |
|
|
w****w
发帖数: 521 | 21 把老祖宗解答放这里压阵，不精版理不容啊。
把同组的EndDate都设成同一个，再挑最小的StartDate。
--------------------------------------
select UserId,min(StartDate) as StartDate, EndDate
from
(select UserId,StartDate,
(select min(EndDate)
from work_time B
where A.UserId=B.UserId and A.StartDate<=B.StartDate
and not exists
(select *
from work_time C where B.UserId=C.UserId and B.StartDate
StartDate
and datediff(day,B.EndDate,C.StartDate)<30)
... 阅读全帖 |
|
s**********o
发帖数: 14359 | 22 其实是这样，如果EXCEL FILE的 LAST MODIFIED DATE和上一次FILE LOADDATE的时间不
一致，
就重新LOAD EXCEL FILE到SQL SERVER里，SSIS把EXCEL FILE的 LAST MODIFIED DATE读
到PACKAGE
VARIABLE里了，怎么跟TABLE里记录的LOADDATE比啊，好像是用DATEDIFF，要注意什么
啊 |
|
s**********o
发帖数: 14359 | 23 最后解决了，把VARIABLE传到SQL里去DATEDIFF
然后传出来到一个变量，然后用个CONDITION FX
就可以了，SSIS的DATETIME是个DATE，搞了半天 |
|
w****w
发帖数: 521 | 24 select CustomerID
from (
select a.CustomerID,a.OrderDate,a.SalesOrderID,COUNT(b.OrderDate) Cnt
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderHeader] b
on a.CustomerID=b.CustomerID
where DATEDIFF(day,a.OrderDate,b.OrderDate) between 0 and 10
group by a.CustomerID,a.OrderDate,a.SalesOrderID
) c
group by c.CustomerID
having MAX(Cnt)>=5;
The key is for each order, count the number of orders in next 10 days.
OrderID here is for order identification, otherwise o... 阅读全帖 |
|
w****w
发帖数: 521 | 25 select CustomerID
from (
select a.CustomerID,a.OrderDate,a.SalesOrderID,COUNT(b.OrderDate) Cnt
from [Sales].[SalesOrderHeader] a join [Sales].[SalesOrderHeader] b
on a.CustomerID=b.CustomerID
where DATEDIFF(day,a.OrderDate,b.OrderDate) between 0 and 10
group by a.CustomerID,a.OrderDate,a.SalesOrderID
) c
group by c.CustomerID
having MAX(Cnt)>=5;
The key is for each order, count the number of orders in next 10 days.
OrderID here is for order identification, otherwise o... 阅读全帖 |
|
v******n
发帖数: 421 | 26 估计得写宏了，查了下vba，有DATEDIFF这个 |
|
n*w
发帖数: 3393 | 27 比如
- case when b>d and c<1000 then 1 else 0 end
- datediff(m, x, y) < 30
这些都是有效的sql syntax，希望这些规则可以在database那边也可以直接用。 |
|
w****w
发帖数: 521 | 28 来自主题: Programming版 - 求最佳解法 不让贴sql code?
s elect * f rom
(
s elect a.ID,a.Date,
(
s elect sum(b.Amount)
f rom Transactions b
w here a.ID=b.ID and a.Date<=b.Date
and datediff(day,a.Date,b.Date)<365
) as OneYearTotal
f rom Transactions a
)
w here OneYearTotal>100 |
|
w****w
发帖数: 521 | 29 来自主题: Programming版 - 求最佳解法 下面程序产生22m transactions，8分钟。
import sys
from datetime import date,timedelta
from random import randint
ID_NUM=100000
DAY_NUM=3650
StartDate=date(2004,1,1)
fmt="%m/%d/%Y"
with open(sys.argv[1],"w") as fo:
fo.write("IDtDatetAmountn")
for i in xrange(22000000):
RandonId=randint(1,ID_NUM)
RandonDate=StartDate+ timedelta(days=randint(0,DAY_NUM))
RandonAmt=5*randint(1,20)
fo.write("%dt%st%dn" % (RandonId,RandonDate.strftime(fmt),RandonAmt))
下面程序产生id，80分钟
import s... 阅读全帖 |
|
n*********n
发帖数: 5605 | 30 分别把把x，y covert成POSIXct class后
有什么类似别的语言里的datediff()的fucntion可以
算y,x的interval的function吗？
谢谢 |
|
