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.
| Author |
Topic |
|
evanburen
Posting Yak Master
167 Posts |
 Posted - 2011-09-25 : 12:34:24
|
I've been working on this a few days and can't seem to get it right. I've uploaded images to explain my situation.    I am trying to create a report that looks like Work in Progress Aging Report. There are only 2 tables involved to produce this report, Requests_Vangent and IMF_Types, which are joined by the field IMF_ED (see a.png). b.png contains some possible values and the field types for IMF_Types. Requests_Vangent.DateSubmitted is the field used to determine "Business Days Since Receipt". |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-25 : 13:43:11
|
[code]SELECT IMF_type,[1],[2],[3],[4],[5],[6],[7], ,[9],[10],[10+]FROM(SELECT it.IMF_type,CASE WHEN DATEDIFF(dd,rv.DateSubmitted,GETDATE()) > 10 THEN '10+' ELSE CAST(DATEDIFF(dd,rv.DateSubmitted,GETDATE()) AS varchar(1))END AS DaysOpen,1 AS OccurFROM dbo.Requests_Vangent rvINNER JOIN dbo.IMF_types itON it.IMF_ID = rv.IMF_ID)mPIVOT (SUM(Occur) FOR DaysOpen IN ([1],[2],[3],.....[10],[10+]))p[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2011-09-25 : 16:26:37
|
| That's amazing....thank you very much. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-26 : 00:48:53
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Aging Report