Query Function

Jan 16, 2008

Has anyone ever written an SQL (Select, etc.) function that could be placed in the App_Code folder of a project? I have a few web forms that have a couple dozen queries and I'm trying to build a good function to reduce clutter. The function I made (below) is in the App_Code folder and can be used by doing: Dim dr As SqlDataReader = GlobalFunctions.BuildSQLSelect("blah", "blah") in any one of my pages.

Public Shared Function BuildSQLSelect(ByVal ConnectionStringType As String, ByVal QueryString As String)
   Dim ConnectionString As String = Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(ConnectionStringType)
   Dim Connection As New SqlConnection(ConnectionString)
   Dim Command As New SqlCommand(QueryString, Connection)
   Command.Connection.Open()
  
   Return Command.ExecuteReader()
End Function

It works fine, but has one major flaw that prevents me from using it. I can't (at least I don't think I can) call Command.Connection.Close() once Return is hit and the function exits (especially since I still need to work with the DataReader).

Does anyone know of a better solution or know how to fix mine so I don't have tons of open connections floating around? Thanks!

View 2 Replies


ADVERTISEMENT

Query To Retrieve Max Price Without Using Max Function And Sub Query

Mar 5, 2014

ItemIDItemNamePrice

1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00

the above is the existing table and i need a query to retrieve max price with out using max function and sub query

View 6 Replies View Related

Function Vs. Sub-Query

Oct 19, 2004

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?

Bjorn

View 2 Replies View Related

To Use Function In A Query

Feb 15, 2006

hi
i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting..
chakri

View 14 Replies View Related

Function Query

May 8, 2008

i have function:


can anybody help me to figure this issue? how can i get that.
Thank you.

View 2 Replies View Related

Query With Function Won't Run

May 29, 2008

Hi...



I get the following error when trying to run this query in reporting services, but it executes perfectly in Management Studio, all I did was copy and paste:

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Incorrect syntax near '.'.

------------------------------
ADDITIONAL INFORMATION:

Incorrect syntax near '.'. (Microsoft SQL Server, Error: 102)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476





SELECT dv.product ,
dv.itemname ,
dv.u_vlgx_plc,
dv.shorted ,
dv.onhand ,
dv.po_num ,
t10.docduedate
FROM
(SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname AS t6_cardname,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE CONVERT(VARCHAR(8),t1.date_upld,3) = @Date
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
) dv

OUTER APPLY comparison.dbo.podatetest(dv.po_num) AS t10


GROUP BY dv.product ,
dv.itemname ,
dv.u_vlgx_plc ,
dv.shorted ,
dv.onhand ,
t10.docduedate,
dv.po_num ,
dv.t6_cardname
ORDER BY dv.u_vlgx_plc,
dv.t6_cardname,
dv.product




I've worked out that it doesn't like me passing dv.po_num through the table valued function. If I change this to a static value, rather than the result of the case statement further up, reporting services will run the query.



Any idea how I can fix this? Thanks!

View 2 Replies View Related

Aggregate Function In Sub-query

Jan 15, 2014

I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.

SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);

My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.

View 3 Replies View Related

Add Count Function To My Query

Dec 17, 2007

Hello,

I have created the following query... and need to get
the total records display for my report. I have tried
adding in the count(*) function to my select list, but I get
errors. Any help is appreciated.

SELECT
A.ParentSubjectName,
A.ParentSubject,
A.SubjectId,
B.CreatedOn

FROM dbo.Subject A
INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId

WHERE A.ParentSubjectName LIKE 'ACDelco Products%'

AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30')

AND A.SubjectId IN
(
'C44ADE3E-527B-DC11-8A2D-00170857BDE7',
'F8758E52-527B-DC11-8A2D-00170857BDE7',
'7E65F458-527B-DC11-8A2D-00170857BDE7',
'7F65F458-527B-DC11-8A2D-00170857BDE7',
'2BE35262-527B-DC11-8A2D-00170857BDE7',
'2AE35262-527B-DC11-8A2D-00170857BDE7',
'A2002127-527B-DC11-8A2D-00170857BDE7',
'41A8A66F-527B-DC11-8A2D-00170857BDE7',
'A3002127-527B-DC11-8A2D-00170857BDE7',
'D6C08B45-527B-DC11-8A2D-00170857BDE7',
'C439FB4B-527B-DC11-8A2D-00170857BDE7'
)

ORDER BY B.CreatedOn[/blue]

View 3 Replies View Related

Joining Function - Query

Feb 27, 2008

CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
(
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)
)

--Returns a result set that lists all the copds
AS
BEGIN
WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_
)

-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]
RETURN
END
GO

i m getting error that Incorrect syntax near the keyword 'WITH'.

can anyone tell me how to join functions in sql?

thanks.

View 4 Replies View Related

Count/sum Function Query

Mar 14, 2008

create function mytotalcount

(@audit varchar(50), @startdate datetime, @enddate datetime)

returns table

as

return

(

select t.value,sum(t.countvalue) as totalcount from
(

select

sm.value,count(sm.value) as countvalue

from subjectbase s
join stringmap sm
on s.organizationid = sm.organizationid

inner join audit a
on s.subjectid=a.subjectid

inner join incidentbase i
on i.subjectid=s.subjectid

where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and
sm.attributename = 'contractservicelevelcode' and
sm.ObjectTypeCode = 112

group by sm.value

) t

group by t.value

)

value totalcount
------------------
NHLBI Employee329
NIH Employee329
Public329
VIP329


instead of different values i m getting same...
there is something wrong in joins..can anyone help me?

thanks.

View 2 Replies View Related

Median Query Without Using Function

Feb 24, 2006

I have a table (cars) with 3 fields:VIN, Class, sell_price101, sports, 10000102, sports, 11000103, luxury, 9000104, sports, 11000105, sports, 11000106, luxury, 5000107, sports, 11000108, sports, 11000109, luxury, 9000i need to write a query that WITHOUT USING A FUNCTION will return themedian selling price for each class of car. result should look like:Class, Med_Priceluxury, 9000sports, 11000thanks to all u SQLers

View 4 Replies View Related

Query On Datediff Function

May 18, 2007

Hi,



I am trying to use DataDiff function and I have used the following queries:



1.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test

Expected Result: 0 milliseconds

Actual Result: 0 milliseconds



2.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test

Expected Result: 1 milliseconds

Actual Result: 0 milliseconds



3.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test

Expected Result: 2 milliseconds

Actual Result: 3 milliseconds



4.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test

Expected Result: 3 milliseconds

Actual Result: 3 milliseconds



5.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test

Expected Result: 4 milliseconds

Actual Result: 3 milliseconds



6.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test

Expected Result: 5 milliseconds

Actual Result: 6 milliseconds



7.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test

Expected Result: 6 milliseconds

Actual Result: 6 milliseconds



8.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test

Expected Result: 7 milliseconds

Actual Result: 6 milliseconds



9.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test

Expected Result: 8 milliseconds

Actual Result: 6 milliseconds



10.

select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test

Expected Result: 9 milliseconds

Actual Result: 10 milliseconds



Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.



Thanks,

Tim

View 1 Replies View Related

Function Help: Parameter Query

Apr 26, 2006

What SQL Function Criteria string replaces [forms]![myForm].[myField]?

I have a function that I want to pass criteria to from a drop down list. I tried using the same Access string in the Function but it does not work.

View 1 Replies View Related

How CREATE FUNCTION This Query

Feb 24, 2008

Code Snippet
Declare @DBName as varchar(100)
Declare @Query as varchar(8000)

SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue

Set @Query ='
SELECT
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice),
a.MoeenName_L1
FROM
ReciptItems LEFT OUTER JOIN
' + @DBName + '.dbo.Categories AS a
ON ReciptItems.acc_TopicCode = a.TopicCode
GROUP BY
ReciptItems.acc_TopicCode,
ReciptItems.acc_DetailCode,
ReciptItems.acc_CTopicCode,
a.MoeenName_L1'

Exec (@Query)




View 10 Replies View Related

Query Table-value Function From ADO In C++ (not .net)

Apr 22, 2008

Hey everybody!

I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function.
I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )

Can anyone direct me to an example, or write a small sample?



Thanks a lot in advance!!!

View 15 Replies View Related

How Do I Test A Sql Function From The Query Analyzer?

Mar 30, 2007

I have a SQL database that has a function that returns an id value from a table after you pass in a text variable. I would like to test this functionality in Query Analyzer but when I try to do it this way:
exec dbo.fnc_ORGUNIT_GetByName 'Dummy'
It just says 'Query executed successfully' without any resutls in the results pane. What am I doing wrong?
Thanks! 

View 2 Replies View Related

Convert Function Query Error

Jul 27, 2005

I am in the middle of creating an editable DatGrid:

Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs)
    AccessoryGrid.EditItemIndex = e.Item.ItemIndex
End Sub

Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs)
    e.NewValues.Add("@AccessoryID",
AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex)
("AccessoryID"))
    e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text)
    e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text)
End Sub

For some reason, I get an error message like this:
Server Error in '/' Application.

Disallowed
implicit conversion from data type nvarchar to data type smallmoney,
table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT
function to run this query.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException:
Disallowed implicit conversion from data type nvarchar to data type
smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use
the CONVERT function to run this query.

Source Error:





An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.







Stack Trace:




[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277






My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?

I have been trying to get rid of this error by trying to change the
field type within my database with no success, I keep on getting the
same error either way.

I would be very greatful if anybody can  help me.

View 2 Replies View Related

Aggregate Function - Tough Query For Me...

Oct 17, 2006

I currently have the following query:

Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers

This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.

Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.

[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.

Here is the query I started to build before I ran into serious trouble:

Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName

This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...

Have I done an okay job of explaining the problem?

The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.

Thanks for any advice!

Zoop

View 3 Replies View Related

Instr Function For MSQL Query

Jun 4, 2007

Hi All

My SQL is extremly rusted so I need some help with a very basic function. I have a character field which is built up using a category code + '-' + number. The problem I have is that the category codes are all different lengths and the items were added using 9 instead of 09. I'm trying to clean up the data so that the same item with e.g. category code DZ20 cannot be added as DZ20-1 and DZ20-01. How do I find the position of the '-' in the Query Analyser for MSSQL 2000?

View 1 Replies View Related

SQL Query - Using Result Of Create Function

Aug 24, 2004

I created a function that will return
from OpenDataSource('.....') tablename
where ... is fully populated.

However, I can't figure out how to use it?

For example

select functiona (parameter) as data_src

this returns the "from" statement above

I then try to run

select * data_src

So how do I reference the contents of data_src in the select?

Thanks for any help

View 1 Replies View Related

Append Query With Isnull Function

May 21, 2004

i'm trying to run an append query using data from 2 tables. i want to replace nulls with blanks ('') bellow is my statment. when i run this statment with out the iif(isnull)) statmentes the query works fine. is there another way of replacing my nulls with blanks.
Thank you,
Thomas

insert into tblcustomers (cusName, cusNumber, Active, cusContact, cusCrLimit,cusTerms)
SELECT dbo.tblCustomersIOA.CustomerName, dbo.tblCustomersIOA.Cust#,
dbo.tblCustomersIOA.Active,
iif(isnull(dbo.tblCustomersIOA.Contact),'',dbo.tbl CustomersIOA.Contact) ,
dbo.tblCustomersIOA.CreditLimit,
FROM dbo.tblCustomerNotesIOA RIGHT OUTER JOIN
dbo.tblCustomersIOA LEFT OUTER JOIN
WHERE (dbo.tblCustomersIOA.CountryID = 1) AND (dbo.tblCustomersIOA.StateID = 2);

View 1 Replies View Related

Query Needing Sum Function/group By?

Apr 24, 2008

select bicycle_shop.bicycle_shop_id, bicycle_shop.company_name, order_.unit_price
from order_ inner join bicycle_shop on order_.order_serial_number = bicycle_shop.order_serial_number
order by order_.unit_price DESC;

this query will bring up a few records for each shop, of the one or more bikes they bought and each bike's price, how do i have these come out to a sum(of the price), so that each bike shop will have a sum of revenue. someone told me i need to use group by and the sum function but i dont know how

View 5 Replies View Related

Adding Sum Function To Query To Sum 12 Records

Sep 23, 2014

I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:

IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C

select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB
INTO #TMP1C
from Vantage.dbo.SBB_BASE (NOLOCK)
where PRIN_SBB in (6000,7500)

[Code] .....

Not entirely sure what I am doing wrong, or why the numbers are coming back so high.

View 1 Replies View Related

Nested Query With Distinct Function

Jan 12, 2015

I need to know how many widgets are located at each factory.

I have a table called "Widgets". The pertinent column(s) are:

Factory UID

By using only this table I can group the results by the FactoryUID to get the answer. However, this table does not tell me the factory name.

I have a table called "Factories". The pertinent column(s) are:

FactoryUID
FactoryName

I can join these two tables by the FactoryUID. But I don't know how to write this query so that my results will look like the following table:

FactoryName Widgets
Factory1 100
Factory2 200
Factory3 300

View 6 Replies View Related

Query With A Big Function..advice...ideas...

Mar 27, 2007

Ok, im fairly new to SQL (real world use) uni wasn't all that, so im throwing out my idea for some feedback, and to see if it is at all possible.

I have 2 tables, 1 is called Hotels, and 1 is called Flights. I want...to list every Hotel, with price, availability date, and Flight date from a (GetDate()) function, up untill a date specified in a query.

At first this sounds easy. However... a hotel can be available for up to a month or two at a time, so at least 60 records for 1 hotel.

In this query i want a SUM of the Hotel price between the GetDate() date, and the end date specified in the query.

Can this all be done in 1 SQL query?

Thanks in advance for any input you may provide.

Gnub

View 16 Replies View Related

SQL Server2005 Query &#043;Date Function

Feb 5, 2008

I wrote a function and a SQL to get the 3 columns Date,Total Orders & Amount, for dates between Date Started and Date Completed if I pass different Dates in the SQL I get the correct result but if I pass same dates then I don't get the result I am looking for .

For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns.
But I give same dates for Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.

Some how I could not trace what could be the error in my SQL Function.

I appreciate if I could get some work around for this.

Thanks!



create function dbo.CreateDateList(@start datetime, @end datetime)
returns @t table ( [date] datetime )
as
begin
if @start is null or @end is null
return

if @start > @end
return

set @start = convert(datetime, convert(varchar(10), @start, 120), 120)
set @end = convert(datetime, convert(varchar(10), @end, 120), 120)

while @start < @end
begin
insert into @t ( [date] ) values (@start)
set @start = dateadd(day, 1, @start)
end

return
end

**********SELECT qUERY***********

SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol
FROM dbo.CreateDateList(@DateFrom , @DateTo) l
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101)
Union
SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol
FROM dbo.CreateDateList(@DateFrom , @DateTo) l
LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101))
WHERE StoreID=@StoreID
Order by Date

View 3 Replies View Related

Help Needed For Datediff Function For SQL Query

Apr 9, 2007

Hi Experts,I am working on SSRS 2005, and I am facing a problem in counting theno of days.My database has many fields but here I am using only two fieldsThey are Placement_Date and Discharge_DateIf child is not descharged then Discharge_Date field is empty.I am writing below query to count the number of days but is is notworking it is showing the error"The conversion of a char data type to a datetime data type resultedin an out-of-range datetime value."select casewhen convert(datetime,Discharge_Date,103) = '' thendatediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())elsedatediff(day,CONVERT(datetime,Placement_Date,103),CONVERT(datetime,Discharge_Date,103))end NoOfDaysfrom Placement_DetailsSo please tell me where I am wrong?Any help will be appriciated.RegardsDinesh

View 3 Replies View Related

Is It Possible To Set The Query Parameter By Giving A Function Name

Apr 14, 2008

I am trying to do a query similar to this:

SELECT * FROM TRANSACCTION_HISTORY WHERE TXN_DATE=@RepDate

For the query parameter @RepDate, I would like to pass a function to it. The function is a calculation of date based on today's date (e.g. Today() function to be simple). So that users don't have to type in the date every time.


Now the question is: is it really possible to do that in RS? Because I am getting a type conversion error, when I put Today() in the Define Query Parameters disalog box.

Thanks for helping!

View 1 Replies View Related

Query Function In Stored Proc... How To Do It??

May 10, 2007

how can i view all the records in my stored procedure???



how is the query function done??



for example i had my datagrid view... and of course a view button that will trigger a view action in able to view the entire records that i input....



i am not familiar with such things..



pls help me to figure this out..



thanks..



im just a begginer when it comes to this..



pls help me..



thanks..



View 1 Replies View Related

Input Parameter To Function In SQL Query

Mar 24, 2006

I am trying to use a Execute SQL task in which I call a query and get back a scalar value. I THINK I have it set up correctly, yet I am getting a very unhelpful error message of:

Error: 0xC002F210 at Determine Previous Trade Date, Execute SQL Task: Executing the query "SELECT[Supporting].[dbo].[fGetOffsetTradeDate](?, -1) AS [PreviousTradeDate]" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The Parameter Mapping has a single INPUT entry of data type DATE mapped to parameter 0.

The Result Set property (in General) is set to Single Row and there is a single entry in the Result Set config which maps [PreviousTradeDate] to a variable.

Odd thing is, if I replace the ? in the query with a date (say '03/24/2006') everything works fine. This would indicate that my query syntax is fine.

View 4 Replies View Related

Transact SQL :: How To Use Divide Function In Query

Jun 9, 2015

When I am doing the divide all values are showing Zero.

DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample

and also I want to show theĀ  QTR with %, for example, 66.57%

View 6 Replies View Related

Function Call In Dataset Query

Jul 19, 2007

Hello Guys,



I have a question that seems easy but I can not figure out...



Premise:

Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.



I have a Dataset that has a calculation for a column within a select statement



Query Pseudocode:



select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah")



Custom Code:



Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
if denominator = 0 then
return 0
else
return numerator/denominator
end if
End Function



How To use:



If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.



Problem:

How do I add this code reference in the following dataset select statement



select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from

(subquery"blah" )

Union

(Subquery"blah") table1











I tried to do this:

from this:

[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...

to this

[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...









But it did not work...gave me this error:



TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.

------------------------------
ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------








Help!



P.S.

this is a Matrix report and this select statement is within one of the datasets that fill a matrix.



View 8 Replies View Related

Analysis :: Subset Function In MDX Query

May 18, 2015

I need other function act like subset function using mdxquery.

In my mdxqueries,contain subset,order functions are available.

subset and order is getting poor query performance.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved