Simplify Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simplify Query

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 01:40:27

Guys,

I have my query.
Is there any other means to simplify this scripts.

Select
tb.esn_no,
Dateadd(day, datediff(day,0, tb.audit_date),0),
tb.supplier_id,
m.model_code,
tb.tlcs_po_no
into #data
From tb_batch_esn tb with (nolock)
inner JOIN model AS m with (nolock)
ON tb.model_id = m.model_id
Where tb.audit_date between('2010-11-02 00:00:00') and ('2010-11-30 23:59:59')
and tb.rma_type_id in (1,4,5,6,7,8,9)
Group by tb.esn_no,
Dateadd(day, datediff(day,0, tb.audit_date),0),
tb.supplier_id,
m.model_code,
tb.tlcs_po_no
Order by tb.esn_no
[Code]
[/Code]

Thanks.

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 02:04:17

why do you need the group by? I dont see you aggregating anything. Is the attempt to get distinct list of values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 02:14:16

There are some records with multiple transaction at the same time they have the same records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 02:15:13

is the requirement like out of them you want to return distinct set of values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 02:35:20

I can use also the distinct. i just want only if there is other simplified script assigh from this.

Here is my sample data.

ESN_NO---SUPPLIERID--MODELID--TLCS_PO_NO--RMA_TYPE_ID--AUDIT_DATE--
-----------------------------------------------------------------
00000472900 --2--8686--PH0049896--5--2011-05-17 21:37:08.187
00000472900 --2--8686--PH0049896--5--2011-05-17 21:37:08.343
000100001245690--2--5056--PH0050893--5--2011-06-29 20:42:04.493
000100001245690--2--5056--PH0050893--5--2011-06-29 20:42:04.493
000100001245690--2--5056--PH0050893--5--2011-06-29 20:42:06.113
000100001245690--2--5056--PH0050893--5--2011-06-29 20:42:06.113

I removed the timestamp of audit date using
Dateadd(day, datediff(day,0, tb.audit_date),0)

Thanks.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-01-11 : 02:46:08

Please do not answer this topic. its already done.
Thanks Visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 03:16:08