Tuesday, March 26, 2013

[T-SQL] I think I already know the answer but...

[T-SQL] I think I already know the answer but...

I think I already know the answer but...

Posted: 26 Mar 2013 12:48 AM PDT

I suspect the answer is going to be "No" but thought I'd check with more experienced heads than my own.I have several third party applications that I'm supporting and cannot change the T-SQL they are using but I CAN optimize the hell out of their database if necessary. My question is this:There are several tables that are being hit with "Select * from..." and those queries return incredibly slowly. There are no indexes or primary keys on these tables, but there are a few candidates should that route be helpful. So the question is.. would an index or primary key speed up a select * statement? If so, why?

Extract Saturday through Friday as the weekly date range

Posted: 25 Mar 2013 05:40 AM PDT

I have data with attendance hours/dates for every day of the month/year. I need to create a query where I can select a range of dates, such as Jan. 1 2013 to March 31, 2013, but output should be a weekly total of attendance hours for each Saturday through Friday weekly range only within the specified beginning and end dates above. Can this be done?

Alpha Numeric number Generation

Posted: 25 Mar 2013 05:49 AM PDT

Hello, Need help with in writing a Stored Procedure for Auto Alpha Numeric Generation. If I pass an Alpha numeric in the format mentioned below , I should get next number based on the following logic: Format : It consists of 6 digit Alpha Numeric Code , where E is constant. For ex : E00001 SP should generate like this:E00001 to E99999 when it reaches to E99999 then 9 on left will be replaced with "A"- EA0001EA0001 - EA9999EB0001 - EB9999EZ0001 - EZ9999 when it reaches EZ9999 it should be incrmented to E0A001 Thanks, Nick

how to populate month value equally to all business days value and help me to write Stored procedure for below scenario?

Posted: 25 Mar 2013 11:08 PM PDT

Hi Every Body, i am strucked up in creating stored procedure.... My scenario is:I Have a table Sales_Month with values like this : State District Division Month_Number Total35 3 15 1 692.166635 3 15 2 692.166635 3 15 3 692.166635 3 15 4 692.166635 3 15 5 692.166635 3 15 6 692.166635 3 15 7 692.166635 3 15 8 692.166635 3 15 9 692.166635 3 15 10 692.166635 3 15 11 692.166635 3 15 12 692.166635 4 15 1 786.916635 4 15 2 786.916635 4 15 3 786.916635 4 15 4 786.916635 4 15 5 786.916635 4 15 6 786.916635 4 15 7 786.916635 4 15 8 786.916635 4 15 9 786.916635 4 15 10 786.916635 4 15 11 786.916635 4 15 12 786.916635 8 15 1 1318.7535 8 15 2 1318.7535 8 15 3 1318.7535 8 15 4 1318.7535 8 15 5 1318.7535 8 15 6 1318.7535 8 15 7 1318.7535 8 15 8 1318.7535 8 15 9 1318.7535 8 15 10 1318.7535 8 15 11 1318.7535 8 15 12 1318.7535 9 15 1 623.666635 9 15 2 623.666635 9 15 3 623.666635 9 15 4 623.666635 9 15 5 623.6666I want to create a store procedure to get output as State District Division Month_Number Total Business Days Sales per day Date35 3 15 1 692.1666 22 0.0000 1/1/2013 Holiday35 3 15 1 692.1666 22 31.4621 1/2/201335 3 15 1 692.1666 22 31.4621 1/3/201335 3 15 1 692.1666 22 31.4621 1/4/201335 3 15 1 692.1666 22 0.0000 1/5/2013 Sat'Day35 3 15 1 692.1666 22 0.0000 1/6/2013 Sun'Day35 3 15 1 692.1666 22 31.4621 1/7/201335 3 15 1 692.1666 22 31.4621 1/8/201335 3 15 1 692.1666 22 31.4621 1/9/201335 3 15 1 692.1666 22 31.4621 1/10/201335 3 15 1 692.1666 22 31.4621 1/11/201335 3 15 1 692.1666 22 0.0000 1/12/2013 Sat'Day35 3 15 1 692.1666 22 0.0000 1/13/2013 Sun'Day35 3 15 1 692.1666 22 31.4621 1/14/201335 3 15 1 692.1666 22 31.4621 1/15/201335 3 15 1 692.1666 22 31.4621 1/16/201335 3 15 1 692.1666 22 31.4621 1/17/201335 3 15 1 692.1666 22 31.4621 1/18/201335 3 15 1 692.1666 22 0.0000 1/19/2013 Sat'Day35 3 15 1 692.1666 22 0.0000 1/20/2013 Sun'Day35 3 15 1 692.1666 22 31.4621 1/21/201335 3 15 1 692.1666 22 31.4621 1/22/201335 3 15 1 692.1666 22 31.4621 1/23/201335 3 15 1 692.1666 22 31.4621 1/24/201335 3 15 1 692.1666 22 31.4621 1/25/201335 3 15 1 692.1666 22 0.0000 1/26/2013 Sat'Day35 3 15 1 692.1666 22 0.0000 1/27/2013 Sun'Day35 3 15 1 692.1666 22 31.4621 1/28/201335 3 15 1 692.1666 22 31.4621 1/29/201335 3 15 1 692.1666 22 31.4621 1/30/201335 3 15 1 692.1666 22 31.4621 1/31/2013Like that i want to display for all State-Ditrict-Divison Combination.If Date is either Holiday or weekend(Sat'Day and Sun'Day) then "salesperday" should be zero. I have created following view for DatesCREATE VIEW [dbo].[view1]AS SELECT Date_id ,DateKey,DayKey ,DateMonthId ,Year_Month_Id ,ClosingDateMonthId ,Day_Ind = 1 ,WeekEnd_Ind = CASE WHEN DATENAME(DW,DATEKEY) IN ('Saturday','Sunday') THEN 1 ELSE 0 END ,CASE WHEN Holiday_Ind = 1 AND DATENAME(DW,DATEKEY) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END AS Holiday_Ind FROM DimDate Note: from the above view we have to get business days and Holidays (including weekends). we have already another table which contains all the columns ([StateId] [int] NULL,[District] [Division] [Month_Number] [Total] [BuisDays][SalesPerDay] [Date] All columns has values except for (BusiDays,SalesPerday,Dates: these are nulls). I want update that table with "Busidays,salesperday,Dates" values from this query.Please! Please! Please! Kindly help me to create stored procedure. i am trying from morning onwards.

Return 1 record with data from multiple tables

Posted: 25 Mar 2013 12:44 PM PDT

All,I have data in numerous tables. The first 2 tables are simple:tblProducts-----------------ProductIDProductNameCategoryIDtblCategories----------------CategoryIDCategoryNameEach product links to a category. That part is easy as I can return the values I need from both tables for the ProductID passed in, like this:ProductID | ProductName | CategoryID | CategoryName--------------------------------------------------------------13 | Magnets | 1 | Misc.However, I have a third table that will always have 2 records for every Product:tblProductPricing----------------ProductPricingIDProductIDProductQuantity (int)ProductCostEvery product is sold in quantity of 1 and some other quantity (let's say 10). The cost for 1 is $1, the cost for 10 is $8 because there's a discount for buying in bulk.How can I get the details of the Product plus the 2 records from tblProductPricing in a single record, like this:ProductID | ProductName | CategoryID | CategoryName | ProductSize1 | ProductCost1 | ProductSize2 | ProductCost2--------------------------------------------------------------13 | Magnets | 1 | Misc. | 1 | $1 | 10 | $8I can't figure it out without seemingly terrible code and I'd like to know if there's an easy way to do this.Thanks,Mark

how to shows results from three queires to one please?

Posted: 25 Mar 2013 11:10 PM PDT

Hello AllI have below 3 sql queries which gives results on order by accounts, Now the question is how to shows 1strecord from first query, then after that second query results (if sameaccount no exists) then third query results (if same account no exists),so the expected results willshows like it is coming from single query but it will use 3 queriesNow rsults look like belowFirst Query Results103455,3000,'Clev',200,'14/3/2012','Sr Surgant Gen','16/12/2012','pention premium'103457,2000,'Silv',990,'13/3/2012','Cye Mers','17/11/2012','RollOver'103461,1400,'Clev',110,'23/3/2012','Prince Chau','12/11/2012','Savings'103462,5500,'Disl',450,'30/3/2012','NULL','12/10/2012','pention premium'103463,3770,'Goat',220,'17/3/2012','Manhodu Kim','19/11/2012','Benefits'103464,3890,'Perk',320,'26/3/2012','Silvra Gen','10/12/2012','pention premium'Second Query Results103455,'ZZZ',2000,5.54,'Y','14/3/2014','Active'103463,'YYY',3000,3.54,'N','17/3/2014','Active'103464,'XXX',5000,6.45,'N','16/5/2014','Active'103464,'XXX',4000,3.45,'Y','16/3/2014','Active'Third Query Results103455,'ZZZ',2000,5.54,'Y','14/3/2012','INActive'103457,'YYY',3000,3.54,'N','17/3/2012','InActive'103461,'XXX',5000,6.45,'N','16/5/2012','InActive'103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'expected Results is (just one results, break by acct No across the three queres as below)Expected Results (One results)103455,3000,'Clev',200,'14/3/2012','Sr Surgant Gen','16/12/2012','pention premium'103455,'ZZZ',2000,5.54,'Y','14/3/2014','Active'103455,'ZZZ',2000,5.54,'Y','14/3/2012','INActive'103457,2000,'Silv',990,'13/3/2012','Cye Mers','17/11/2012','RollOver'103457,'YYY',3000,3.54,'N','17/3/2012','InActive'103461,1400,'Clev',110,'23/3/2012','Prince Chau','12/11/2012','Savings'103461,'XXX',5000,6.45,'N','16/5/2012','InActive'103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'103461,'XXX',4000,3.45,'Y','16/3/2012','InActive'103462,5500,'Disl',450,'30/3/2012','NULL','12/10/2012','pention premium'103463,3770,'Goat',220,'17/3/2012','Manhodu Kim','19/11/2012','Benefits'103463,'YYY',3000,3.54,'N','17/3/2014','Active'103464,3890,'Perk',320,'26/3/2012','Silvra Gen','10/12/2012','pention premium'103464,'XXX',5000,6.45,'N','16/5/2014','Active'103464,'XXX',4000,3.45,'Y','16/3/2014','Active'Queries--------SELECT AcctNoGiaaBalanceOffice,totalbalItemDate ContactLastContactedPilPlanFROM SemerSouther order by AcctNoSELECT AcctNoVirtualName,Capbal,VadareRate,PilPlan,LoadDate, 'Active' StatusFROM TableClevland where LoadDate > GETDATE() order by AcctNoSELECT AcctNoVirtualName,Capbal,VadareRate,PilPlan,LoadDate'INActive' as StatusFROM TableClevland where LoadDate <= GETDATE() order by AcctNoPlease assist meThanks in advanceDhani

Reorder a custom order column

Posted: 25 Mar 2013 06:02 PM PDT

Hi,Been stuck on this for a little while and hoping that someone can help me figure out my logic problems.So my main issue is when i want to change the ordering and move and earlier number to a later number (Think numbers 1 to 8 and i want to move number 2 to the number 6 position)The other side works (moving 6 to 2).The initial column is only there so i can verify my code - it does not exist in the real table.There is only one order column in the real table.The changed column is where im hoping to see the correct reordering done.As this is required to work over multiple tables im hoping it would be suitable to convert into an iTVF or something similar.Some guidance on how to do it and then use it would be great.[code="sql"]IF OBJECT_ID('TempDB..#temp13','U') IS NOT NULL drop table #temp13select *into #temp13from ( select 1 "changed", 1 "initial" union all select 2,2 union all select 3,3 union all select 4,4 union all select 5,5 union all select 6,6 union all select 7,7 union all select 8,8) bdeclare @startNum as int = 1declare @oldnum as int = 3if @startNum < @oldnumbegin update #temp13 set changed = -1 from #temp13 where changed = @oldnum update #temp13 set changed = changed+1 from #temp13 cross join Tally where N >= @startNum and N = changed and N <= @oldnum update #temp13 set changed = @startNum from #temp13 where changed = -1endelsebegin --unsure what needs to go here --tried multiple things but none have worked so far endselect *from #temp13[/code]Expected output where @startNum < @oldnum (startnum 2 oldnum 4)changed initial1 13 24 32 45 56 67 78 8Expected output where @startNum > @oldnum (startnum 4 oldnum 2)changed initial1 14 23 32 45 56 67 78 8So after shuffling the numbers around i would also need to ensure that they are numbered 1 to x.If something isnt clear or more info is required please let me know and i will do my best.

Query help

Posted: 25 Mar 2013 12:09 PM PDT

[code="other"]I need a query to get the expected outputTable:Studentstguid stuName studwor stid------------------------ ----------------- ----------------- -----------642-4d5d-9af0-4c7a18dd ChrisName Chris 2554171-8655-2de255b88e08 ChrisCity SAN City 1792a0d-4100-bd1c-343882 ChrisCounty Wendy 17948f0-b455-5207b187e639 ChrisphoneNumber This is a test phone 1794d5d-9af0-4c7a18ddd7b2 ChrisDName WTS Test 1804041-ba50-1085acf7d86c ChrisDType This is for Dtpetest 180Expected output:ChrisName ChrisCity ChrisCounty ChrisphoneNumber ChrisphoneNumber ChrisDType---------- ------------ ------------- ----------------- ---------------- ---------- Chris SAN City Wendy This is a test phone WTS Test This is for DtpetestThanks for help in advance.[/code]

help with inventory of database logins and permissions

Posted: 25 Mar 2013 07:13 AM PDT

I ran this script to get a list of all database logins, but it does not give the same results as when I expand Database->Security-> Logins.SELECT name, type_desc FROM sys.database_principalsI've been tasked with inventorying names of all databases, logins, and corresponding permissions. Which will be best approach?

Splitting rows in HTML from sp_send_dbmail

Posted: 25 Mar 2013 06:45 AM PDT

Greetings,I would like to send a daily email that shows in HTML the changes of our staff from the previous day. We have a table called EmployeeHistory that includes records for changes to our Employee tables with the date of the change along with the EmpId, Name, etc.I have not done much work with HTML but I have the following code that selects records that have changed into a temp table called #History and then formats it into HTML and sends the email using sp_send_dbmail:--SelectCurr.EmpID as CurrEmpID,Curr.FirstName as CurrFirstName,Curr.LastName as CurrLastName,Curr.JobTitle as CurrJobTitle,Curr.DivisionID as CurrDivisionID,Curr.DepartmentID as CurrDepartmentIDCurr.DateChanged as CurrDateChanged,Prev.EmpID as PrevEmpID,Prev.FirstName as PrevFirstName,Prev.LastName as PrevLastName,Prev.JobTitle as PrevJobTitle,Prev.DivisionID as PrevDivisionID,Prev.DepartmentID as PrevDepartmentID,Prev.DateChanged as PrevDateChangedinto #Historyfrom EmployeeHistory as Currjoin EmployeeHistory as Prevon Curr.EmpID = Prev.EmpID ANDDATEDIFF(day,Prev.DateChanged, Curr.DateChanged) = 1 AND (Curr.FirstName <> Prev.FirstName orCurr.LastName <> Prev.LastName orCurr.JobTitle <> Prev.JobTitle orCurr.DivisionID <> Prev.DivisionID orCurr.DepartmentID <> Prev.DepartmentID)order by Curr.LastName, Curr.FirstNameDECLARE @HTML NVARCHAR(MAX);SET @HTML = N'

Staff Data changes

' + N'' + N'' + N'' + N'' + CAST ( ( SELECT td = CurrDateChanged, '', td = CurrEmpID, '', td = CurrLastName, '', td = CurrFirstName, '', td = CurrJobTitle, '', td = CurrDivisionID, '', td = CurrDepartmentID, '', td = PrevDateChanged, '', td = PrevEmpID, '', td = PrevLastName, '', td = PrevFirstName, '', td = PrevJobTitle, '', td = PrevDivisionID, '', td = PrevDepartmentID, '' from #History order by CurrLastName, CurrFirstName for XML PATH('tr'), TYPE ) as NVARCHAR(MAX) ) + N'
Date ChangedEmpIdLast NameFirst NameJob TitleDivisionDepartment
' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Gmail', @recipients = 'XXXX', @subject = 'Staff Data changes', @body = @HTML, @body_format = 'HTML' --For each employee that has changed since yesterday, I would like the current data formated onto one row of the HTML and the previous data formatted onto a second row.What is the syntax needed to separate this data into two rows? Thank you for your help.gmrose

PK insertion error

Posted: 25 Mar 2013 07:37 AM PDT

Hello everyone.I hope someone can confirm or deny my thinking here an offer some advice. Here is my issue.I do a daily load into a staging table.The staging table then loads the data into the main table.On successful completion the staging table is truncated at end of my SP ready for next load.Next day runs again , etc , etc.Now getting a error.error 2627 - PK violation.Violation of PRIMARY KEY constraint 'PK_DandD1'. Cannot insert duplicate key in object 'XXX.DandD'. The duplicate key value is (4714280, 1117455).I have a Composite PK which is made up of the 2 values listed.On checking the destination table I can see an entry already for that PK , so my insertion from the staging table fails.My questions are, and I hope i know the answer already , just need it sanity checked.1. If my insertion fails for 1 row, all insertions contained in the staging table will fail. ( the rest do not seem to voilate the PK)2. If I remove the 1 row in the destination table , all my subsequent insertions from staging table that do not voilate the PK will work ? - then investigate violation reasons etc.Does that make sense ?

Odd behavior

Posted: 25 Mar 2013 07:58 AM PDT

I have the follwing line generating a where condition for me in my dynamic sql statement[code="sql"](select cast(sys.all_columns.name as nvarchar) + ' = ''1900-01-01 00:00:00.000'' or ' from sys.all_columns where sys.all_columns.object_id = ac.object_id and sys.all_columns.system_type_id = 61 for xml path ('')) as Conditions[/code]which generates something like [quote]if exists (select top 1 * from [Address] where createdDT = '1900-01-01 00:00:00.000' or lastModifiedDT = '1900-01-01 00:00:00.000')[/quote]Seem good so far?If I change the code to a less than or equal to like this:[code="sql"](select cast(sys.all_columns.name as nvarchar) + ' <= ''1900-01-01 00:00:00.000'' or ' from sys.all_columns where sys.all_columns.object_id = ac.object_id and sys.all_columns.system_type_id = 61 for xml path ('')) as Conditions[/code]I get THIS in my now NON-Executable statement[quote]if exists (select top 1 * from [Address] where createdDT lt;= '1900-01-01 00:00:00.000' or lastModifiedDT = '1900-01-01 00:00:00.000')[/quote]What am I missing?

Problem with CAST to VARCHAR with SUBSTRING Function

Posted: 19 Jan 2013 01:24 AM PST

I'm haveing trouble with a simple CAST to VARCHAR Statement.[code="sql"]SELECT CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2))) = '98' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) WHEN LEFT(CAST(Customer.STARTDATE, 2) AS VARCHAR(2)) = '99' THEN CAST(substring(Customer.STARTDATE, 3, 2) AS VARCHAR(2)) END AS LossMoFROM Customer[/code]Any help would be greatly apreciated.

First Stored Procedure

Posted: 20 Jan 2013 11:33 AM PST

I have written my first stored procedure to try to pass a table name in a variable to SSRS.USE XXXXSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCreate Procedure link_table_sp@link varchar(50) outputASBeginset @link = (SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables where name like '%link%')ENDThen in SSRSEXEC link_table_sp('SELECT * FROM' + @link)then try to call it in SSRSEXEC link_table_sp('SELECT * FROM' + @link)But it says incorrect syntax near select.

Some guidance needed....

Posted: 25 Mar 2013 01:54 AM PDT

I am wondering how I should go about handling weighted by month calculations for a trending report. Currently, I just use (total/) * 12 to get the trending number.Info:I work at a law firm where all the attorneys have a target number for the year. Based on historical numbers by month, each month is weighted (the tendency is that they close more towards the end of the year so it is weighted heavier). As a stop gap (due to time limitations), I am going to use static numbers until I can create something to calculate the weight 'on the fly'.So my questions is, should I do the calculations in the expresion on report or with T-SQL in SELECT statement? How should I handle cumulative weighted total? These are the static percent numbers by month:(0.04, 0.06, 0.09, 0.08, 0.09, 0.08, 0.09, 0.09, 0.08, 0.09, 0.08, 0.13)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog