Selecting Acording to Columns - 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

Selecting Acording to Columns

Author

Topic

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-18 : 05:33:42

Hi.

I need to do the following. Select Columns as per date for last 3 days.

My data

Item | 18/12/2008 | 17/12/2008 | 16/12/2008 | 15/12/2008 etc.
--------------------------------------------------------------
a | 2 | 5 | 55 | 54
c | 9 | 7 | 1 | 4
b | 7 | 7 | 101 | 3

My script must check today's date ( 18/12/2008 )
and return the following

Item | 18/12/2008 | 17/12/2008 | 16/12/2008 |
---------------------------------------------
a | 2 | 5 | 55 |
c | 9 | 7 | 1 |
b | 7 | 7 | 101 |

Please Help.

Regards.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 05:35:31

You Can Do This By Using Dynamic SQL

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 05:36:40

Item | 18/12/2008 | 17/12/2008 | 16/12/2008 | 15/12/2008 etc.
these r the columns in ur data then select Item ,18/12/2008 ,17/12/2008 ,16/12/2008

r try this using pivot

if not can u explain ur requirement briefly

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-18 : 05:57:14

Tommorow it will add a date and so one. I giving this for a person to check soh every day. To make it easy my report should pull the last 3 days of data. I know, what a bad table design.

Regards

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-18 : 06:31:26

Does your table have a seperate column for every date? - what will it be like in a few years?
It would be easier to have a single Date column which you PIVOT to display for the last 3 days. The pivot can be either in your report or inside a View. So data is stored sensibly and user sees it as he wants.

Deon Smit
Starting Member

47 Posts

Posted - 2008-12-18 : 07:24:32

Do you have any guidlines for me about PIVOTING in SQL. Don't know it and is keen to learn.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 07:28:36

read in books online

go through this examples for pivot are there
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116505

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-18 : 11:57:11

You could use the older method of CASE:

SELECT [ID]
,Case when ([ModDate] >= GetDate()-1 and [ModDate] < GetDate()+1) then [ModDate] end as Today
,Case when ([ModDate] >= GetDate()-2 and [ModDate] < GetDate()-1) then [ModDate] end as [D-1]
,Case when ([ModDate] >= GetDate()-3 and [ModDate] < GetDate()-2) then [ModDate] end as [D-2]
,Case when ([ModDate] >= GetDate()-4 and [ModDate] < GetDate()-3) then [ModDate] end as [D-3]
,[textString]
,[teststring2]
FROM TableNameWHERE mODdATE > getDate()-3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources