Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help converting query to dynamic dates

Posted on 2012-08-10
5
Medium Priority
?
690 Views
Last Modified: 2012-08-15
I have a report Labour reportWhere I use the table GLAcctPeriod to define the Fiscal Year's periods (i.e. we begin our year May 1 so May 1, 2012 is Period 1 of FiscalYear 2013).  This works as long as I'm only looking at the current and previous fiscal years, there is no data from previous fiscal years.  I want my report to be default of 2 years but we may wish to look at a 5 year period which my current set up would not allow for.  

I was wondering if someone could help me figure out how to replace the GLAcctPeriod table with the code so I can pass multiple parameters (i.e. 2011, 2012, 2013 or 5 for five years) and have the  FiscalYear, Period, PeriodBegDate and PeriodEndDate filled in dynamically?

SELECT     GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate, SUM(WIP.Whours) 
                      AS Labour
FROM         GLAcctPeriod CROSS JOIN
                      WIP
WHERE     (WIP.Wdate BETWEEN GLAcctPeriod.PeriodBegDate AND GLAcctPeriod.PeriodEndDate) AND (WIP.WCodeCat = 'Bill')
GROUP BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate
ORDER BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period

Open in new window


I could be because its Friday but my head hurts just thinking about this.  All help is greatly appreciated and have a great weekend!!!
0
Comment
Question by:HSI_guelph
  • 2
  • 2
5 Comments
 
LVL 12

Assisted Solution

by:Habib Pourfard
Habib Pourfard earned 750 total points
ID: 38282183
You can pass multiple parameters if you pass years as a comma separated string.
DECLARE	@FiscalYears VARCHAR(64)

SET @FiscalYears = '2010, 2011, 2012'

Open in new window


Then you need to split comma separated to multiple values. the following function do this:
CREATE FUNCTION [dbo].[Split] ( @List VARCHAR(MAX) )
RETURNS @ParsedList TABLE ( ID INT )
AS BEGIN
    DECLARE @ID VARCHAR(10),
        @Pos INT

    SET @List = LTRIM(RTRIM(@List)) + ','
    SET @Pos = CHARINDEX(',', @List, 1)

    IF REPLACE(@List, ',', '') <> '' 
        BEGIN
            WHILE @Pos > 0
                BEGIN
                    SET @ID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
                    IF @ID <> '' 
                        BEGIN
                            INSERT  INTO @ParsedList ( ID )
                            VALUES  ( CAST(@ID AS INT) )
                        END
                    SET @List = RIGHT(@List, LEN(@List) - @Pos)
                    SET @Pos = CHARINDEX(',', @List, 1)

                END
        END	
    RETURN
   END

Open in new window



Now you can build your dynamic query:

WHERE GLAcctPeriod.FiscalYear IN (SELECT * FROM  dbo.SplitIDs(@FiscalYears))

Open in new window


Your query could be written like this:
DECLARE	@FiscalYears VARCHAR(64)

SET @FiscalYears = '2008, 2009, 2010, 2011, 2012'

SELECT     GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate, SUM(WIP.Whours) 
                      AS Labour
FROM         GLAcctPeriod CROSS JOIN
                      WIP
WHERE     (WIP.Wdate BETWEEN GLAcctPeriod.PeriodBegDate AND GLAcctPeriod.PeriodEndDate) AND (WIP.WCodeCat = 'Bill')
AND GLAcctPeriod.FiscalYear IN (SELECT * FROM  dbo.SplitIDs(@FiscalYears))
GROUP BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period, GLAcctPeriod.PeriodDescription, GLAcctPeriod.PeriodBegDate, GLAcctPeriod.PeriodEndDate
ORDER BY GLAcctPeriod.FiscalYear, GLAcctPeriod.Period

Open in new window

0
 
LVL 18

Expert Comment

by:vasto
ID: 38282394
0
 

Author Comment

by:HSI_guelph
ID: 38288296
Thanks for responding!  My main problem is that GLAcctPeriod only stores the last 2 years of periods
only data in GLAcctPeriodSo I think I need to remove the table reference and dynamically set the information to get previous fiscal years.

I don't have access to create stored procedures so I'm working with what I got in dataset queries.
0
 
LVL 18

Accepted Solution

by:
vasto earned 750 total points
ID: 38288344
You can create a dynamic list of dates as it is shown in this example:
http://www.r-tag.com/Pages/Sample_OvertimeCalculation.aspx

Check the function fnGetDatesInPeriod (in the upper right corner). You might need to tweak it a little bit ( as it is now it returns all days in a period and you need just the months)

You can use a command and add a CTE directly to the command if you don't want to use an external function
0
 

Author Closing Comment

by:HSI_guelph
ID: 38297445
Thank you for the replies!!  I will try the function but I might just make a table with dates in it to get around this in the meantime.  TYVM!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question