April 16, 2012 at 10:02 am
I'm trying to wrap my head around how to best do this. I would like a stored proc that would return a result set with columns based on a date range.
For example
Start Date '2/1/2012' to End Date '2/8/2012'
The table would have a date column with multiple rows for a client.
example
clientkey DocDate
1234 2/2/2012
1234 2/5/2012
1234 2/8/2012
I want to return the following result
clientkey Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day 8(the columns names aren't important, they just correlate to the date range)
1234 X X X (Again whats in the row is unimportant, it could be the dates, or x's)
Thanks for any help!
April 16, 2012 at 11:31 am
While it's possible to do this in T-SQL, it's not the best tool for the job. Without knowing how you are planning to use these results, it's difficult to tell you what the best tool would be.
If you have to use T-SQL, there is a good article by Jeff on dynamic pivots and cross-tabs[/url].
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 16, 2012 at 11:40 am
It's basically for a crystal report.
I've thought about trying to accomplish the same thing through Crystal, but thought I would look SQL first.
I figured it out with a pivot in SQL, which if I keep working on it will give me the desired results, unfortunately this is a database in SQL 2000 mode because of all it's * joins. The front end program has filters which uses * joins and this will take weeks to fix in order to allow us to move to a more recent SQL mode.
April 16, 2012 at 11:55 am
Crystal Reports has a crosstab object that was specifically designed for this type of operation. It's MUCH, MUCH easier to do this Crystal than in T-SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 18, 2012 at 12:33 am
I can certainly empathize with your plight, having worked in the dank dungeon of SQL 2000 for some time. The following will work for you there:
DECLARE @docs TABLE
(clientkey INT, DocDate DATETIME)
INSERT INTO @docs (clientkey, DocDate)
SELECT 1234,'2012-02-02'
UNION ALL SELECT 1234,'2012-02-05'
UNION ALL SELECT 1234,'2012-02-08'
DECLARE @startdate DATETIME
SET @startdate = '2012-02-01'
SELECT clientkey, MAX(D1) AS [D1], MAX(D2) AS [D2], MAX(D3) AS [D3], MAX(D4) AS [D4]
,MAX(D5) AS [D5], MAX(D6) AS [D6], MAX(D6) AS [D6], MAX(D8) AS [D8]
FROM (
SELECT clientkey
,CASE WHEN DocDate = DATEADD(day, 0, @startdate) THEN DocDate ELSE NULL END AS [D1]
,CASE WHEN DocDate = DATEADD(day, 1, @startdate) THEN DocDate ELSE NULL END AS [D2]
,CASE WHEN DocDate = DATEADD(day, 2, @startdate) THEN DocDate ELSE NULL END AS [D3]
,CASE WHEN DocDate = DATEADD(day, 3, @startdate) THEN DocDate ELSE NULL END AS [D4]
,CASE WHEN DocDate = DATEADD(day, 4, @startdate) THEN DocDate ELSE NULL END AS [D5]
,CASE WHEN DocDate = DATEADD(day, 5, @startdate) THEN DocDate ELSE NULL END AS [D6]
,CASE WHEN DocDate = DATEADD(day, 6, @startdate) THEN DocDate ELSE NULL END AS [D7]
,CASE WHEN DocDate = DATEADD(day, 7, @startdate) THEN DocDate ELSE NULL END AS [D8]
FROM @docs
) x
GROUP BY clientkey
However, this only works when the period is specifically 8 days long. If you need that period to vary, you're only recourse is to use dynamic SQL and build up both CASE (in the derived table) and the columns on your outer SELECT with a loop on the date range.
She's a mighty ugly baby, but she's my baby!
Let me know if you don't know how to do it using Dynamic SQL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 8:42 am
dwain.c (4/18/2012)
However, this only works when the period is specifically 8 days long. If you need that period to vary, you're only recourse is to use dynamic SQL and build up both CASE (in the derived table) and the columns on your outer SELECT with a loop on the date range.She's a mighty ugly baby, but she's my baby!
Let me know if you don't know how to do it using Dynamic SQL.
T-SQL is simply the wrong tool for this situation. That's why the solution is ugly.
The OP has already said that he is using Crystal Reports. Crystal Reports is the right tool.
Part of being a good programmer is knowing what tools are best for given jobs. Yes, you sometimes have to make do when you don't have the right tools available. That's not the case here. Crystal Reports is the right tool, and it's available. The T-SQL approach should only be used when you have to "make do."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply