SQL Server 2012 :: Adding Flags Depending On Criteria
Feb 12, 2014
I have a data output with many rows. In order to group things with flags, I do this in excel using 2 formulas which *** a flag of 0 or 1 in 2 new columns.
This takes a long long time as I have hundreds of thousands of rows and wondered of I could do it in sql?
Its transact SQL and the formulas I use in excel are:
I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE( Column1 int FLAG1 bit FLAG2 bit )
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0 (Any combination of these flags) Select * from TABLE inner join SOMEOTHERTABLE on TABLE.Column1 = SOMEOTHERTABLE .Column1 where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2 or Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
in fact i'm a beginner and i don't really see how i can do some tasks
when i display a report i want the reader to choose a client, and when he chooses a client, he can choose in the second parameter list, the missions that correspond to THIS client
so i have 3 datasets
one in which i get all my data needed and i have a filter in where part of query
Code Snippet where client=@client and mission=@mission
and i filter on these 2 values
in the second dataset , i gather all the clients i have by doing this query
Code Snippet select distinct name from client
and i have this third dataset where i gather all the missions corresponding to one client
and here i write
Code Snippet select mission_label from client where name=@client
the two fields are in the same time and in this third dataset i apply a filter on Parameters!client.value
but when i execute the report, the second parameter doesn't work, it gives me no choice of mission for a client, it doesn't return anything while when i execute the query in the dataset i have these values
Hi everyone, I am building a search page and am a bit stuck. The page has an sqldatasource with no select command and i am passing the select command and parameters depending on the optionsm selected. If a tick box is ticked and a drop down selected i need to add a parameter to the datasource and generate the select command. then if 1 and or 2 trext boxes are filled in, add the 1 or 2 select parameters then generate the select command. The select command is correct as far as i can tell , but the parameters are not being passed to the datasource so it doesn't select anything. my code behind is in the page load handler and at the moment only adds the parameters in one place so i could test. Does anyone have any idea what i have done wrong?
Cheers Chris CODE BEHINDProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim SQLstr As String
If cb_Today.Checked = True And dd_Area.SelectedValue.ToString <> "" ThenLocManSearch.SelectParameters.Add("Area", TypeCode.String, dd_Area.SelectedValue.ToString()) SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') AND ([available] LIKE '%" & Date.Today & "%') AND ([viewable] = 'True')" ElseIf cb_Today.Checked = False And dd_Area.SelectedValue.ToString <> "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') " ElseIf txt_FName.Text <> "" And txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%') and [LastName] LIKE '%" & txt_LName.Text.ToString() & "%')" ElseIf txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([LastName] LIKE '%" & txt_LName.Text.ToString() & "%')" ElseIf txt_FName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%')" End If Response.Write(SQLstr) LocManSearch.SelectCommand = SQLstr End Sub
If an Account's value reaches 20 for StatusID = 1 and 40 for StatusID = 2, that is a called "Good". I want to find out which order made the Account become "Good".
By looking at the data, it is understandble that AccountID 1 crossed Status ID 1's limit of 20 with order 2, but the status ID 2's limit was only crossed after the 6th order was placed. So my output should show 6 for AccountID 1.
For AccountID 2, value of statusID 1 was 5 with orderid 3, but it reached the limit for status id 2 of 40 with order 4. But the first condition was not met. so it shouldn't be seen in the output.
Same with AccountID 3 as well, It reached the limit of status id 1 with order 5 but the limit for order 2 wasn't reached so it should be ignored as well.
I wrote the code as below, its working fine but I still know there are better ways to write since I will be working with atleast a million records.
;WITH CTE AS ( SELECT OrderID,AccountID, StatusID, SUM(Value) OVER(Partition By AccountID, StatusID ORDER BY OrderID) AS RunningTotal FROM @Input
I have a dataset where i want to select the records that matches my input values. But i only want to try macthing a field in my dataset aginst the input value, if the dataset value is not NULL.
I always submit all 4 input values.
@Tyreid, @CarId,@RegionId,@CarAgeGroup
So for the first record in the dataset i get a succesfull output if my input values matches RegionId and CarAgeGroup.
I cant figure out how to create the SQl script for this SELECT?
I am trying to import data from 4 columns in a spreadsheet, the Columns are (Last Name - First Name - ID - Code) and this spreadsheet has around 10k records. I want to add what is in this spreadsheet to the query I have below that uses the EXCEPT operator but I am not sure the best way to go about it.
Using the example I have filtered below for the name "Denise Test", at the end of the day I want everything that is in the spreadsheet to also be excluded from the results.
So before the spreadsheet lets say the 2nd query referencing table C has the following results for Denise Test
Last_Name First_Name ID Code Test Denise 1 5 Test Denise 2 4
After adding the spreadsheet I want it to show this:
Last_Name First_Name ID Code Test Denise 1 5 Test Denise 2 4 Test Denise 3 3
Here is the query as it stands now without the inclusion of the spreadsheet:
SELECT ta.last_name, ta.first_name, tb.ID, tb.code FROM TableA ta INNER JOIN TableB tb
Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.
-------------------------------------- UPDATE dbo.Asgnmnt SET CustomerPending = 'Y' FROM dbo.Asgnmnt WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')
ALTER Table Asgnmnt ALTER column CustomerPending varchar(1)
UPDATE dbo.Asgnmnt SET CustomerPending = 'N' FROM dbo.Asgnmnt WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')
ALTER Table Asgnmnt ALTER column CustomerPending varchar(1) ---------------------------------------
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join ( select VendorPackageId from ValidVendorPackages where Vendor = @VENDOR and Sitecode = @SITECODE and PackageType = @PACKAGETYPE )HB on HA.VendorPackageId = HB.VendorPackageId and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 )
Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in. Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_1 and ValidItemType = @VALIDITEMTYPE_1 and ItemValue = @ITEMVALUE_1 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_2 and ValidItemType = @VALIDITEMTYPE_2 and ItemValue = @ITEMVALUE_2 ) and CriteriaId in ( select CriteriaID from ValidItemCriteria where Destination = @DESTINATION and LengthOfStay = @LENGTHOFSTAY and Ages = @AGE and ComponentType = @COMPONENTTYPE_3 and ValidItemType = @VALIDITEMTYPE_3 and ItemValue = @ITEMVALUE_3 )
Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''
I have two tables a and b, where I want to add columns from b to a with a criteria. the columns will be added by month criteria. I want to keep all the records in a, and join columns from b. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a:
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr] ,a.[all_val_yr]
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say Location1 : C:LoadFilesImport Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3, Second Set: VD_1, VD_2, VD_3, Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1 AP_2 goes to Table2 AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201 If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion. As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ? CREATE TABLE [dbo].[LoginsExpiry]( [LoginName] [varchar](50) NULL, [ExpiryDate] [date] NULL, [Roles] [varchar](500) ) ON [PRIMARY] GO
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.
However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?
Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, why one location is better than another location?
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems OPEN childcur_inventory FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like - JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK) ON User_Count_Id = Analyst_Id_fk WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk )
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
DECLARE @ID UNIQUEIDENTIFIER, @SOMEDATE DATE SELECT * FROM myTable WHERE ID = @ID AND DATEFIELD=@SOMEDATE I want to pass values to @ID and @SOMEDATE, such that it meets the WHERE criteria for all values in the respective fields.
What parameter value should I pass such that all values are selected? In the actual SP, I have uniqueidentifier, varchar and date parameters.
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE ' + @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244 Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int EXEC@return_value = [dbo].[EP_Conterbalances] @Start_Date_For_Totals_Date = N'20120831', @EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
We have the following trace flags present in startup in SQL server 2000:
809 1204 3605 3913
Need to understand if these should be required in SQL Server 2005 + SP2 version. I have run the upgrade advisor tool which indcates that the behaviour of some flags has changed and some other TFs are no longer applicable. Hence, I want to know about the above mentioned TFs.
automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:
1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent. 2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT 3) Run the DDL code below. 4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes. 5) Connect to an availability group listener and call CREATE DATABASE :)
I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.
The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.
* AlwaysOn Self-Population Script * By: Steve Gray / steve@mostlyharmful.net * Usage: Free, but buy me a beer if you're ever in Brisbane. **/ USE [master] GO IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
how i can add date using ssis or data tools 2012..My flat files had no date..den my instructor gave us a database where it has recordstartdate, recordenddate and currentdate.how i suppose to add date?
When adding a node to a SQL Server 2012 Standard edition cluster, how I do I identify the location for SQL server shared components and the rest of the SQL Server installation binaries?
When adding a node to a SQL Server 2012 Standard edition cluster all the binaries went to the C: drive default location. We put those files on a different drive when installing the first node. What needs to be done so both nodes have the binaries on the same drives and folders?
add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061 312062 312063 312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
The script is failing at this point "DATEADD(mm, RowNum, salesdate) subscriptionrowdate" dont know exactly where i am going wrong.
This is my code
SELECT *, CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog, DATEADD(mm, RowNum, salesdate) subscriptionrowdate FROM ( SELECT viasatsubscriptionid, firstproductregistrationdate, salesdate, baseenddate, ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum FROM stage_viasatsubscription )a
I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:
SELECT cdsDistance AS Distance , prsFirstName , prsLastName , min(crtFinalTime) AS MinTime FROM tb....... INNER JOIN etc.. GROUP BY cdsDistance, prsFirstName, prsLastName ORDER BY min(crtFinalTime)
In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.
If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate") If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")
Changing the code to:
SELECT cdsDistance AS Distance , prsFirstName , prsLastName , MIN(crtFinalTime) AS MinTime , MIN(cdsStartDate) AS RaceDate , MIN(cdsDistanceNumber) AS DistanceNumber
FROM tb....... GROUP BY cdsDistance, prsFirstName, prsLastName ORDER BY min(crtFinalTime)
This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.
An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.
Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)
Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.
As an example a few times:
DistanceFirst nameLast name Time Date Distance nr. 500 Yuya Oikawa 34.49 201311155 500 Yuya Oikawa 34.49 201311153 500 Yuya Oikawa 34.49 201311172
Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.
I am working on a project that was assigned to me that has to do with data in one of our SQL databases. I have the following query that takes information from a single table and averages test scores for each student.
--Group all scores from same student and average them together
with cte_names as ( SELECT StudentID, MAX(StudentName) AS StudentName FROM LDCScores WHERE schoolYear='2014-2015' AND term = 3 GROUP BY StudentID
[code].....
I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.
In one of my views I am having trouble finding where to put it in my existing statement:
USE [pec_prod] GO /****** Object: View [dbo].[PEC_Claim_Export_All] Script Date: 8/10/2015 9:18:26 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[PEC_Claim_Export_All]
[Code] ....
Msg 156, Level 15, State 1, Procedure PEC_Claim_Export_All, Line 56 Incorrect syntax near the keyword 'OPTION'.
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357 SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount FROM location_views vws,extlinks lnks WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357) OR (lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
