July 3, 2007 at 12:16 am
HI All,
Can some one show me write this in better way ? lol took 40 min to run
select DATEADD(d, 0, DATEDIFF(d, 0, DATEADD(d, -(datepart(dd,s.date_shipped)-1), s.date_shipped)))as Date_Shipped,m.sku_code,m.sku_no,l.part_no,m.description,s.order_no, s.ext, l.location,s.cust_code,a.address_name, s.status,s.user_code,p.cat_first_rel as Release_Date,l.shipped - l.cr_shipped as Net_Qty,l.price,Sales =CASE s.type WHEN 'C' THEN isnull(l.cr_shipped * l.price * (1-(l.discount/100))*-1 ,0) WHEN 'I' THEN isnull(l.shipped * l.price * (1-(l.discount/100)) ,0)else ''endfrom orders s, ord_list l, pa_inv_attributes p, inv_master m, shippers sh, armaster awhere l.part_no = p.part_noand a.customer_code = s.cust_codeand l.part_no = m.part_noand s.order_no = l.order_noand s.ext = l.order_extand s.order_no = sh.order_noand l.order_no not in ('286877')and s.ext = sh.order_extand sh.line_no = l.line_noand s.status in ('T', 'R', 'S')and l.part_no not like 'FREIGHT%'and a.address_typ
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support
July 3, 2007 at 1:05 am
select DATEADD(d, 0,
DATEDIFF(d, 0,
(DATEADD(d, -(datepart(dd,s.date_shipped)-1), s.date_shipped))))
as Date_Shipped,
I took a look at your statement and cannot make any head or tail of what you are trying to achieve by using the DateAdd, DateDiff and DatePart functions so indiscriminately.
The innermost part extracts the dd value of the date and reduces it by 1 (effectively returning the previous day's value).
The second part reduces the shipped_date by this first value (effectively returning the first date of the month in most cases except where the shipped date is itself the first of the month)
The third and fourth parts are adding deadweight to the statement by calling DateAdd and DateDiff with arguments of zero.
What exactly are you aiming for?
No offence intended, but as my professor used to say, it is okay to bark up the wrong tree sometimes, but only if you are in the same forest.
Post back and we'll take it up further.
July 4, 2007 at 6:11 pm
HI,
Thanks, What i trying to do is sum all the Date_shpped by months
if date_shipped '2007-07-04 10:-1:467' like in SQL so i can't sum and want to make it like this
2007-01-01 00:00:000
I'm not good at explain sorrry.
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support
July 4, 2007 at 7:07 pm
This will convert the date shipped to the first day of the month at time 00:00:00.000
dateadd(mm,datediff(mm,0,s.date_shipped),0)
As for the run time, we really don't have enough information about your tables, indexes, constraints, amount of data, etc. to say why it runs so long. Also, the query you posted is cutoff at the end and so poorly formatted that it is really hard to read. Also, you should use that ANSI join syntax instead of doing joins in the where clause to make it easier to understand.
You should start by looking at the query plan.
July 4, 2007 at 11:38 pm
Asela,
Am I gettin you wrong OR are you actually trying to sum up dates? If you want to sum up some other numeric values by date or if you want to group your fields on a report by date then confirm and you will be assisted accordingly.
July 4, 2007 at 11:50 pm
As Michael said, it's impossible to say for sure what is causing the performance bottleneck without more information (although based on some guesses as to how things are structured, it's likely a combination of factors). As for the code formatting, use your own style, but please make it easy for people to read. I personally do something like this, but there are plenty of variations out there that are all easy to follow. People will be far more likely to help you if they don't have to format your code just to be able to read it.
SELECT
Date_Shipped = dateadd(mm,datediff(mm,0,s.date_shipped),0)
,m.sku_code
,m.sku_no
,l.part_no
,m.description
,s.order_no
,s.ext
,l.location
,s.cust_code
,a.address_name
,s.status
,s.user_code
,Release_Date = p.cat_first_rel
,Net_Qty = l.shipped - l.cr_shipped
,l.price,Sales = CASE s.type
WHEN 'C' THEN isnull(l.cr_shipped * l.price * (1-(l.discount/100))*-1 ,0)
WHEN 'I' THEN isnull(l.shipped * l.price * (1-(l.discount/100)) ,0)
ELSE ''
END
FROM
orders s
INNER JOIN ord_list l ON
s.order_no = l.order_no
AND s.ext = l.order_ext
INNER JOIN pa_inv_attributes p ON
l.part_no = p.part_no
INNER JOIN inv_master m ON
l.part_no = m.part_no
INNER JOIN shippers sh ON
s.order_no = sh.order_no
AND s.ext = sh.order_ext
AND l.line_no = sh.line_no
INNER JOIN armaster a ON
s.cust_code = a.customer_code
WHERE
l.order_no not in ('286877')
and s.status in ('T', 'R', 'S')
and l.part_no not like 'FREIGHT%'
and a.address_typ --- your code was cut off here
July 5, 2007 at 12:39 am
HI All,
Thank you sooooo much for all of you.
Cosy
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply