Calculate value to make new columns

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

General SQL Server Forums

New to SQL Server Programming

Calculate value to make new columns

Author

Topic

tig2810
Starting Member

9 Posts

Posted - 2009-09-03 : 07:14:19

Hi

Newbie question. I have a query below but I want to add 3 new columns to say where Diff = 0, Diff < 0 and Diff > 0. I'm sure this is easy but I cant find it on Google as I dont know the correct termanology to search for?

Thanks for any help.

SELECT Description, ScheduledDate, ActualDate, datediff(day,ScheduledDate,ActualDate) As Diff
FROM tasks inner join joborderheader on parentid = jd_pk
inner join orgheader on jd_oh_buyer = oh_pk
WHERE Type = 'MIL' and
ParentTableCode = 'JD' and
Code = 'TESTCODE'

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-09-03 : 07:31:49

question is not clear... what is the expected output?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-03 : 07:33:03

You need only one new column


SELECT 
Description, 
ScheduledDate, 
ActualDate, 
datediff(day,ScheduledDate,ActualDate) As Diff ,
case
  when datediff(day,ScheduledDate,ActualDate) = 0 then 'Diff = 0'
  when datediff(day,ScheduledDate,ActualDate) > 0 then 'Diff > 0'
  else 'Diff < 0'
end as DiffColumn
FROM tasks inner join joborderheader on parentid = jd_pk
inner join orgheader on jd_oh_buyer = oh_pk
WHERE Type = 'MIL' and
ParentTableCode = 'JD' and
Code = 'TESTCODE'

No, you're never too old to Yak'n'Roll if you're too young to die.

tig2810
Starting Member

9 Posts

Posted - 2009-09-03 : 07:50:32

Sorry I was unclear. I have put my current output ans expected output.

I need columns to say 0 or 1 to see if if something is true so I can then use COUNT in a GROUP BY.
Thanks for your help. Hopefully the below makes it clearer.

Current

Description ScheduledDate ActualDate Diff
Ex Factory 2009-07-14 00:00:00 2009-07-14 17:33:00 0
Origin Receival at Wharf / Depot 2009-07-16 00:00:00 2009-07-16 17:33:00 0
Order Placed 2009-07-13 17:22:00 2009-07-13 17:29:00 0
Order Confirmed NULL 2009-07-24 00:00:00 NULL
Ex Factory 2009-07-19 00:00:00 2009-07-19 00:00:00 0
Order Confirmed NULL 2009-07-24 00:00:00 NULL
Received at Origin Depot 2009-07-04 02:39:00 2009-07-05 00:00:00 1
Departed Origin Port 2009-07-06 02:39:00 2009-07-07 00:00:00 1

Required

Description ScheduledDate ActualDate Diff Diff = 0 Diff > 0 Diff < 0
Ex Factory 2009-07-14 00:00:00 2009-07-14 17:33:00 0 1 0 0
Origin Receival at Wharf / Depot 2009-07-16 00:00:00 2009-07-16 17:33:00 0 1 0 0
Order Placed 2009-07-13 17:22:00 2009-07-13 17:29:00 0 1 0 0
Order Confirmed NULL 2009-07-24 00:00:00 NULL 0 0 1
Ex Factory 2009-07-19 00:00:00 2009-07-19 00:00:00 0 1 0 0
Order Confirmed NULL 2009-07-24 00:00:00 NULL 0 0 1
Received at Origin Depot 2009-07-04 02:39:00 2009-07-05 00:00:00 1 0 1 0
Departed Origin Port 2009-07-06 02:39:00 2009-07-07 00:00:00 1 0 1 0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-03 : 07:57:25

[code]SELECT
Description,
ScheduledDate,
ActualDate,
datediff(day,ScheduledDate,ActualDate) As Diff ,
case
when datediff(day,ScheduledDate,ActualDate) = 0 then '1'
else '0'
end as [Diff = 0],
case
when datediff(day,ScheduledDate,ActualDate) > 0 then '1'
else '0'
end as [Diff > 0],
case
when datediff(day,ScheduledDate,ActualDate) < 0 then '1'
else '0'
end as [Diff < 0],
FROM tasks inner join joborderheader on parentid = jd_pk
inner join orgheader on jd_oh_buyer = oh_pk
WHERE Type = 'MIL' and
ParentTableCode = 'JD' and
Code = 'TESTCODE'
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-03 : 07:59:53

quote:

Originally posted by webfred

SELECT 
Description, 
ScheduledDate, 
ActualDate, 
datediff(day,ScheduledDate,ActualDate) As Diff ,
case
  when isnull(datediff(day,ScheduledDate,ActualDate),-1) = 0 then '1'
  else '0'
end as [Diff = 0],
case
  when isnull(datediff(day,ScheduledDate,ActualDate),-1) > 0 then '1'
  else '0'
end as [Diff > 0],
case
  when isnull(datediff(day,ScheduledDate,ActualDate),-1) < 0 then '1'
  else '0'
end as [Diff < 0],
FROM tasks inner join joborderheader on parentid = jd_pk
inner join orgheader on jd_oh_buyer = oh_pk
WHERE Type = 'MIL' and
ParentTableCode = 'JD' and
Code = 'TESTCODE'

ISNULL() added!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources