query with datediff() function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query with datediff() function

Author

Topic

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 12:05:30

I have the following data

declare @sample table
(InvoiceID int, Duedate datetime, paymentdate datetime)

insert @sample
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005')union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULLunion all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 1, '2/5/2005', NULL

Now I want to see if the payment date is null (payment has not been made) i want the following 3 columns

InvoiceID count(Pending <=30 days) count(Pending >30 days)

I will have 3 columns and will see how many are late by less then 30 days and how many are late by >30 days

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-18 : 12:40:07

Like this?


set nocount on
declare @sample table
(InvoiceID int, Duedate datetime, paymentdate datetime)


insert @sample
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 1, '2/5/2005', NULL

--Now I want to see if the payment date is null (payment has not been made) i want the following 3 columns
--InvoiceID count(Pending <=30 days) count(Pending >30 days)

--I will have 3 columns and will see how many are late by less then 30 days and how many are late by >30 days

select	invoiceid
	,sum(over30)  [count(Pending >30 days)]
	,sum(under30) [count(Pending <=30 days)]
from	(
	select	invoiceid
		,over30 = case when datediff(day, duedate, getdate()) > 30 then 1 else 0 end
		,under30 = case when datediff(day, duedate, getdate()) between 0 and 30  then 1 else 0 end
	from	@sample
	where paymentdate is null
	) a
group by invoiceid


output:
invoiceid   count(Pending >30 days) count(Pending <=30 days) 
----------- ----------------------- ------------------------ 
1           4                       2

Be One with the Optimizer
TG

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-18 : 12:46:49

Hmm... I read it like this, but same general idea.

Select
InvoiceID
, Sum(Over30) As Over30
, Sum(Under30) As Under30
From
(Select
InvoiceID
, Case When PaymentDate Is Null And DateDiff(dd, DueDate, GetDate()) > 30 Then 1 Else 0 End As Over30
, Case When PaymentDate Is Null And DateDiff(dd, Duedate, GetDate()) <= 30 Then 1 Else 0 End As Under30
From @sample) A
Group By
InvoiceID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-18 : 12:48:34

Yeah, I realized that after I posted and edited my response to be more like yours, Joe (a few seconds before your response

)

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-18 : 13:00:02

[code]
insert @sample
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005' union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 1, '2/5/2005', NULL

select
invoiceid, sum( case
when isnull(datediff(d,duedate,getdate()),-1) < 30
then 1 else 0 end) as [Less than 30]
,sum( case when isnull(datediff(d,duedate,getdate()),-1) > 29
then 1 else 0 end) as [30 or More]
from
@sample
where
paymentdate is null
group by
invoiceid
[/code]

EDIT: Had this a while ago but been yakin on the phone

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 13:36:11

I want to ask one more question
since the original query is more complicated and uses more than 1 (table about 10)
how can i give the column name an alias, like Joe is selecing the alias and then selecting the count by doing the case. What if I have multiple tables do i do the same thing then to give an alias to column name
say I have another table with SalesPerson

Now I want to select
Select
Salesperson
InvoiceID
, Sum(Over30) As Over30
, Sum(Under30) As Under30
From

HOW CAN I WRITE THIS QUERY and I need all the coulmn names.

declare @sales table
(InvoiceID int, Salesperson varchar(20))

insert @sales
select 1, 'JOE'

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-18 : 13:51:42

Join the tables sounds like the answer, but i am not sure of the question? Follow the first link in my signature and restate your question.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 13:58:11

THIS IS THE DATA

declare @sample table
(InvoiceID int, Duedate datetime, paymentdate datetime)

insert @sample
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 1, '2/5/2005', NULL

declare @sales table
(InvoiceID int, Salesperson varchar(20))

insert @sales
select 1, 'JOE'

Now I want a query to give me

Salesperson, invoiceid, count(Pending >30 days), count(Pending <=30 days)

How do I join the tables I can not use temporary tables in here. So need do it without them.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 14:05:20

Do a LEFT JOIN!

declare @Invoice table (InvoiceID int, Duedate datetime, paymentdate datetime)

insert @Invoice
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 1, '2/5/2005', NULL

declare @salesperson table
(InvoiceID int, Salesperson varchar(20))

insert @salesperson
select 1, 'JOE'



SELECT		sp.SalesPerson,
		i.invoiceid,
		sum(case when isnull(datediff(d, i.duedate, getdate()), -1) < 30 then 1  else 0 end) [Less than 30],
		sum(case when isnull(datediff(d, i.duedate, getdate()), -1) > 29 then 1 else 0 end) [30 or More]
from		@salesperson sp
left join	@invoice i on i.invoiceid = sp.invoiceid
where		i.paymentdate is null
group by	sp.SalesPerson,
		i.invoiceid

Peter Larsson
Helsingborg, Sweden

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 14:16:44

Thanks PESO, but paymentdate is null applies only to the 2 case statements. I want to see all the sales person in there. Even if they have no pending payments I should be able to see their names.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 14:17:54

What happens if you try to remove the WHERE clause, and run the query again?

Peter Larsson
Helsingborg, Sweden

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 14:21:06

THIS IS NEW DATA

insert @Invoice
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 2, '2/5/2005', '11/4/2005'union

declare @salesperson table
(InvoiceID int, Salesperson varchar(20))

insert @salesperson
select 1, 'JOE' union all
select 2, 'SAM'

I WANT TO SEE BOTH THE PEOPLE IN THE RESULT BUT FOR SAM THE TOTALS WILL BE 0 in the case statements.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 14:29:00

And Zero SUM for SAM is not the wanted result?

declare @Invoice table (InvoiceID int, Duedate datetime, paymentdate datetime)

insert @Invoice
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 2, '2/5/2005', '11/4/2005'

declare @salesperson table (InvoiceID int, Salesperson varchar(20))

insert @salesperson
select 1, 'JOE' union all
select 2, 'SAM'


SELECT		sp.SalesPerson,
		i.invoiceid,
		sum(case when i.duedate is null then 0 when datediff(d, i.duedate, getdate()) < 30 then 1 else 0 end) [Less than 30],
		sum(case when i.duedate is null then 0 when datediff(d, i.duedate, getdate()) > 29 then 1 else 0 end) [30 or More]
from		@salesperson sp
left join	@invoice i on i.invoiceid = sp.invoiceid
where		i.paymentdate is null
group by	sp.SalesPerson,
		i.invoiceid

Peter Larsson
Helsingborg, Sweden

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 14:36:51

I will include that in the case statement above. I did not looked that well
Sometime you just can not think right>>>>>>>>>>>>>>>

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-09-18 : 14:38:09

Dang. Go to lunch and Peter backs up a truck dumping answers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 14:39:54

You did all the hard work. I just filled in the blanks.

Lunch?
Can I come work for your company?

It is 8:40 pm here now...

Peter Larsson
Helsingborg, Sweden

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 14:40:21

That is the wanted result and if i do

declare @Invoice table (InvoiceID int, Duedate datetime, paymentdate datetime)

insert @Invoice
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 2, '2/5/2005', '11/4/2005'

declare @salesperson table (InvoiceID int, Salesperson varchar(20))

insert @salesperson
select 1, 'JOE' union all
select 2, 'SAM'

SELECT sp.SalesPerson,
i.invoiceid,
sum(case when i.duedate is null then 0 when datediff(d, i.duedate, getdate()) < 30 then 1 else 0 end) [Less than 30],
sum(case when i.duedate is null then 0 when datediff(d, i.duedate, getdate()) > 29 then 1 else 0 end) [30 or More]
from @salesperson sp, @invoice i
---left join @invoice i on i.invoiceid = sp.invoiceid
where i.invoiceid = sp.invoiceid
--i.paymentdate is null
group by sp.SalesPerson,
i.invoiceid

then i get all the results I need to show SAM in there as well.
I am trying this too but any help would be appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 14:41:47

Why did you go from a LEFT JOIN to a CROSS JOIN all of a sudden?

SELECT sp.SalesPerson,
i.invoiceid,
sum(case when i.duedate is null then 0 when datediff(d, i.duedate, getdate()) < 30 then 1 else 0 end) [Less than 30],
sum(case when i.duedate is null then 0 when datediff(d, i.duedate, getdate()) > 29 then 1 else 0 end) [30 or More]
from @salesperson sp
left join @invoice i on i.invoiceid = sp.invoiceid
where i.invoiceid = sp.invoiceid
group by sp.SalesPerson,
i.invoiceid

Have you at any time tested the suggestions given here?

Peter Larsson
Helsingborg, Sweden

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 14:46:39

There is no NULL for SAM so it should be zero I still see a 1 for SAM
need to debg this also it should be :

SELECT sp.SalesPerson,
i.invoiceid,
sum(case when i.paymentdate is null then 0 when datediff(d, i.duedate, getdate()) < 30 then 1 else 0 end) [Less than 30],
sum(case when i.paymentdateis null then 0 when datediff(d, i.duedate, getdate()) > 29 then 1 else 0 end) [30 or More]
from @salesperson sp
left join @invoice i on i.invoiceid = sp.invoiceid
where i.invoiceid = sp.invoiceid
group by sp.SalesPerson,
i.invoiceid

we are counting the NULL payment dates where its been more than 30 days and less then 30 days

Sam should have both zeros, I still get 1 in 30 or more for him

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 14:51:05

Yes, because you gave this sample data (09/18/2006 : 14:21:06 ), where Sam has an invoice.

insert @Invoice
select 1, '7/5/2006', '3/5/2005' union all
select 1, '8/5/2006', '4/5/2005'union all
select 1, '9/5/2006', NULL union all
select 1, '3/5/2006', NULL union all
select 1, '2/5/2005', '9/5/2005' union all
select 1, '2/5/2005', NULL union all
select 1, '9/5/2006', NULL union all
select 1, '9/5/2006', '11/5/2005'union all
select 1, '2/5/2005', NULL union all
select 2, '2/5/2005', '11/4/2005'

Peter Larsson
Helsingborg, Sweden

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-09-18 : 14:55:20

But for his invoice payment has been made so it should be ZERO in both the cases

select invoiceid
,sum(over30) [count(Pending >30 days)]
,sum(under30) [count(Pending <=30 days)]
from (
select invoiceid
,over30 = case when datediff(day, duedate, getdate()) > 30 then 1 else 0 end
,under30 = case when datediff(day, duedate, getdate()) between 0 and 30 then 1 else 0 end
from @sample
where paymentdate is null
) a
group by invoiceid

gives the correct result. But I do not want to do it this way. I am still trying to work it out to get the correct result

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources