Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

Return data from multiple tables

Hi there,

I have tables with such structure

transaction_YYMM
(idx,date,company_id,value)

where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

best regards
RafalI want to define query (maybe view, procedure):

Quote:

Originally Posted by

select * from [?] where date>='2007-01-01' and date<='2007-04-30'

A UNION ALL query will combine multiple result sets:

SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704

You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating a
partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rafa Bielecki" <rafal@.bielecki.infowrote in message
news:f2ubuk$i1o$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

Hi there,
>
I have tables with such structure
>
transaction_YYMM
(idx,date,company_id,value)
>
where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one
>
best regards
Rafal
>

|||Uzytkownik "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netnapisal w
wiadomosci news:JOz4i.22889$JZ3.9830@.newssvr13.news.prodigy.n et...

Quote:

Originally Posted by

A UNION ALL query will combine multiple result sets:
>
SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704
>
You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating
a partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.

thank you Dan, your help is very important to me
Rafal|||Rafa Bielecki (rafal@.bielecki.info) writes:

Quote:

Originally Posted by

I have tables with such structure
>
transaction_YYMM
(idx,date,company_id,value)
>
where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

In additions to Dan's response, I like to point out that from a logical
point of view, the above design is flawd. It's a lot easier to deal with a
single table. If there are enourmous volumes, it can still be motivated
with partitioning, but then we are talking enourmous values like tens
of millions of rows per month.

--
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

return current date

Hi,

Can I write a code in SQL that return the current date? If so, how?

Thanks!

WillYou can use the GETDATE() function:

SELECT GETDATE() AS CurrentDateTime

And you can use the CONVERT function to format that datetime value.

Terri|||Convert(varchar(10),GetDate(),101)

returns the date as:

01/01/2004

Return all records in 15 min intervals.

Hello All,

First off thanks to all who try to help me.

I have a table with a date timestamp that includes the minute in 24 hour format..
I need to return all the records in the date range between '1/1/2007' and '1/31/2007' which is very simple.
The part I am having a hard time with is in the where clause.
I retrieve all the records for the date range into a #temptable
Now I need to sort these into 15 minute intervals based on the timestamp, but the sproc does'nt get passed a time only two dates also I need to provide a count of the records returned.

I have tried to create a temp table with 96 records being that there are 96 intervals of 15 minutes in 24 hours.
and using that as kinda a join.

I have tried to do something with datediff(_) with a dateadd(_)+15 in the where clause.

I also tried a loop 96 times.

I know there are a few ways to do it.I just dont know which ones.

I've only been using tsql for 2 months and have come pretty far, but this situation evades me.

Please enlighten me with your infinite wisdom of the tsql language. Help !

Is this the idea?

declare @.sampleData table
( rid integer primary key,
sampleDate datetime
)

insert into @.sampleData
select iter,
cast ('12/25/6' as datetime) + 70 * dbo.rand()
from small_iterator (nolock) -- A table of integers 1-32767

select top 5
rid,
sampleDate,
p.startOfInterval,
p.endOfInterval
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= '1/1/7'
and sampleDate < '2/1/7'
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
order by rid

-- rid sampleDate startOfInterval endOfInterval
-- -- - -
-- 1 2007-01-02 14:58:52.183 1900-01-01 14:44:59.997 1900-01-01 15:00:00.000
-- 3 2007-01-30 08:00:32.787 1900-01-01 08:00:00.000 1900-01-01 08:15:00.000
-- 4 2007-01-16 19:59:58.257 1900-01-01 19:45:00.000 1900-01-01 19:59:59.997
-- 5 2007-01-04 10:34:13.883 1900-01-01 10:30:00.000 1900-01-01 10:44:59.997
-- 8 2007-01-22 08:14:12.333 1900-01-01 08:00:00.000 1900-01-01 08:15:00.000

select left(convert (varchar(8), p.startOfInterval, 108), 5) as startOfInterval,
left(convert (varchar(8), p.endOfInterval, 108), 5) as endOfInterval,
count(*) as intervalCount
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= '1/1/7'
and sampleDate < '2/1/7'
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
group by p.startOfInterval, p.endOfInterval
order by p.StartOfInterval

-- startOfInterval endOfInterval intervalCount
-- - -
-- 00:00 00:15 151
-- 00:15 00:30 158
-- 00:30 00:45 134
-- ...
-- 23:30 23:44 145
-- 23:44 00:00 158

|||

Getting Closer..

Thank you very much Kent.
I wish I could write code like that in Tsql and actually "Get it to work" for me.

I need the data to look like this which for the most part the above does.

Date 1/1/2007 <Whatever user chooses

Time Interval Number Calls

12:00 12:15 147
12:15 12:30 117
12:30 12:45 215

The existing data I have looks like this for the timestamp field.

12/22/2003 9:29:00 AM
1/5/2004 1:31:00 PM
7/2/2003 3:53:00 PM
7/7/2003 1:27:00 PM

I have thought about parsing out the strings then back converting them but with sql we shouldnt have to do that.
I'm going to attempt to plug the above code in and see what I can do with it. More suggestions are welcome !

|||Do you need me to post my dbo.rand() function and small_iterator table?|||

You could change the SELECT list to that shown below.

Displaying the date above the output results (i.e. handling presentation) isn't what SQL Server is good at. It's far better to handle this in the client code or in Reporting Services.

Chris

SELECT CAST(DATEPART(hh, p.startOfInterval) AS VARCHAR(2)) + ':'

+ CAST(DATEPART(mi, p.startOfInterval) AS VARCHAR(2)) + ' '

+ CAST(DATEPART(hh, e.endOfInterval) AS VARCHAR(2)) + ':'

+ CAST(DATEPART(mi, e.endOfInterval) AS VARCHAR(2)) AS [Time Interval],

COUNT(*) AS [Number Calls]

|||

Kent - I know you weren't talking to me, but it would be good if you could. I've noticed you use small_iterator in a number of your posts.

Is it just a table of sequential integers?

Cheers
Chris

|||

Yes, small_iterator is a list of integers 1-32767; stand by and I will post this stuff. Here is another shot at your summary (the SMALL_ITERATOR and dbo.rand() will follow in a minute):

declare @.searchDate datetime
set @.searchDate = '3/1/7'

declare @.sampleData table
( rid integer primary key,
sampleDate datetime
)

insert into @.sampleData
select iter,
cast ('12/25/6' as datetime) + 70 * dbo.rand()
from small_iterator (nolock) -- A table of integers 1-32767

select left(convert (varchar(8), p.startOfInterval, 108), 5) + ' ' +
left(convert (varchar(8), p.endOfInterval, 108), 5) + ' ' as [ Time Interval],
count(*) as intervalCount
from @.sampleData a
inner join
( select iter,
cast ((iter-1) * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as startOfInterval,
cast (iter * cast (cast ('0:15:00.000' as datetime) as float) as datetime)
as endOfInterval
from small_iterator (nolock) -- a table of integers 1-32767
where iter <= 96
) p
on sampleDate >= @.searchDate
and sampleDate < (@.searchDate + 1)
and cast(cast(sampleDate as binary(4)) as datetime) >= p.startOfInterval
and cast(cast(sampleDate as binary(4)) as datetime) < p.endOfInterval
group by p.startOfInterval, p.endOfInterval
order by p.StartOfInterval

-- Time Interval intervalCount
-- -- -
-- 00:00 00:15 4
-- 00:15 00:30 6
-- 00:30 00:45 6
-- ...
-- 23:30 23:44 2
-- 23:44 00:00 2

|||

Here is my small_iterator table; it is simply a table of numbers. To get some ideas about tables of numbers, give this website a look:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go

/* -- */
/* This routine is used to populate the small_iterator table. */
/* This query ran in 1 second in development and should run at */
/* a similar speed in production. */
/* -- */

truncate table SMALL_ITERATOR

insert into small_iterator
select number from master.dbo.spt_values (nolock)
where name is null
and number <= 255

insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 127
order by 256 * j.iter + i.iter

delete from small_iterator where iter = 0

select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR

go

dbcc dbreindex (small_iterator, '', 100)
go

update statistics small_iterator
go

exec sp_recompile small_iterator
go

|||

Here is my RAND scalar UDF; it comes in handy at times for generating mock data:

create view dbo.vRand
as
select rand () as vRand
go

create function dbo.rand ()
returns float
as
begin

return (select vRand from dbo.vRand)

end

go

create function dbo.randList
( @.pm_listSize integer
)
returns @.randList table
( rid integer,
iRand float
)
as
begin

declare @.upperBound integer

set @.upperBound = ceiling (convert(float, (@.pm_listSize+1))
/ convert (float, 32767))

insert into @.randList
select 32767*(j.iter-1) + i.iter - 1 as rid,
dbo.rand() as iRand
from small_iterator i (nolock)
inner join small_iterator j (nolock)
on j.iter <= @.upperBound
and 32767*(j.iter-1) + i.iter - 1 <= @.pm_listSize
and 32767*(j.iter-1) + i.iter - 1 > 0

return

end

go

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81913&SiteID=1

declare @.interval int
set @.interval=30

select dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0) [dt],
count(*) [cnt]
from Northwind..Orders
group by dateadd(minute,floor(datediff(minute,0,OrderDate)/@.interval)*@.interval,0)
order by 1

This is looking like it's going to work for my needs just have to tweak it a bit more..

Thanks to all who helped. One day I'll be able to help with tsql instead of just VB and ASP

Return all months within a range of dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.

CREATE PROCEDURE sp_GetContactScheduleDates
@.MonthFrom int,
@.YearFrom int,
@.MonthTo int,
@.YearTo int,
@.DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 >= CAST(cast(@.MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@.YearFrom as nvarchar) as smalldatetime)
 And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 <= CAST(cast(@.MonthTo as nvarchar) + '/' + cast(@.DaysInMonth as nvarchar) + '/' + cast(@.YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO

However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.

set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

Return a todays Date query?

Hello , i want to writ a query that returns ruslts for today's date only,How to do it? i tried to filter the results using Now() function but it did`t work, any help please?

hi leonardo ,

try this

select columns from tablename where convert(varchar, column_with_date_datatype, 103) = convert(varchar,getdate(),103)

hope it helps

regards,

satish

|||

Hi Leo,

U can also use the split function to receive the date.

I think the now.tostring gives date<space>time

so,

dim dat() as string

dat=split(now.tostring)

Msgbox(dat(0).tostring)

dat(1)-->time

-PSK

|||

Thanks for the help

but what i want is to build up a query in the SQL Server that returns today's date in SQL Statment

Select JoinDate

From Employee

Where **********;

how to have a result for a today's date , a week before and a month before ...

|||

you could use

dateadd() function in that case, for more help refer to sql books online.

thanks,

satish.

|||

You could get the start of the day like so DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)..

This means you would do something like...

SELECT * FROM table WHERE Created >= DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)

Enjoy, Steve

Return a row with columns for each day in a date range

Given in a record in from a Table called WorkSchedule:

idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8

I need to return for each record in the WorkSchedule Table

1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006

8 8 8 8..................8

Please help.

Thank you.

-Robert

Hi Robert,

Let's assume your source table was called "WS":

with Hours (MinDate, MaxDate, WorkDate, WorkHrs)

AS

(

SELECT StartDate as [MinDt], EndDate AS [MaxDt], StartDate AS [WorkDt], HoursWorked

FROM WS

WHERE idWorkSchedul = 1

UNIONALL

SELECT MinDate, MaxDate,DATEADD(day,1, WorkDate)AS [WorkDate], WorkHrs

FROM Hours h

WHERE WorkDate <= MaxDate

)

select*from Hours

This will I think give you a few ideas anyway (you can pivot the resultset if indeed you needed the resultset to mimic the example output you supplied). Also note that we need to return the MaxDt and MinDt so we can limit the recursive function via the WHERE WorkDate <= MaxDate clause as a recursive CTE will not allow a sub query in the where clause.

Cheers,

Rob

|||

is the employee column needed

|||

hi,

Sql server has a limitation of 1024 columns

your requirements exceeds that limitations

regards

joey

here's a tests script. its not finished becaused i encountered the limitation

use northwind
create table dates
(
dateid int identity(1,1),
date1 datetime
)
go

declare @.mydate datetime
select @.mydate ='1/1/2000'
while @.mydate<>'1/31/2010'
Begin
insert dates(date1) values ( @.mydate)
select @.mydate=dateadd(day,1,@.mydate)
end
go

select * from dates
go

create table worksched(
idWorkSchedul int identity(1,1),
StartDate datetime,
EndDate datetime,
HoursWorked int
)
insert worksched(startdate,enddate,hoursworked)
values( '1/1/2000','1/1/2006',8)

declare @.startdate datetime
declare @.enddate datetime
select @.startdate='1/1/2000'
select @.enddate='1/1/2006'
select IDENTITY(int, 1,1) AS ID_Num,
date1 INTO #MYTEMP from dates where date1
between @.startdate and @.enddate

--drop table mytest
CREATE TABLE MYTEST1
(EMPLOYEE_ID VARCHAR(10)
)

DECLARE @.CMD nVARCHAR(200)
DECLARE @.CTR INT
DECLARE @.NAME VARCHAR(10)
SELECT @.CTR=0
WHILE @.CTR<>(SELECT MAX (ID_NUM) FROM #MYTEMP)
BEGIN
SELECT @.CTR=@.CTR+1
SELECT @.NAME = CONVERT( VARCHAR(10), DATE1 ,110) FROM #MYTEMP WHERE ID_NUM=@.CTR
select @.cmd ='ALTER TABLE MYTEST1 ADD ['+ @.NAME +'] INT'
--select @.cmd
exec sp_executesql @.cmd
END

select * from mytest1

|||

Here's an idea that may be of use, though I wouldn't really call it 'rows and columns', it's more of a play-with-strings for display purposes only. Each date will not be a separate column, it's just one long formatted string for the specific purpose.

Using the following example, to generate the days in the range is pretty straight forward with a number table.

create table #workSched
( id int not null, StartDate datetime not null, EndDate datetime not null, hrs int not null )

insert #workSched
select 1, '20060101', '20060331', 8 union all
select 2, '20060401', '20060831', 8

Assuming we have these two rows, then this query would produce a 'normal' resultset for each day between start and end
(the 'n - 1' is due to my numberstable starts with one, not zero)
Also, it's necessary to do this one workid at a time, it won't work for all in one go with just a straight query. However, it may be possible to package the idea into a UDF to get a simulation of a 'single-pass' (though performance may still be an issue)

select id,
dateadd(day, n -1, startDate) as workDay,
hrs
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

We could use this and build two strings, one with days and the other with the hours, keeping formatting in mind so that the two would be spaced accordingly.

declare @.workDay varchar(8000), @.hrs varchar(8000)
select @.workDay = '', @.hrs = ''

-- build the 'row' of dates
select @.workDay = @.workDay + convert(char(10), dateadd(day, n -1, startDate), 121) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

-- buld the 'row' of hours, evenly spaced according to date
select @.hrs = @.hrs + convert(char(10), hrs) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

-- display
select @.workDay
union all
select @.hrs

-- ....
2006-01-01 2006-01-02 2006-01-03 ....
8 8 8 ....

If you're looking for something for display or reporting use, then perhaps this idea could work for you..?
(it's not that pretty, but it works.. =;o)

/Kenneth

|||You can do the pivoting on the client side easily especially since you may have large number of date values. If you are building a report then it is a very trivial operation. So send the data as rows (dates as rows) and pivot on the client side. Solutions in TSQL will require dynamic SQL or fixed column names and other procedural techniques which will slow in terms of performance.

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Retrieving Mutiple rows

I have a table like this.

Depositors Table

Value(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Please Can anyone write a simple query for this?

Thanks

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select top 100 sum(grandtotal),

case when saledate = dateadd("d",-1,dateadd("month",0,'07/20/2007')) then 'yesterday' --cast (saledate as nvarchar(30))

when saledate < dateadd("d",-1,dateadd("month",0,'07/20/2007')) and saledate >= dateadd("d",-7,dateadd("month",-1,'07/20/2007')) then 'Last 7 days'

when saledate < dateadd("day",-1,dateadd("month",-1,'07/20/2007')) and saledate >= dateadd("day",-2,dateadd("month",-3,'07/20/2007')) then 'Last month'

when saledate < dateadd("day",-2,dateadd("month",-3,'07/20/2007')) and saledate >= dateadd("d",-1,dateadd("year",-2,'07/20/2007')) then 'Last 1 year'

else '...'

end , saledate

from sale group by saledate order by saledate desc

select sum(amount), duration from @.temptable group by duration order by max(date) desc

Bad formatting but query works..

I checked it..

in my database i have old date so i need to use old date.. but you can use today's date..

|||

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

|||

shamen wrote:

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate = dateadd("d",-1,GETDATE()) then 'yesterday'

when Startdate between dateadd("d",-1,GETDATE()) and dateadd("d",-7,GETDATE()) then 'Last 7 days'

when startdate between dateadd("d",-7,GETDATE()) and dateadd("d",-30,GETDATE()) then 'Last month'

Else 'ABC'
end as duration, startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

May it works now. For testing purpose always keep default value so atlease you can know that condition is going where

Retrieving Mutiple rows

I have a table like this.

Depositors Table

Value(int) StartDate(Date) AccountID(int)

I want to create a report from this table. the report should look like this.

Value No of Accounts Average Value

For Yesterday

For Last 7days

For Last 30 days

Please Can anyone write a simple query for this?

Thanks

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select top 100 sum(grandtotal),

case when saledate = dateadd("d",-1,dateadd("month",0,'07/20/2007')) then 'yesterday' --cast (saledate as nvarchar(30))

when saledate < dateadd("d",-1,dateadd("month",0,'07/20/2007')) and saledate >= dateadd("d",-7,dateadd("month",-1,'07/20/2007')) then 'Last 7 days'

when saledate < dateadd("day",-1,dateadd("month",-1,'07/20/2007')) and saledate >= dateadd("day",-2,dateadd("month",-3,'07/20/2007')) then 'Last month'

when saledate < dateadd("day",-2,dateadd("month",-3,'07/20/2007')) and saledate >= dateadd("d",-1,dateadd("year",-2,'07/20/2007')) then 'Last 1 year'

else '...'

end , saledate

from sale group by saledate order by saledate desc

select sum(amount), duration from @.temptable group by duration order by max(date) desc

Bad formatting but query works..

I checked it..

in my database i have old date so i need to use old date.. but you can use today's date..

|||

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

|||

shamen wrote:

Thanks..

I tried with this one..But I did not get what I want.

I changed it lil bit.

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate >= GETDATE()-1 then 'yesterday'

when Startdate >= GETDATE()-7 then 'Last 7 days'

when startdate >=GETDATE()-30 then 'Last month'

end , startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

Query works. But it does not show values for duration. As example, it does not show whether its yesterday , Last7days or etc.

But I want to get the report as shown above.....

declare @.temptable table (amount decimal(10,2) , duration nvarchar(50),date datetime)

insert into @.temptable(amount,duration,date)

select sum(Amount),

case when startdate = dateadd("d",-1,GETDATE()) then 'yesterday'

when Startdate between dateadd("d",-1,GETDATE()) and dateadd("d",-7,GETDATE()) then 'Last 7 days'

when startdate between dateadd("d",-7,GETDATE()) and dateadd("d",-30,GETDATE()) then 'Last month'

Else 'ABC'
end as duration, startdate

from CD group by startdate order by startdate desc

select sum(Amount), duration from @.temptable group by duration order by max(date) desc

May it works now. For testing purpose always keep default value so atlease you can know that condition is going where

Retrieving Most recent transaction date

Hi,

I have a fact table containing the transaction code and date on a daily basis. I need to find out for a particular account the most recent date when a particular transaction 'abc' was received. I need to find out most recent dates for other such transactions as well to be displayed on one single report. Basically I would need something like:

ABC Date - Most recent date when 'ABC' transaction was sent

XYZ Date - Most recent date when 'XYZ' transaction was sent. ........

I thought of using the filter available on the cube browser, but if I use filter and use the '=' operator I can only specify one transaction code. But I need dates for different transaction codes.

Any thoughts on this would be greatly appreciated.

Thanks.

Here's a sample Adventure Works query, which returns the last order date for each Promotion listed:

>>

With

Member [Measures].[LastDate] as

Tail(NonEmpty([Date].[Date].[Date].Members,

{[Measures].[Order Quantity]})).Item(0).MemberValue

select

{[Measures].[Order Quantity],

[Measures].[LastDate]} on 0,

Non Empty [Promotion].[Promotion].[Promotion].Members on 1

from [Adventure Works]

-

Order Quantity LastDate
No Discount 238,806 7/31/2004
Volume Discount 11 to 14 18,181 6/30/2004
Volume Discount 15 to 24 10,713 6/1/2004
Volume Discount 25 to 40 2,321 6/1/2004
Volume Discount 41 to 60 85 4/1/2004
Mountain-100 Clearance Sale 456 6/1/2002
Sport Helmet Discount-2002 492 7/1/2002
Road-650 Overstock 304 8/1/2002
Sport Helmet Discount-2003 680 7/1/2003
Touring-3000 Promotion 1,581 9/28/2003
Touring-1000 Promotion 775 9/23/2003
Mountain-500 Silver Clearance Sale 382 6/1/2004

>>

Retrieving Coinciding date with Sequence Number

I have a table that contains a sequence number for every calendar work day and the matching calendar date. I am linking a table that has a start date that I need to work with. I want to add a cycle time number to the sequence number for the given start date. I then want the report to show the calendar date that matches the new sequence number.

I know this is very confusing, I am attaching an image that shows the fields which may make it a little easier to understand. Any help would be greatly appreciated.

Thanks,
Babs827Why does the second sequence number is 270?

Retrieving by months only

I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.

Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.SELECT * FROM yourTable WHERE
MONTH(ColumnName) = 9

Retrieving all users in db with specified AGE range

Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.

So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).

How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?

TIA.

You can use the DateDiff function to get there age

http://msdn2.microsoft.com/en-us/library/ms189794.aspx

|||

Thanks for the reply. But what about my database table implementation... is that the right way to go? i'm assuming having an age field is poor practice and I should just have a dob field.

And for the next step, how would I determine the list of users who are say, between the ages of 40 and 55? The DateDiff requires that I know 2 dates ahead of time and that would force me to do a table scan (would be time costly if the table gets huge), computing all dob values in the table with the current time to see if a user falls between 40 and 55. Is there a way that I can convert age into a DateTime? If so, I could compute the DateTimes for the min and max values that are inputted by the user. And then I can maybe do like a "WHERE dob BETWEEN min AND max"...... something to that effect to retrieve the necessary rows. BETWEEN works for integers, not sure for DateTime.

Sorry for the stupid questions, I'm a relative newbie. TIA.

|||

Anybody?

Ok, I think this is the way to go. Just have a DOB field in the Member table. Having an age field is bad practice, I'd think. Then, when a user wants to search for all members within a specified age range (a min and a max age value in years), I would translate the age values into a DateTime format (current time minus age to derive DOB) on the application side. Then I would run an SQL query along the lines of "SELECT * FROM Member WHERE dobDateMin BETWEEN dobDateMax". This should return all members whose DOBs fall within the age range.

The question is, how do I calculate a DOB in DateTime given an age in years? Pseudo-speaking, it would be like.... take the current system time, minus the age in years, and derive the date in DateTime format, so I can feed it into the SQL statement.

Am I on the right track with this? How do all the sites that allow searches for age ranges do this?

TIA

|||

Yes, you would use the dateadd function for this.

SELECT *

FROM MyTable

WHERE DoB Between DATEADD(year,0-@.MaxAge,floor(cast(getutcdate() as float))) AND DATEADD(year,0-@.MinAge,floor(cast(getutcdate() as float)+1))

Assuming that DoB is a datetime field in the MyTable table, and there is an index on that field, it will do an index scan range to return your results. GetUTCDate() may not give you the date you are looking for, adjust as necessary.

|||

Thank you - that seems like what I'm looking for and I appreciate the example so I could visualize how that could be used here. I would have liked to do all of the calculating on the app side to avoid database overhead, but I'll take what I can get. Yes, the DOB field is already indexed to avoid a costly table scan. So do you think other sites use this approach when trying to return results of users within an age range? I'll try it out and see if it works. Thanks.

***** BTW, just out of curiosity, why did you cast the third parameter into float and then floor it? Is this so that you can get pinpoint accuracy at the date level? If so, since the third field accepted a datetime, don't we have to recast it again into a datetime (or smalldatetime)? Like ....

WHERE dob BETWEEN DATEADD(year,0-@.MaxAge,CAST(FLOOR(CAST(getdate() AS float)) AS datetime)) AND DATEADD(year,0-@.MinAge,CAST(FLOOR(CAST(getdate() AS float)+1) AS datetime))

|||

It's a quick hack. Datetimes when converted to floats are in a format that the date portion is stored in a whole numbers, and the time portion as a fraction of a number. By flooring it, we lose the time portion (or more accurately, we get the very smallest time for that date -- exactly midnight).

As for casting it back again, SQL Server will do that as an implicit conversion (It doesn't need to be stated, but you can if you want).

As for where to calculate the dates, I would do it on the server side. It's not really a hard calculation to make for SQL Server, and it abstracts the implementation of the age search to SQL Server. Meaning, if at a later time, we decide that we need to (for whatever reason, performance, scalability, integration) change how we do the search the application code doesn't need to change. We could for example, add an age column to the member table, run a batch process at night that goes through and updates all the user's age, and change the query (assuming it's in a stored procedure, or depends on a view) and the application(s) wouldn't even notice except for possibly better performance. All depends on your environment really. If you aren't using stored procedures then it won't really buy you very much though.

Retrieve report parameters names and type programmatically

Hi,
I need to retrieve a report parameters names and their type ( being
string, list, date etc ) programmatically so i can dynamically assign them.
Can anyone help?
Thanks,use the web service that comes with reporting services ...
the method GetReportParameters is exactly what u need ...
"Loui Mercieca" wrote:
> Hi,
> I need to retrieve a report parameters names and their type ( being
> string, list, date etc ) programmatically so i can dynamically assign them.
> Can anyone help?
> Thanks,
>
>

Retrieve record just updated

I want to add an update trigger to keep a LastDateModified column up to date. I also want to make the trigger as generic as possible so I can cut&paste into others, the column has the same name throughtout the system. How can I retrieve the record I just updated so I can change the date? Also, if my update trigger updates a record, will this cause a loop?

In the context of a TRIGGER, there are two virtual tables, known as inserted and deleted.

To update the [LastDateModified] column, your TRIGGER action would be something like this:

Code Snippet

UPDATE MyTable

SET LastDateModified = getdate()

FROM inserted i

JOIN MyTable m
ON m.PKColumn = i.PKColumn

And no, the TRIGGER does not cause a loop.

|||The trigger "should not" cause a loop, unless recursive triggers are on in the database.|||This works perfectly except for one thing, the "architect" created several table without keys. Now, I get to go back and fix the mistakes of someone that thought they were "Super SQL Designer".

Retrieve only last 4 records. Date descendent ordered.

Hello,
I want to load only the last 4 records of a MSSQL table considering they
are ordered descendent by date. What should my SQL be?
Thank You,
MiguelHi
You can use the TOP function to do this. See Books online for more details
SELECT TOP 4 MyDate
FROM MyTable
ORDER BY MyDate DESC
John
"Shapper" <mdmoura*NOSPAM*@.gmail.*DELETE2SEND*com> wrote in message
news:eGCRmjmTFHA.2172@.tk2msftngp13.phx.gbl...
> Hello,
> I want to load only the last 4 records of a MSSQL table considering they
> are ordered descendent by date. What should my SQL be?
> Thank You,
> Miguel
>|||DECLARE @.intMyCount int
SET @.intMyCount = 4
SET ROWCOUNT @.intMyCount
SELECT MyDate
FROM MyTable
ORDER BY MyDate DESC
-- disable rowcount
SET ROWCOUNT 0
Message posted via http://www.webservertalk.com

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.

Retrieve insertiondate

Hi folks,
Does anyone know how to retrieve the date when data has been inserted
thnx in advanceYou can create a column of the datetime data type. Set the default for that column to "getDate()". This should give you a value of the current date at the time of the insert.|||thnx for your reply,

I know that I can solve it like you suggested, but I want to know the insertiondate of a excisting record..

Can't I retrieve this info with the system tables or something like that

thnx in advance|||Generally, no.

I suppose you could restore an old copy of the database and just start restoring transaction logs until the record you are looking for pops up, but I'm guessing you don't have a full-time employee to devote to this for the next week.|||I wish I had,

But thnx for sharing that with me, now I can leave it and go further

thnx