Using a stored proc. I need a generic starting point. I am trying to get order categories ie. oranges, bananas, pears on the horizontal and then the daily totals vertically for each day of the month. Just say the # under ea is the total of boxes received daily. The total under each category would be determined by the receipt date. Also
I need a total category for each fruit under each as month-date and year-to-date. This should be updated daily. Obviously my actual prog is more complicated then this but I just need an idea where to start.
Product Oranges Bananas Pears Total Fruit
03/1/08 1 2 5 8
03/2/08 0 2 1 3
03/3/08 4 0 1 5
mtd
ytd
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
I am not a SQL Server expert, normally work with a few other databases. We are running SQL Serer 2008 R2. I need to determine by day how much log space is being used. This is needed so I can size the file system appropriately to handle an outage of our backup system. The goal would be to have the log file system large enough to handle 24 hours worth of logs. I have found statements for current log usage, but not one for daily total logs generated.
I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance
I'm trying to concatenate fields in SQL stored proc for use in textfield in asp.net dropdownlist. I'm running into a problem when I tryto use a DateTime field, but can't find the answer (so far) on theInternet. Was hoping someone here would know?My sql stored proc:SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As'SelectInfo'FROM tblAnomalyWHERE WorkOrder=@varWO AND Signoff=NullORDER BY DateEnteredbut I get the error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I'm not the brightest bulb on the block, so any clues greatlyappreciated!Thanks, Kathy
Hi, I need to list out all the stored proc for all the DB in entire server. My inpute parameter will be table name and column1/column2/column3...etc. Please give me your input for my request. Thank, Ravi
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
--Select Capital_Amount + Interest_Amount + Insurance_Amount + Admin_Fee --from Loan Where loan_no = '9110001AA667' --Select top 3* from loan
Select sum(Capital_Amount) As Capital_Amount from Sales Select * from Sales
How can l run these two Queries in the same view. l want to display what l have in the salesNew View and at the same time sum all the amount columns.But l'm not being successful. Is it achievable or l have to have two separate views?
CREATE View SalesNew AS SELECT DISTINCT tr.Transaction_Date, tr.Loan_No, n.Store, n.User_Issued, n.LoanBook As Company, p.Product, n.Capital_Amount, n.Interest_Amount, n.Insurance_Amount, n.Admin_Fee, n.Total_Amount FROM Transaction_Record tr INNER JOIN Loan n ON tr.loan_No = n.loan_No AND tr.loan_No = n.loan_No INNER JOIN Product p ON n.product = p.product
--2nd query
Select n.loanbook As Company,Sum(n.Capital_Amount) As Capital_Amount, Sum(n.Interest_Amount) As Interest_Amount, Sum(n.Insurance_Amount) As Insurance_Amount, Sum(n.Admin_Fee) As Admin_Fee, Sum(n.Total_Amount) As Total_Amount From Loan n Group By n.loanBook with RollUp
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:
this is what I have so far:
SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts FROM Accounts_Users GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined) ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)
How do I produce a column that shows the running total of accounts?
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example: ... .... Group1 Value 10 20 Sub Total 1: 30
Group2 Value 15 25 Sub Total 2: 40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.
Hi, I have a matrix with 2 row groups and 1 column group.
CGroup1 Val1
CGroup1 Val2
Total
- RGroup1 Val1
RGroup2 Val1
In
In
Out
RGroup2 Val2
In
In
Out
- RGroup1 Val2
RGroup2 Val3
In
In
Out
RGroup2 Val4
In
In
Out
Total
Out
Out
Out
I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:
=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?
Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?
I've created an SSRS report in Report builder and I'm displaying it in SharePoint 2010. What I would need to do is to sort according to my totals -column. My report structure is as follows:
The first and column second columns are the ID and the name of a customer. There are over 35.000 consolidated customers in the database.
The third column is the column which I would like my report to be sorted upon. It gives the sum (Amount_EUR) of all different productlines sold to the customer.
The fourth column in this report design model is the product groups sold to that customer. There over twenty of product groups visible when Running the report.
This is what my report looks like when finished (you'd have to scroll a lot to the right if I took the whole of the report so I cut only a part of it to be visible):
I tried to add a sorting option from the reports Tablix properties and adding this code as the sorting function:
=SUM(Fields!Amount_EUR.Value)
But I'm getting an error: "A sort expression for the tablix 'Tablix1' includes an aggregate function. Aggregate functions cannot be used in data row sort expressions."
Ok, so no aggregate functions aren't allowed to the sort.
If I just put:
=Fields!Amount_EUR.Value and sort by that from Z to A(biggest to largest) it doesn't have any effect. The report is still sorted alphabetically by the ParentID.
I've also tried some other code bits, but they all seem to be saying that I couldn't put an aggregate function into the sort expression....but my Total -column is already calculated with a Sum -function....
Is it possible sort my report based on that Total -column? If yes, how? I'm using SSRS 2008
I've created a Report Builder Column chart with months as the x-axis (category) and revenue as the y-axis (value). This correctly displays the revenue for each month, but I'd like to display a running total of the revenue for the year.
I'm currently displaying: Jan $2 Feb $1 Mar $4
I want to display: Jan $2 Feb $3 Mar $7
I know this is possibly using the RunningTotal formula in Reporting Services, but I'd like to do this in Report Builder so the users can change and create their own graphs with running totals. Is there a way to do this in Report Builder?
how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,
MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key
so the request is
need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below
1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1) then '2012 and 2013 accts UN Veriverted' 2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1) then '2012 and 2013 accts Veriverted' 3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1) then '2012 and 2013 accts NONVERT' 4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1) then '2012 last year accounts' 5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1) then '2012 This year accounts'
for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
for ex 2)
insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')
for ex 4) 2012 records there is not match acctno and name in 2013 recods
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
for ex 5) 2013 records there is no match of name and acct no with 2012 records
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013') insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013') insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013') insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')
Expected Results (just for format)
AcctTypeDescription,SumofShares, OtotalutStand '2012 and 2013 accts UN Veriverted',2700,234 '2012 and 2013 accts Veriverted' ,2890,234 '2012 and 2013 accts NONVERT' ,4533,325 '2012 last year accounts' ,2334,567 '2012 This year accounts' ,2222,877
Hi. I have records that have a status of either approved, not approved or unknown. I want to create a crystal report that shows a summary of the records - in short how many of each and what percentage. How can I do this? The approved, not approved and unknown are not number columns so I can't add them to get a total. I know I can create a cursor in T-SQL to get the counts but how do I send them back to the asp.net code? --------------------- Here is part of the cursor I have created. IF @@FETCH_STATUS = 0 BEGIN IF @txtPrintSuit = 'U' SELECT @numUnkCount = @numUnkCount + 1 IF @txtPrintSuit = 'Y' SELECT @numSuitCount = @numSuitCount + 1 IF @txtPrintSuit = 'N' SELECT @numUnsuitCount = @numUnsuitCount + 1
FETCH curs_Briefs INTO @txtPrintSuit END ------------ How do I get @numUnkCount, @numSuitCount and @numUnsuitCount back to the asp.net application? Can the RETURN statement hold more than one value?
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
How can I make this stored procedure concept work:
----------------------------------------------- Create Procedure MyProc @MyValue varchar(5) As Declare @ColumnName as varchar(11) Set @ColumnName = 'Price_' + @MyValue Select Sum(Price) As @ColumnName --???? From MyTable where Item = @MyValue Return -----------------------------------------------
Using @MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
