convert week number to date in report designer

I have a report where I'm grouping based on week number, and I need to
translate the week number to the starting Monday of that week. This is the
format expression that I'm using
=Month(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
1, " & Year(Now()))))& "/" &
Day(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January 1,
" & Year(Now()))))
It works most of the time; however, it gives me some weird results when a
month or day ends with a zero. For example, October 8 is coming back 141/8
Any suggestions on the format expression I should use to convert the week
number to a date value?I solved my problem. I was trying to go about it the hard way.
Originally I had my query set up to return week(new_date) for the week
number. I added to my query to also select new_date for the date value.
Then in report designer, I kept my group for the columns of my matrix as
week number, but for the expression for the group header, I chose to display
the date value.
"Joel Lindstrom" <joel.lindstrom@.CEI.COM> wrote in message
news:A41C4802-4422-4956-A4E1-761E5B1A395E@.microsoft.com...
>I have a report where I'm grouping based on week number, and I need to
>translate the week number to the starting Monday of that week. This is the
>format expression that I'm using
> =Month(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
> 1, " & Year(Now()))))& "/" &
> Day(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
> 1, " & Year(Now()))))
> It works most of the time; however, it gives me some weird results when a
> month or day ends with a zero. For example, October 8 is coming back
> 141/8
> Any suggestions on the format expression I should use to convert the week
> number to a date value?

Convert row with text field to column

Hi experts;

How can I generate the result using typical SQL statement based on the following tables?

Table a - Salesman (salesId, Name) pk : salesId

Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a

The result set :

salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator

For example:

Table a

SalesId Name

S001 Peter

S002 Alice

Table b

InvoiceNo SalesId InvoiceAmt

INV001 S001 $100

INV002 S001 $100

INV003 S001 $400

INV004 S002 $200

Result set

SalesId Name Sum(InvoiceAmt) InvoiceNos

S001 Peter $600 INV001, INV002, INV003

S002 Alice $200 INV004

Thanks

Hi,

You can use the COALESCE function in order to get columns of more than one row as a string expression.

You can find samples at below links.

http://www.kodyaz.com/articles/article.aspx?articleid=29
http://www.kodyaz.com/forums/thread/76.aspx

It is better to create a user defined function which uses the Coalesce and then call this function in your select queries to get related list of invoice no's as one string seperated with commas.

A sample function may be like the below one.

CREATE FUNCTION udf_GetFileList

(

@.RelatedPKID int

)

RETURNS nvarchar(4000)

AS

BEGIN

Declare @.Files nvarchar(4000)

SELECT

@.Files = COALESCE(@.Files + ' ' ,'') + SystemFileName + ','

FROM

AttachedFiles

WHERE

RelatedPKID = @.RelatedPKID

RETURN @.Files

END

GO

I hope this helps.

Eralper

http://www.kodyaz.com

|||

Check out the techniques in this article:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

The "select @.variable = @.variable + columnValue" approach has its dangerous limitations, and should be avoided if possible (It is a neat trick that I have used too, so I am not trying to moralize, just note the issues :)

In 2005, however, there is a much better way of doing things using the XML, functionality, which is outlined in that article.

|||

Hi Eralper;

Thank you. It worked.

You see my SQL as follows:

create function get_invlist
( @.SalesID NvarChar(10))
Returns nvarchar(4000)
as begin

declare @.inv NvarChar(4000);
select @.inv=COALESCE(@.inv + '','')+InvoiceId +',' from sales, invoice
where
sales.salesid = invoice.salesid
and sales.salesid = @.SalesID
Return @.inv
end
go

end function

select sales.salesid, salesName, sum(invamt), test.dbo.get_invlist(sales.salesid)
from sales, invoice
where
sales.salesid = invoice.salesid
group by sales.salesid, salesname;

Result

S0001 Peter 600.00 INV001 ,INV002 ,INV003 ,
S0002 Alice 500.00 INV004 ,

Regards.

|||

Hi Louis;

I agreed you that using @.variable will have limitations. I will test your suggestion later.

Could I use classical SQL to implement this kind of requirement? (i.e. not use @.variable or XML or new function)

Thanks

|||

Hi Louis;

I tried your method. It is better than before.

Please see my SQL.

select sales.salesid, salesName,
Replace((select rtrim(InvoiceId)
as "data()"
from invoice
where
sales.salesid = invoice.salesid
for XML PATH ('')), ' ', ',')
as "@.InvID"
from sales
group by sales.salesid, salesname;

It is so dynamic and easy to understand.

Thanks.

|||I wish it was my method :) It was Aaron Bertrand's. Glad it helped.

Convert month name, year range to mm/dd/yy

Using SQL 2000. I need to select rows based on a date range. Both the
beginning date and ending date of the range will be entered in the
mm/dd/yy format. There are 2 columns in the table called MonthOfEval
and YearOfEval, both varchar(9) and both with data like "August" and
"2005-2006". I think I need to use these 2 columns to create a date in
the mm/dd/yy format, then use that newly created date to see if it's
within the date range. How would I go about doing this or is there a
better way?

Thanks for any help or advice.(manning_news@.hotmail.com) writes:
> Using SQL 2000. I need to select rows based on a date range. Both the
> beginning date and ending date of the range will be entered in the
> mm/dd/yy format. There are 2 columns in the table called MonthOfEval
> and YearOfEval, both varchar(9) and both with data like "August" and
> "2005-2006". I think I need to use these 2 columns to create a date in
> the mm/dd/yy format, then use that newly created date to see if it's
> within the date range. How would I go about doing this or is there a
> better way?

First of all, when you are in SQL Server you should work with dates
in the datetime datatype, which is a binary container. When you pass
dates to SQL Server, you use parameterised commands or stored procedures.
You pass the client API the date as a string, and the API interprets it
according to regional settings.

If you need to manipulate dates as strings in SQL Server, use the format
YYYYMMDD, as this format is not subject to different interpretations
depending on settings.

Next, it is not clear what "2005-2006" and "August" would mean. The first
I could interpret as 2005-01-01 to 2006-12-31. A single month name is
more tricky. OK, from 08-01 to 08-31, but which year? Any of 2005 and
2006? Or is here some broken fiscal year involved, so that 2005-2006
means 2005-07-01 to 2006-06-30?

The general advice for this sort of question is to post:

o CREATE TABLE (preferrably simplified) for your table.
o INSERT statements with sample data.
o The desired result given the sample.

This has both the effect of clarifying what you are asking for, and to
make it easy to develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Convert letter to integer based on order in alphabet

Is there any way I can convert a letter of the alphabet to its numerical
position in the alphabet.
So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
I'd prefer not to join to a seperate reference table if possible.Terri wrote:
> Is there any way I can convert a letter of the alphabet to its numerical
> position in the alphabet.
> So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
> I'd prefer not to join to a seperate reference table if possible.
How about this?
SELECT ASCII(YourOneCharColumn) - ASCII('A') + 1 FROM YourTable|||CREATE TABLE alpha (alpha_char CHAR(1) NOT NULL PRIMARY KEY, alpha_num
INTEGER NOT NULL) ;
SELECT alpha_num
FROM some_table AS T
JOIN alpha AS A
ON T.col = A.alpha_char ;
David Portas
SQL Server MVP
--
"Terri" <terri@.cybernets.com> wrote in message
news:di6qca$357$1@.reader2.nmix.net...
> Is there any way I can convert a letter of the alphabet to its numerical
> position in the alphabet.
> So if I select a field with 'A' I want to return 1, 'B' = 2, ... 'Z' = 26
> I'd prefer not to join to a seperate reference table if possible.
>

convert Julian Date to Calendar Date

How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
following guideline found at Internet?
To convert Julian date to Gregorian date:
double JD = 2299160.5;
double Z = Math.Floor(JD+0.5);
double W = Math.Floor((Z - 1867216.25)/36524.25);
double X = Math.Floor(W/4);
double AA = Math.Floor(Z+1+W-X);
double BB = Math.Floor(AA+1524);
double CC = Math.Floor((BB-122.1)/365.25);
double DD = Math.Floor(365.25*CC);
double EE = Math.Floor((BB-DD)/30.6001);
double FF = Math.Floor(30.6001*EE);
double Day = BB-DD-FF;
double Month;
double Year;
if((EE-13) <= 12 && (EE-13) > 0)
Month = EE-13;
else
Month = EE-1;
if(Month == 1 || Month == 2)
Year = CC-4715;
else
Year = CC-4716;Sam
declare @.julian char(8)
select @.julian = '1996.031'
declare @.dt datetime
select @.dt =
DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.juli
an,1,4)+'0101',212))
select @.dt
"Sam" <cybersam88@.hotmail.com> wrote in message
news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
> How do I convert Julian Date to Calendar Date in T-SQL / DTS based on
> following guideline found at Internet?
> To convert Julian date to Gregorian date:
> double JD = 2299160.5;
> double Z = Math.Floor(JD+0.5);
> double W = Math.Floor((Z - 1867216.25)/36524.25);
> double X = Math.Floor(W/4);
> double AA = Math.Floor(Z+1+W-X);
> double BB = Math.Floor(AA+1524);
> double CC = Math.Floor((BB-122.1)/365.25);
> double DD = Math.Floor(365.25*CC);
> double EE = Math.Floor((BB-DD)/30.6001);
> double FF = Math.Floor(30.6001*EE);
> double Day = BB-DD-FF;
> double Month;
> double Year;
> if((EE-13) <= 12 && (EE-13) > 0)
> Month = EE-13;
> else
> Month = EE-1;
> if(Month == 1 || Month == 2)
> Year = CC-4715;
> else
> Year = CC-4716;
>|||Well, if you really want to use that formula, then just assign a bunch of
values, along the lines of:
declare @.JD float,
@.Z float,
@.W float,
@.X float,
@.AA float,
@.BB float,
@.CC float,
@.DD float,
@.EE float,
@.FF float
set @.JD = 2299160.5
set @.Z = @.JD+0.5
set @.W = (@.Z - 1867216.25)/36524.25
set @.X = @.W/4.0
set @.AA = @.Z+1+@.W-@.X
set @.BB = @.AA+1524
set @.CC = (@.BB-122.1)/365.25
set @.DD = 365.25*@.CC
set @.EE = (@.BB-@.DD)/30.6001
set @.FF = 30.6001*@.EE
declare @.day float, @.month float, @.year float
set @.Day = @.BB-@.DD-@.FF
if ((@.EE-13) <= 12) and ((@.EE-13) > 0)
set @.Month = @.EE-13
else
set @.Month = @.EE-1
if(@.Month = 1 OR @.Month = 2)
set @.Year = @.CC-4715
else
set @.Year = @.CC-4716
select @.day, @.month, @.year
But I'm not sure that's right. I think a Julian date is just the number of
days since some date about 4000 BC... and that you do it like this:
declare @.mydate datetime
set @.mydate = dateadd(dd, @.JD-2451545, '1-jan-2000')
But this is assuming you just have an integer for your number. If it's a
float, then you will also want to do something like:
select dateadd(second,86400 * (@.JD - convert(bigint,@.JD)),@.mydate)
Which just adds on the fraction to the number of seconds.
Hope this helps,
Rob|||How about convert VB Script to T-SQL?
CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
> Sam
> declare @.julian char(8)
> select @.julian = '1996.031'
> declare @.dt datetime
> select @.dt =
> DATEADD(dd,convert(int,right(@.julian,3))
-1,convert(datetime,substring(@.ju
li
> an,1,4)+'0101',212))
> select @.dt
>
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:uTQSoztSFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Sam
declare @.julian char(8)
select @.julian = REPLACE('1996.031','.','')
SELECT CAST(CAST(@.julian /1000 AS CHAR(4))+'01'+CAST(@.julian %1000 AS
CHAR(2))AS DATETIME)
"Sam" <cybersam88@.hotmail.com> wrote in message
news:elKd5AwSFHA.3244@.TK2MSFTNGP15.phx.gbl...
> How about convert VB Script to T-SQL?
> CalendarDate = Date(1900+INT(JulianDate/1000),1,MOD(JulianDate,1000))
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O8TdBbuSFHA.3980@.TK2MSFTNGP12.phx.gbl...
DATEADD(dd,convert(int,right(@.julian,3))
- 1,convert(datetime,substring(@.juli[color
=darkred]
>