Query - Nearest Birthdays To Mine
Dec 7, 2007
I have a date (my birthday). I would like to find the closest birthdays to mine, both before and after my birthday. I would like to list the people in my database who are the closest age to me, but in that order. So sorting my table by age and taking a row below and above my birthday is not going to work. This is because the three people below me may all have their birthday the next day, while those above me may have theirs years before mine.
Birthdays sorted by date:
05/10/1979 jim
12/01/1980 bob
10/04/1983 mike
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
05/07/1999 dylan
The order I wish to achieve is:
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
12/01/1980 bob
05/10/1979 jim
05/07/1999 dylan
Thanks in advance.
Mike
View 4 Replies
ADVERTISEMENT
Nov 24, 2014
I have a table with 257 mil records with latitude and longitude data.
My goal is to find the closest intersecting values from a locations table (88 rows) and update any of the 257 mil records that are applicable with the location_Name and Location_Group_Name.
The query I have works but doesn't perform well on such a big data set.
CREATE TABLE #Positions -- Base table 257 mil rows. Actual table has 20 columns
(
IDBigInt PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),
[Code] ....
Attached you will find the tables, test data, a function to measure distance and some queries that work but are too slow for this much data.
View 9 Replies
View Related
Mar 7, 2008
Im trying to get out all peoples names whos birthdays are coming up in the next 2 weeks, can anyone tell me why this : SELECT FullNameFROM UsersWHERE (CONVERT(datetime, DOBMonth + '/' + DOBDay + '/' + DOBYear) >= DATEADD(dd, -14, GETDATE())) AND (CONVERT(datetime, DOBMonth + '/' + DOBDay + '/' + DOBYear) <= DATEADD(day, 0, GETDATE()))returns nothing when I know theres a few birthdays that should be returned. Thanks Si!
View 2 Replies
View Related
Feb 26, 2004
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
View 14 Replies
View Related
Jun 27, 2006
The package is corrupt, who knows why, who knows what. It was running fine but there was a bug in an active script. Having failed miserably to debug this (note the ability to put a breakpoint in a script would be nice and dear god do not ask for edit and continue in here, it is bad enough as it is!) i eventually stuck a ref to the winforms namespace and debugged with a hundred messagebox.show statements. I did try to fire up an event that would write to an event tlog and i'm pretty sure that that is where the main problem lies as the package is still trying to load/use/connect to some log options but it did run for a while after i abandoned this path. Either way, I need some clue as to what bits of the xml file i can hack cos that is all i can get access to. 4 miles of xml, somwhere within the errors below are related. Clue me please or its curtains. Note also, the package is corrupt everywhere - in bin, in deployment, could not the last good compile get saved somewhere for recovery purposes as opposed to being overwriten with the bust one?
Error 1 Error loading Copy of pckSplitSourceNQ.dtsx: Element "{AD446434-4355-42C7-AD07-3D64A37C1671}" does not exist in collection "LogProviders". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 2 Error loading Copy of pckSplitSourceNQ.dtsx: Element "{F43808C7-03B1-4A09-86B1-08274073DA5C}" does not exist in collection "Executables". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 3 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading value "<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" IDREF="{F43808C7-03B1-4A09-86B1-08274073DA5C}" DTS:IsFrom="0"/>" from node "DTS:Executable". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 4 Error loading Copy of pckSplitSourceNQ.dtsx: Error
loading value "<DTS:PrecedenceConstraint
xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property
DTS:Name="Value">0</DTS:Property><DTS:Property
DTS:Name="EvalOp">2</DTS:Property><DTS:Property
DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property
DTS:Name="Expression"></" from node "DTS:PrecedenceConstraint".
G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of
pckSplitSourceNQ.dtsx 1 1
Error 5 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading value "<DTS:SelectedLogProvider xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:InstanceID="{AD446434-4355-42C7-AD07-3D64A37C1671}"/>" from node "DTS:SelectedLogProvider". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 6 Error loading Copy of pckSplitSourceNQ.dtsx: Error
loading value "<DTS:LoggingOptions
xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property
DTS:Name="LoggingMode">0</DTS:Property><DTS:Property
DTS:Name="FilterKind">0</DTS:Property><DTS:Property
DTS:Name="EventFilter"
DTS:DataType="8">2,10,OnProgress,23,ScriptComponen" from node
"DTS:LoggingOptions". G:Visual Studio 2005
ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx
1 1
Error 7 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading a task. The contact information for the task is "Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 8 Error loading 'Copy of pckSplitSourceNQ.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
THX
View 3 Replies
View Related
Mar 1, 2008
I want to retreive users that had their birthdays 7days before today and will have their birthday 7 days from now (so 14 days in total).What would the SQL for such a thing look like?I now have ths, but that doesnt work when you are at the beginning of a month (march 1). So there must be a more clever way :)select firstname from userswhere (month(ud.birthdate)=month(getdate())) and (month(ud.birthdate) between month(getdate())-1 and month(getdate())+1 ) and (day(ud.birthdate) between day(getdate())-7 and day(getdate())+3 )
View 4 Replies
View Related
Mar 14, 2007
Hello,
I just joined the forum and consider myself a SQL beginner. I have installed and supported, run backups and similar, but haven't done much with queries and scripting.
I have a MS SQL 2000 server with a company database on it. Amongst other things, the database contains names, birthdays and e-mail addresses. How difficult would it be to setup an automated query to run every morning comparing birthdays to the current date and send automated e-mails out to matching people saying "Happy birthday ...."?
Thanks in advance,
MrNobody
"If I'm not there, I must be somewhere else"
View 8 Replies
View Related
Jan 26, 2007
Hi there,
I am doing a project on web usage mining of my universities server logs and im just wondering how i go about mining them in sql server 2005?
Do i mine them in one table? do i normalise the web log data? what algorithms will i use on them as im trying to get usage patterns from the users and also where most of the users come from.
Thanks in advance
Gary
View 3 Replies
View Related
Jun 15, 2015
I have very little experience with programming and data mining, but I am working on a project where I need to take data from one spreadsheet and place it in another. Since it is hard to describe what I would like to do, I will provide an example:
SPREADSHEET 1
Column 1, Column 2
100, ?
101, ?
102, ?
103, ?
SPREADSHEET 2
Column 1, Column 2
102, 202
100, 200
103, 203
101, 201
In this example, the data in Column 1 is always tied to the data in Column 2 (i.e., 100 in Column 1 means 200 in Column 2, etc.) However, the data for Column 2 is only available in SPREADSHEET 2; moreover, the data is not in the same order in both spreadsheets.
My question is how can I create some sort of program where I can transfer the data from SPREADSHEET 2 into SPREADSHEET 1?
View 2 Replies
View Related
Jan 21, 2004
Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated
View 12 Replies
View Related
Oct 7, 2006
Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.
I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.
I'm having some problems, currently I have this statement:
Code:
DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)
INSERT INTO @tbl
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) >= DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)
INSERT INTO @tbl -- those are the one who allready have had birthday this year
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) < DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)
SELECT * FROM @tbl
It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.
I thought about adding an additional check:
Code:
AND DATEPART(day, dato) >= DATEPART(day, getdate())
in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.
Anyone have any suggestions? Is there an easier way to do it?
Dag
View 7 Replies
View Related
Sep 16, 2014
I have a column called as NDM$ What I want do it round it the nearest value example I am giving below
34.100->34%
39.8->40
35.4->35 some thing like that.
View 6 Replies
View Related
Nov 16, 2006
T-SQL:How to round to the nearest WHOLE number ?sofrom -- to-------------170 --17096.58 --97thanks
View 1 Replies
View Related
May 11, 2007
Hi
Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?
Thanks,
Neil
View 7 Replies
View Related
Dec 6, 2007
I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.
View 3 Replies
View Related
Mar 19, 2013
How would I match datetimes in records structured as follows:
Code:
Record1 AccountNo StartDateTime EndDateTime
1 1234 4/30/2012 8:00 AM NULL
2 1234 NULL 5/15/2012 8:00 AM
Desired Result:
Code:
AccountNo StartDateTime EndDateTime
1234 4/30/2012 8:00 AM 5/15/2012 8:00 AM
Of course there are multiple accounts, about 2100 in this case but they very by time periods, and multiple start and stop dates for an account. I need to get the start times and match them w/ the nearest end times but AFTER the value of the start time. Nearest end times must be forced to correspond to the nearest start time but there are some start times w/o end times and end times w/o start times due to user data entry errors. I need a solution that handles this. It is ok w/ the customer to make the assumption of nearest times supposedly going together so they can show the users the errors. I am on SQL Server 2008 R2.
View 4 Replies
View Related
Sep 8, 2014
how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.
Also, the solution should not be too cumbersome because it will be used in a high volume environment.
View 5 Replies
View Related
Nov 23, 2005
I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated
View 1 Replies
View Related
May 19, 2008
Hi,
I have such a scenario:
- two tables with record containing car vehicle number, datetime of message and other data like weight ect.
- first table contains only two messages for one car per one day
- second has many messages for one car for one day
I would like to get a list of messages from first table but joined with the nearest (previous) record for the same car from second table.
Thanks,
Przemo
View 13 Replies
View Related
Jan 7, 2008
OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).
Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?
I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.
View 4 Replies
View Related
Jun 28, 2015
how to round the nearest value after round of decimal and return integer.
declare @data decimal(18,2)
set @data = 5.55
-- output
select '5'
set @data = 5.58
-- output
select '6'
View 3 Replies
View Related
Oct 13, 2012
Table :
ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
View 1 Replies
View Related
Aug 12, 2015
I am getting the time difference between two dates using
DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,
My output looks like
1.33
0.17
1.50
etc
I'd like to round to the nearest quarter hour
1.50
0.25
.150
etc
View 4 Replies
View Related
Dec 6, 2007
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.
SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)
View 4 Replies
View Related
Sep 25, 2015
Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?
Here is a very basic example of what I am trying to do:
--example: '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate()
--example: '2007-09-22 15:07:00.000' this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)
View 24 Replies
View Related
Oct 7, 2003
I'm having a problem finding the nearest age. If the last bday is closer then the current age, if the next bday is closer then current age + 1 year. I'm familar with dateadd, datediff and datepart. Here's some psudo code...
declare @bday datetime ,@age int
set @bday = '01/26/1956'
set @thisdate = getdate()
set @age = case
when next @bday is closer than the past birthdate then age(@bday) + 1yr
when next @bday is farther than past @bday then age(@bday)
end
I know there is not an age() function, but that's part of what I'm looking for. I'm also stumped on getting the nearest age. What I had only worked for part of the year, prior to around June 15th, (183 days into the year)
TIA
JeffP...
View 3 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Jan 22, 2001
Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
View 1 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
