Friday, August 2, 2013

[T-SQL] Insert a Total Row Into a Dynamic Table For Reporting

[T-SQL] Insert a Total Row Into a Dynamic Table For Reporting

Insert a Total Row Into a Dynamic Table For Reporting

Posted: 02 Aug 2013 12:58 AM PDT

I am creating a table based on a dynamic PIVOT. This table is used for reporting, i'm emailing out the results using DB send mail. I need to add a total row to this table BEFORE I email. Here is the code that creates the table.-- Construct the full T-SQL statement-- and execute dynamicallySET @sql = N'SELECT Customer, ' + @selectcols +' into DailySalesTotalsFROM (SELECT Customer, sorderdate, isnull(qty,0) as QuantityFROM dbo.dailysales) AS DPIVOT(sum(quantity) FOR sorderdate IN(' + @cols + N')) AS P order by 2 desc;';EXEC sp_executesql @sql; I've thought about then doing a Insert into DailySalesTotalsSelect 'Total', 0,0,0,0,0,0,0but, then how to update the zeros to the column totals without knowing the column headers? Is there a way to loop and do that? Or reference ordinal position? Would love some help and suggestions! Thanks!

Linked server restirction

Posted: 02 Aug 2013 12:51 AM PDT

Hi friends,Is there a way to restrict linked server access for a particular login in sql server 2008r2.Thanks in advance.

IDENTITY_INSERT

Posted: 01 Aug 2013 10:51 PM PDT

Hi, when I try this query it gives me an error [i]An explicit value for the identity column in table 'DC1..DMAuditHistory' can only be specified when a column list is used and IDENTITY_INSERT is ON.[/i]DECLARE @projectId intSELECT @projectId = ProjectId FROm Project with(nolocK) WHERE ProjectNo = '61N24643'SET IDENTITY_INSERT DC1..DMAuditHistory ONINSERT INTO DC1..DMAuditHistory SELECT * FROM DMAuditHistory with(nolock) WHERE ProjectId = @projectId SET IDENTITY_INSERT DMAuditHistory OFF

How To Use IS NOT NULL in a Case Statement

Posted: 01 Aug 2013 05:38 PM PDT

Hi Guys, I'd like to know a correct way of using the query below in a case statement:and it.bitIsAsset = Case it.bitIsAsset When 1 Then it.intItemId IS NOT NULL Else 'All' End --it ia as Alias for my tableThis is used in the WHERE Clause, If bitIsAsset = 1 Then the intItemId mustn't be NULL Else 'All' but my query doesn't work.any help would be appreciated.Thanks Teee

need query help

Posted: 01 Aug 2013 10:15 AM PDT

Given the following table, create table highdollardx (dx char(10),ssn char(9), year int, paid money)and some sample rows like thisinsert highdollardx values ([034.0],[148569256],[2011], 115.3)insert highdollardx values ([075],[148569256],[2010], 113.58)insert highdollardx values ([078.10],[148569256],[2010], 75.71)insert highdollardx values ([110.1],[148569256],[2012], 500.76)insert highdollardx values ([173.21],[147400434],[2012], 0)insert highdollardx values ([173.21],[147400434],[2013], 1264.76)insert highdollardx values ([211.2],[148569256],[2010], 73.82)insert highdollardx values ([216.4],[148569256],[2012], 85.6)insert highdollardx values ([221.2],[148569256],[2010], 75)insert highdollardx values ([236.5],[147400434],[2010], 8.75)insert highdollardx values ([236.5],[147400434],[2011], 9.7)insert highdollardx values ([250.00],[148569256],[2012], 223.89)insert highdollardx values ([272.0],[148569256],[2011], 188.83)insert highdollardx values ([272.0],[148569256],[2012], 567.97)insert highdollardx values ([272.0],[148569256],[2013], 248)insert highdollardx values ([287.5],[148569256],[2011], 395)insert highdollardx values ([305.00],[148569256],[2011], 1142)insert highdollardx values ([309.0],[147400434],[2010], 520)insert highdollardx values ([309.0],[147400434],[2011], 300)insert highdollardx values ([338.18],[147400434],[2010], 267.75)insert highdollardx values ([346.10],[147400434],[2011], 61.94)insert highdollardx values ([346.10],[147400434],[2012], 46.44)insert highdollardx values ([346.90],[147400434],[2010], 141.01)insert highdollardx values ([346.90],[147400434],[2011], 129.58)insert highdollardx values ([346.90],[147400434],[2012], 76.22)insert highdollardx values ([366.14],[147400434],[2010], 81.68)insert highdollardx values ([366.16],[147400434],[2010], 46.27)insert highdollardx values ([366.16],[147400434],[2012], 46.27)insert highdollardx values ([368.8],[147400434],[2012], 0)insert highdollardx values ([368.8],[148569256],[2012], 123.82)insert highdollardx values ([372.14],[148569256],[2010], 82)insert highdollardx values ([380.23],[148569256],[2011], 481.97)insert highdollardx values ([380.23],[148569256],[2012], 93.8)insert highdollardx values ([380.4],[147400434],[2012], 0)insert highdollardx values ([401.1],[147400434],[2010], 12.13)insert highdollardx values ([401.1],[147400434],[2011], 45.19)insert highdollardx values ([401.1],[147400434],[2013], 21.69)insert highdollardx values ([401.1],[148569256],[2010], 64.46)insert highdollardx values ([401.1],[148569256],[2011], 653.15)insert highdollardx values ([401.1],[148569256],[2012], 86.45)insert highdollardx values ([401.9],[148569256],[2010], 165.85)insert highdollardx values ([401.9],[148569256],[2011], 64.46)insert highdollardx values ([461.1],[148569256],[2011], 99)insert highdollardx values ([461.9],[148569256],[2010], 82)insert highdollardx values ([461.9],[148569256],[2013], 159.51)insert highdollardx values ([462],[148569256],[2012], 121.05)insert highdollardx values ([465.9],[147400434],[2011], 46.04)insert highdollardx values ([473.2],[148569256],[2012], 823.1)insert highdollardx values ([477.9],[147400434],[2010], 178.57)insert highdollardx values ([477.9],[147400434],[2011], 398.31)insert highdollardx values ([477.9],[147400434],[2012], 911.96)insert highdollardx values ([493.90],[147400434],[2010], 244.34)insert highdollardx values ([520.6],[148569256],[2012], 91.33)insert highdollardx values ([595.0],[148569256],[2011], 86.45)insert highdollardx values ([595.9],[148569256],[2011], 84.45)insert highdollardx values ([600.00],[147400434],[2010], 62.25)insert highdollardx values ([600.01],[147400434],[2010], 9.31)insert highdollardx values ([600.10],[147400434],[2010], 139.05)insert highdollardx values ([600.10],[147400434],[2011], 45.19)insert highdollardx values ([600.10],[147400434],[2012], 37.7)insert highdollardx values ([600.10],[147400434],[2013], 23.03)insert highdollardx values ([611.72],[147400434],[2010], 80.92)insert highdollardx values ([611.72],[147400434],[2011], 217.47)insert highdollardx values ([627.4],[148569256],[2010], 77.72)insert highdollardx values ([695.89],[148569256],[2010], 30.6)insert highdollardx values ([696.1],[147400434],[2010], 45.19)insert highdollardx values ([696.1],[147400434],[2011], 26.18)insert highdollardx values ([696.1],[147400434],[2012], 53.13)insert highdollardx values ([698.8],[148569256],[2011], 30.6)insert highdollardx values ([702.0],[147400434],[2013], 174.68)insert highdollardx values ([702.11],[148569256],[2010], 45)insert highdollardx values ([709.2],[147400434],[2010], 152.36)insert highdollardx values ([709.2],[147400434],[2011], 43.65)insert highdollardx values ([709.2],[147400434],[2013], 135.93)insert highdollardx values ([709.8],[147400434],[2010], 100.52)insert highdollardx values ([715.15],[147400434],[2010], 70837.7)insert highdollardx values ([715.15],[147400434],[2011], 4783.06)insert highdollardx values ([715.16],[148569256],[2012], 169.45)insert highdollardx values ([715.94],[147400434],[2011], 213.86)insert highdollardx values ([715.95],[147400434],[2010], 85.96)insert highdollardx values ([715.96],[147400434],[2013], 0)insert highdollardx values ([717.2],[147400434],[2013], 94.75)insert highdollardx values ([717.9],[147400434],[2012], 74.61)insert highdollardx values ([719.45],[147400434],[2010], 289.81)insert highdollardx values ([719.45],[147400434],[2011], 171.69)insert highdollardx values ([719.45],[148569256],[2011], 792.49)insert highdollardx values ([719.46],[147400434],[2012], 86.43)insert highdollardx values ([719.46],[147400434],[2013], 244.72)insert highdollardx values ([721.3],[148569256],[2010], 149.02)insert highdollardx values ([721.3],[148569256],[2013], 61.45)insert highdollardx values ([722.10],[148569256],[2011], 10497.2)insert highdollardx values ([722.52],[148569256],[2010], 243.98)insert highdollardx values ([722.52],[148569256],[2011], 351.6)insert highdollardx values ([722.83],[148569256],[2012], 249.9)insert highdollardx values ([723.2],[147400434],[2012], 180)insert highdollardx values ([723.3],[147400434],[2010], 365.66)insert highdollardx values ([723.3],[147400434],[2011], 103.08)insert highdollardx values ([723.3],[147400434],[2012], 117)insert highdollardx values ([724.02],[148569256],[2010], 1653.34)insert highdollardx values ([724.02],[148569256],[2011], 17914.5)what Select Statement returns the dx with the highest paid amount for each ssn and year?iow, the result should be eight rows, four for each ssn and year, with the highest paid amount and the dx responsible for it.thanks a ton...going bald with it.

[HELP] FOR XML

Posted: 21 Jul 2013 09:16 PM PDT

Hi All,I need use SQL (for xml) to write in this customize format of XML as below:<track_n_trace tranid="1234" ordernum="ABC123"> <current_location>MY</current_location> <status code="OK" reason_code="FINE"> <remark>KUU1234</remark> </status> <consignment_number>A5555</consignment_number> <total_shipped overall_qty="0" box_qty="0"/></track_n_trace>

Find potential locks caused by joined queries

Posted: 01 Aug 2013 02:46 AM PDT

I have an application that is slowing down under a fairly heavy load and I believe some of my relational logic are creating locks that is creating a queue. Is there a way to use SSMS to analyze a query to determine what type of lock the server will attempt to place on the resources?

Grouping by columns to create single record

Posted: 01 Aug 2013 04:05 AM PDT

I am in a time crunch, many things on my plate today, and I just can't seem to get this problem, thought I would toss it out to the forum, and someone would easily see the answer.My Table looks like this:AttribID FormatID-------- ---------12 3415 3419 3425 3412 4915 4927 49I want to basically query the table like this: Give me FormatID when AttribID=12 AND AttribID=15 AND NOT AttribID = 27, and I want to get back one answer:34I know it is a grouping issue, but I am not getting the exact syntax, and am running out of time. Can anyone see the obvious solution that I am missing :angry:ThanksBrian

String Manipulation

Posted: 01 Aug 2013 07:44 AM PDT

Hello EveryoneI have a strange thing that I am trying to code for, and I am almost there, but I know there is a function.I have a string of text that I need to select, which is only the first word. The rest I do not care about. There is a blank space between the two words, there are only two words and I will only need the first word.Sample:Irvine StreetI need only the word "Irvine"I cannot, for the life of me think of the function name. I have tried substring, but that is not doing what I need.Thank you in advance for your comments, suggestions and assistanceAndrew SQLDBA

update records based on condition

Posted: 01 Aug 2013 05:48 AM PDT

hii have table , which can have millions of rows.Member primarycondition primaryintensity secondarycondition secondaryintensityM2345 hf 1M2345 COPD 1M2345 CAD 1M2345 dia 1M2345 Ast 1M2345 hf 2M2345 COPD 2M2345 CAD 2M2345 dia 2M2345 Ast 2what i need to do is if member has more than 1 condition, theni need to make 1 as a primary and another one secondary condition. with same primary intensitybased on this order hf COPD CAD dia Ast so the result will be Member primarycondition primaryintensity secondarycondition secondaryIntensityM2345 hf 1 M2345 COPD 1M2345 CAD 1M2345 dia 1M2345 Ast 1M2345 hf 2M2345 COPD 2M2345 CAD 2M2345 dia 2M2345 Ast 2i need to do it for all member,if member has more than 1 primary condition.any help?

Executing an SP in a new SPID?

Posted: 01 Aug 2013 04:43 AM PDT

Hi all,I'm coding a stored procedure which will be the control mechanism for a series of ETL subprocesses. This control procedure will run through several loops, monitoring various states, and executing these subprocess stored procedures when needed. The challenge is, I'd like to execute each of these subprocess stored procedures in their own session, so that they can run in parallel. I don't want the control procedure to wait for the completion of any of the subprocess stored procs.I think I could do this with xp_cmdshell calling SQLCMD, but that seems pretty kludgy, and I'd have to convince our admins to enable xp_cmdshell. I think I could also create, execute, and delete a SQL Agent job on the fly, but that seems even more kludgy. Is there a simpler way to execute a stored procedure in a new SPID?Thanks!

CTE

Posted: 01 Aug 2013 02:49 AM PDT

How do I nested common table expression?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog