Forum Replies Created

Viewing 15 posts - 16 through 30 (of 34 total)

  • RE: Store output in Tables of Store Procedure which gives OUTPUT in more than 1 DataSet (Table)

    As a work-around and I hope this helps but the key here is you must dump the result sets into a temp tables. In the stored procedure I usually use...

  • RE: Trying to get around using the MERGE statement

    Look up composable DML on this site/google. Something like this should work but would be a major help if we had sample data.

    INSERT @NewPolicyEventRiskUnits

    SELECT PolicyEventTrID,StagingPolicyRiskUnitTrID,PolicyRiskUnitTrID

    FROM( MERGE Policy.PolicyRiskUnit AS Target

    ...

  • RE: Generate Statistical Number Series

    We have a indexed table in our database called Tally but on the fly I usually just cross join the sys.all_columns tables multiple times. My solution is below.

    DECLARE @INT BIGINT...

  • RE: Crosstab or Pivot variant - stuck!

    Great point guys. Here is a better solution with dynamic sql.

    IF OBJECT_ID('tempdb..#basetable') IS NOT NULL

    DROP TABLE #basetable

    CREATE TABLE #basetable

    (

    caseid INT,

    formname VARCHAR(255),

    name VARCHAR(255),

    value VARCHAR(255)

    )

    DECLARE @sql VARCHAR(MAX)

    DECLARE @cols VARCHAR(MAX)

    DECLARE @joins...

  • RE: Find which rows have a column value that is less than the previous row's column value

    Try this too: DECLARE @tab TABLE

    ( ID INT , ColValue INT)

    ;

    INSERT INTO @tab (ID, ColValue)

    SELECT...

  • RE: Using a CASE statement...possible to Group By?

    Nevermind...my solution was wrong 🙁

  • RE: Crosstab or Pivot variant - stuck!

    I was bored but this should work:

    IF OBJECT_ID('tempdb..#basetable') IS NOT NULL

    DROP TABLE #basetable

    CREATE TABLE #basetable

    (

    caseid INT,

    formname VARCHAR(255),

    name VARCHAR(255),

    value VARCHAR(255)

    )

    DECLARE @sql VARCHAR(MAX)

    DECLARE @cols VARCHAR(MAX)

    DECLARE @joins VARCHAR(MAX)

    INSERT #basetable(caseid,formname,name,value)

    VALUES

    (1, 'Highways', 'roadname',...

  • RE: Japnese char in XML

    It is probably coming from the TVF dbo.fn_get_attrib_guid_from_xml. Have you stepped through this because I believe the rest of your code looks fine?

  • RE: Update using subquery help!

    This is getting good.

    I understand both sides of this argument. Now sure if there is an correct answer. 😉

  • RE: Many aggregates in one query

    I think you may end up with some type of union no matter what but here is my solution...Change DENSE_RANK to RANK if you need to.

    DECLARE @PRODUCTS TABLE (

    Product_id int

    ,ProductName...

  • RE: a very COMPLEX aggregation query

    Here is my final attempt...You probably want to re-check your test data and desired results to see if everything is sync'd properly.

    ;WITH CTE

    AS (

    SELECT

    c1.Case_ID,

    c1.Legal_Omn,

    c1.PPL_Area,

    Review_Status =

    CASE

    WHEN c1.Review_Status NOT IN...

  • RE: a very COMPLEX aggregation query

    Based on the explanation,testdata and your desired results...why would WA_TENANT be in the column for 30-60 days for "Active"? Look at your test data:

    UNION ALL SELECT 220, 'Civil', 'WA_Tenant', 5,...

  • RE: a very COMPLEX aggregation query

    After reading this thread I'm not even close to being clear as to what you want but here is my interpretation. If there duplicate review statuses for a ppl_area(tenant)...I choose...

  • RE: a very COMPLEX aggregation query

    Below is code using Grouping Sets SSRS 2008...1 table scan

    DECLARE @Case_Review TABLE

    (Case_ID int, Legal_Omn varchar(30),PPL_Area varchar(30),Revision int

    ,Review_Status varchar(30),Change_Date datetime);

    insert into @Case_Review

    SELECT 220, 'Civil', 'WA_Tenant', 1, 'Proposed', '2012-04-17 12:17'

    UNION ALL SELECT...

  • RE: DMV for Data Source Views

    Sorry, corrected the original post. DMV = Dynamic Management View

Viewing 15 posts - 16 through 30 (of 34 total)