Date function

Question

Date function

farooq.hbs

SSCrazy

Points: 2083
More actions
February 14, 2013 at 7:49 am

#268648

Hi guys,
I'm struck on one thing.. help me out to get of this issue.
I have table
Name Phase Phase_Start_Date Phase_end_date
ABC Define 2012-11-24 2012-11-24
ABC Measure 2012-11-25 2013-01-26
ABC Analyze 2013-01-27 2013-03-09
ABC Improve 2013-03-10 2013-04-14
ABC Control 2013-04-15 2013-05-06
In this above example for the project ABC there is different phase of different dates
So i want to result like this
Let say user selects from 01-10-2012 to 31-09-2013 (One full circle)
Name Oct Nov Dec Jan Feb March April May June July August September
ABC Null Measure Measure Analyse Analyse Improve Control Control

The result-set depends upon on the phase start and end date... It should display the max date phase if two phase are present in the same month

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

This is entirely too vague. Can you post ddl and sample data? I think you need to use a calendar table and then a cross tab but from what you posted I just can't tell. Take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

farooq.hbs SSCrazy Points: 2083 More actions · Answer 2

Thanks for the reply.

I used calendar month table, but it returning two or three rows for one name project because some 2 or 3 phase are present in the one month so..

I need only one row matching with maximum date phase if the month contain 2 or 3 phase in it

I cant post DDL... Bcoz it is very large database.. I just gave the sample

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

farooq.hbs (2/14/2013)
Thanks for the reply.
I used calendar month table, but it returning two or three rows for one name project because some 2 or 3 phase are present in the one month so..
I need only one row matching with maximum date phase if the month contain 2 or 3 phase in it
I cant post DDL... Bcoz it is very large database.. I just gave the sample

DDL is the create table scripts. Can you turn your sample into something that is consumable? Please read the article about best practices. You don't have to post your entire database, just the table structure of your sample and inserts for the data. Then explain what you want as output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

If you can't post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (as INSERT INTO statements) for the tables involved, and expected results based on the sample data; just how do you expect anyone to provide you with any real answers? All you are going to get are shots in the dark that may, if you are really lucky, help.

We are volunteers here and the more you provide to help us understand your problem, the more likely you are to get good answers in return. Remember, we can't see what you see.

Please read the first article I reference below in my signature block, it provides you with the guidanace you need to post the information we need to help you.