Saturday, March 9, 2013

[SQL Server 2008 issues] How to get Below T-SQL qury Output..?

[SQL Server 2008 issues] How to get Below T-SQL qury Output..?

How to get Below T-SQL qury Output..?

Posted: 08 Mar 2013 05:23 PM PST

[size="3"][font="Courier New"]Hi All,This is the query I have writtenDECLARE @FromDate DATETIMEDECLARE @EndDate DATETIMESET @FromDate = '2013-01-01 00:00:00.000'SET @EndDate = '2013-02-13 00:00:00.000'SELECT year(sd.FKDAT) As YEARWISE_DATA, sg.KUNNR As PARTY, sg.NAME1 As NAME, SUM(sd.FKIMG) As QUANTITY, SUM(sd.NETWR) As VALUE_IN_FC, SUM(sd.NTGEW) As WEIGHTFROM Sales_group sg WITH(NOLOCK)INNER JOIN SALES_DATA sd WITH(NOLOCK) ON sg.KUNNR = sd.KUNAGWHERE sd.FKDAT >= @FromDate AND sd.FKDAT <= @EndDateGROUP By sd.FKDAT, sg.KUNNR, sg.NAME1ORDER By 1, sg.KUNNR ASC[b]Below is the output i am getting,[/b]2013 HA010 ADK 360.000 36988.20 9206.4342013 HA010 ADK 205.000 31363.80 9299.8482013 HA018 AGRI 295.000 42646.25 12578.1492013 HA018 AGRI 119.000 29587.75 8816.1122013 HA018 AGRI 21.000 10289.65 2882.4882013 HA018 AGRI 249.000 57764.20 17605.415[b]Required Output I want[/b]2013 HA010 ADK 565.000 68352.00 18506.312013 HA018 AGRI 684.000 140287.85 41882.164Thanks & Regards,Bhushan[/font][/size]

query help

Posted: 08 Mar 2013 03:11 AM PST

Hi All,can any one help on below query declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)insert @tbl(ID,patientName,age,city,Mobile) select 1,'Ramesh',20,'HYD',12345678 union all select 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union all select 1,'Ramesh new',20,'HYDERABAD ' ,87654321 select * from @tbl TBLID ID patientName age city Mobile1 1 Ramesh 20 HYD 123456782 1 Ramesh 24 HYD 123456783 1 Ramesh new 20 HYDERABAD 87654321i want output as mentioned below format which columns data got changed Columns OLDDATA NEWDATApatientName Ramesh Ramesh newCity HYD HYDERABAD Mobile 12345678 87654321please prepare any dynamic query

Ensuring SARGable Stored Proc Search Parameter

Posted: 08 Mar 2013 05:08 AM PST

I am creating a query within a stored procedure and I am trying to create a parameter where I can pass a value to the query, but I want the parameter to be SARGable.The field is TIMES_TESTED SMALLINT and is either a 1 (first-time test taker) or NULL (retest). I don't have any control of the data in the table, but I have complete control over how the stored procedure works.So the lazy way to do this would be to accept a parameter for the stored procedure:[code="sql"] ... , @firstTime smallint = NULL -- NULL any record, 0 retests, 1 first-time[/code]And in my query's WHERE clause, use this abomination:[code="sql"] AND (@firstTime IS NULL OR ISNULL(TIMES_TESTED, 0)=@firstTime)[/code]So how could I improve this?

SSRS 2008R2 showing counties (not just states)

Posted: 08 Mar 2013 01:53 PM PST

I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way to get City>County>State rollup data somewhere or get shapefiles for Counties in the US? I'm working on a database for someone that would be infinitely more useful if I could show like a heat map for sales by county across the country... there's a really cool map [url=http://www.mssqltips.com/sqlservertip/2552/creating-an-ssrs-map-report-with-data-pinpoints/]here[/url] that shows the county lines in it... and that's the part I want - the Counties. The granularity of the data I have is not too good, so county-level or so is about right.Is there an easy way to create a map like that? (like a color-coded map from election night, but a county-by-county instead of state-by-state?) If so, how? And where would I get the shapefiles for the counties?Thanks!Pieter

Monthly balance from two tables

Posted: 25 Dec 2012 04:58 PM PST

I have following information with two tables[code="sql"]CREATE TABLE [dbo].[voucherCr]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix] [nvarchar](50) NULL, [voucherno] [nvarchar](50) NULL, [crparty] [int] NULL, [cramount] [float] NULL)[/code][code="sql"]CREATE TABLE [dbo].[voucherDr]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix] [nvarchar](50) NULL, [voucherno] [nvarchar](50) NULL, [drparty] [int] NULL, [dramount] [float] NULL)[/code][code="sql"]CREATE TABLE [dbo].[voucher]( [srno] [int] IDENTITY(1,1) NOT NULL, [vouchertype] [nvarchar](50) NULL, [voucherprefix] [nvarchar](50) NULL, [voucherno] [nvarchar](50) NULL, [dt] [date] NULL, [details] [nvarchar](255) NULL, [invoicetype] [nvarchar](50) NULL)[/code]Here between all three tables, vouchertype,voucherprefix,voucherno columns are inter related.Now I have following data for three tablesVoucher[code="sql"]srno vouchertype voucherprefix voucherno dt details invoicetype1 PURCHASE P 1 2012-12-25 RETAIL INVOICE2 PAYMENT R 1 2012-12-25 3 PURCHASE P 2 2012-12-25 RETAIL INVOICE4 PURCHASE P 3 2012-12-25 RETAIL INVOICE[/code]VoucherCr[code="sql"]srno vouchertype voucherprefix voucherno crparty cramount1 PURCHASE P 1 2 550002 PAYMENT R 1 1 550003 PURCHASE P 2 2 280004 PURCHASE P 3 2 30550[/code]VoucherDr[code="sql"]srno vouchertype voucherprefix voucherno drparty dramount1 PURCHASE P 1 4 550002 PAYMENT R 1 2 550003 PURCHASE P 2 4 280004 PURCHASE P 3 4 290005 PURCHASE P 3 97 11606 PURCHASE P 3 98 2907 PURCHASE P 3 46 508 PURCHASE P 3 66 50[/code]Now I want results as followsMonth Credit DebitDecember 168550 168550

Join two tables

Posted: 07 Mar 2013 11:22 PM PST

I have two tables with following dataTable1[code="sql"]Month dramount------ ---------April 1709.75March 5000[/code]Table2[code="sql"]Month cramount------ ---------March 6295[/code]I want to join two tables and want results as following[code="sql"]Month dramount cramount------ --------- ----------April 1709.75 NULLMarch 5000 6295[/code]How can i achieve this?

How to update same record that fires trigger?

Posted: 08 Mar 2013 07:25 AM PST

There is a flag column that basically indicates that record changed since the last "housecleaning".Can I create a trigger on the table that would set that flag "True" just for that particular record that changed?Thank you

Transaction log in Subscriber database for Transactional Replication

Posted: 04 Mar 2013 08:31 AM PST

I have created replication between two SQL Server 2008R2 databases and it has been functioning well since the initial setup 3 months ago. The publisher database is the back-end for an OLTP application with 50 users who are working constantly, making changes and add new information. The Subscriber database is used for reporting. Today I noticed that the Subscriber database was set to Full Recovery Mode with no transaction log backups. I panicked for a minute and took a quick look at the Transaction Log drive on the Subscriber server - it has 50% free space, 24 GB. I was amazed that I had been avoiding what normally would cause a drive-full issue.My question is this. Since replication is constantly making changes to the Subscriber database (updating various tables as they change in the Publisher database), why is the Subscriber log file not filling up and giving me grief since I don't run backups on the Subscriber database - and haven't done for the entire three months this has been running? Gail Shaw mentioned in another forum question while I was researching this, that 'Replication does not in any way use the Subscriber's log file'. I'm not sure that I understand the seeming contradiction been the updating going on in the Subscriber database, Full Recovery mode, no Transaction Log backups taking place and the Transaction Log not filling up.Can anyone enlighten me and/or point me to articles that might help me understand this more thoroughly??Thank you. Willem

Update field in dimDate table

Posted: 08 Mar 2013 05:47 AM PST

HiI have a dimDate table that I need to update a field DateFull = '1900-01-01 00:00:00.000' = '1900-01-02 00:00:00.000' etc.....I need to change to = '1900-01-02 23:59:59.000'ThanksJoe

Performance difference between LIKE and CHARINDEX?

Posted: 08 Mar 2013 06:02 AM PST

I have a T-SQL script that looks through the firstname column and modifies any firstname with and '&' anywhere in the value. It then blanks out the part of the name from the '&'.I know that LIKE '%&%' results in a table scan. I also know that I can check for a value of > 0 for CHARINDEX but suspect it will also perform a table scan.My question is whether there is some outside chance that one of the methods would be more efficient than the other.Thanks!

expression syneax for multiple where clause

Posted: 08 Mar 2013 02:18 AM PST

Hi all,I am trying to create the Expression equivalent of the following query:SELECT count(TasksName) FROM [db].[dbo].[testdetails] where TopLevelProjectName = '40 Project Folder' and TasksName = 'Project Info'I basically need a working version of this incorrect syntax:=CountDistinct(IIF(Fields!TopLevelProjectName.Value = "40 Project Folder" AND Fields!TasksName.Value = "Project Info"))Thanks in advance,Sharon

Validating trace file events

Posted: 08 Mar 2013 05:09 AM PST

Hello,If I have a trace running and populating some *trc files..My question is how can I validate that i am indeed tracing the correct sp_trace_setevent filters I have set ?Are there dynamic tables where I can fetch the information on what is being traced?Thank you !JR

View Must Return Nulls

Posted: 08 Mar 2013 12:13 AM PST

Hi ,I have a view called vw_TrusteeProductFilter that has a CTE that returns certain number of records.This View is being used in 55 store procedures.But,Now we do not want this view to retrieve any records . So,Instead of unchecking this view in all those store procedures.I just want the view to return nulls.Below is the viewALTER VIEW [Capacity].[vw_TrusteeProductFilter]AS WITH cteTrusteeCapReservations (TrusteeID, ProductTypeLookup, ProductClassLookup, FilterClassLookup,Count) AS ( SELECT ISNULL(BranchTrusteeID,LenderTrusteeID) AS TrusteeID, ProductTypeLookup, ProductClassLookup, FilterClassLookup, COUNT(*) AS [Count] FROM Capacity.tCapacityReservation WHERE CreationDateTime > CONVERT(DATE,GETDATE()) GROUP BY ISNULL(BranchTrusteeID,LenderTrusteeID), ProductTypeLookup, ProductClassLookup, FilterClassLookup UNION SELECT LenderTrusteeID, ProductTypeLookup, ProductClassLookup, FilterClassLookup, COUNT(*) AS [Count] FROM Capacity.tCapacityReservation WHERE CreationDateTime > CONVERT(DATE,GETDATE()) GROUP BY LenderTrusteeID, ProductTypeLookup, ProductClassLookup, FilterClassLookup ) SELECT trc.TrusteeID , trc.CapacityTypeLookup , 3 AS CapacityLevelLookup , CASE WHEN trc.ProductTypeLookup = 1 AND trc.ProductClassLookup = 1 THEN 1 WHEN trc.ProductTypeLookup = 1 AND trc.ProductClassLookup = 2 THEN 2 WHEN trc.ProductTypeLookup = 2 THEN 3 END AS HomeLoanProductTypeLookup , trc.FilterClassLookup , trc.ReferralCount + ISNULL(cte.Count,0) AS ReferralCount , trc.ReferralCapacity , CASE WHEN trc.ReferralCount + ISNULL(cte.Count,0) >= trc.ReferralCapacity THEN 1 ELSE 0 END AS IsCappedOut FROM dbo.tTrusteeReferralCapacity trc JOIN ( SELECT 6 AS CapTypeLookup FROM Filter.tCapacityHoliday a WHERE CapacityHolidayDate = CONVERT(DATE,GETDATE()) UNION SELECT 5 WHERE DATEPART(dw,GETDATE()) IN (1,7) AND NOT EXISTS (SELECT 6 FROM Filter.tCapacityHoliday a WHERE CapacityHolidayDate = CONVERT(DATE,GETDATE())) UNION SELECT 4 WHERE DATEPART(dw,GETDATE()) IN (2,3,4,5,6) AND NOT EXISTS (SELECT 6 FROM Filter.tCapacityHoliday a WHERE CapacityHolidayDate = CONVERT(DATE,GETDATE())) ) ctl ON trc.CapacityTypeLookup = ctl.CapTypeLookup LEFT JOIN cteTrusteeCapReservations cte ON trc.TrusteeID = cte.TrusteeID AND trc.ProductTypeLookup = cte.ProductTypeLookup AND trc.ProductClassLookup = cte.ProductClassLookup AND trc.FilterClassLookup = cte.FilterClassLookup WHERE ISNULL(trc.ProductTypeLookup,-1) IN (1,2) AND ISNULL(trc.ProductClassLookup,-1) IN (-1,1,2) AND ISNULL(trc.FilterClassLookup,-1) > 0 AND ISNULL(trc.[DayOfWeek],-1) = -1 AND ISNULL(trc.IsFixedFilter,-1) = -1 AND trc.PropertyState IS NULL AND ISNULL(trc.QFormSubTypeLookup,-1) = -1

partition_id VS. hobt_id

Posted: 02 Mar 2013 09:44 PM PST

Hello!There is 2 columns in [url=http://msdn.microsoft.com/en-us/library/ms175012.aspx]sys.partitions[/url] view - [b]partition_id[/b] and [b]hobt_id[/b]. From my point of view/experience for any given row from this view [b]partition_id[/b] always equal [b]hobt_id[/b] and I never see the 2 different values. It's seems reasonable, because (simplifying complex thing) we can say: "partition is hobt, hobt is partition". But at the same time article about another view - [url=http://msdn.microsoft.com/en-us/library/ms189792(v=sql.105).aspx]sys.allocation_units[/url] tell us:[quote]If type = 1 or 3, container_id = [b]sys.partitions.hobt_id[/b].If type is 2, then container_id = [b]sys.partitions.partition_id[/b].[/quote]So - these 2 values can ever be NOT equal?? As I said I had never see this in practical. Can you please give the example of table (and DB, if it needs), for which [b]partition_id[/b] and [b]hobt_id[/b] will be NOT equal?

How to create database from .mdf file only

Posted: 10 Jan 2012 04:55 AM PST

Hi,I have installed SQL Server 2012 and downloaded sanple database from the below link, which has AdventureWorks2008R2_Data.mdf for download.http://msftdbprodsamples.codeplex.com/releases/view/55330Now, how to restore AdventureWorks database from AdventureWorks2008R2_Data.mdf file?Thanks

Performance Tuning Question

Posted: 07 Mar 2013 07:48 PM PST

In trying to get a better grip on performance tuning I have a following example which I was wondering if people would comment on. The code is as follows:[code="sql"]Select 'All' as OpID , 'All Ops' as 'Op Name' , 1union allSelect Distinct LEFT(Oprid,4), Name + ' (' + LEFT(Oprid,4) + ')', 2from Dynamicsv5Realtime.dbo.RouteOprTable where dataareaid ='AJB'and Oprid between '3000' and '4000'and LEFT (NAME,3) <> 'DNU'Order by 3,2[/code]I have also attached the plan, and the index is below.[code="sql"]USE [DynamicsV5Realtime]GO/****** Object: Index [I_354OPRIDX] Script Date: 03/08/2013 10:41:13 ******/ALTER TABLE [dbo].[ROUTEOPRTABLE] ADD CONSTRAINT [I_354OPRIDX] PRIMARY KEY CLUSTERED ( [DATAAREAID] ASC, [OPRID] ASC)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY][/code]Is there anything that can be done to improve the query. What about the sorts and merge joins, can they be improved upon? How does the DISTINCT and LEFT function impact things, and should the index have an include for NAME?Thanks for any tips

Not consuming memory

Posted: 08 Mar 2013 12:37 AM PST

My company is rolling out a new product that was supposed to go to beta on March 1. That has been deferred, so the pressure is to get everything done yesterday.It is a complex multi-tiered application running web services, Citrix published apps, multiple databases and instances on a virtualized cluster. Sort of a kitchen sink of input sources. I had ZERO input on the database design and system architecture. So I'm having to learn the system in the middle of the problem.Which probably sounds familiar to most people here, no?The load test was focused on the web services so I was not allowed to capture any SQL statistics. I was only able to watch the defaults available through the Activity Monitor.The strangest thing during the test from the database end is that memory utilization peaked at 1.5 GB on an instance that had 28 GB assigned to it. Today we tested the instance with a few memory hogging scripts just to show that they were configured properly and, as expected, the memory was easily consumed.The load test had some interesting things happen. As the web requests loaded up the front end, the CPU climbed linearly - a nice direct correlation to the number of request hitting the web servers. But as soon as the CPU hit 25% it leveled off even as we doubled, tripled and quadrupled the number of web hits.More interesting is that there were two SQL instances in the test and when the CPU leveled off the waits displayed in the Activity Monitor started climbing up into the thousands. Even more curious is that the waits on each instance were inversely correlated. When one would peak, the other would be at a minimum in a very regular saw toothed pattern.So I have to recommend a "solution" without having any data since I wasn't allowed to pull any during the test.FWIW, disk I/O was fine. Today's memory test showed that the memory allocation of the instance is fine.My first recommendation is going to be to put all the databases on the same instance (there were two databases, one one each instance, that talked to each other a great deal) just to see how it effects the waits and the cross talk between those two databases.Then look at tempDB issues and insist that I be allowed to pull some performance counters DURING the test.I found the oscillation of peak waits very interesting. Has anyone ever seen this type of behavior?I'm not expecting any magic answers here. More just some possibilities so I can drill down into the lower levels.

How to update multiple tables in sql server 2008 ?

Posted: 07 Mar 2013 10:58 PM PST

Hi All,I want to update multiple columns from multiple tables in a single UPDATE Query...Just want to do like below query...UPDATE Table1, Table2SET Table1.Column1 = 'one' ,Table2.Column2 = 'two'FROM Table1 T1, Table2 T2WHERE T1.id = T2.idand T1.id = 'id1'Does Sql Server 2008 provide any mechanism to do so?If Sql Server 2008 provide such nice approach, please share some links with me!Thanks!

Database Mail

Posted: 07 Mar 2013 10:23 PM PST

Hi , How to delete the previously created database mail profiles through SSMS

REG: disk Space

Posted: 07 Mar 2013 06:51 PM PST

Hi, In my C: drive where my sql runs i have only 2 GB left.. I checked log spaces for all databases i found that my one of my production db is 123 GB in size and having a log space of 19 GB. Can i shrink that log file .

Executionlog2 table

Posted: 07 Mar 2013 08:13 PM PST

How much time Executionlog2 table and catlog table in ReportServer database hold the data?Does it store all historical data and it always remain in table or after some time of interval period sql server remove the data from both the table?????????????????

Versioning SQL Objects

Posted: 07 Mar 2013 07:12 PM PST

Good morning set-based thinkers.I wonder if you can help me. In my previous contract, as part of a DBA team, I helped maintain about 1800 databases over 97 servers, with thousands of stored procs.We had a slick release management process based on Visual SourceSafe, and we used the VSS tokens within the sprocs to embed the version info etc. into the comment block upon each check in.My next contract finds me somewhere that has zero release management. As part of my work I have proposed a system similar to the one I have experience with, as I know that it works.However, it has hit a stumbling block with my customer, because he has done thirty seconds of Googling and discovered that VSS is only on extended support until 2017. He's concerned that I'm proposing something with built-in obsolescence. I think that he has a point. Kind of. Four years is an eternity for the IT here. It would be ample time to establish a viable source code control system - and then start looking for an alternative that we can migrate to.But - my question is - am I missing a trick?I only know VSS. I know that TFS can't embed version info as it doesn't have keyword expansion. Or am I wrong?Can Git or Subversion do this?Will Red Gate source code control do this?What's [i]your [/i]solution?Mr. C
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog