jump to URL and ReportParameters

Hi all...
Is there a way to set the format of a date parameter to day/month/year ?
I´m using jump to URL to navigate to another report by setting up the action
property of textbox to:
= Globals!ReportServerUrl & "?/Folder/ReportOne&InitialDate=" &
Parameters!InitialDate.Value
PS.: the language property of textbox is Portuguese (brazil)
... but in URL of "ReportOne" the date is converted to another format
(month/day/year)...
Could anyone help me?
thanks a lotPlease check the Readme file of Reporting Services 2000 SP1 or SP2:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
You need to specify the ParameterLanguage URL parameter.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ricardo" <ricardo@.fundep.br> wrote in message
news:eQ23gAWuFHA.252@.TK2MSFTNGP09.phx.gbl...
> Hi all...
> Is there a way to set the format of a date parameter to day/month/year ?
> I´m using jump to URL to navigate to another report by setting up the
> action property of textbox to:
> = Globals!ReportServerUrl & "?/Folder/ReportOne&InitialDate=" &
> Parameters!InitialDate.Value
> PS.: the language property of textbox is Portuguese (brazil)
> ... but in URL of "ReportOne" the date is converted to another format
> (month/day/year)...
> Could anyone help me?
> thanks a lot
>sql

Julian Dates

Is there a function or quick way to convert a calendar date to a julian
date. For example 1 September 2004 to 4245.
Rus BaileyIs Julian date days from a particular date? If so, you could just use
dateadd()/datediff().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rus Bailey" <Rus.Bailey@.dla.mil> wrote in message
news:OSn9uCFkEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>|||I used a Date Time Picker and a label and it worked for me here is the code I
used
lblJulian.Text = DateTimePicker1.Value.DayOfYear
Hope this helps
"Rus Bailey" wrote:
> Is there a function or quick way to convert a calendar date to a julian
> date. For example 1 September 2004 to 4245.
> Rus Bailey
>
>

Julian Dates

I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?-- If it where in a procedure
DECLARE @.strDate varchar(5)
DECLARE @.dtDate smalldatetime

SET @.strDate = '02364'

SET @.dtDate = DATEADD(dd, CAST(RIGHT(@.strDate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(@.strDate, 2), 3))

SELECT @.dtDate

-- Using within a query
CREATE TABLE #tmp_mydates (mydate varchar(5))

INSERT INTO #tmp_mydates VALUES ('02364')
INSERT INTO #tmp_mydates VALUES ('02264')
INSERT INTO #tmp_mydates VALUES ('02164')
INSERT INTO #tmp_mydates VALUES ('02004')
INSERT INTO #tmp_mydates VALUES ('02002')

SELECT mydate, DATEADD(dd, CAST(RIGHT(mydate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(mydate, 2), 3)) AS myconverteddate FROM #tmp_mydates|||I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?

Q1 [Is there a way to do this using Transact-Sql via Sql Server?]
A1 You may consider creating your own derived "JulianDate" user datatype; and also creating your own user functions or stored procedures to properly interpet, convert to and from, and generally handle them as you might require. (I've found the strategy helpful in handling similar customized Julian date formats for special applications) For example:
dbo.fn_MyJulianDate_Add,
dbo.fn_MyJulianDate_Diff,
dbo.fn_ConvertStandardDate_To_MyJulianDate, dbo.fn_ConvertMyJulianDate_To_StandardDate,
etc.

Julian Datehelp :(

I have a julian date(04159) and I need to convert this to any normal date format yyyymmdd or mmddyy or basically anything that I can work with! I am just about fed up with trying to convert this. It would be simple enough if there want leap years. Does anyone have a function or something out there that can convert this for me? Code that I found on the web turned out to be bogus. Your help will be greatly appreciated. If you have something in VB that would work also. SQLServer preferably.

ThanksIt may be off by a day or so, but it's a start:

select dateadd(day, cast(reverse(cast(reverse('04159') as char(3))) as int), '01/01/' + cast('04159' as char(2)))|||Yeah...Thats sorta where I left off in my efforts. Thanks for your input. I will eventually get it...Hopefully someone in here will see this and have something.|||This is ancient old code, from when SQL 2000 was still in beta, but I think it works. Give this a try:CREATE FUNCTION dbo.f_jDate(@.piJdate INT) RETURNS DATETIME AS
BEGIN
RETURN DateAdd(day, (@.piJdate % 1000) - 1, Convert(DATETIME
, CASE WHEN @.piJdate < 50000 THEN '20' ELSE '19' END
+ Replace(Str(@.piJdate / 1000, 2), ' ', '0') + '-01-01'))
END-PatP|||Thanks Pat....That worked perfectly! Your my hero for the day!

Julian date time Conversion

Can anyone tell me how to convert julian date time to DateTime and Vice Versa?
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||

Try the links below for UDF Julian to DateTime conversion code. Hope this helps.
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-3-udf_DT_FromJulian.htm

http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-2-udf_DT_ToJulian.htm

JULIAN DATE

No Doubt Julian Date conversion is a PAIN in RS. After reading many postings
I came up with the following on the fly conversion for a julian date in our
AS/400 system:
In order to convert the following date 107306 to the regular date: 11/3/2007
I used the following:
First of, the first 2 digits denote the Century: 00 stands for 1900 and 10
stands for 2000
So in the case of 107306 the first 3 digits stand for 2007 and the last 3
stand for number of days.
Here's the formula:
=DATEADD("d", Val(Right(Fields!TransactionDate.Value,3)), "#01/01/200" & Cstr
(Mid(Fields!TransactionDate.Value,3,1)) & "#")
I just wish that when Microsoft marketed RS they had their act together as
far as usability is cocerned, you almost have to be a rocket scientist to
make the reports half way decent! and DON'T get me started on the
ridiculously limited charting functionality!!! Being a recovering Crystal-
holic it has been an absolute BEAR to mimic functionality and looks...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200712/1On Dec 12, 12:14 pm, "patrickpk via SQLMonster.com" <u38565@.uwe>
wrote:
> No Doubt Julian Date conversion is a PAIN in RS. After reading many postings
> I came up with the following on the fly conversion for a julian date in our
> AS/400 system:
> In order to convert the following date 107306 to the regular date: 11/3/2007
> I used the following:
> First of, the first 2 digits denote the Century: 00 stands for 1900 and 10
> stands for 2000
> So in the case of 107306 the first 3 digits stand for 2007 and the last 3
> stand for number of days.
> Here's the formula:
> =DATEADD("d", Val(Right(Fields!TransactionDate.Value,3)), "#01/01/200" & Cstr
> (Mid(Fields!TransactionDate.Value,3,1)) & "#")
> I just wish that when Microsoft marketed RS they had their act together as
> far as usability is cocerned, you almost have to be a rocket scientist to
> make the reports half way decent! and DON'T get me started on the
> ridiculously limited charting functionality!!! Being a recovering Crystal-
> holic it has been an absolute BEAR to mimic functionality and looks...
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200712/1
I don't know what your date format is, but it isn't a Julian Date --
it's some kind of weird mainframe hybrid of Century, Year and
DayOfYear. A Julian Date is a count of days since Jan 1st, 4713 BC.
(See http://aa.usno.navy.mil/data/docs/JulianDate.php). The Julian
date for 11/3/2007 is 2,454,408.
For you, the following .Net formula works to convert to 11/3/2007, and
it gets your 1900-2000 span right too (replace the number with your
Fields...Value):
= #1/1/1900#.AddYears( Floor( 107306 / 1000) ).AddDays( 107306 Mod
1000 )
If you needed to go backwards for some reason, this works (replace the
date with your Fields...Value) :
= 1000 * ( CDate(#11/3/2007#).Year - 1900 ) +
CDate(#11/3/2007#).DayOfYear - 1
-- Scottsql

Julian Date

A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?You could take today's date and subtract it from the first day of the year (DATEDIFF ( datepart , startdate , enddate ) ) to give you an int value for the day of the year (actually day of year -1 since 20040101 - 20040101 = 0). Then convert to a 3 character value and concatinate the two digit year to the front and ... tadaaa ... the julian date.|||? I am not following you?|||You can get the Julian date using:SELECT 1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate())If you need it in character format, you can use:SELECT Replace(Str(
1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate()), 5), ' ', '0')After you've got that, the rest should just be a simple compare.

-PatP|||Or you could go the other way so you can utilize all of SQL Server's functions

DECLARE @.julian char(5), @.gregorian datetime
SELECT @.julian = '04194'
SELECT @.gregorian = DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3)),CON VERT(datetime,'20'+SUBSTRING(@.julian,1,2)+'/01/01'))
SELECT DATEDIFF(dd,GetDate(),@.gregorian)|||Perfect!...Thanks for your help. I was going about it the wrong way. I was trying to convert the julian date and then compare.|||Thanks also Brett That was a road I was eventually going to have to cross.|||udf's...Makes a perfect house warming gift

CREATE FUNCTION udf_JulianToGregorian(@.julian char(5))
RETURNS datetime
AS
BEGIN
DECLARE @.gregorian datetime
SELECT @.gregorian =
DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3))
,CONVERT(datetime
,CASE WHEN SUBSTRING(@.julian,1,2) BETWEEN '00' AND '50'
THEN '20'
ELSE '19'
END
+SUBSTRING(@.julian,1,2)+'/01/01'))
RETURN @.gregorian
END
GO

DECLARE @.julian char(5)
SELECT @.julian = '04194'
SELECT dbo.udf_JulianToGregorian(@.julian)
GO

Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Chris,

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent!

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Joining tables

I have two tables one table T1 only have one result per date table 2 T2 can
range from 0 to 8 results per date,
I want to pull back all of the results in T2 and sum the totals, in my
example it will be summing "Stafftime" and "items", in T1 I need to sum the
"Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I am
getting 61200, as there ate 2 rows in T1.
How can I sum my four colums but only get results for one Date in the T1
table'
Cheers
Mark
Select *
From T1
T1
Row_Date Contracted_Hours OverTime Ext_No
20050209 30600 0 4227
Select *
From T2
T2
RowDate Area Ext_No StaffTime Items
20050209 1 4227 24656 50
20050209 2 4227 6164 10
Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
Sum(OverTime) as OT, Sum(Items) as Completed_Work
From T1
Inner Join T2
On T1.Ext_No = T2.Ext_No
Where RowDate = '20050209' and Ext = 4227Try,
Select
T1.RowDate,
T1.Ext_No,
sum(distinct T1.Contracted_Hours) as Con_Hrs,
Sum(distinct T1.OverTime) as OT,
(select Sum(T2.Items) from T2 where T2.RowDate = T1.RowDate and T1.Ext_No =
T2.Ext_No) as Completed_Work
From
T1
Where
T1.RowDate = '20050209' and T1.Ext_No = 4227
go
AMB
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>|||I think you have to use LEFT OUTER JOIN.
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>|||SELECT
T1.RowDate,
T1.Ext_No,
T1.Contracted_Hours,
T1.OverTime,
T2.StaffTime,
T2.Items
FROM
T1
INNER JOIN
(
SELECT
T2.RowDate,
T2.Ext_No,
SUM(StaffTime),
SUM(Items)
FROM
T2
GROUP BY
T2.RowDate,
T2.Ext_No
) T2group
ON T2group.Ext_No = T1.Ext_No
AND T2group.RowDate = T1.Row_Date
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>

Joining tables

Hi
I would like to join two tables, one containig the names of loaded datafiles
and the date of the loaded datafile i a field called RealDate. The other
tabel is a table containg a list of all the "missing" datafiles which for
some reason wasn't loaded into the database.
The result I would like is a resultset where I get all the loaded
datafilenames as well as all the missing datafilesnames, the later must have
a flag set so I can tell them apart, the field siteid can be used for that
where e.g. -1 or -3 flags indicates not loaded and 1 or 3 flags loaded.
V2Statistik definition
CREATE TABLE [dbo].[Statistik] (
[SiteID] [int] NOT NULL ,
[TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[Realdate] [datetime] NULL ,
[Loaddate] [datetime] NULL ,
[TotalRecords] [int] NOT NULL ,
[SkippedRecords] [int] NOT NULL
) ON [PRIMARY]
a result set could look like this
1 38566.txt 2005-08-02 2005-08-25 16:14:36.863 82462 31435
-1 38572.txt 2005-08-08 0 0 0
-1 38589.txt 2005-08-25 0 0 0
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
-3 38592.txt 2005-08-28 0 0 0
What I have managed so far is this (the join is not correct)
DECLARE @.DateTable TABLE (RealDate DATETIME)
DECLARE @.RealDate DATETIME
SET @.RealDate = '01/01/2005'
WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
DATEADD(DAY, 1, @.RealDate) END
select * from V2Statistik where siteid=1
join '?
SELECT RealDate FROM @.DateTable dt
WHERE NOT EXISTS (SELECT * FROM V2Statistik stk WHERE stk.RealDate =
dt.RealDate)
I not sure how to join these to different tables.
Can you help?
HenryHenry,
Either you failed to include the table structure for the "other table" that
lists the missing datafiles, or I don't understand where that data is. And
I'm not sure whether [Statistik] is the first table you mention, since you
don't explain LoadDate.
If you can post more information, it would help - specifically, it would
help to post the source data that would give the result you want. You
posted the result you want, which is good, but it's impossible to suggest
a query that will give it to you without knowing how your source data
is stored.
Probably, you need something like a UNION ALL query
select ..., Realdate, Loaddate, ...
from <your first table, which contains information on loaded files>
union all
select ..., Realdate, NULL, ...
from <your second table, which contains information on not-loaded files>
The two parts of the union must have the same column structure, so you
will want to put NULL in the select list for Loaddate in the second query.
You can add a column to indicate whether the file was loaded or not, but
I don't think that's necessary, since you can tell if a file was loaded or
not by looking at whether Loaddate is NULL or not.
I don't see any need for a calendar table here, but more information may
make your requirements clearer.
Steve Kass
Drew University
Henry wrote:

>Hi
>I would like to join two tables, one containig the names of loaded datafile
s
>and the date of the loaded datafile i a field called RealDate. The other
>tabel is a table containg a list of all the "missing" datafiles which for
>some reason wasn't loaded into the database.
>The result I would like is a resultset where I get all the loaded
>datafilenames as well as all the missing datafilesnames, the later must hav
e
>a flag set so I can tell them apart, the field siteid can be used for that
>where e.g. -1 or -3 flags indicates not loaded and 1 or 3 flags loaded.
>V2Statistik definition
>CREATE TABLE [dbo].[Statistik] (
> [SiteID] [int] NOT NULL ,
> [TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
> [Realdate] [datetime] NULL ,
> [Loaddate] [datetime] NULL ,
> [TotalRecords] [int] NOT NULL ,
> [SkippedRecords] [int] NOT NULL
> ) ON [PRIMARY]
>
>a result set could look like this
>1 38566.txt 2005-08-02 2005-08-25 16:14:36.863 82462 31435
>-1 38572.txt 2005-08-08 0 0 0
>-1 38589.txt 2005-08-25 0 0 0
>3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
>-3 38592.txt 2005-08-28 0 0 0
>What I have managed so far is this (the join is not correct)
>DECLARE @.DateTable TABLE (RealDate DATETIME)
>DECLARE @.RealDate DATETIME
>SET @.RealDate = '01/01/2005'
>WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
>BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
>DATEADD(DAY, 1, @.RealDate) END
>
>select * from V2Statistik where siteid=1
>join '?
>SELECT RealDate FROM @.DateTable dt
>WHERE NOT EXISTS (SELECT * FROM V2Statistik stk WHERE stk.RealDate =
>dt.RealDate)
>
>I not sure how to join these to different tables.
>Can you help?
>Henry
>
>
>|||I think we are missing a table with the "missing stuff" in it.
Do you know the names of the files to be loaded into the database in
advance? I would assume so, if you have set up proper system for a
data warehouse load.|||Hi
Sorry that I'm unclear about this.
I have one table which is a log of all the tables which has been
successfully loaded into the database.
SiteID, TicketFileName and RealDate are the important fields at present.
It looks like this
CREATE TABLE [dbo].[V2Statistik] (
[SiteID] [int] NOT NULL ,
[TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[Realdate] [datetime] NULL ,
[Loaddate] [datetime] NULL ,
[TotalRecords] [int] NOT NULL ,
[SkippedRecords] [int] NOT NULL
) ON [PRIMARY]
I have another table where I simpely shows the missing files from a given
date, that is files which for some reasons could not be read or loaded into
the database.
That table is a date table purpolated with all dates from the given date.
DECLARE @.DateTable TABLE (RealDate DATETIME)
I then select all dates where realdate does not exist in the V2statistik
(previously called statistik by mistake)
The result set from a select * from V2statistik would look like this
1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
3 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
The result set from DateTable would look like this
2005-01-01
2005-01-02
2005-01-03
...
2005-10-23
I would like a result set of the joined or unioned tables that looks like
this
1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
1 38589.txt 2005-08-25 0 0 0
1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
3 38566.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
3 38589.txt 2005-08-25 0 0 0
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
So I would like to select * from V2Statistik and "merge" it with dates from
the DateTable where the date doesnt exist in V2statistik, the merged
(missing dates) row should have the
Loaddate set to 0 (zero, null, nil) I have changed strategy on the I think
it's easier.
But I see a problem since the realdate will occur as many times as there are
sites (siteid) siteid + realdate is the unique primary key.
Does this clarify it?
regards
Henry|||On Mon, 24 Oct 2005 14:16:58 +0200, henry wrote:
(snip)
>I would like a result set of the joined or unioned tables that looks like
>this
>1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
>1 38589.txt 2005-08-25 0 0 0
>1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
>3 38566.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
>3 38589.txt 2005-08-25 0 0 0
>3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
(snip)
Hi Henry,
I think that the query below is quite close (*) to what you want. It's
too bad that you didn't post any INSERT statements to give me working
data, so I couldn;t test it - but check if it suits your need.
(*) I deliberately left the filename column NULL for the missing files,
for two reasons:
a. logical (if the file is missing, you obviously can't tell the name)
b. attempting to find the numeric part of another file name, increasing
that with the result of a DATEDIFF function and adding back the .txt
part, though possible, would be extremely messy, and it would make the
query very vulnerable for malformed filenames.
SELECT s.SiteID, v.TicketFileName, d.RealDate,
COALESCE(v.LoadDate, 0) AS LoadDate,
COALESCE(v.TotalRecords, 0) AS TotalRecords,
COALESCE(v.SkippedRecords, 0) AS SkippedRecords
FROM (SELECT DISTINCT SiteID
FROM V2Statistik) AS s
CROSS JOIN @.DateTable AS d
LEFT JOIN V2Statistik AS v
ON v.SiteID = s.SiteID
AND v.RealDate = d.RealDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo
Thanks for your help ;o)
However, when I use that script the I don't get any output at all, only
"The command(s) completed successfully."
Which of cause is a positiv thing, not syntax error.
I can send the data as a seperate post with CSV data (don't realy know how
to make the insert data) the table structure is already posted above, do you
think you can use that?
This the complete script so far.
DECLARE @.DateTable TABLE (RealDate DATETIME)
DECLARE @.RealDate DATETIME
SET @.RealDate = '01/01/2005'
WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
DATEADD(DAY, 1, @.RealDate) END
SELECT s.SiteID, v.TicketFileName, d.RealDate,
COALESCE(v.LoadDate, 0) AS LoadDate,
COALESCE(v.TotalRecords, 0) AS TotalRecords,
COALESCE(v.SkippedRecords, 0) AS SkippedRecords
FROM (SELECT DISTINCT SiteID
FROM V2Statistik) AS s
CROSS JOIN @.DateTable AS d
LEFT JOIN V2Statistik AS v
ON v.SiteID = s.SiteID
AND v.RealDate = d.RealDate
Cheers
Henry|||On Wed, 26 Oct 2005 17:10:19 +0200, Henry wrote:

>Hi Hugo
>Thanks for your help ;o)
>However, when I use that script the I don't get any output at all, only
>"The command(s) completed successfully."
>Which of cause is a positiv thing, not syntax error.
Hi Henry,
Whether syntax error or incorrect results - a bug is a bug, and in dire
need of squashing. (In fact, syntax errors are often EASIER to locate
and correct).

>I can send the data as a seperate post with CSV data (don't realy know how
>to make the insert data) the table structure is already posted above, do yo
u
>think you can use that?
I possibly could, if I could afford to spend a few hours playing around
with bcp or trying to import through Excel to SQL Server - but there is
only so much time I can spend in these groups, and I like to help as
many people as I can in that limited time.
Below is a link to a script that will generate INSERT statements from
the data currently in your table. See if that helps you (and if not,
then you can always manually type the INSERT statements for five or ten
rows of sample data).
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Joining fields

Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)Try:
declare
@.year numeric
, @.month numeric
, @.day numeric
select
@.year = 2006
, @.month = 1
, @.day = 2
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jean-Paul Rijnsburger" <Jeepee75@.gmail.com> wrote in message
news:1136207889.492149.133070@.o13g2000cwo.googlegroups.com...
Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)|||It works,
Thanks Tom|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Try:
> declare
> @.year numeric
> , @.month numeric
> , @.day numeric
> select
> @.year = 2006
> , @.month = 1
> , @.day = 2
> select
> convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
This may produce different result depending on language and dateformat
settings. Add a third parameter to control the interpreration:
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day),
111)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Joining date and time

Hi all,
I need some help with joining two fields of type datetime, one with date
relevancy and the other with time.
If i join the integer part of date field with the fraction part of time
field, the joined datetime is not the same.
What's the trick here?
TIA, JozzaOne way... taking date from @.a, time from @.b
declare @.a datetime, @.b datetime
set @.a = getdate()-1
set @.b = dateadd(hh,5,getdate())
select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
convert(varchar(10),@.a,101))
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Can you show us an example? What do you mean by "fraction part of time?"
Keith Kratochvil
"Jozza" <hmm@.hmm.com> wrote in message
news:lrcjg.3576$oj5.1220262@.news.siol.net...
> Hi all,
> I need some help with joining two fields of type datetime, one with date
> relevancy and the other with time.
> If i join the integer part of date field with the fraction part of time
> field, the joined datetime is not the same.
> What's the trick here?
> TIA, Jozza
>|||I thought that datetime is stored the way that integer part of a float
represents the date and the fraction part represents the time.
So adding them together would join them. But it doesn't seem to be the case
on SLQ server.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OvXKJ0hjGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Can you show us an example? What do you mean by "fraction part of time?"
> --
> Keith Kratochvil
>
> "Jozza" <hmm@.hmm.com> wrote in message
> news:lrcjg.3576$oj5.1220262@.news.siol.net...
>|||Converting fields to varchar, concatenate strings and convert it back to
datetime does the trick. (which was not exactly what your exemple was, but i
got the idea)
Is there any other way where i could add fields together in mathematical
terms, because i suspect there could be and error in string conversions when
different locale formats are used. Or am i wrong?
Thanks, Jozza
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:22B16AB3-DF86-4878-B181-727F79448589@.microsoft.com...
> One way... taking date from @.a, time from @.b
> declare @.a datetime, @.b datetime
> set @.a = getdate()-1
> set @.b = dateadd(hh,5,getdate())
> select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
> convert(varchar(10),@.a,101))
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Well concatenating the strings might lead to wrong date format if the string
format changes. Thats why I didn't go for the concatenation.
And the example I gave was in mathematical terms :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||After looking at the example a little bit longer i realize that you are
absolutely correct.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:33203119-C451-4C10-9733-F8D9EA1B0229@.microsoft.com...
> Well concatenating the strings might lead to wrong date format if the
> string
> format changes. Thats why I didn't go for the concatenation.
> And the example I gave was in mathematical terms :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>

Join using max value

I want to join two tables together but only join the where the column
in table 2 (of type date) is the maximum value. Is this possible?

Note table 1 is the main table it is also joined to other tables too.Hello,

Would something like this work?

select
*
from
table1 t1
join table2 t2 on t1.joinkey=t2.joinkey
where
t2.date = (select max(datefield) as max_date from table2)

Greg.

ree32 wrote:
> I want to join two tables together but only join the where the column
> in table 2 (of type date) is the maximum value. Is this possible?
> Note table 1 is the main table it is also joined to other tables too.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. It is a bitch to debug code that you cannot see.|||Actually, if you read his request, he is only asking if its possible.

So a simple YES, (sans any explanation of technique), would appear to
suffice.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112319257.363540.54270@.f14g2000cwb.googlegro ups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications. It is a bitch to debug code that you cannot see.|||Thanks for the idea, I will test it out later.

"Greg" <gregdenison@.gmail.com> wrote in message news:<1112314603.380779.78130@.g14g2000cwa.googlegroups.c om>...
> Hello,
> Would something like this work?
> select
> *
> from
> table1 t1
> join table2 t2 on t1.joinkey=t2.joinkey
> where
> t2.date = (select max(datefield) as max_date from table2)

Join to a single date rate table?

Given an exchange rate table like:
Create Table XRates
(
EffectiveDate smalldatetime,
FromCury char(3),
ToCury char(3),
Rate float
)
and Transaction table like
Create Table Trans
(
TranDate smalldatetime,
TranCury char(3),
TranAmt float
)
What would be the best SQL query to join from the Transaction table to the
correct exchange rate based on the transaction date? Any sugestions?
Thanks in advanceHere is one way (SQL Server 2005):
WITH Transactions
AS
(SELECT T.TranDate, T.TranCury, T.TranAmt,
R.ToCury, R.Rate,
ROW_NUMBER() OVER(
PARTITION BY R.FromCury
ORDER BY R.EffectiveDate DESC) AS seq
FROM Trans AS T
JOIN XRates AS R
ON T.TranCury = R.FromCury
AND T.TranDate >= R.EffectiveDate)
SELECT TranDate, TranCury, TranAmt, ToCury, Rate
FROM Transactions
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Join Tables that don't have exact matches

I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
The second table can have the same Sales Tax Code with a different Tax Rate
and Effective Date. I want to select only one Tax Rate record from the
second table and that is the one that has the greatest effective date that is
also less than the invoice date.
I have tried the Top 1 method in a join and where statement, but it is not
actually limiting the records from the second table.
can you post some sample records and expected result set out of it.
Vishal Parkar
vgparkar@.yahoo.co.in
"Queryless in SLC" wrote:

> I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
> and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
> The second table can have the same Sales Tax Code with a different Tax Rate
> and Effective Date. I want to select only one Tax Rate record from the
> second table and that is the one that has the greatest effective date that is
> also less than the invoice date.
> I have tried the Top 1 method in a join and where statement, but it is not
> actually limiting the records from the second table.

Join on agragate function between tables

A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:

Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)

A typical result:

DOW NOR
3 8934
6 22397
7 23328
1 23401
4 1938
2 24399
5 1112

Trying to join two or more tables in all sorts of variants of this:

Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)

Here I get this as a typical result:

DOW1 Amount 1 DOW2 Amount 2
6 332953802 6 332953802
3 42248886 3 42248886
1 330281714 1 330281714
7 335759904 7 335759904
4 1232568 4 1232568
5 210168 5 210168
2 366985359 2 366985359

Where the numbers are way off.

Any suggestions?

When you Join the tables, based only on the day of the week, your resultset will have each record in Table1 for a day of the week combined with each record in Table2 for the same day of the week. That is why your numbers are so far off.

Here is a simple example that shows what's happening.

Drop Table Table1

Create Table Table1(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

Drop Table Table2

Create Table Table2(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

insert Table1 values( '1/1/2006', 'Fred' )

insert Table1 values( '1/2/2006', 'Barney' )

insert Table2 values( '1/1/2006', 'Wilma' )

insert Table2 values( '1/1/2006', 'Pebbles' )

insert Table2 values( '1/2/2006', 'Betty' )

insert Table2 values( '1/2/2006', 'BamBam' )

Select DatePart(dw, dato ), Count(*)

From Table1

Group

By DatePart(dw, dato )

Select DatePart(dw, dato ), Count(*)

From Table2

Group

By DatePart(dw, dato )

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

t1.valCol,

t2.valCol

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

valCol valCol

-- -- - -

1 1 Fred Wilma

1 1 Fred Pebbles

2 2 Barney Betty

2 2 Barney BamBam

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

Count(t1.dato),

Count(t2.dato)

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

Group

By DatePart(dw, t1.dato ),

DatePart(dw, t2.dato)

-- -- -- --
1 1 2 2
2 2 2 2
Select Coalesce( t1.dow, t2.dow ),

t1.howmany,

t2.howmany

From (

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table1

Group by DatePart(dw, dato )

) t1

Full Outer Join

(

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table2

Group by DatePart(dw, dato )

) t2

On t1.dow = t2.dow

howmany howmany
-- -- --
1 1 2
2 1 2

Select dayofweek,

(Select Count(*) from Table1 where DatePart(dw, dato ) = dayofweek ),

(Select Count(*) from Table2 where DatePart(dw, dato ) = dayofweek )

From (

Select 1 as dayofweek

Union All

Select 2 as dayofweek

Union All

Select 3 as dayofweek

Union All

Select 4 as dayofweek

Union All

Select 5 as dayofweek

Union All

Select 6 as dayofweek

Union All

Select 7 as dayofweek

)t1

dayofweek
-- -- --
1 1 2
2 1 2
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0

There are several ways to get the days of the week. If you know that one of the tables will have records for every day, you can use that. There is a system table that has lists of numbers, that is another good source.

|||

Excellent stuff. I allready guessed the reason for my results but no idea how to stop it.