Transact SQL :: Sorting By Minimum Of Multiple DATETIME Columns

Apr 22, 2015

I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-

Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport])
Days Remaining (x8 columns) (named as "TS_" + Credential)

I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-

SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass],
[Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE
WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND

[code]....

I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.

View 9 Replies


ADVERTISEMENT

Sorting Problem? Most Recent Entry When Selecting Multiple Columns

Oct 19, 2007

I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available).

actions table (columns: action_id, request_id, action_type, action_time, action_reason)

I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once.

My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria


SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time


So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission)

Hopefully this has made sense so far.

So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?

View 5 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Transact SQL :: Split Rows By Day / By Datetime And Partition By Columns

Jul 22, 2015

I am trying to spilt records into days by the start - End datetime.

I would send an image and data but because I am new to the forum, I am blocked sending images.

"Body text cannot contain images or links until we are able to verify your account"

How I can forward an image.

View 15 Replies View Related

Unpivot Multiple Columns With Datetime NULLs

Apr 3, 2007

Hi there,

I'm trying to unpivot a table in SSIS: The pivoted table basically looks like

ID DATE1 TEXT1 DATE2 TEXT2

----------------------------------

ID1 D1 T1 D2 T2

...

The unpivoted result looks like

ID DATE TEXT

-------------------

ID1 D1 T1

ID1 D2 T2

€¦



It works, but the one problem I am facing is: If D1 IS NULL in the pivoted table then D1 in the unpivoted table contains some strange value that is neither NULL nor a valid datetime. In a data viewer I get the in the DATE field "Fehler: Die Parameter Year, Month and Day beschreiben eine nicht darstellbare Datetime." (i. e. "Error: The parameters Year, Month and Day form an invalid datetime."). The loading of the OLE DB target yields an error because the DATE value cannot be converted to a valid datetime.



Anybody having a solution to this?

View 6 Replies View Related

Transact SQL :: COUNT And SUM Of Multiple Columns

Sep 2, 2015

I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.

CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]....

View 8 Replies View Related

Transact SQL :: Multiply Multiple Columns From One Row

Nov 23, 2015

I have a table with the following format

I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].

Is this possible using T-SQL 2012?

View 5 Replies View Related

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date
-
ET_C_IsValid 

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies View Related

Transact SQL :: First Values In Multiple Columns Selection

May 19, 2015

I'm trying to run something like this:

Select ID, FIRST(forename), FIRST(surname) from table1
GROUP BY ID;

I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.

There are records like:

ID      forename     surname
--------------------------------
1    John    Kormack
1   James  Dope
2   Erin    Dupes
3   Will  Hugh
3    Walter Heisenberg

So I want to pull out:

1 John Kormack
2 Erin Dupes
3 Will Hugh

How can I run it in TSQL?

View 6 Replies View Related

Transact SQL :: How To Break Rows Into Multiple Columns

Jul 14, 2015

I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.

Row data
ID
1
2
3
4
5
6
7
8
9
...
98
99
100

Output will be like
c1  c2   c3    c4    c5....
1    5    9      13
2    6    10    14
3    7    11    15
4    8    12    16

View 4 Replies View Related

Transact SQL :: Multiple Columns Needs Median Calculation

Oct 27, 2015

I need to get the median for the 10 columns in my table. For the sake of example, I've reduced it to 2 columns. 

The code below works perfectly if i compute for only 1 column and certainly doesn't for multiple columns.

Is there a way to better handle the median computation in one pass, if multiple columns are involved?

DECLARE @tbl as table (id_n int, col1 int, col2 int)
insert into @tbl
values (1, 1, 2), (1,3, 4), (1, 5, 7), (2, 4, 7), (2, 7, 7), (2, 3, 5), (2,5, 5), (3, 1, 2), (3, 3, 5), (3, NULL,11)
select *
from @tbl
order by id_n, col1

[Code] .....

View 6 Replies View Related

Transact SQL :: Single Row Multiple Columns Into One Column

Jun 4, 2015

I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.

Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)

[code]....

View 10 Replies View Related

Transact SQL :: Row Number Over Partition By Multiple Columns

Sep 22, 2015

I have the following query

WITH summary AS
(SELECT tu.SequenceNumber,
tu.trialid,
tu.SBOINumber,
tu.DisplayFlag,

[Code] ....

I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.

Sample Data
SequenceNumber   TrialID      SBOINumber
1                           1            5000
2                           1            5000
3                           2            5000
4                           2            5000
5                           1            5001
6                           3            5001
7                           3            5001

Should return SequenceNumber 1 and 3, 5, 6

View 11 Replies View Related

Transact SQL :: Search Functionality With Multiple Columns - Writing SP

Jul 20, 2015

What is the most efficient way to write an SP to tackle all kinds of combinations here (where a user could give any search input).I know this must be fairly common to come across this situation.I have written an SP which will take in all the parameters and based on "IF" statements and using "LIKE" in SQL, this SP returns search results.But I wanted to know if there was more efficient ways of doing this, as you can imagine you might end up having several combinations of IF conditions.

View 9 Replies View Related

Transact SQL :: How To Count Where Two Tables Multiple Columns Match

May 4, 2015

There are two tables

TABLE 1 (NAME - Groupseats)

id session course groupcode sub1 sub2 sub3

1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His

From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.

Here is the TABLE 2 Structure and sample data:

table 2 (NAME - tblstudetail)

id studentID session course sub1 sub2 sub3

1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco

AND so no..........................

Now i just want to COUNT the Number of Groups Filled in tblStudateil.

View 10 Replies View Related

Transact SQL :: Multiple Inserts Different Columns And Tables Into One Table

Oct 12, 2015

I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.

If object_ID(N'Bezeichnungen') is not NULL
   Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
 Artikelnummer nvarchar(18),
 Artikelbezeichnung nvarchar(80),
 Artikelgruppe nvarchar(13),
 
[code]...

View 19 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

View 2 Replies View Related

Transact SQL :: Convert Multiple Lines With Different Values In Value Columns In 1 Line

Dec 1, 2015

I have this query:

SELECT
          ID1,
          ID2,
          type,
          (case when type = '1' then sum(value) else '0' end) as Value1,
          (case when type = '3' then sum(value) else '0' end) as Value2,
          (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y' 
GROUP BY ID1, ID2, Type

Which returns:
ID1     ID2        Type     Value1     Value2     Value3
005    11547    0          0.00         0.00        279.23
005    11547    1          15.23       0.00        0.00
005    11547    3          0.00         245.50    0.00

And I want to obtain this result:
ID1     ID2        Value1     Value2     Value3
005    11547     15.23       245.50    279.23

View 5 Replies View Related

Transact SQL :: Select Multiple Columns From Table But Group By One Column

Jun 17, 2015

I have a SQL query like this

select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode

As per this query I got the result like this

CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED     BNT    1     1
AED     BNT     12     1
AED     SCN     1     1
AED     SNT     1     3

[Code] ....

But I wish to grt result as

CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED     BNT   13     2
AED     SCN     1     1
AED     SNT     11     7
AFN     BPC    8     6

[Code] ....

I also tried this

select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode

But of course this codes gives an error, but how can I get my desired result??

View 5 Replies View Related

Transact SQL :: Include Minimum Item Values Only

Aug 3, 2015

My sql gives me the results as shown in #TEMTAB Table.some times Mitem_id is repeated for the same Group_id,SGroup_id  with different item_id

ex: SQ322,SQ323,SQ324,SQ325 are repeated here.
CREATE TABLE #TEMTAB
(Group_id int, SGroup_id int, item_id int, Mitem_id varchar(10)
,YN varchar(2),value varchar(8),reason varchar(20))

[code]...

View 8 Replies View Related

Transact SQL :: Update Records With Minimum Date In Table?

Nov 19, 2015

I want to update the STATUS column based on min date in my table.

For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.

View 3 Replies View Related

Datetime -&> Binary -&> Bigint (for Sorting)

Apr 20, 2007

this is more of a theoretical question and not necessarily for best practice...though if reliable and efficient, i'd use it.

a lot of my sprocs return xml and are then processed in xslt...so when i need to sort by date (in xslt), i pass the datetime to a function that ultimately returns a bigint...it seems to work, but i'm not 100% confident...

can anyone confirm that the resulting integer values can be relied on for sorting?


function return: cast((cast(@dt as binary(8))) as bigint)

small test:

sql Code:






Original
- sql Code




declare @i int, @d datetime
declare @t table (id int identity, dt datetime, bd binary(8), bi bigint)
set @i=1
set @d = getdate()
while @i between 1 and 20
begin
insert @t(dt, bd, bi)
select
@d,
cast(@d as binary(8)),
cast((cast(@d as binary(8))) as bigint)

set @d = dateadd(mi, @i, @d)
set @i=@i+1
end

select *
from @t
order by bi






DECLARE @i int, @d datetimeDECLARE @t TABLE (id int identity, dt datetime, bd BINARY(8), bi bigint) SET @i=1SET @d = getdate()WHILE @i BETWEEN 1 AND 20BEGIN    INSERT @t(dt, bd, bi)    SELECT         @d,         CAST(@d AS BINARY(8)),         CAST((CAST(@d AS BINARY(8))) AS bigint)     SET @d = dateadd(mi, @i, @d)    SET @i=@i+1END SELECT *FROM @tORDER BY bi

View 1 Replies View Related

Sorting By Several Columns

Jul 24, 2006

Hello all,
I'm verry new to SQL server, and find myself trying to sort a table ascendingly be several columns. For instance:

SELECT * FROM Sort ODER BY start_year AND start_month ASC

I've tried to run this, but it simply does not work.

Is there a simple way to do something like this?

Any help is greatly appreciated! Thanks so much
-Robert

View 3 Replies View Related

Sorting Columns Question

Jun 29, 2004

hi all

an Interesting question

I have a column which stores a versin number in this format

1.5.5.19
1.5.5.9
...
...

I want to be able to sort this text column in an ascending order. Unfortunately it gives me 1.5.5.19 followed by 1.5.5.9 which is not the case.

Please let me know ASAP

Krish+
v

View 9 Replies View Related

Stuck With Sorting Columns

Dec 23, 2005

I have the following table (which is an import from another system Ican't mod):-CREATE TABLE [tbl_wsg_maternity_observations] ([documentname] [varchar] (40),[clientguid] [decimal](16, 0) ,[docguid] [decimal](16, 0) ,[displayname] [varchar] (80),[valuetext] [varchar] (255) ,[valuenum] [float] NULL) ON [PRIMARY]GOWheredocumentname is the name of the documentclientguid is the unique identifier for my patientdocguid is the unique id for the documentdisplayname is the dataitem (e.g. diagnosis)valuetext is the "answer" (e.g. kidney failure)valuenum is used instead if the valuetext is an integer (e.g.number of toes)I am trying to split/change this table so that I have a different tableper document, with one row per patient occurance with the displaynamesas columns.I have been using the following but it is slow and for large tablestakes hours (literally) to run:-SELECT distinctclientguid,(SELECT DISTINCT case when t2.[ValueText] is null thencast(t2.[Valuenum] as varchar(10)) else t2.[ValueText]end FROMtbl_wsg_maternity_observations t2 WHERE 'How many vessels present incord' = t2.[Displayname] AND t1.ClientGUID = t2.ClientGUID ANDt1.docGUID = t2.docGUID) as [How many vessels present in cord],<SNIP...more identical lines, one per dataitem>INTOtbl_wsg_baby_delivery_detailsFROMtbl_wsg_maternity_observations t1WHEREdocumentname = 'Mat Baby Delivery Details'Does anyone have any ideas how to do this faster and preferably moresimply?Will

View 4 Replies View Related

Transact SQL :: Sorting Alphanumerical In Report Builder

Sep 3, 2015

I am trying to sort alphanumeric varchar in a column.Elsewhere PATINDEX is used for sql, but the function is not available in Report Builder. How to create an expression/

View 9 Replies View Related

Query Questions, Filtering Or Sorting Of Columns

Oct 26, 2004

I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information.
I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns
I will use Honda Accord as an example.
Honda has made the Accord since probably the 80's
I know that if I
SELECT yearnum
FROM YearNum
ORDER BY yearnum

I am using C++ Builder too..
it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord.
Is there anyway to filter out multiple years, so I could just have the regular order of years?

View 1 Replies View Related

Sorting Columns In Grid Data Viewer

Aug 28, 2007

Has anyone ever looked at the way the grid data viewer sorts it data when
clicking on a column header? If you click on a column header, something
happens to the sorting of the data in the viewer, but it's not always clear
to me _what_ is happening. It appears the data is sorted ascending on the
column you clicked on. If you click again the sort order seems to inverse to
descending. However, if you look closely, it turns out that the data is not
always sorted correctly, especially when you click on an integer valued
column.

I found this when doing a demo on the AdventureWorks demo extracting data
from the SalesOrderDetail table. If you sort on the OrderQty column, data is
correctly sorted in ascending quantities. However, if you click the column
again, orders with an order quantity of 2 are displayed on top (while there
are orders with a much higher order quantity) and if you scroll down the
list, you notice that there is no clear sorting anymore. The same happens
with other columns.

Is this supposed to work as I would expect it to do or is there a logical
explanation for the behaviour I see?

--
Best regards,

Hans Geurtsen
Docent Kenniscentrum

Info Support
De Smalle Zijde 39
3903 LM Veenendaal
The Netherlands
www.infosupport.nl

View 1 Replies View Related

How Can I Apply Dynamic Sorting For Columns In Matrix Reports

Apr 30, 2008

Hi,

We have a matrix report which displays columns in a default sorting order. This report columns vary dynamically depending on the user input.

e.g. If user wants to see the report for column Alfa, Beta , Gama then a report will be genarted with column Alfa, Beta , Gama sorted in alphabetical order.






Site
%Risk
Alfa
Beta
Gama

X
2
1
2
3

Y
10
4
5
6
However the users want the Columns to be sorted in the order which they provide the inputs e.g. if the user entered Gama, Alfa, Beta the report should display the columns in the same order instead of applying the default sorting order.






Site
%Risk
Gama
Alfa
Beta

X
2
3
1
2

Y
10
6
4
5

Any thoughts on ways to achieve this in SSRS matrix report would really help.

Cheers,
Viv

View 3 Replies View Related

Reporting Services :: Sorting On Calculated Report Items Columns

Jun 21, 2015

I need to sort my tablix report where I have several calculated columns like:

=ReportItems!Textbox47.value+ReportItems!Textbox48.value..

Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube

View 3 Replies View Related

Transact SQL :: Difference Between Datetime In One Row And Datetime In The Row Above

May 21, 2015

I have a table that has a unique ID and a datetime of when something changed.

See example:
ID    TimeStamp
16094    2013-11-25 11:46:38.357
16095    2013-11-25 11:46:38.430
16096    2013-11-25 11:46:38.713
16097    2013-11-25 11:46:38.717
16098    2013-11-25 11:46:38.780

[Code] ....

Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.

View 10 Replies View Related

Multiple Master Detail Dropdown Lists Sorting

Jan 29, 2007

I have multiple drop down lists that I would like to have send parameters to a stored procedure that does a select on the data.  (Kind of like a search feature)  But if a ddl isnt populated, I want that to act like a * and get all.  For instance, I have a persontype, state, and country drop down lists.  If I drop the state one down to WA, I want the gridview to populate with all the people in WA regardless or their type.  If I populate type and state, have it give me both regardless of country.
What does the stored procedure or select syntax look like for this?  (What would the default values of my dropdown boxes?)
my current sql select statement (The bottom 3 are bit fields which are throwing me off a bit too).  do i need to have some IsNull statments around each variable in the select?SELECT

PersonFirstName
, PersonLastName
, PersonStreetAddress
, PersonCity
, PersonZip
, PersonEmail
, PersonEmailDate
, PersonPhone1
, PersonNotes
, InterestChapterLeaders
, InterestMembers
, InterestUniLeader
, PersonGradYear
, PersonTypeID
, CountryID
, StateID
, SchoolID
, PersonID

FROM tblPerson

WHERE

tblPerson.PersonTypeID = @PersonTypeID
and tblPerson.StateID = @StateID
and tblPerson.CountryID = @CountryID
and tblPerson.SchoolID = @SchoolID
and tblPerson.InterestChapterLeaders = @InterestChapterLeaders
and tblPerson.InterestMembers = @InterestMembers
and tblPerson.InterestUniLeader = @InterestUniLeader 

View 8 Replies View Related







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