Need help... - SQL Server Forums

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

Need help...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 10:19:19

How do I write the last part in bold?

SELECT AlldiariesID, WeeklyID, hun, recno, week_start_date, drycde, drydat, dryfu, curstat
FROM AllDiaries
WHERE (drycde IN ('d8', '5y', 'u5', 'n7', 'n8', 'kz')) and drydat > 270 days of today's date

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:21:48

SELECT AlldiariesID, WeeklyID, hun, recno, week_start_date, drycde, drydat, dryfu, curstat
FROM AllDiaries
WHERE (drycde IN ('d8', '5y', 'u5', 'n7', 'n8', 'kz')) and drydat > dateadd(day,datediff(day,0,getdate())+270,0)

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-13 : 10:21:52

[code]drydate > dateadd(d,270,dateadd(d, datediff(d, 0, getdate()), 0))[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:23:45

270 days ahead or behind?
drydat > dateadd(day,datediff(day,0,getdate()),270)

or

drydat > dateadd(day,datediff(day,0,getdate()),-270)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 10:27:10

Okay I tried both of your ways and not getting any records in return.

SELECT     AlldiariesID, WeeklyID, hun, recno, week_start_date, drycde, drydat, dryfu, curstat
FROM         AllDiaries
WHERE     (drycde IN ('d8', '5y', 'u5', 'n7', 'n8', 'kz')) and drydat > dateadd(d,270,dateadd(d, datediff(d, 0, getdate()), 0))

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 10:28:28

Okay I have to join a new column and get that total from the column then it will show me the ones that are over 270 days. Sorry.

Okay will be back in a few if I can't sum it up.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:28:58

Then there are no records in the table

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:29:02

ok ..so now you can do below things

1. post some sample data along table structure
2. give your expected out of them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 10:46:22

That's exactly what happened no records appeared.

Okay here's some data:


Area   recno       week_start_date         Drycde          Drydat
H20	01	2010-03-26 00:00:00.000	   5Y	2008-02-14 00:00:00.000	
H20	06	2010-03-26 00:00:00.000	   D8	2010-04-07 00:00:00.000	
H21	06	2010-03-26 00:00:00.000	   5Y	2008-04-24 00:00:00.000	
H22	02	2010-03-26 00:00:00.000	   5Y	2009-02-20 00:00:00.000	
H23	04	2010-03-26 00:00:00.000	   5Y	2008-02-14 00:00:00.000	
H24	01	2010-03-26 00:00:00.000	   5Y	2008-02-14 00:00:00.000	
H24	01	2010-03-26 00:00:00.000	   KZ	2009-10-03 00:00:00.000

Using this query I want to get the listed drycde (listed below) Total by Area and total over 270 days old

The data in the Area field is H20 - H25.

SELECT     AlldiariesID, A.WeeklyID, W.Area,hun, recno, A.week_start_date, drycde, drydat, dryfu, curstat
FROM         AllDiaries A
join weeklydiariesfile W
on A.weeklyid = W.weeklyid
WHERE     (drycde IN ('d8', '5y', 'u5', 'n7', 'n8', 'kz'))

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:48:42

then shouldnt it be?


SELECT     AlldiariesID, A.WeeklyID, W.Area,hun, recno, A.week_start_date, drycde, drydat, dryfu, curstat
FROM         AllDiaries A
join weeklydiariesfile W
on A.weeklyid = W.weeklyid
WHERE     (drycde IN ('d8', '5y', 'u5', 'n7', 'n8', 'kz'))
AND Drydat < DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 10:51:19

Yes thanks I thought I had to add the Area's together but since the drydat is different that works it list them all. Thanks! Whew so happy this forum is here!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 10:53:49

and Can i reiterate the need to provide proper information from you next time to get quick and accurate solution!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 10:57:24

quote:
Originally posted by JJ297

Yes thanks I thought I had to add the Area's together but since the drydat is different that works it list them all. Thanks! Whew so happy this forum is here!

It is becuase you didn't respond to this

270 days ahead or behind?

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 11:01:01

I will thanks!

It's total over 270 days old (behind today's date).

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 11:02:41

You are right I missed Visakhl6 question:

270 days ahead or behind?
drydat > dateadd(day,datediff(day,0,getdate()),270)

or

drydat > dateadd(day,datediff(day,0,getdate()),-270)

Thanks again!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 11:03:25

or

drydat > dateadd(day,datediff(day,0,getdate())-270,0)

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-13 : 11:10:31

This is what I have and

This gives me 9746 records
Drydat > DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

This gives me 11590 records
Drydat < DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

So to show total over 270 days old I'm using:
Drydat > DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 11:33:10

quote:
Originally posted by JJ297

This is what I have and

This gives me 9746 records
Drydat > DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

This gives me 11590 records
Drydat < DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

So to show total over 270 days old I'm using:
Drydat > DATEADD(dd,DATEDIFF(dd,0,GETDATE())-270,0)

Thanks again!

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources