Datetime to hour 4

[TariqMehmod]

Sir I have this data

Group by hours

Or

First value of every hour

There will be single date ever time.

Please help

 

[bborissov]

Check DATEPART() function.

Borislav Borissov
VFP9 SP2, SQL Server

 

[TariqMehmod]

Sir, datepart() will reply only parts of date not group by clause.

 

[bborissov]

Did you try it?

Borislav Borissov
VFP9 SP2, SQL Server

 

[TariqMehmod]

yes sir

but when i use this

[pre]SELECT DATEPART(HOUR, DATE)as hours,reading from bin1_empty where batch=15 group by DATEPART(HOUR, DATE)[/pre]

it says

Msg 8120, Level 16, State 1, Line 1
Column 'bin1_empty.reading' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I want
10 - 805

 

[bborissov]

As in VFP you should put EVERY field that is not in Aggregate function in GROUP BY.
And here there is NO such thing like SET ENGINEBEHAVIOR.

Borislav Borissov
VFP9 SP2, SQL Server

 

[TariqMehmod]

 

[bborissov]

The result is right.
What do you want?

Borislav Borissov
VFP9 SP2, SQL Server

 

[TariqMehmod]

Sir I need only one entry against every hour.
Group by hour.
Please see topmost post.

 

[bborissov]

Then put Reading in some aggregation function and remove it from GROUP BY

Borislav Borissov
VFP9 SP2, SQL Server

 

[TariqMehmod]

Sir I am confuse what to do next?

 

[bborissov]

OK in the first column you want Hour,
what do you want in second one?

Borislav Borissov
VFP9 SP2, SQL Server

 

[TariqMehmod]

Sir in the second column there must be the top most value of every hour like shown in my first post of this thread.

 

[Andrzejek]

You should be able to easily add a calculated column to your output (Hour - Hr) like this:
[pre]

ID Date Qty Hr[blue]
1 2019-02-05 10:18:14.000 160 10[/blue]
2 2019-02-05 10:25:35.000 155 10
3 2019-02-05 10:45:37.000 150 10[blue]
4 2019-02-05 11:47:31.000 145 11[/blue]
5 2019-02-05 11:55:29.000 140 11
6 2019-02-05 11:59:59.000 135 11[blue]
7 2019-02-05 12:14:34.000 130 12[/blue]
8 2019-02-05 12:27:15.000 125 12
[/pre]
And I assume you have a PK on that table, like an ID, right?

So then there is an easy query to ask for a MIN(Date) Group By Hr and you can get the ID of that records, like 1, 4, and 7
Once you have those IDs, you can re-query your data asking only for those IDs...


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy,
Thanks for posting data that can be copied and pasted into a table. Images are sometime good but lack functionality.

Try this query:

SELECT Date, DatePart(hour,Date) as Hr, Qty
FROM bin1_empty A
WHERE Date = 
(SELECT Min(Date)
 FROM bin1_empty B
 WHERE DatePart(hour,A.Date)= DatePart(hour,B.Date))

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TariqMehmod]

Sir your codes work fine, but

Now I am trying to get data from two tables like this

[pre]SELECT Date, DatePart(hour,Date) as Hr, reading,batch
FROM bin1_empty A
WHERE Date =
(SELECT Min(Date)as date
FROM bin1_empty B
WHERE DatePart(hour,A.Date)= DatePart(hour,B.Date) and batch=16
union all
SELECT Min(Date)as date
FROM bin1 c
WHERE DatePart(hour,A.Date)= DatePart(hour,c.Date) and batch=16)[/pre]

but I am facing this error message

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How to overcome this.

Please

 

[gmmastros]

[red]Subquery returned more than 1 value. [/red]

This occurs because your subquery is returning multiple rows. Specifically in your where clause, you have this...

WHERE Date = 
([!]SELECT Min(Date)as date
 FROM bin1_empty B
 WHERE DatePart(hour,A.Date)= DatePart(hour,B.Date) and batch=16 
 union all
 SELECT Min(Date)as date
 FROM bin1 c
 WHERE DatePart(hour,A.Date)= DatePart(hour,c.Date) and batch=16[/!])

If you run the code highlighted in red, I guarantee you will get multiple rows. Your subquery has a "union all" which means that results from multiple queries will be combined in such a way that the result will have multiple rows. You can get a single row with a union all query, but only if one of the queries doesn't produce any rows.

Now, each query uses a min function, so you are probably getting 2 rows in the subquery. Your whole query will work if you can get your subquery to return just 1 row.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhookom]

TariqMehmod,
A couple suggestions:
[ul]
[li]In addition to screen shots, consider posting some data in text so it can be copied[/li]
[li]Try to provide all the requirements and conditions in your original question.[/li]
[li]Provide some basic context/story behind your question.[/li]
[/ul]

Apparently you have a couple bin locations

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016