SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with a query to generate a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bmooberry
Starting Member

USA
4 Posts

Posted - 08/07/2013 :  11:18:40  Show Profile  Reply with Quote
Hey everyone. I'm a SQL rookie and would sure appreciate some help with generating a view. I have a query that outputs the data I want for the view, but I've been told by the guy who wrote it that it's too complicated to make into a view, and he's not sure how to fix that. I'll paste the query at the end of this post, but first I'll explain what it's doing and why.

We have a business process called "allocation" that occurs once a month. The date it occurs isn't random, but it can't be defined by a formula either, and we don't have a schedule of when future ones will occur until management tells us a few days ahead of time. For reporting, we measure a number of our important metrics by what we call "allocation months", rather than calendar months. An allocation month is defined as the day after an allocation through the date of the next allocation. For example, if we allocated on June 18th and July 22nd, that allocation month (which we would call allocation month August) would run from June 19th through July 22nd. We then have a range I'm currently calling "drop month" (terrible name), which would follow the same date range in the example I just listed, June 19th through July 22nd, only it would be called July rather than August.

We currently implementing a BI tool that will generate our reports for us (we currently use manually created spreadsheets). The snag we've run into is that the BI tool needs us to create a view that lists out every calendar day and defines what allocation month and drop month that day falls into in order to create a custom time hierarchy so we can use allocation month and drop month as time attributes in our reports. If we don't do this, the BI tool can't group the measures by the correct date ranges, skewing the results. We've tried a number of workarounds, and the only thing that will work is an actual view in the database. The guy I was working with said he could probably create a table and make it work, but we'd like to avoid creating a new table if possible.

The dates that past allocations have occurred are saved in our database. So what this query does is create the allocation month ranges I talked about before and then runs every calendar date through those ranges, and labels is appropriately. One element that makes the query more complicated is that the most recent allocation month will always have a start date, but never an end date (because the allocation hasn't happened yet, thus isn't recorded in the database). The query works around that and still labels the calendar days correctly, but the way we did it screws up some less complicated ways we had of writing the query.

Anyway, I'd be very grateful for any kind of help or suggestions. Getting the proper date ranges for our reports seems like it should be trivial, and it's very frustrating that this is what's holding us up. Here is the current query:


begin
DECLARE @CalendarMonths TABLE (date date,
PRIMARY KEY (date)) ;

DECLARE @VALUES table (
startDate date,
endDate date,
DropMonth int,
alocAllocationYM int
);

DECLARE @basedate date,
@enddate date,
@lastDate date;

SELECT @basedate = (
SELECT MIN (ndpmDateProcessed)
FROM tbl_NeedAlloc_PublishedMain
WHERE ndpmPublishType = 'Standard Needs') ;
SELECT @lastDate = (
SELECT MAX (ndpmDateProcessed)
FROM tbl_NeedAlloc_PublishedMain
WHERE ndpmPublishType = 'Standard Needs')

SELECT @enddate = GETDATE();

WHILE @basedate <= @enddate
BEGIN
INSERT INTO @CalendarMonths (date)
VALUES (
@basedate) ;
SELECT @basedate = DATEADD (DAY, 1, @basedate) ;
END;

WITH StartMonth
AS (SELECT ROW_NUMBER () OVER (ORDER BY ndpmNeedPubMain_ID) AS rowNum,
ndpmDateProcessed
FROM tbl_NeedAlloc_PublishedMain
WHERE ndpmPublishType = 'Standard Needs') , EndMonth
AS (SELECT ROW_NUMBER () OVER (ORDER BY ndpmNeedPubMain_ID) AS rowNum,
ndpmDateProcessed
FROM tbl_NeedAlloc_PublishedMain
WHERE ndpmPublishType = 'Standard Needs')
insert into @VALUES SELECT DATEADD(DAY, 1, sm.ndpmDateProcessed) startDate,
cast(em.ndpmDateProcessed as date) endDate,
dbo.YMFmt (DATEADD (MONTH, 1, sm.ndpmDateProcessed)) AS DropMonth,
dbo.YMFmt (DATEADD (MONTH, 2, sm.ndpmDateProcessed)) AS alocAllocationYM

FROM StartMonth sm
INNER JOIN EndMonth em ON sm.rowNum + 1 = em.rowNum;


insert into @VALUES values (dateadd(DAY,1,@lastDate), GETDATE(), dbo.YMFmt (DATEADD (MONTH, 1, @lastDate)), dbo.YMFmt (DATEADD (MONTH, 2, @lastDate)));

select *
from @VALUES v LEFT JOIN @CalendarMonths c ON c.date >=v.startDate
AND c.date <= v.endDate ;
end



ScottPletcher
Constraint Violating Yak Guru

USA
349 Posts

Posted - 08/07/2013 :  12:52:20  Show Profile  Reply with Quote
Excellent explanation!

I think the code below might simplify your existing code. If it's close, we can adjust it further, and eventually get it converted into a view (which is 100% doable).

One thing did confuse me: I would think the final table should contain every date with its corresponding alloc month, drop month, etc.. So I did a LEFT JOIN from the CalendarDates (all dates) to the existing dates in the current table.

I didn't do the final INSERT of the end/last month; we can do that later.

This code is more a test-of-concept than a final product.


DECLARE @ExistingDates TABLE (
    id int IDENTITY(1, 1) NOT NULL,
    ndpmDateProcessed datetime NOT NULL,
    PRIMARY KEY ( ndpmDateProcessed )
    )

INSERT INTO @ExistingDates
SELECT DISTINCT ndpmDateProcessed
FROM tbl_NeedAlloc_PublishedMain
WHERE 
    ndpmPublishType = 'Standard Needs' AND
    ndpmDateProcessed IS NOT NULL --remove if not needed
   

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]     
),
CalendarDates AS (
    SELECT DATEADD(DAY, tally, Base_Date) AS CalendarDate
    FROM (
        SELECT 
            MIN(ndpmDateProcessed) AS Base_Date,
            MAX(ndpmDateProcessed) AS Last_Date  
        FROM @ExistingDates
    ) AS Control_Dates
    INNER JOIN cteTally ON
        cteTally BETWEEN 0 AND DATEDIFF(DAY, Control_Dates.Base_Date, Control_Dates.Last_Date)
)
--INSERT INTO @Values ( ... )
SELECT 
    DATEADD(DAY, 1, ed_start.ndpmDateProcessed) AS startDate,
    CAST(ed_end.ndpmDateProcessed AS date) AS endDate,
    dbo.YMFmt (DATEADD (MONTH, 1, ed_start.ndpmDateProcessed)) AS DropMonth, 
    dbo.YMFmt (DATEADD (MONTH, 2, ed_start.ndpmDateProcessed)) AS alocAllocationYM
FROM CalendarDates cd
LEFT OUTER JOIN @ExistingDates ed_start ON
    ed_start.ndpmDateProcessed = cd.CalendarDate
LEFT OUTER JOIN @ExistingDates ed_end ON
    ed_end.id = ed_start.id + 1





Go to Top of Page

bmooberry
Starting Member

USA
4 Posts

Posted - 08/07/2013 :  14:32:44  Show Profile  Reply with Quote
Wow, thanks Scott. I'll take a look at this with the guy who wrote the original query I posted and see what we can do. I'm glad to hear that what we want to do can be done. I really appreciate the help.
Go to Top of Page

bmooberry
Starting Member

USA
4 Posts

Posted - 08/08/2013 :  09:36:59  Show Profile  Reply with Quote
Hey Scott, if it wouldn't be too much trouble, could you walk me through what you're doing in that query you wrote? My coworker is busy the next couple of days putting out some fires, and I don't know enough SQL to be able to follow what you did. Thanks!
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
349 Posts

Posted - 08/08/2013 :  11:10:15  Show Profile  Reply with Quote
Sure. Based on my reading of the original problem, it looked like column "ndpmDateProcessed" in table "tbl_NeedAlloc_PublishedMain" contained allocation month dates.

Since those dates must be joined to each other, and are read repeatedly, I create a local table, @ExistingDates, to hold all the distinct allocation month dates so they can be indexed and looked up quickly.

The first two CTEs -- cteDigits and cteTally -- are used to create a (virtual) tally table on the fly during the query. [Google "tally table" to get more info on tally tables in general.]

The tally table is used to quickly generate rows for all days between ndpmDateProcessed dates, instead of having to loop. For example, if sequential ndpmDateProcessed dates are June 18th and July 22nd, the tally table is used to generate a separate row for June 19, June 20, June 21, ..., July 20, July 21 and July 22.

Basically it (hopefully) duplicates the general logic of the original code, but without loops and multiple accesses to the tbl_NeedAlloc_PublishedMain table.

Edited by - ScottPletcher on 08/08/2013 12:05:26
Go to Top of Page

bmooberry
Starting Member

USA
4 Posts

Posted - 08/19/2013 :  11:41:19  Show Profile  Reply with Quote
Hey Scott, thanks again for the help. My apologies for the delayed response - my wife gave birth last Friday and I was out last week as a result.

Anyway, the guy I've been working with took some of what you wrote and sort of Frankenstein'ed it into his own and put together something that does exactly what we need it to do. Thanks so much for your help!
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
349 Posts

Posted - 08/19/2013 :  11:53:52  Show Profile  Reply with Quote
You're welcome ... glad it helped!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000