Help with stored procedure - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 10:13:20

Here's what my table looks like:


Out_DryDte1    Out_DryDte2   Out_DryDte3   week_start_date

12/2/2008      11/2/2008      1/2/2009       20090313

I want to use the week_start_date field and check the other fields to see if the date is less than 01/09/2009 if so list them. So I should have this record listed three times in the results.

Viyayisonly helped me write some of this (I modified it)

Is there an easier way to write this stored procedure below? I've tried this

SELECT * from
yourtable

WHERE ((Out_DryDte1 < '01/09/2009') AND (Out_DryDte2 < '01/09/2009') AND
(Out_DryDte3 < '01/09/2009') AND (Out_DryDte4 < '01/09/2009')
(Out_DryDte5 < '01/09/2009') AND (Out_DryDte6 < '01/09/2009')
(Out_DryDte7 < '01/09/2009') AND (Out_DryDte8 < '01/09/2009') AND (Week_Start_Date = '20090313'))
or

((Out_DryDte1 < '01/09/2009') AND (Out_DryDte2 < '01/09/2009') AND
(Out_DryDte3 < '01/09/2009') AND (Out_DryDte4 < '01/09/2009')
(Out_DryDte5 < '01/09/2009') AND (Out_DryDte6 < '01/09/2009')
(Out_DryDte7 < '01/09/2009') AND (Week_Start_Date = '20090313'))

Vijayisonly helped me with this. It's similar to what I want to do I was getting the 579ws out of each field. Hope this makes sense.

Select * into Getting579ws
FROM(
select * from BringingNFlatFile where charindex('5w',DryCd1+DryCd2+DryCd3+DryCd4+DryCd5+DryCd6+DryCd7+DryCd8) > 0
union all
select * from BringingNFlatFile where charindex('7w',DryCd1+DryCd2+DryCd3+DryCd4+DryCd5+DryCd6+DryCd7+DryCd8) > 0
union all
select * from BringingNFlatFile where charindex('9w',DryCd1+DryCd2+DryCd3+DryCd4+DryCd5+DryCd6+DryCd7+DryCd8) > 0
) T

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 10:25:42

I'm sure you'll have reasons why you can't normalize this but I feel obliged to suggest it...

queries on de-normalized data is always going to be messy and inefficient. Any chance you can re-organize the design so that you have all the dates in a single column? So the PK would be something like (logicalRowID, DateTypeID) If you had that normalized design then these types of queries becomes much more efficient.

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 10:40:34

I can't because this record is coming from the mainframe. The data was downloaded into a flat file. So this is how one row of data is set up.

Here's the entire record:


Fname  Lname   Out_DryDte1    Out_DryDte2   Out_DryDte3
K      ADAMS   1/2/2009	      12/2/2008      11/2/2008

The results I'm looking for is to have K Adams listed down three times since K has dates less than 1/9/2009.

Does that make sense?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 10:53:16

Do all the dates need to be less than 1/9/2009 for any of K's rows to show up or you want to see any row where the date < 1/9/2009?
And is that date '1/9/2009' a constant that will act for the entire table everytime or is it a parameter or is it stored in another column that you didn't list? (like week_start_date perhaps?)

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 11:08:08

Oop's I did forget the week_start_date Column


Fname Lname  Out_DryDte1   Out_DryDte2   Out_DryDte3  week_start_date
K     ADAMS   1/2/2009	   12/2/2008     11/2/2008    2009/03/13

I have to go back 60 days from the week_start_date which took me to 1/9/2009 (I have to select the closest Friday)

I want to see any row that has a date that's 2009/03/13 - 60 days which brings me to 1/9/2009. So the above record would be listed three times.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 11:16:39

Try this:


select * from  <YourTable>
unpivot (val for cols in([Out_DryDte1], [Out_DryDte2], [Out_DryDte3])) up
where val < dateadd(day, -60, week_start_date)

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 11:22:39

Thanks tried that but getting incorrect syntax near the word For

select * from <YourTable>
unpivot (val for cols in([Out_DryDte1], [Out_DryDte2], [Out_DryDte3])) up
where val < dateadd(day, -60, week_start_date)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 11:42:47

This works for me. Are you using sql server 2005 or higher and is the database compatiblility level set to 90 or greater?


declare @t table
       (Fname varchar(10)
       ,Lname varchar(20)
       ,Out_DryDte1 datetime
       ,Out_DryDte2 datetime
       ,Out_DryDte3 datetime
       ,week_start_date datetime)
insert @t
select 'K', 'Adams', '12/2/2008', '1/2/2008', '1/2/2009', '20090313' union all
select 'L', 'Adams', '12/3/2008', '12/3/2008', '1/31/2009', '20090314'

select * from  @t
unpivot (val for cols in([Out_DryDte1], [Out_DryDte2], [Out_DryDte3])) up
where val < dateadd(day, -60, week_start_date)

OUTPUT:
Fname      Lname                week_start_date         val                     cols
---------- -------------------- ----------------------- ----------------------- --------------
K          Adams                2009-03-13 00:00:00.000 2008-12-02 00:00:00.000 Out_DryDte1
K          Adams                2009-03-13 00:00:00.000 2008-01-02 00:00:00.000 Out_DryDte2
K          Adams                2009-03-13 00:00:00.000 2009-01-02 00:00:00.000 Out_DryDte3
L          Adams                2009-03-14 00:00:00.000 2008-12-03 00:00:00.000 Out_DryDte1
L          Adams                2009-03-14 00:00:00.000 2008-12-03 00:00:00.000 Out_DryDte2

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 12:34:06

Yes I'm using SQL 2005. How do I set the database compatiblility level to 90 or greater?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 12:35:59

I just tried it again and I got Unpivot is not supported then the next error message said incorrect syntax near the word For

select * from my table
unpivot (val for cols in([Out_DryDte1], [Out_DryDte2], [Out_DryDte3])) up
where val < dateadd(day, -60, week_start_date)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-23 : 12:37:53

Right click on database / Properties / Options / Compatibility Level

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 12:41:20

Use this to check the current setting: (change the db name to whatever database you are running this in)

exec sp_dbcmptlevel tempdb --,90

Uncomment the lastparameter to SET the level to 90

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 12:47:13

Thanks guys it was set to 80. Never knew anything about that.

Thanks TG the query is working and gave me exactly what I wanted.

Until again...

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 12:50:09

One last question... Can you explain what the query is doing so I can understand and be able to do this on my own the next time.

If it's not much of a problem.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 13:08:46

Unpivot is one of the features new in 2005. Try playing with some examples in Books Online. It basically "pivots" a row with several columns into several rows of column/value pairs. So that is performing the "normalization" I recommended earlier in the thread.

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-23 : 14:57:37

Great thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources