Getting the past 24 hours worth of data?

I have a need to query my data and return only the last 24 hours (versus last day) worth of data.

My date and time fields are called:

calc_date = the date
Calc_time= the time
RealTime = Calc_date+Calc_time as realtime

I know if I use getdate()-1 that will get me the last day, but I need the last 24 hours. Any ideas on how would I do this?

I think what you're after is the DATEADD() function, so to get the start of the last 24 hours you could use:

DATEADD(hh,-24,GetDate())

e.g. SELECT DATEADD(hh,-24,GetDate()) AS [24HoursAgo]

hh = hours

|||

Hi Jim,

Follow this example, I hope it'll help you.

declare @.date nvarchar(10),

@.time nvarchar(8),

@.lastday datetime

select

@.date = '2007-07-08',

@.time = '15:45:20',

@.lastday = convert(datetime, (@.date + ' ' + @.time))

select

'Before:' as [Info],

@.date as [Date],

@.time as [Time],

@.lastday as [DateTime]

select

'After:' as [Info],

@.date as [Date],

@.time as [Time],

dateadd(hh, -24, @.lastday) as [DateTime]

|||

Actually, Jim, if you want the last 24 hours GETDATE() - 1 should work just fine. Look at this:

Code Snippet

select getdate() as now,
getdate()-1 as [24 hours ago]

/*
now 24 hours ago
-
2007-08-16 07:22:42.340 2007-08-15 07:22:42.340

|||

Your query might be like this..

Code Snippet

select

*

From

<Table Name>

Where

Cast(Calc_date+Calc_timeas datetime) between Getdate() and Getdate() -1

getting similar fields in same output row for grouping totals

I have a table similar to the below:

Field1 Field2
EXE1 Age
EXE2 Child Under 10
EXE3 Not a citizen
WEB1 Cares for a child under 10
WEB2 Be a US Citizen

and I am getting the totals for the records grouped by Field1 - not a
problem. So, output is something like:

Field2 Total %
Age 10 .4
Child under 10 15 .5
Not a citizen 15 .5
Cares for < 10 20 .6
Be a US citizen 25 .7

However, IF there is an EXE# basically the same as a WEB#, then I need
to put them together in my report output, like so:

Field EXEtotal WEBtotal Total %
Age 10 0 10 .4
Child <10 15 20 35 1.0
Citizen 15 25 40 1.1

So, I'm getting the data I need returned, but don't know how to sort it
in my report to give me the new output I need.

I think I need to do this in my report output rather than my SQL
because I need to pull the individual information. Is there a way to
say "if Field 1 = 'WEB2' and Field 1 = 'EXE3', list in same row, then
total? I just don't know how to get them in the same row...

Thanks!

I would create a new column in the table to keep track of the parent group, ie.. your Web1 and Exe1 would be in the same Parent group.

Let me know if you can't make sense of it.

sql

Getting Rid of DBNull values in DB

As a part of migration to .net we are redesigning sql server 7 database and moving it to sql server 2005. There are a lot of fields with dbnull value in the existing db. It would be nice to populate them with some default value while transfering data to the new datbase on sql server 2005.

What is the best way to deal with the issue, are there any possible drawbacks and problems.

Your input will be greatly appreciated.

Getting rid of NULLs/having default values is generally a good thing. It removes a lot of overhead in your code to constantly check for NULLs. You can write UPDATE queries to update the NULLs to some default values. A sample update query could be:

UPDATE yourTable SET Price = 0 WHERE Price IS NULL

sql

Getting Result of query inside another query

Hi to all,

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

create procedure marks1
as
@. sql1 as varchar(50)

@. sql1=select registerno ,subjectcode from mark;

begin

select * from marksetting where registerno='@.sql1.registerno' and
subjectcode='@.sql1.subjectcode';

end

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

Thanks in Advance.I just need to get two fields from a table and manipulate the results

Quote:

Originally Posted by

in next query of a procedure.I planned to code like what you see
below,

Why not use a single query? For example:

CREATE PROCEDURE dbo.marks1
AS

SELECT *
FROM dbo.marksetting
JOIN dbo.mark ON
mark.registerno = marksetting.registerno AND
mark.subjectcode = marksetting.subjectcode;
GO

To answer your question, if the first query returns no more than a single
row, you could assign the result values to variables for use in the second
query:

CREATE PROCEDURE dbo.marks1
AS

DECLARE
@.registerno int,
@.subjectcode int

SELECT
@.registerno = registerno,
@.subjectcode = subjectcode
FROM dbp.mark;

SELECT *
FROM dbo.marksetting
WHERE
registerno = @.registerno AND
subjectcode = @.subjectcode;
GO

If the first query might return more than one row, you could store the
result in a temp table or variable for use in the second query:

CREATE PROCEDURE dbo.marks1
AS

DECLARE @.results TABLE
(
registerno int,
subjectcode int
);

INSERT INTO @.results
SELECT
registerno,
subjectcode
FROM dbo.mark;

SELECT *
FROM dbo.marksetting
JOIN @.results AS r ON
marksetting.registerno = r.registerno AND
marksetting.subjectcode = r,subjectcode;
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"meendar" <askjavaprogrammers@.gmail.comwrote in message
news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...

Quote:

Originally Posted by

>
Hi to all,
>
I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,
>
create procedure marks1
as
@. sql1 as varchar(50)
>
@. sql1=select registerno ,subjectcode from mark;
>
begin
>
select * from marksetting where registerno='@.sql1.registerno' and
subjectcode='@.sql1.subjectcode';
>
end
>
>
can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.
>
Thanks in Advance.
>

|||On May 22, 3:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>
Why not use a single query? For example:
>
CREATE PROCEDURE dbo.marks1
AS
>
SELECT *
FROM dbo.marksetting
JOIN dbo.mark ON
mark.registerno = marksetting.registerno AND
mark.subjectcode = marksetting.subjectcode;
GO
>
To answer your question, if the first query returns no more than a single
row, you could assign the result values to variables for use in the second
query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE
@.registerno int,
@.subjectcode int
>
SELECT
@.registerno = registerno,
@.subjectcode = subjectcode
FROM dbp.mark;
>
SELECT *
FROM dbo.marksetting
WHERE
registerno = @.registerno AND
subjectcode = @.subjectcode;
GO
>
If the first query might return more than one row, you could store the
result in a temp table or variable for use in the second query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE @.results TABLE
(
registerno int,
subjectcode int
);
>
INSERT INTO @.results
SELECT
registerno,
subjectcode
FROM dbo.mark;
>
SELECT *
FROM dbo.marksetting
JOIN @.results AS r ON
marksetting.registerno = r.registerno AND
marksetting.subjectcode = r,subjectcode;
GO
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"meendar" <askjavaprogramm...@.gmail.comwrote in message
>
news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...
>
>
>
>
>

Quote:

Originally Posted by

Hi to all,

>

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>

Quote:

Originally Posted by

create procedure marks1
as
@. sql1 as varchar(50)

>

Quote:

Originally Posted by

@. sql1=select registerno ,subjectcode from mark;

>

Quote:

Originally Posted by

begin

>

Quote:

Originally Posted by

select * from marksetting where registern...@.sql1.registerno' and
subjectcod...@.sql1.subjectcode';

>

Quote:

Originally Posted by

end

>

Quote:

Originally Posted by

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

>

Quote:

Originally Posted by

Thanks in Advance.- Hide quoted text -

>
- Show quoted text -

Thanks Dan!

Indeed it was very helpful to me.|||On May 22, 3:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>
Why not use a single query? For example:
>
CREATE PROCEDURE dbo.marks1
AS
>
SELECT *
FROM dbo.marksetting
JOIN dbo.mark ON
mark.registerno = marksetting.registerno AND
mark.subjectcode = marksetting.subjectcode;
GO
>
To answer your question, if the first query returns no more than a single
row, you could assign the result values to variables for use in the second
query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE
@.registerno int,
@.subjectcode int
>
SELECT
@.registerno = registerno,
@.subjectcode = subjectcode
FROM dbp.mark;
>
SELECT *
FROM dbo.marksetting
WHERE
registerno = @.registerno AND
subjectcode = @.subjectcode;
GO
>
If the first query might return more than one row, you could store the
result in a temp table or variable for use in the second query:
>
CREATE PROCEDURE dbo.marks1
AS
>
DECLARE @.results TABLE
(
registerno int,
subjectcode int
);
>
INSERT INTO @.results
SELECT
registerno,
subjectcode
FROM dbo.mark;
>
SELECT *
FROM dbo.marksetting
JOIN @.results AS r ON
marksetting.registerno = r.registerno AND
marksetting.subjectcode = r,subjectcode;
GO
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"meendar" <askjavaprogramm...@.gmail.comwrote in message
>
news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...
>
>
>
>
>

Quote:

Originally Posted by

Hi to all,

>

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>

Quote:

Originally Posted by

create procedure marks1
as
@. sql1 as varchar(50)

>

Quote:

Originally Posted by

@. sql1=select registerno ,subjectcode from mark;

>

Quote:

Originally Posted by

begin

>

Quote:

Originally Posted by

select * from marksetting where registern...@.sql1.registerno' and
subjectcod...@.sql1.subjectcode';

>

Quote:

Originally Posted by

end

>

Quote:

Originally Posted by

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

>

Quote:

Originally Posted by

Thanks in Advance.- Hide quoted text -

>
- Show quoted text -

Thanks Dan!

Indeed it was very helpful to me.|||I'm glad I was able to help.

--
Dan Guzman
SQL Server MVP

"meendar" <askjavaprogrammers@.gmail.comwrote in message
news:1179837037.793991.296140@.b40g2000prd.googlegr oups.com...

Quote:

Originally Posted by

On May 22, 3:33 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>>
>Why not use a single query? For example:
>>
>CREATE PROCEDURE dbo.marks1
>AS
>>
>SELECT *
>FROM dbo.marksetting
>JOIN dbo.mark ON
> mark.registerno = marksetting.registerno AND
> mark.subjectcode = marksetting.subjectcode;
>GO
>>
>To answer your question, if the first query returns no more than a single
>row, you could assign the result values to variables for use in the
>second
>query:
>>
>CREATE PROCEDURE dbo.marks1
>AS
>>
>DECLARE
> @.registerno int,
> @.subjectcode int
>>
>SELECT
> @.registerno = registerno,
> @.subjectcode = subjectcode
>FROM dbp.mark;
>>
>SELECT *
>FROM dbo.marksetting
>WHERE
> registerno = @.registerno AND
> subjectcode = @.subjectcode;
>GO
>>
>If the first query might return more than one row, you could store the
>result in a temp table or variable for use in the second query:
>>
>CREATE PROCEDURE dbo.marks1
>AS
>>
>DECLARE @.results TABLE
>(
> registerno int,
> subjectcode int
>);
>>
>INSERT INTO @.results
> SELECT
> registerno,
> subjectcode
> FROM dbo.mark;
>>
>SELECT *
>FROM dbo.marksetting
>JOIN @.results AS r ON
> marksetting.registerno = r.registerno AND
> marksetting.subjectcode = r,subjectcode;
>GO
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"meendar" <askjavaprogramm...@.gmail.comwrote in message
>>
>news:1179827614.563723.255190@.b40g2000prd.googlegr oups.com...
>>
>>
>>
>>
>>

Quote:

Originally Posted by

Hi to all,

>>

Quote:

Originally Posted by

I just need to get two fields from a table and manipulate the results
in next query of a procedure.I planned to code like what you see
below,

>>

Quote:

Originally Posted by

create procedure marks1
as
@. sql1 as varchar(50)

>>

Quote:

Originally Posted by

@. sql1=select registerno ,subjectcode from mark;

>>

Quote:

Originally Posted by

begin

>>

Quote:

Originally Posted by

select * from marksetting where registern...@.sql1.registerno' and
subjectcod...@.sql1.subjectcode';

>>

Quote:

Originally Posted by

end

>>

Quote:

Originally Posted by

can it be possible to get the results as shown in the code? else
propose an alternative for this scenario.

>>

Quote:

Originally Posted by

Thanks in Advance.- Hide quoted text -

>>
>- Show quoted text -

>
Thanks Dan!
>
Indeed it was very helpful to me.
>

Getting Names of Tables and Fields

Hi All,

I need to get the names of tables and fields (or atleast the fields) from a database. Is there a query I can use that will return what all the db's tables and those tables fields are?

Thanks

LeeHere is the query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
SELECT INFORMATION_SCHEMA.COLUMNS.column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName'

Hope it helps.|||Cool, thanks. Is there an easy way to get all the foreign keys for a database?

I should have specified, SQL 2000.

Thanks

Lee|||If I remember right, you need join three information schema views to find all the foreign keys in a database, I am not aware if there is more easy to do it. If you need it I will find the query and post here.