|
On this query, I get 2 sets of results for each part number, which is probably what I asked for.
I'm trying to get 1 set of results for each part number, I just need to figure out the proper method.
I'll try some critical thinking here, it can't be the group by clause, so perhaps I'm suppose to wrap the union in a select statement like my count in the post below? But then I have the distinct, and I don't want to mess that up.
Once I have this done, I think I'm done writing for these old account mate dos programs.
Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "
queryString +=
" UNION ALL "
queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO "
|
|
|
|
|
I think got it. The numbers look correct on a couple of test items.
I'm shocked that it works, doesn't really make sense to me how the inner select lines up with the main select.
queryString +=
" SELECT " & _
" DISTINCT FITEMNO " & _
", SUM(FSHIPQTY) " & _
", AVG(FCOST) " & _
", AVG(FPRICE) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST)" & _
" FROM " & _
"(" & _
" SELECT " & _
" h.FITEMNO " & _
" , h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" UNION ALL " & _
" SELECT " & _
" v.FITEMNO " & _
" , v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
")" & _
" GROUP BY FITEMNO "
|
|
|
|
|
Sort of a dumb question here, I have 2 DBF files, fox-pro DBF files that are identical, in which I want to union all and get the count of the FITEMNO, which there is 1 in the first file and 1 in the 2nd file, so I get a count of 2.
Is this normal behavior or did I construct the statement wrong below?
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
UNION ALL
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
)
|
|
|
|
|
That's what you asked for.
Suggestions:
0) Move the GROUP BY to the outer SELECT
or
1) Use SUM in the outer SELECT
|
|
|
|
|
OK, Thanks
That's what I thought.
|
|
|
|
|
Hi everyone,
I have a view with multiple outer joins and a on a lot of the fields I'm invoking a scalard functions.
The result is a very poor performance. I've been browsing the internet quite some time now (also read the article: Using Table-Valued Functions in SQL Server) but I can't seem to manage it on my own.
I know the difference between SVF and TVF and that's why I want to use TVF.
This is the Scalard function I want to recreate into a Table Valued Function:
The Shipping unit of my articles must be divided by 100 except for articles < 99999 or article 4859696
USE [Staging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Devide_Shipping_Unit]
(
@ValueString nvarchar(8),
@ArticleString nvarchar(8)
)
RETURNS Int
AS
BEGIN
DECLARE @result int
IF ISNUMERIC(@ValueString)<> 1 OR ISNUMERIC(@ArticleString)<> 1
BEGIN
set @result = 0
END
ELSE
BEGIN
if Cast(@ArticleString AS int) <= 99999 or @ArticleString = '4859696'
set @result = 1 --CAST(@ValueString AS int) / 1000
Else
set @result =CAST(@ValueString AS int) / 100
END
return @result
END
GO
Another scalar function is a conversion of dates to a numeric datatype:
ALTER FUNCTION [dbo].[CONVERT_DATE_TO_NUMERIC]
(
@dateValueString nvarchar(10)
)
RETURNS Numeric(8, 0)
AS
BEGIN
DECLARE @result Numeric(8, 0)
--DECLARE @dateValueString nvarchar(10) = '03/12/2013'
DECLARE @startdate nvarchar(10) = ''
-- set @dateValueString = '2013-07-23'
set @dateValueString = REPLACE(@dateValueString,'/','')
set @dateValueString = REPLACE(@dateValueString,'-','')
declare @_year nvarchar(4) = LEFT(@dateValueString,4)
declare @_month nvarchar(2) = SUBSTRING(@dateValueString,5,2)
declare @_day nvarchar(2) = RIGHT(@dateValueString,2)
set @startdate = @_year + @_month + @_day
IF ISNUMERIC(@startdate)<> 1
BEGIN
set @result = 0
END
ELSE
BEGIN
set @result = @startdate END
return @result
END
GO
Can anybody please help me I'm stuck 
Ambertje
|
|
|
|
|
Have you used SQL Profiler to identify the expensive parts of the query. It is much more likely you are missing indexes. Try that before dumping on the scalar functions.
A scalar that does not reference another database object (eg looks up a table) is not going to have a great deal of impact.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm working with Microsoft SQL Server Management Studio, I've searched for the profiler but can't find it.
Do you mean an SQL Server Profiler?
Can you give me an example or a link to where I can find that please?
This is what I get when I Set Statistics IO and Time ON:
Table 'Worktable'. Scan count 252, logical reads 20839, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AD'. Scan count 1, logical reads 109674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'D_Article'. Scan count 1, logical reads 729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'porcKD'. Scan count 1, logical reads 1473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 91947 ms, elapsed time = 119500 ms.
I wanted to create indexes on the View but a View doesn't allow to create indexes.
The view gets the data out of the bottom (STAGING) area in the form of text files.
The View is used to load the data through SSIS in TOP area (DWH)
No table in the Staging area contains indexes.
|
|
|
|
|
Ambertje wrote: a View doesn't allow to create indexes
Yes it does[^]. You just need to make sure the view is created WITH SCHEMABINDING.
However, to properly use an indexed view on any edition lower than SQL Enterprise, you'll need to use the WITH ( NOEXPAND ) query hint.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
My view contains aggregates, outer joins, subqueries so With Schemabinding wont work
|
|
|
|
|
Ambertje wrote: The view gets the data out of the bottom (STAGING) area in the form of text files.
Ah I thought you were doing something sensible, expecting performance from text files is not going to work.
I suggest you bulk copy the text files into staging tables, do attempt to apply transformations, and then use a stored proc to do the transforms from staging to your destination.
I would expect an order of magnitude performance gain with proper tuning of the staging indexes and procedure design. If the data is extreme you may want to look into dropping staging indexes before the insert and reimplementing them after import.
Doing transforms on the way in is a really lousy design, I know SSIS and all the tools do this but it is just wrong. Extract, Load, Transform
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try adding WITH SCHEMABINDING to your functions, between the RETURNS <type> and As lines.
http://www.sqlservercentral.com/blogs/sqlstudies/2014/09/15/i-schemabound-my-scalar-udf-and-you-wont-believe-what-happened-next/[^]
ISNUMERIC is not reliable. All of the following values will be considered numeric, but cannot be cast to an integer:
- ISNUMERIC('-')
- ISNUMERIC('.')
- ISNUMERIC('-$.')
If you're using SQL 2012 or higher, use the new TRY_PARSE function[^] instead. Otherwise, test that the string doesn't contain any non-numeric characters:
If @ValueString Like '%[^0-9]%' Or @ArticleString Like '%[^0-9]%'
Begin
set @result = 0
End
Assuming you're starting with a date or datetime / datetime2 type, your CONVERT_DATE_TO_NUMERIC function can be replaced with:
Convert(int, Convert(char(8), YourDateColumn, 112))
Passing 112 to the Convert function[^] formats the date as yyyyMMdd. Converting that to an integer gives the same result as your function.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm going to suggest something weird.
It looks as if you are using the scalar function to convert a single column into another. That could also be done before your query is executed, and that "might" save time. Might, as I haven't tested it 
Add the required int-column for the date, and run an update-query to do the conversion. Your table is now one column larger, but lost a calculation. You could keep the converted date consistent by updating them over a trigger when a record is inserted/modified. If you're not allowed to modify the original table, you can always create a new table and add the primary key of the original.
That way you can eliminate the need for the function completely, and move the calculation to the moment the records is inserted/updated. That delay may be hardly noticeable when manipulating a single record, while it adds up to a lot of calculations if it has to be done "on the fly".
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am making a stored procedure in "SQL SERVER 2008" for "item allotment" Which has a different items and has to assign to particular user.
What I am doing is, A table has a three columns 1-Total_item,2-Allotted_item and 3-Available_item , When i click the button ASSIGN it has to assign the item to particular User and the available item must have to be shown in Available_item column
Suppose---Before allotment
Total_item Allotted_item Available_item
10 0 0
8 0 0
Now After Allotment----
Total_item Allotted_item Available_item
10 1 9
8 2 6
I am going wrong Please help How could I make This happen
Thanks
|
|
|
|
|
How does your item table look like?
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
I HAVE 2 TABLES
TABLE 1 = "ITEM ALLOTMENT" it has initially "total_item" = 10 or some other value, "allotted_item" = 0 and "item_available" = 0
CONSISTS OF COLUMNS---
ITEM_NAME NVARCHAR(50)
TOTAL_ITEM--- INT
ALLOTTED_ITEM---- INT
ITEM_AVAILABLE--- INT
AND
TABLE 2 = ITEM it only stores the allocation details after clicking assign button
CONSISTS OF COLUMNS---
ITEM_ID (PRIMARY KEY)
PRODUCT_NO -- nvarchar(50)
ITEM_NAME -- nvarchar(50)
now after allotment details are filled in "ITEM" table that this "item_name" is allocated to this "product_no" and what is has to done in "item allotment" table is just after allotment its value must be updated that means column name
"allotted_item" value must be increased and in "item_available" column the subtracted value is have to shown example
If in "total_item" there is 10 items stored after allocation "allotted_item" value will be increased to 1 and subtracted value will be shown in "item_available" column
all these have to be done on assign button click. So, I that i am using Stored Procedure
Please help me out ,I am failing to which.
Thanks
|
|
|
|
|
What is problem you are facing?, You should use the Begin and end trans block and update both tables in the same block on click of assign button.
|
|
|
|
|
The Problem is in deceasing the value after assigning item.
|
|
|
|
|
What is the command/Query you are using to do so and what issue do you face.
|
|
|
|
|
This isn't normalized.
I'd suggest that you drop table1 and add an allotted column to table2 which contains to whom it's allotted (null or ID).
Then you use a query to get the state of allottments, such as:
SELECT ITEM_NAME
,Count(ITEM_ID) Total_item
,Count(Alloted) Allotted_item
,Count(ITEM_ID) - Count(Alloted) Available_item
FROM Table2
GROUP BY ITEM_NAME
Wrong is evil and must be defeated. - Jeff Ello
Any organization is like a tree full of monkeys. The monkeys on top look down and see a tree full of smiling faces. The monkeys on the bottom look up and see nothing but assholes.
|
|
|
|
|
I have a table with students' details and email address and I send email to them
I want to see the logs for each student if it was sent successfully or not. for example:
STUD_1 student1@email.com failed
STUD_2 student2@email.com sent
Can we do it like this? The idea is to get the sent_status from
sysmail_mailitems (or any other accurate/relevant source) for each student.
|
|
|
|
|
If I understand the question correctly, you can use a query like
SELECT mailitem_id,
recipients,
subject,
sent_status
FROM sysmail_allitems
For table description, see sysmail_allitems[^]
|
|
|
|
|
can we link sysmail_allitems to my student table? to see if the email was sent or not to a particular student?
|
|
|
|
|
If the students table contains the email address, then you can use that to check if the recipient is equal to student.email. If multiple recipients can be found ind recipients field then you can join using a string search with CHARINDEX function.
So either something like:
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE LOWER(sai.recipients) = LOWER(s.email)
or
SELECT ...
FROM sysmail_allitems sai,
student s
WHERE CHARINDEX(LOWER(s.email), LOWER(sai.recipients)) > 0
|
|
|
|
|
It's usually cleaner to use the ANSI JOIN syntax, so that you keep the join conditions separate from any filter conditions.
SELECT
...
FROM
sysmail_allitems sai
INNER JOIN student s
ON sai.recipients = s.email
Also, most databases don't use a case-sensitive collation, so there shouldn't be any need to use the LOWER function here.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
