handle query

Question

handle query

PiyaKaGhar

Mr or Mrs. 500

Points: 568
More actions
December 27, 2014 at 12:59 pm

#293862

HI
for eg. I have a table which has 'products', different 'Phases' of manufacture (Design, Sew, Polish, Wash, Ready, Clean, and many more), this Phases have 'Start date' and 'End date' for each phase, products can have any phase. see attached how the I want the result. How can I write query to get this output.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

The answer would be to convert your string-based dates to real ones to get the date differentials and sums and then apply either a Cross Tab or Pivot to it all. You can see an example of how to do both at the following URL.

http://www.sqlservercentral.com/articles/T-SQL/63681/

If you want a more detailed answer in the form of code, please see the article at the first link under "Helpful Links" in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

I am curious how 2004/08/28 - 2004/08/24 = 4 and 2004/08/31 - 2004/08/31 = 1. Seems to me the result of the first should be 5 OR the result of the second should be 0. Obviously the rules are different for each as presented.