|
Thanks a lot. Your answer pointed me to the answer I am looking for.
|
|
|
|
|
If there is only one entry for a PID and the sell was today, then date == sysdate
--> Division by zero ...
|
|
|
|
|
But good catch. Off the top of my head, it was the best I could figure out. Implementation details are left to the user to decide upon. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
But wouldn't this be worth a vote ...
|
|
|
|
|
Assuming you are using you are using sql server
SELECT
PID,
AVG(qty)/DATEDIFF (d,MAX(date), MIN(date))
FROM TableName
WHERE date > DATEADD(-6, GETDATE())
GROUP BY pid
The where clause may not be as accurate as you need.
I build this by breaking the requirements down into parts
Get the average and no of days for each pid
do the calc
Then I combined the 2 queries. Note this is untested code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks a lot. Your answer pointed me to the answer I am looking for.
|
|
|
|
|
Hi all,
I have two dates, from date and to date. i need to display only sundays between that days[IN SQLSERVER2008]
How can i do this. Please help and thanks in advance
Rakesh
|
|
|
|
|
One way to do this is to use a function which generates the dates between the range and then in where clause check if sunday is in date range using DATEPART function with weekday option. One example of generating the dates is mentioned in Using Table-Valued Functions in SQL Server[^], function DatesBetween. If yuo choose to use such approach the query could be something like:
SELECT ...
FROM DatesBetween(startdate, enddate) a
WHERE DATEPART(weekday, a.DateValue) = 1;
Note that the datepart result may be different in your environment depending on the SET DATEFIRST option.
|
|
|
|
|
The linked example is slow and horrible.
Multiple inserts into a table is no longer required on SQL 2008. Use CTE's.
|
|
|
|
|
CTE is one possibility. Curious to know what makes the example horrible? Note that the insert in the example isn't using a persistent table but a table-type which is quite different.
|
|
|
|
|
Quick and dirty trial. Not particularly scientific but gives you some idea. On my local instance of SQL Server, this script (note the long time frame to bump the execution time to something measurable):
DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'
;WITH dateRange (date)
AS
(
SELECT
@start Date
UNION ALL
SELECT
DATEADD(day, 1, Date) Date
FROM
dateRange
WHERE
Date < @end
)
SELECT * FROM dateRange WHERE DATEPART(WEEKDAY,date) = 1
OPTION (MAXRECURSION 32767);
returns immediately (<1sec)
Whereas this script:
DECLARE @start DATETIME = '1950-01-01'
DECLARE @end DATETIME = '2011-03-31'
DECLARE @dates TABLE (date DATETIME)
WHILE (@start <= @end)
BEGIN
INSERT INTO @dates VALUES (@start);
SET @start = DATEADD(day, 1, @start);
END;
SELECT * FROM @dates WHERE DATEPART(WEEKDAY,date) = 1
is still running 1min25seconds later after typing this message (Edit: Final execution time: 7m4s).
Check the actual execution plans for each to see why one is "horrible".
modified on Thursday, March 24, 2011 6:01 AM
|
|
|
|
|
Thanks for the info. Wondering why your test takes so long. I ran the same statements and both execution times were <1 sec... Also the plan seemed quite good... 
|
|
|
|
|
|
One possible reason could be if you have either statistics io or statistics time on in Management Studio. This would cause a flood in the messages tab since every insert is reported separately to Studio. This, of course, won't happen with CTE which is handled as a single statement thus reporting only one statistics.
|
|
|
|
|
On SQL 2008 you can use a Common Table Expression to generate a recursive list. You can then use the DATEPART function to select only sundays from this list:
DECLARE @start DATETIME = '2011-01-01'
DECLARE @end DATETIME = '2011-03-31'
;WITH dateRange (date)
AS
(
SELECT
@start Date
UNION ALL
SELECT
DATEADD(day, 1, Date) Date
FROM
dateRange
WHERE
Date < @end
)
SELECT * FROM dateRange
WHERE DATEPART(WEEKDAY,date) = 1 -- 1 is Sunday
|
|
|
|
|
Hi, I have two int columns in a table that have a sample data as below
and I need to update these columns with
1).If ColA has a non negative number then update ColB with 0 (zero).
2).If ColA has a negative number then update ColB with the same value
that ColA has.
Can some please help me with sql?
Thanks in advance.
L
-------------------------
ColA | ColB
-----------|------------
-1 -1
-1 -1
-3 -1
4 -1
-1 -1
7 -1
3 -1
73 -1
-3 -1
39 -1
32 -1
35 -1
3 -1
31 -1
3 -1
397 -1
400 -1
403 -1
406 -1
-1 -1
412 -1
436 -1
421 -1
421 -1
424 -1
-1 -1
430 -1
433 -1
-1 -1
415 -1
442 -1
445 -1
-1 -1
-3 -1
487 -1
523 -1
568 -1
472 -1
475 -1
478 -1
-3 -1
490 -1
493 -1
496 -1
502 -1
505 -1
511 -1
514 -1
517 -1
520 -1
-1 -1
526 -1
538 -1
541 -1
544 -1
556 -1
559 -1
550 -1
457 -1
574 -1
-1 -1
580 -1
583 -1
586 -1
589 -1
595 -1
625 -1
604 -1
625 -1
610 -1
-1 -1
622 -1
625 -1
-1 -1
-1 -1
637 -1
-1 -1
649 -1
652 -1
655 -1
658 -1
661 -1
664 -1
667 -1
670 -1
673 -1
-1 -1
685 -1
688 -1
691 -1
697 -1
700 -1
703 -1
709 -1
712 -1
715 -1
718 -1
721 -1
-1 -1
-3 -1
-1 -1
-1 -1
-1 -1
-1 -1
|
|
|
|
|
I would use two Update statements.
Begin Transaction
update MyTable
set ColB = 0
where ColA > 0
update MyTable
set ColB = ColA
where ColA < 0
By using a transaction you can review the number of rows affected and decide whether you want to
Commit or Rollback the transaction.
><david><
|
|
|
|
|
What if ColA == 0?
I may or may not be responsible for my own actions
|
|
|
|
|
One version is:
UPDATE TableName
SET ColB = CASE
WHEN ColA < 0 THEN 0
ELSE ColA
END;
Correction based on the good feedback by musefan:
UPDATE TableName
SET ColB = CASE
WHEN ColA >= 0 THEN 0
ELSE ColA
END
The need to optimize rises from a bad design.My articles[^]
modified on Wednesday, March 23, 2011 12:16 PM
|
|
|
|
|
I think you misread the requirement (even thou you got an accepted answer), ColB should be 0 for NON-negative numbers
I may or may not be responsible for my own actions
|
|
|
|
|
You're right for non-negative it's vice versa. I think the OP still got the idea. 
|
|
|
|
|
UPDATE table SET colB = DECODE( SIGN( colA ), -1, colA, 0 )
|
|
|
|
|
Reading excel file with OPENROWSET of sql server.
Working fine and getting values.
Now, if the file which i am reading is in use (opened), it gives error.
How should i capture this error, I tried
Declare @error varchar(50)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\webex\cis\CIS overview.xls',
'SELECT * FROM [CIS Overview$]')
IF(@@error <> 0 )
BEGIN
Set @error = 'Error reading file'
RETURN
END
But its not even reaching to IF clause,
terminate giving message
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
How can i capture this error ?
reagards
|
|
|
|
|
|
Any other Light ?
As i am using sql 2000 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
