• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

ms sql Retrieving Results and Passing Parameters

I would like to query the table FiscalMonth based on an todays date or a date entered, get the then get the previous month and the previous month previous year and use those values in a query to return rows for a Detail table.  I have been able to accomplish this but it is not pretty and there are some problems such as if the current date is in January of the current year and going to December of the previous year.

I think I am making this harder than it is but I am drswing a blank.

Thanks in advance for the help.
John
0
j_heck
Asked:
j_heck
  • 6
  • 5
1 Solution
 
stalhwCommented:
declare @today datetime
SELECT @today=isnull(@today,GETDATE())

--current month
SELECT * FROM FiscalMonth WHERE month=DATEADD(mm,DATEDIFF(mm,0,@today),0)
--previous month
SELECT * FROM FiscalMonth WHERE month=DATEADD(mm,DATEDIFF(mm,0,@today)-1,0)
--previous year
SELECT * FROM FiscalMonth WHERE month=DATEADD(mm,DATEDIFF(mm,0,@today)-12,0)

Not sure if that's what you need, but I usually use DATEADD & DATEDIFF to get data from previous month, or year...
0
 
stalhwCommented:
Also I'm not sure if your table FiscalMonth as a Month field of just dates?
If it's a day by day table then it would be more like:

SELECT * FROM FiscalMonth WHERE theday BETWEEN DATEADD(mm,DATEDIFF(mm,0,@today),0) AND DATEADD(mm,DATEDIFF(mm,0,@today)+1,0)

etc for the others
0
 
j_heckAuthor Commented:
Stalhw,

The FiscalMonth table is just the month and year with a begin and end date for the date range that fiscal month and year cover.  I do have a calendar table that has the individual dates with the fiscal month and year in them..  Our fiscal month runs from a Sunday to Saturday and it is set up as 4,4,5 weeks for Jan, Feb, Mar and then repeats the same way for the rest of the year.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
stalhwCommented:
Could you show a sample of your FiscalMonth table?
Because I'm not sure I understand correctly your setup.
0
 
j_heckAuthor Commented:
Here is an example of the FiscalMonth table:

FYNbr      FMNbr      BgnDate                       EndDate
2011      10      2011-10-02      2011-10-29
2011      11      2011-10-30      2011-11-26
2011      12      2011-11-27      2011-12-31
2012      1      2012-01-01      2012-01-28
2012      2      2012-01-29      2012-02-25
2012      3      2012-02-26      2012-03-31
2012      4      2012-04-01      2012-04-28
2012      5      2012-04-29      2012-05-26
2012      6      2012-05-27      2012-06-30
2012      7      2012-07-01      2012-07-28
2012      8      2012-07-29      2012-08-25
2012      9      2012-08-26      2012-09-29
2012      10      2012-09-30      2012-10-27
2012      11      2012-10-28      2012-11-24
2012      12      2012-11-25      2012-12-29
2013      1      2012-12-30      2013-01-26
2013      2      2013-01-27      2013-02-23
2013      3      2013-02-24      2013-03-30

I understand your previous select statements for getting the date ranges but how is the select done so that the begin date and end date ranges go into parameters to be used in that current query and then passing them to other queries?
0
 
stalhwCommented:
As long as you have 12 Fiscal Month per year, the following should work
declare @today datetime

-- set @today yourself, or use current day
SELECT @today=isnull(@today,GETDATE())

-- clean @today to remove time part of it, so that BETWEEN includes EndDate
SELECT @today=DATEADD(dd,DATEDIFF(dd,0,@today),0)

--current month
SELECT DATEFROMPARTS ( FYNbr, FMNbr, 1 ) FROM FiscalMonth 
WHERE @today BETWEEN BgnDate AND EndDate

--previous month
SELECT DATEADD(mm,-1,DATEFROMPARTS ( FYNbr, FMNbr, 1 )) FROM FiscalMonth 
WHERE @today BETWEEN BgnDate AND EndDate

--previous year
SELECT DATEADD(mm,-12,DATEFROMPARTS ( FYNbr, FMNbr, 1 )) FROM FiscalMonth 
WHERE @today BETWEEN BgnDate AND EndDate

Open in new window


But I don't really understand your last comment, passing it to other queries?
Passing what?

Now that we got the selecting the correct Fiscal Month part done, can you explain the big picture? What are you trying to accomplish in the end?
0
 
j_heckAuthor Commented:
Yes I do have 12 Fiscal Months per year.  I need to process records so that I can get sales, transaction counts and item counts for a the fiscal month that just ended, the fiscal quarter the month is in and a year to date figures.  Then compare those to the same time frams for the jprevious Fiscal Year.  So when I calculate the fiscal information for this year I need to get the previous year's fiscal information so that it can all be processed via scripts and set up to run via a SQL Job with out any user intervention.  It will run two days after the end of the fiscal month and send the results to users in a spreadsheet format.

Does that make more sense?
0
 
stalhwCommented:
ok, so you already ahve your sql scripts to get the data you need.
And now you have the way to figure out the fiscal months you need,
so you should be able to do what you need now?
no?

Or do you need a query to select all the fiscal months?
How exactly do you join your fiscal month table to your sales table?
0
 
j_heckAuthor Commented:
I think I am all set with this part now.  Thanks for the help on this.

One question I have and it is more of a procedural or processing question than anything else.  This holds total sales by store by day of week, it also has it in it columns that hold Regular Sales, Sale Sales, and Promotional Sales.  Each row is tagged with a R,S, or P depending upon the type of sale it is.  Now to get these 3 sale types broken out is it better to do it at the same time I am getting all of the sales by doing something like:

RegularSales = CASE WHEN SaleType = 'R' then Sum(Sales) END
RegularSalesCount = CASE WHEN SaleType = 'R' then Count(Sales) END
SaleSales = CASE WHEN SaleType = 'S' then Sum(Sales) END
SaleSalesCount = CASE WHEN SaleType = 'S' then Count(Sales) END
PromotionalSales = CASE WHEN SaleType = 'P' then Sum(Sales) END
PromotionalSalesCount = CASE WHEN SaleType = 'P' then Count(Sales) END

in the same script as the original Total sales counts or is it better to do an update afterwards wtih this?  Is there a possibility that I would run into trouble doing it the way I listed it above?
0
 
stalhwCommented:
I'm not sure bout the syntax you gave...
CASE then SUM/COUNT, i'm not sure that will work as expected, but try it, you'll see.

I usually do it like this:
SELECT 
SUM( CASE WHEN SaleType = 'R' then Sales END ) as RegularSales,
SUM( CASE WHEN SaleType = 'R' then 1 END ) as RegularSalesCount,
SUM( CASE WHEN SaleType = 'S' then Sales END ) as SaleSales,
SUM( CASE WHEN SaleType = 'S' then 1 END ) as SaleSalesCount,
SUM( CASE WHEN SaleType = 'P' then Sales END ) as PromotionalSales,
SUM( CASE WHEN SaleType = 'P' then 1 END ) as PromotionalSalesCount

Open in new window

0
 
j_heckAuthor Commented:
Thanks for the help stalhw.  That works great and there are many other ways I can use this kind of scripting.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now