|
Eddy Vluggen wrote: Not my downvote
I didn't think so.
Eddy Vluggen wrote: I appreciate sarcasm
As do I, but none was intended.
Eddy Vluggen wrote: hard to globalize
I don't see the problem, and I was making a general response to your general statement.
|
|
|
|
|
PIEBALDconsult wrote: I didn't think so.
I rather see a discussion than a mere anonymous judgement.
PIEBALDconsult wrote: As do I, but none was intended.
My apologies, but your statement does require some additional musings on your motivation. The downvote indicates that someone didn't understand your point of view, whereas they might have agreed if they could read why you take that standpoint.
PIEBALDconsult wrote: I don't see the problem
It's not "a problem"; it's an approach like many, with it's own merits. I don't like accented letters and spaces in a columnname (like Scheisse in German or Ohm in Hindi, because it messes with the report-generator and the ORM) I know that Sql Server can use long names, just like DOS moved from 8.3 to long filenames. That doesn't mean that other vendors' updated their products and also handle long names.
It's not hard to fetch a list of translated names. The endusers all have powerfull PC's that are mostly used for surfing, so I like to offload some of the work to the clients. The clients' PC knows well enough in what formatting the client his dates and doubles wants. There's also a chance that your data is being reused in multiple platforms; so, Sql Server would have to know whether it can use the full columnname "Phone number" or the abbreviated one "Phone" for the Mobile-platform.
I can also understand why you'd put as much logic as possible in your dataserver; give one the chance to update part of the logic by changing things in the server, without the need for recompilation of redistribution. You'll have another way of translating those headers; so either way it'll be a solved problem.
..now I'm curious to how you overcame said obstacles in Oracle.
I are Troll 
|
|
|
|
|
PIEBALDconsult wrote: Sure it is. You should have the SQL do as much as possible and avoid having to do work in code. That's one of the functions of views.
I would say that without qualification that is not true.
If I have a requirement that an Oracle proc should produce a report file then the point of that proc is in fact dedicated to correctly producing a format that represents a 'report'. So it must of course provide headers.
But if I am writing a java/C# application whose point is to produce a report then the java code should provide the format. The java/C# code should provide the headers. Not the SQL.
And for headers that require spaces it requires a a quoted identifier on a view. I would never do that. I would rather write a view to support the data constraints and provide additional functionality that provides the actual formatting.
|
|
|
|
|
jschell wrote: I would say that without qualification that is not true.
I'm not sure what you're saying -- are you saying I should qualify my statement? I said "should ... as much as possible", that's the qualification.
jschell wrote: producing a format that represents a 'report'. So it must of course provide
headers.
Exactly.
jschell wrote: if I am writing a java/C# application whose point is to produce a report
That's generally a rather poor scheme, unless the reports are very specialized and something like Crystal (  ) won't do. For most reports a general report engine is a far better technique. I do that to output XML of complex data.
I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable. I can add the ability to mine a couple of different ways.
|
|
|
|
|
My upvote, I like explanation 
PIEBALDconsult wrote: I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable.
Another good reason to do put formatted data and columnnames in the table. 
I are Troll
|
|
|
|
|
PIEBALDconsult wrote: That's generally a rather poor scheme, unless the reports are very specialized and something like Crystal ( ) won't do.
Presumably you are comparing the difference between creating a report engine versus using an existing commercial/free one.
In either case the engine itself contains the headers, while the data comes from the database.
PIEBALDconsult wrote: <layer>I wrote such a reporting system on my last job and there is one at my new job. Neither has the ability to alter the column headings so it must all be done in the query. The engine simply outputs the contents of a DataTable. I can add the ability to mine a couple of different ways.
I have written report engines and used several others including Crystal.
Whether the headers need to be modified (presumably by the user) has nothing to do with where they originate.
Nor does it have anything to do with what customizable features are presented to the user.
What is has to do with is separation of concerns. Your argument could lead to the supposition that one should use quoted identifiers for most tables because most tables will be used in a report and so one might as well start with the report header name.
|
|
|
|
|
I think you have pretty good justifications for handling the names in the query, so 5 from me.
|
|
|
|
|
Table [Sold]
ID PID Qty Date
-- --- --- ---------
1 1 8 jun 1, 2010
2 3 5 jul 1, 2010
3 3 2 aug 1, 2010
4 1 1 sep 1, 2010
5 3 4 sep 30, 2010
6 2 3 oct 8, 2011
7 2 5 nov 1, 2011
8 3 2 dec 1, 2011
9 1 8 jan 1, 2011
10 2 5 feb 1, 2011
Hi all,
I have difficulty composing a rather complex (for me) query. I would like to know the average qty sold of each PID per day based on the last 6-month data. If data is less than 6 month, then adjust accordingly.
So on the data above:
Today: Mar 24, 2011
6-month cut-off: Sep 24, 2010
I want to get:
PID Qty/day
--- -------
1 8/182 = 0.0440
2 (3+5+5)/168 = 0.0774 -> 168: because the oldest sale is oct 8
3 (4+2)/182 = 0.0330
Is there any way I can do this? Somebody help me, please? Thanks in advance.
|
|
|
|
|
select PID
,sum(qty)/(sysdate-min(date)) <<-- fixed this to account for sign.
from (
select PID
,qty
,date
from your_table
where date >= sysdate - 182
)
group by PID;
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]
modified on Thursday, March 24, 2011 9:13 AM
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
