November 7, 2008 at 6:33 am
Hello I need help with a query. What I'm am trying to do is get the correct "BD" for any year selected.
The query below works below if I hard code the year but only of that year.
This is not efficient because then I think i would have to make separate tables for all years and create different reports for all the different years. Also, Report data is monthly so
Could any one help me with the query so When I use a year parameter for a year it will calculate the correct BD for that report year and Importer.
I have tried use where (reportdate) >=2000 but that gave me incorrect results for BD by the year and importer.
-----------------------------------------------------------------------
DECLARE @days float; DECLARE @minDate datetime; DECLARE @maxDate datetime;
SELECT @maxDate = max(reportdate)
FROM imports
WHERE year(reportdate)= '2007'
SElect @minDate = '01/01/2007';
SET @days = datediff(day, @minDate, @maxDate) + 1;
SELECT *, (qty / @days) AS bd
into
FROM IMPORTS
WHERE year(reportdate)= '2007';
-----------------------------------------------------------------------------
I hope this is clear. If I have left any important data out please let me know.
Thanks,
Ravi
November 7, 2008 at 6:42 am
Can you post some sample data and your table structure?
November 7, 2008 at 6:43 am
what are the possible years?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 7, 2008 at 6:47 am
Christopher Stobbs (11/7/2008)
what are the possible years?
The possible years are for right now 2000 to 2008.
but there is only data for 2008 up to Junt.. The data is updated everymonth.
November 7, 2008 at 6:56 am
Ok without a table structure or sample data and a sample of what you want to output this is hard.
but this is what I have so far:
[font="Courier New"]
DECLARE @imports TABLE
(
qty DECIMAL(10,2),
reportdate DATETIME
)
INSERT INTO @imports
SELECT 5,'2007-01-1' UNION ALL
SELECT 2,'2007-01-2' UNION ALL
SELECT 3,'2007-01-2' UNION ALL
SELECT 6,'2007-01-10' UNION ALL
SELECT 1,'2008-01-10' UNION ALL
SELECT 1,'2008-02-10' UNION ALL
SELECT 1,'2008-03-10' UNION ALL
SELECT 1,'2008-04-10'
SELECT *
FROM @imports
SELECT reportdate, qty/Days AS [BD]
FROM @imports i
INNER JOIN (
SELECT
YEAR(reportdate) AS [Year],
(DATEDIFF(dd,CONVERT(DATETIME,CAST(YEAR(MAX(reportdate)) AS VARCHAR) + '-01-01'),MAX(reportdate)) + 1) AS Days
FROM @imports
GROUP BY YEAR(reportdate)
) n
ON n.[Year] = YEAR(reportdate)[/font]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 7, 2008 at 7:16 am
steveb (11/7/2008)
Can you post some sample data and your table structure?
I hope I am answering your question properly...
----------------------------------------when I select from this table the BD (perday) data for 2007 is correct.-------
Table (Imports2007)
Columns
QYT (Int,Null)
CurrentName (nvarchar(30),null)
City (nvarchar(20),null)
State (nvarchar(2),null)
ReportDate (datetime,null)
Country (nvarchar(4),null)
COUNTRY-NAME (nvarchar(50),null)
Region (smallint,null)
bd (float,null)
------------------------------------------------------------------------------------
I would like to use the Imports table since it contains all years from 2000 to 2008. but the BD calculation comes incorrect.
for example
Importer 1 data for 2007 by a certain country and region is 90.53 using the imports2007 table in the From statement.
Importer 1 data for 2007 by a certain country and region is 1.55 using the importsallyears table in the From statement.
November 7, 2008 at 7:21 am
ok based on your first post I wrote up a query.
Does that query work?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 7, 2008 at 7:36 am
Christopher Stobbs (11/7/2008)
ok based on your first post I wrote up a query.Does that query work?
I think its getting there. I should have clarified this earlier.
the data is entered into the system at the end of everymonth. So for QTY i have i have 12 entries for every importer. i.e. 1/31/200x,2/28,200x,3/3/200x and so on.
To get the BD from QTY i had to calculate it by / by 365 or min max + 1 i think...
Inthe report I have it set up by
COUNTRY
NAME|CITY|STATE|REGION|BD|
I hope that help and not cause any more confusion....
November 7, 2008 at 7:56 am
HI there,
OK this is still not clear to me.
Please could you read this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Then re-post your data samples for input and output
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply