Conditionally Extend Query With Clauses

Jan 14, 2008

Howdy folks, first time poster.

In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?


-- The basic query
SELECT column
FROM table
WHERE clause

-- Additional clause, only appended to query
-- if expression evaluates to true
IF @parameter <> default_value
BEGIN
AND additional_clause
END


Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?


-- The basic query

SELECT column
FROM table
WHERE clause

-- Additional clause, should only effect result
-- set when the parameter is not default_value
AND table.column =
CASE @parameter
WHEN default_value THEN
-- identity, table.column=table.column,
-- should have no effect other than just a long query
table.column
ELSE
@parameter
END

View 8 Replies


ADVERTISEMENT

Displaying SELECT Query Results Conditionally...

Apr 7, 2008


Hello all,

I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:

SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla
FROM tableX....
WHERE call_status=open.....


I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.

At the moment the SELECT query above gives me the following results:

Call Ref sla

10001 In SLA
10002 Breach
10002 In SLA
10003 In SLA
10004 In SLA
10005 Breach
10005 In SLA
... ...


What it should look like is this....

Call Ref sla

10001 In SLA
10002 Breach
10003 In SLA
10004 In SLA
10005 Breach
... ...


Please let me know if anyone has the answers or any clues to this! thanks.

View 5 Replies View Related

Extend Search Function

Mar 6, 2007

HelloIn my website I have a textbox to search some products, but it would like to extend my search-function so that it could find special characters. For example:When I search Mexico, I would like to find México. Is there an easy way to do this?Thanks in AdvanceWesley

View 1 Replies View Related

How Do I Extend The Size Of A Database?

Oct 4, 2007

I have a database that is in production. It is almost full. How would I go about extending the size of it? Would it involve an outage?

View 4 Replies View Related

IF THEN CLAUSES

Feb 23, 2006

I'm importing a text field that is called advertiserTYPE. It will be one of four options in the parenthesis below:

advertiserTYPE (FSBO,BROKER,DEVELOPER,REALTOR) VarChar(50)

I need to be able to construct a statement that says something like:

If advertiserTYPE = FSBO
Then INSERT INTO [COLUMN1]
If advertiserTYPE = BROKER
Then INSERT INTO [COLUMN2
If advertiserTYPE = DEVELOPER
Then INSERT INTO [COLUMN3]

Am I on the right track??

View 8 Replies View Related

Transaction Log Failed To Auto Extend

Sep 6, 2001

Hi

Had a weird error yesterday, can anybody shed any light on it.

I got the following error in a SQL 7(sp2) database.
'The log file for database 'IT2PROD' is full. Back up the transaction log for the database to free up some log space..
Error: 9002, Severity: 17, State: 2'

The Transaction Log has unrestricted file growth and auto grow file set and the disk has over 20gb of free space.

The only explanation I can think of is that the Log tried to extend during the hourly backup. Would this cause the above error ??? The backup file is usually about 80mb but was over 300mb when this happened.

Any ideas ????

Thanks in advance

Gary

View 4 Replies View Related

Is It Possible To &#34;extend&#34; Role/user Rights Using SP...

Jun 8, 2001

Hi All,

I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.

TIA,
Shamil

View 4 Replies View Related

T-SQL (SS2K8) :: Extend Script For Dependencies?

Jul 8, 2015

I found this script for dependencies and want to extend it:

I want to add the obj_typ (U, FN, P, V, TR...) for the dependency

How can I do this?

Here is the script:

WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel, obj_type )
AS
(
SELECT
o.[object_id] AS referenced_id ,
o.name AS referenced_name,

[Code] ...

View 3 Replies View Related

Extend Alter Procedure Command

Jun 27, 2007

Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?

Thanks,

David

View 9 Replies View Related

Extend Existing Report Items

Jun 6, 2007

Hi,

I'm totally new to SSRS2005 and I was wondering if I could extend the existing report items. For instance, I'd like to add a few extra properties to the textbox report item. I tried wrting a custom Textbox control that inherits from System.Windows.Forms.Textbox. When I imported the dll in VS, the new textbox showed up in the toolbox, but it was grayed out.

Any idea what I'm missing here?

Thanks,
Phil

View 3 Replies View Related

How Do You Extend The Expression Langauage With New Functions?

Jun 2, 2006

Hi,

I have a number of functions which I'd like to be able to add to the expression language to avoid having to use numerous script components, but I can't find any information on how this is possible. Can anyone provide any advice, please?

Thanks in advance,
Lawrie

View 1 Replies View Related

Multiple Where Clauses

Feb 21, 2007

Hello helpful people :)
 
I have this sproc.
ALTER PROCEDURE dbo.cis_UpdateCourseUserWithGrade
@Grade nvarchar,
@UaaStudentId nchar,
@CourseId int
AS
UPDATE cis_CourseUser
SET Grade =@Grade
WHERE UaaStudentId = @UaaStudentId
AND WHERE CourseID = @CourseId
RETURN
 the ANE WHERE isn't much liked.  How can I update the field with the grade where both of the conditions are true?  I'm getting an invalid syntax near @UaaStudentId
 
Thanks.
 

View 4 Replies View Related

Using ISNULL In WHERE Clauses

Apr 29, 2008

I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else? 

View 4 Replies View Related

Dynamic WHERE Clauses

May 19, 2008

So how do you do dynamic WHERE clauses with asp.net?
 I have a QueryString parameter with a value that will look like this.  |1|  or  |1||2|  or  |1||2||3|  or etc.  For each number in the value I want to add an EXISTS function to the WHERE clause.  I have accomplished this in T-SQL.  But I can't figure out how to bind asp.net control to it.  It gives me 'Invalid syntax near the keyword 'ORDER'.  I know this has something to do with .net not being able to exaluate the schema properly.
So I tried Linq to Sql.  Well, so happens that my WHERE clause is pointing to the existance of records in another table which the default install of Linq to Sql doesn't seam to support.  I came across this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2979081&SiteID=1) that explains how to dynamically query across multiple tables.  I tried this and got errors when trying to plug in the additional code.  And this still does not let me dynamically add where clauses.
 I tried the who Predicate thing and ran into the same problem where I can't reference the existance of values in foreign tables.
 Here is my sproc.  Can anyone tell me how to get this into a asp.net environment.  I also do NOT want to auto-generate fields in my GridView.ALTER PROCEDURE dbo.Item_SelectByFilters      @Item_Category_Id int = NULL,       @ItemSpecificValueIdList varchar(100) = NULL,       @debug bit = 0 AS DECLARE @sql        nvarchar(4000),        @paramlist  nvarchar(4000) SELECT @sql = 'SELECT     Item_Id, Number, Primary_Item_Category_Id, Secondary_Item_Category_Id, Engineering_Document_Id, CategoryIdList              FROM         Item              WHERE     (1 = 1)' IF @Item_Category_Id IS NOT NULL SELECT @sql = @sql + ' AND                                           Item.CategoryIdList Like ''|'' + CONVERT(varchar, (@xItem_Category_Id) + ''|''' IF @ItemSpecificValueIdList IS NOT NULL       BEGIN            WHILE @ItemSpecificValueIdList <> ''            BEGIN                  DECLARE @StartLocation int                  SELECT @StartLocation = CHARINDEX('-', @ItemSpecificValueIdList, 1)                  IF @StartLocation <> 0                        BEGIN                              DECLARE     @EndLocation int                              SELECT @EndLocation = CHARINDEX('|', @ItemSpecificValueIdList, @StartLocation + 1)                              IF @EndLocation <> 0                                     BEGIN                                          DECLARE @ValueLength int                                          SELECT @ValueLength = @EndLocation - @StartLocation - 1                                          DECLARE @ValueBetween varchar(10)                                          SELECT @ValueBetween = SUBSTRING(@ItemSpecificValueIdList, @StartLocation + 1, @ValueLength)                                          IF @ValueBetween IS NOT NULL SELECT @sql = @sql + ' AND                                           EXISTS(SELECT Item_Specific_Value_Id FROM Item_Specific_Value WHERE Item_Id = Item.Item_Id AND Item_Specific_Value.Item_Specific_Value_Id = ' + @ValueBetween + ')'                                    END                        END                  SET @ItemSpecificValueIdList = SUBSTRING(@ItemSpecificValueIdList, @EndLocation + 1, len(@ItemSpecificValueIdList) - @EndLocation)            END      END SELECT @sql = @sql + '                     ORDER BY Item.Number' IF @debug = 1   PRINT @sql SELECT @paramlist = '@xItem_Category_Id int' 
EXEC sp_executesql @sql, @paramlist, @Item_Category_Id
 

View 20 Replies View Related

Dynamic WHERE Clauses

May 27, 2008

Here is another project that seams to be the same as my previous post but a little simpler.  http://forums.asp.net/t/1263330.aspx 
My Item_Category table is self referencing.  I have a list of category Ids in a particular order representing the tree of categories from the current category to the top.  For example: 1 - Fasteners, 4 - Screws, 12 - Sheet Metal Screws  or the reverse.
I need to translate this so I can bind a cookie crumb type control to it.  ie.  Fasteners > Screws > Sheet Metal ScrewsI originally accomplished this like so;
Dim q = From ic In itemdc.Item_Category _Where valueId.Contains(ic.Item_Category_Id) _ Select Id = ic.Item_Category_Id, ic.Name But found that this does not order them properly.  Remember that the list of Ids is in a particular order.  I need a query to result to the below.  I was using a horizontal DataList control as the cookie crumb control.  Again, I am trying to do this with Linq to Sql without using Stored Procedures even though I am the DBA as well.  Or is there some other better way to do this?








Id
Name
Sort

1
Fastener
1

4
Screws
2

12
Sheet Metal Screws
3
Finally, as I mentioned in my previous post, I accomplished this concept with a stored procedure but then the asp.net controls could not recognize the schema, hence I could not bind the controls to the data source control.

View 2 Replies View Related

CASE And Where Clauses

Oct 28, 2014

I have an existing SPROC which works the way it should do and was difficult to construct. But now I need to add 1 more condition and hopefully that should be it however I am struggling how to do this. Here is a snip of the existing SPROC.

quote:
IF @columnName = 'Rating - Fire' OR @columnName = 'Rating - PPE' OR @columnName = 'Rating - Reactivity' OR @columnName = 'Rating - Health'
BEGIN
SELECT @totalRecords = (SELECT COUNT(p.[SID]) FROM S_Summary p INNER JOIN S_Detail detail ON detail.SID = p.SID
WHERE
CASE @columnName

[code]...

so this works fine but now I need to add 1 more thing..if a new parameter is supplied (lets call it @stringBranch), then I want to join another table and also match the param value to a field in that table along with any existing WHERE conditions being applied to this:

quote:
WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P

[code]...

View 9 Replies View Related

Multiple Where Clauses

Feb 23, 2006

I'm trying to use multiple where clauses but its not working. I want it to look something like this:

INSERT INTO [USCondex_Development].[dbo].[miamiheraldExceptions]([InvalidEmails], [InvalidAdPrintID], [InvalidPropertyStreetAddress], [InvalidPropertyPrice])
SELECT [AdvertiserEmail],[AdPrintId],[AdvertiserAddress], [PropertyPrice]
FROM [Development].[dbo].[table2]
WHERE advertiseremail is NULL
and WHERE adPrintID is NULL
and WHERE firstinsertdate is NOT NULL
and WHERE propertystreetaddress is NOT NULL
and WHERE propertyprice < 100

But it's not working.

View 7 Replies View Related

IF/CASE For WHERE Clauses

Nov 28, 2007

I am trying to wrap my WHERE clause with an IF or a CASE but cannot seem to get it to work. This is what I am trying:

WHERE
CASE
WHEN EVENT_TYPE='d' THEN
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000
ELSE
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 AND act_type.act_type_sc <> 'CLOSURE'
END
ORDER BY B.incident_id DESC, act_reg.act_reg_id DESC

Basically I want to run a different WHERE clause based on a value (EVENT_TYPE). The error message I am getting is:
Incorrect syntax near '='.

View 5 Replies View Related

Is It Possible To Extend SQL-TRANSACT With User-defined Function

Jun 20, 2000

View 3 Replies View Related

Could Not Find Enough Space On Disks To Extend Database

Mar 2, 2005

Hello,

I am managing a sqlserver 6.5 database in my company. I get the message that the datafiles should be expanded but whenever I try to expand it the following message appears:

Could not find enough space on disks to extend the database. Meanwhile, I have about 6 gigabytes free space on my disks. Please help me out.
Thanks,

Albert

View 2 Replies View Related

DB Design :: Steps To Extend Table Partitions

Jun 1, 2015

SQL 2014 I've inherited a db that has several partitioned tables.  They are partitioned by month.  We're approaching the last partition, 11-30-2015, so we need to extend the tables.  My question is how do I do this?  There are Partition Schemes and Partition Functions setup at the db level.  I've figured out how to ALTER those.  Next I go to a table that I know is partitioned, right-click Storage and select Manage Partitions. 

My only option is to "create a staging table for partition switching".  Not knowing what switching is, I'm not sure if this is what I want to do.  All I want to do is add new partitions to the table - and remove some of the old ones since they are empty due to archiving of data.

So, what is the proper steps to adding new partitions to a table that is already partitioned?

View 4 Replies View Related

How To Setup Table Boarder Extend To The End Of Page?

Nov 7, 2007

Hi all,


Does anyone know how to setup the "table details" to take up all the space in a page (extend the boarder to the end of the page) even there is only one record in the table details?

Normally, for example, if you got a table in a report, if the table has only one record, the boarder of table will only take up maybe 1/4 of the page space (header, 1 line details, footer). if the table has 5 records, it may take up half of the page, but the boarder of table neven extend to the end of the report and i wwant it happen!!!!!!! Thank you.

Cheers,
Bryan

View 8 Replies View Related

How Can I Extend The Number Of Characters In MS SQL For Text Entry?

Oct 27, 2006

Using MS Access as a front end and SQL as a back end how can i get past the 4000 character limmit per table?

View 9 Replies View Related

Complex 'order By' Clauses

Apr 11, 2007

I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)I thought I'd be able to do something like:ORDER BY (price != 0), pricethinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.any suggestions?thanks! 

View 1 Replies View Related

Is Is Possible To Have Multiple WHERE Clauses In SELECT?

Dec 4, 2007

If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:


SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING
FROM content_hits_tbl

Can't you have multiple WHERE clauses in the SELECT statement?

Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...

I'm using VS05 connecting to a SQL database.

Sincerely,
Tommy


View 3 Replies View Related

SQL Server 2012 :: Counting With Where Clauses

Sep 18, 2014

Below is a script that count the amount of Void properties we had in a specific month.

SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'

[Code] ....

It tells me I have 53 total voids.

What I also want is a column next to this to say how many of those voids back in April are STILL Void.

So basically the WHERE clause would still be the same -

WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'

but with the added -
AND HIST.[END-DATE] IS NULL

So ideally I'm after two columns with figures in them and then going forward I can then calculate other months as well.

View 5 Replies View Related

Subquery Totals Based On Clauses

Aug 1, 2013

I have a Master table with a OrderNbr which is also contained in the Detail table.

It's a 1 to Many relationship, respectively.

I want to update the MASTER.FinalizedDate using a "select top 1 FinalizedDate order by FinalizedDate DESC" from the Detail table but the clause is ALL the Status have to be "F". So OrderNbr 12345 should not get updated because it contains a 'O'. OrderNbr 67899 should get updated in the Master table to 2/26/2013 because all have a 'F' and the last date to post is the official finalized date.

Here is what I came up with.......so far, but not sure how to work in the Status piece on 1 to M.

The rub here is that even if one row has the 'O' status I want to ignore the update. If all have the 'F' then I want the udpate to happen.

Update MASTER
Set FinalizedDate = (select top 1 d.FinazliedDate from Detail d
where m.OrderNbr = d.OrderNbr
and d.Status not in ('O')
Order by FinalizedDate DESC)
From MASTER m

How do I not include all 3 rows for OrderNbr 12345 because one row has the Status "O" in the DETAIL table?

Here are the table looks........

MASTER
OrderNbr Ytotals Ztotals Xtotals Finalized Date
12345$1,500$1,500$1,200
67899$1,200$1,100$900

DETAIL
OrderNbrItemNbr PriceStatusFinalized Date
1234563453453 $1,400F1/2/2013
12345554444 $1,500F1/2/2013
12345545444 $2,200O NULL
67899333334 $899F2/24/2013
678993434344 $659F2/24/2013
67899434676 $499F2/26/2013
6789978888 $599F2/24/2013

View 3 Replies View Related

Error In Group By And Distinct Clauses

May 2, 2008



hai,
i'm using a table in sql.the name of register. the table datas are

FirstName LastName UserName Pwd dob
krishna murthy ckm0006 asdfg 1985-04-01 00:00:00.000
krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja fdd ddd ddd 1985-01-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000

i want the result as:[group by or distinct by FirstName && order by dob]

FirstName LastName UserName Pwd dob

krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000

i tried many queries i'm getting error Like this


Column 'register.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

can anyone resolve my problem...

View 5 Replies View Related

Multiple Clauses In A CASE Statement

Jan 12, 2008



Hi
I'm not sure if I have stated my subject line correctly for what I want to achieve, but I will attempt to explain it below.

In addition to what I have in my script below, I also need to include the following clauses:

1. where TransPerPaySequence.FinancialYTDCode like '2007', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72, it needs to be 35; and

2. where TransPerPaySequence.FinancialYTDCode like '2008', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72 or 35, it needs to be 31.

I would really appreciate any assistance that can be provided.

Thanks


SELECT DISTINCT
pc.PositionClassificationCode, pc.Description AS positionclass, pg.PositionGroupCode, pg.Description AS positiongroup, p.Description AS position,
e.PreferredName + ' ' + e.LastName AS employeename, SUM(ha.Quantity)
/ ((CASE p2.PositionGroupCode WHEN 'AC' THEN 72 WHEN 'AL' THEN 75 WHEN 'EX' THEN 80 WHEN 'MG' THEN 80 WHEN 'SM' THEN 80 END) *
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%'))) AS FTE,
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence AS TransPerPaySequence_1
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%')) AS payseq
FROM HistoricalAllowance AS ha LEFT OUTER JOIN
Position AS p ON ha.PositionCode = p.PositionCode LEFT OUTER JOIN
PositionGroup AS pg ON p.PositionGroupCode = pg.PositionGroupCode LEFT OUTER JOIN
PositionClassification AS pc ON p.PositionClassificationCode = pc.PositionClassificationCode LEFT OUTER JOIN
WAP ON ha.WAPCode = WAP.WAPCode LEFT OUTER JOIN
Employee AS e ON ha.EmployeeCode = e.EmployeeCode LEFT OUTER JOIN
Position AS p2 ON e.PositionCode = p2.PositionCode LEFT OUTER JOIN
TransPerPaySequence AS tpps ON ha.PaySequence = tpps.PaySequence
WHERE (e.EmployeeCode IN ('83', '739')) AND (ha.AllowanceCode IN ('005', '201', '203', '101')) AND (tpps.FinancialYTDCode LIKE '2007%')
GROUP BY pc.PositionClassificationCode, pg.PositionGroupCode, pc.Description, pg.Description, p.Description, e.PreferredName, e.LastName,
p2.PositionGroupCode

View 8 Replies View Related

Join 2 Selects And Distinguish 2 Where Clauses

May 8, 2008

hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.

SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID
FROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)=100))

SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)<>100))

View 3 Replies View Related

Transact SQL :: Updating Table Rows With Overlapping Dates (extend)

Dec 2, 2015

This question is extension from the topic Updating table Rows with overlapping dates: [URL] .....

I am actually having a table as following:

Table Name: PromotionList

Note that the NULL in endDate means no end date or infinite end date.

ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0

And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.

Flag = 1, success merge row. Flag = 2, fail row

ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1

The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.

ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1

Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.

Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.

Original table:

ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL

Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL

Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).

By taking the min date and max date of the same group, the following table should be achieve after the table is updated.

Final outcome:

ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL

View 2 Replies View Related

Dynamic Sql Where And Order By Clauses In Stored Procedure

Feb 17, 2008

Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate]     -- Add the parameters for the stored procedure here @finfileid int,     @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;     -- Insert statements for procedure here     SELECT     MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId,                       SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail                        / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId)                       AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId)                       AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName,                       MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName,                       MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId,                       MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM         ProjectFinFileItems INNER JOIN                       ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN                       FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN                       Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN                       Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN                       ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN                       Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid  --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)

View 2 Replies View Related

Variable Number Of Where Clauses In A Stored Procedure

Jun 20, 2001

I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?

A simple illustation of this is as follows
A statement with two clauses :-

Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'

The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved