I am using the below statement to get some dates grouped by date, in my SP.
SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week]
FROM dbo.test
LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID
WHERE (dbo.view1.[ACCOUNT ID] = @accountid
OR (dbo.view2.[ACCOUNT ID] = @accountid
AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @StartDate, 102)AND CONVERT(DATETIME, @EndDate, 102))
GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
i gave startdate as 1/1/2007 and endDate as 2/18/2007
i am getting the reuslt as
count year week
42 2001 32
2 2001 39
1 2001 51
1 2002 17
1 2002 19
106 2002 21
183 2002 22
226 2002 23
.................................
...................................
1208 2007 1
1292 2007 2
actually i should get only the last 2 rows.
Can anyone please point out, why i am getting the 2001, 2002 data? and how to fix that?
Hi, I have this stored procedure that gives me the result by week for the last 5 week.SELECT DateDiff(week,Datesold,GetDate()),SUM(Nb)FROM TSalesWHERE DateDiff(week,Datesold,GetDate())>=0 AND DateDiff(week,Datesold,GetDate())<=5 and project=136GROUP BY DateDiff(week,Datesold,GetDate())The only problem is if there is no data for one week, I am going to have only 4 result.I would like to 5 results all the time even if the SUM(nb)=0.I tried coalesce but with no success.Thank you
I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:
ContactNumType1 (From the Tables) File_Name1 (From the sp) File_Name4 (From the sp) File_Name3 (From the sp)
ContactNumType2 (From the Tables) File_Name2 (From the sp) File_Name7(From the sp)
ContactNumType3 (From the Tables) File_Name5 (From the sp)
ContactNumType4 (From the Tables) File_Name6 (From the sp)
File_Name10 (From the sp) File_Name8(From the sp) File_Name9 (From the sp)
So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?
------------------------------------------------------------------------------------------- Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc) - y axis; the cumulative number of orders
The chart needs two series:
Previous Week. The running count of orders placed that week. Current Week. The running count of orders placed this week.
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
- [URL] ... - [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
while(select MAX(wrh) from @tem1 where wrh = 0) < 1 begin update @tem1 set wrh = (select toaccount from @tem1 where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1) where wrh = (select max(wrh) from @tem1 where wrh = 0 ) and reportdate = (select min(reportdate) from @tem1 where wrh = 0) end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh 129029 Jan 201409:3019:15008:0009:20024:00065:54 129028 Jan 201409:0018:45008:0009:18016:00056:34 129027 Jan 201409:0018:45008:0009:18008:0009:18 129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end 129024 Jan 201409:1718:45008:0009:01040:00045:16 129023 Jan 201409:1918:46008:0009:06032:00036:15 129022 Jan 201409:1718:47008:0009:05024:00027:09 129021 Jan 201409:1618:35008:0008:46016:00018:04 129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...
In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????
I need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I am creating a set of reports in Crystal showing emails sent and delivered from users within our organisation.
Each report uses a different stored procedure.
Report 1 shows emails sent:
Sender: Person in our org Recipient: Person outside our org Number: number of emails sent
Report 2 shows emails recived:
Sender: person outside our org Recipient: Person in our org Number: Number of emails received
In crystal, the reports are grouped around the sender for report 1, and the recipient for group 2 (therefore the reports are grouped around the person in our organisation).
Now I need to create a report showing the details of the two reports combined into one, but this creates a problem when i try to group. i need to distinguish between who is in our organisation and who is it, and then to group by them.
does anyone have any idea how this may be done?
i want the report to look like this:
Person in our organisation: ----------------------------- Sent: bob@yahoo.co.uk 26 sally@hotmail.com 4 peter@msn.com 12 Subtotal: 42
I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.
The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK GO create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK ( @Datedatetime ) returnsvarchar(10) as /* Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. */ begin
declare @YearWeekDayOfWeekvarchar(10)
Select --Format to form YYYY-W01-D @YearWeekDayOfWeek = convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+ right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) + '-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1) from ( select YearStart = -- Case finds start of year case whenNextYrStart <= @date thenNextYrStart whenCurrYrStart <= @date thenCurrYrStart elsePriorYrStart end from ( select -- First day of first week of prior year PriorYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690), -- First day of first week of current year CurrYrStart = dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690), -- First day of first week of next year NextYrStart = dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690) from ( select --Find Jan 4 for the year of the input date Jan4= dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0)) ) aaa ) aa ) a
return @YearWeekDayOfWeek
end go
-- Execute function on first day, first day+60, -- and first day+364 for years from 1990 to 2030.
select DT= convert(varchar(10),DT,121), YR_START_DT = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT), YR_START_DT_60 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60), YR_START_DT_365 = dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364) from ( select DT = getdate()union all select DT = convert(datetime,'1990/01/01') union all select DT = convert(datetime,'1990/12/31') union all select DT = convert(datetime,'1991/12/30') union all select DT = convert(datetime,'1993/01/04') union all select DT = convert(datetime,'1994/01/03') union all select DT = convert(datetime,'1995/01/02') union all select DT = convert(datetime,'1996/01/01') union all select DT = convert(datetime,'1996/12/30') union all select DT = convert(datetime,'1997/12/29') union all select DT = convert(datetime,'1999/01/04') union all select DT = convert(datetime,'2000/01/03') union all select DT = convert(datetime,'2001/01/01') union all select DT = convert(datetime,'2001/12/31') union all select DT = convert(datetime,'2002/12/30') union all select DT = convert(datetime,'2003/12/29') union all select DT = convert(datetime,'2005/01/03') union all select DT = convert(datetime,'2006/01/02') union all select DT = convert(datetime,'2007/01/01') union all select DT = convert(datetime,'2007/12/31') union all select DT = convert(datetime,'2008/12/29') union all select DT = convert(datetime,'2010/01/04') union all select DT = convert(datetime,'2011/01/03') union all select DT = convert(datetime,'2012/01/02') union all select DT = convert(datetime,'2012/12/31') union all select DT = convert(datetime,'2013/12/30') union all select DT = convert(datetime,'2014/12/29') union all select DT = convert(datetime,'2016/01/04') union all select DT = convert(datetime,'2017/01/02') union all select DT = convert(datetime,'2018/01/01') union all select DT = convert(datetime,'2018/12/31') union all select DT = convert(datetime,'2019/12/30') union all select DT = convert(datetime,'2021/01/04') union all select DT = convert(datetime,'2022/01/03') union all select DT = convert(datetime,'2023/01/02') union all select DT = convert(datetime,'2024/01/01') union all select DT = convert(datetime,'2024/12/30') union all select DT = convert(datetime,'2025/12/29') union all select DT = convert(datetime,'2027/01/04') union all select DT = convert(datetime,'2028/01/03') union all select DT = convert(datetime,'2029/01/01') union all select DT = convert(datetime,'2029/12/31') union all select DT = convert(datetime,'2030/12/30') ) a
In Oracle database we can group our stored procedures in packets. It is very useful when we are creating big business solutions with hundreds stored procedures. Can we do something like this in SQL server?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing: SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @CategoryID", cn); However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say: SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @CategoryID", cn); The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :). Appreciate if someone could help,
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
