MS SQL Server :: Help Needed In This Complex Logic

Complex Date Logic - Help Needed

Sep 21, 2004

This concerns eligibility healthcare information. A member can have multiple rows in the table showing they are eligible for different date ranges with different health plans. eff_date and term_date are the fields in this table. Term_date can be NULL.

I need a WHERE statement that shows members
1) eligible between 1/1/2004 and 8/15/2004

(term_date is null or term_date >= '8/15/2004')
and eff_date <='1/1/2004'

2) members are allowed only one gap in this timeframe of up to 45 days. *NEED HELP

3) a gap of 1 day should not be counted as a gap in enrollment. *NEED HELP

Any help on #2 and #3 would be appreciated.

View 1 Replies View Related

Query Needed For Complex Logic

Mar 28, 2008

Table:GRoupAllocation
______________________
GroupId (Primarykey)
GroupName

TableystemAllocation
_______________________
SystemId(Primarykey)
SystemName
GroupId (foreignkey)

TableeviceAllocation
_______________________
DeviceId (Primarykey)
DeviceName
SystemId (foreignkey)
_______________________

TableensorAllocation
_______________________
SensorId (Primarykey)
SensorName
GroupId (foreignkey)
SystemId (foreignkey)
deviceid(foreignkey)
_______________________

Table:GRoupAllocation
______________________
GroupId GroupName
1 Group1
2 Group2
3 GRoup3
4 Group4

TableystemAllocation
___________________________
SystemId SystemName GroupId
1 system1 1
2 system2 2
3 system3 3
4 system4 2
5 system5 1

TableeviceAllocation
___________________________
DeviceId DeviceName SystemId
1 Device1 1
2 Device3 3
3 Device4 2

TableensorAllocation
____________________________________________
SensorId SensorName GroupId SystemId DeviceId
1 sensor1 1 1 1
3 sensor3 2 2
4 sensor4 3 3 3

my results should be like this:

Results:
___________________________________________________________________
GroupName SystemName DeviceName SensorName
___________________________________________________________________
Group1 system1 Device1 sensor1
Group1 null null sensor2
GRoup2 system2 null sensor3
GRoup2 system4 null null
Group3 system3 Device3 sensor4
Group4 null null null

so i need to populate the results in treeview.my treeview looks lika this:

Group1
|____System1
|________Device1
|_______sensor1

|_______system5

Group2
|____System2
|________Device1
|_______sensor1

|____System4
|____Device4

like this i need to populate. i need to show all the groupname and belonging systemName and belonging devicename and belonging sensorname

so please give me query for this complex operation please
criteria's
1.GRoup can have systems and system can have devices and device can have sensors
2.GRoup can have systems and systems can have sensors[no device]
3.GRoup can have systems and systems can have devices [no sensor]
4.GRoup can only have system [no device, no sensor]
5.GRoup can have only sensor[no system, no device]
so please give me query for this. not stored procedures.i need query for this

View 3 Replies View Related

Complex Sql Logic... Need Help

Feb 15, 2007

okay;

i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.

tblSlots:
PKSlotINDEX
datStartTime
datEndTime

then i have a table called tblPersons

tblPersons:
PKPersonINDEX
txtLast
txtFirst

then i have a table called tblSchedule

tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX

i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.

wow complicated isnt it...

the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.

thanks!

View 1 Replies View Related

Looking For The Change Using A Complex Logic

Apr 25, 2008

I have records like below

ID Facility Procedure Date Action

1001 A 888 07/01/2007

1002 A 888 07/31/2007

1003 B 888 09/01/2007

1004 B 888 09/15/2007

1005 B 888 10/11/2007

1006 B 999 10/24/2007

1007 B 777 10/25/2007

1008 B 777 10/30/2007

I have to update an action column based on the business logic below.

1. if there is a change in FACILITY with the same PROCEDURE and the DATE between new facility and the date of previous record is less than 30 days ,

then update ACTION = TP

2. if there is a change in procedure , update Action = TS

3. is there is a change in FACILITY but have different PROCEDURE, then NO UPDATE

The output should look like below:

ID 1003 has TP because the FACILITY has changed with the same PROCEDURE compare to the previous record and the DATE is less than 30 days

ID 1006 has TS because the PROCEDURE has changed compare to previous record

ID 1009 didn’t get changed because it has different PROCEDURE even though FACILITY got changed...

ID Facility Procedure Date Action

1001 A 888 07/01/2007

1002 A 888 07/31/2007

1003 B 888 08/05/2007 TP

1004 B 888 09/15/2007

1005 B 888 10/11/2007

1006 B 999 10/24/2007 TS

1007 B 999 10/25/2007

1008 B 777 10/30/2007 TS

1009 D 666 11/07/2007

What is the best way to do this? Do I have to use a cursor to compare each record? Can you sow me some code examples?

View 7 Replies View Related

Please Help With Complex Update Statement Logic

Nov 8, 2006

hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')

View 22 Replies View Related

Logic For A Complex Function&& Which Tasks To Use

May 28, 2008

Input Columns

Jan,Feb,March,April,May,June,July......December

All these columns Have Boolean 'Y' Or 'N' Data

Here is what i want as output

Example

Jan 'Y'
Feb 'Y'
March'Y'
April'Y'
May 'N'
June 'Y'
July 'Y'
..
..
..
Dec 'Y'

Output should be:

Start date:jan/year
End date:April/year
Again the start date:May/year
End Date: dec/year

So basically For the same person i should have two recods...

Text file to sql server 2005

How can i achieve this
Please let me know

View 14 Replies View Related

Finding A Change Complex Logic..

Apr 25, 2008

I have records like below

ID Facility Procedure Date Action

1001 A 888 07/01/2007

1002 A 888 07/31/2007

1003 B 888 09/01/2007
1004 B 888 09/15/2007

1005 B 888 10/11/2007

1006 B 999 10/24/2007
1007 B 777 10/25/2007
1008 B 777 10/30/2007

I have to update an action column based on the business logic below.

1. if there is a change in FACILITY with the same PROCEDURE and the DATE between new facility and the date of previous record is less than 30 days ,

then update ACTION = TP

2. if there is a change in procedure , update Action = TS

3. is there is a change in FACILITY but have different PROCEDURE, then NO UPDATE

The output should look like below:

ID 1003 has TP because the FACILITY has changed with the same PROCEDURE compare to the previous record and the DATE is less than 30 days

ID 1006 has TS because the PROCEDURE has changed compare to previous record

ID 1009 didn��t get changed because it has different PROCEDURE even though FACILITY got changed...

ID Facility Procedure Date Action

1001 A 888 07/01/2007

1002 A 888 07/31/2007

1003 B 888 08/05/2007 TP

1004 B 888 09/15/2007

1005 B 888 10/11/2007

1006 B 999 10/24/2007 TS

1007 B 999 10/25/2007

1008 B 777 10/30/2007 TS

1009 D 666 11/07/2007

What is the best way to do this? Do I have to use a cursor to compare each record? Can you sow me some code examples?

View 2 Replies View Related

Complex Row Level Security Logic In SecurityFilter

Oct 8, 2007

Hi, I am trying to replicate the row level security rules in reporting service and realize the "security filter" property is a pure client side filter expression.

In our old system, we are using a set of rules to decide the row level security, something like:

if user is owner of this file or
( if user's department matches this file and the user is the department manager ) or
if this user has unfinished task associated with this file or
...

So obviously it need more information than the just GetUserId(). What we have done is creating a CLR UDF as GetUserCaseList( int uid ) and returns a two column table ( case_id, case_right ).

We want to somehow inject these logics into the reporting while the user is using reportBuilder.exe, but we cannot find a way to put it into the data source view and/or the semantic data model.

I am pretty sure I am not the only guy who's having this problem, but I cannot find any hint....

Thanks in advance.

Jian

View 3 Replies View Related

Please Need Rescue- Complex Update Logic Swap

May 14, 2008

please need rescue- complex update logic
this is my table

1

2

3

4

5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555

1111

A

B

C

7

8

9

G

H

I

J

K

L

M

N

2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A

3333

1

2

3

A

B

C

C

E

Y

I

O

W

Y

P

i need to update for example the eployee 1111 with employee 3333
but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
in field- fld2,fld22,fld222 )

Code Snippet
---update eployee 1111 with employee 3333
-so
if i put the value 2
than ------------------ swap value between 2 employee
set empid1= 1111
set empid2=3333
value_swap=2
if value_swap=2
than
update fld2,fld22,fld222
with fld2,fld22,fld222
------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
--------------------in field- fld2,fld22,fld222

value_swap

=1

=2

=3

=4

=5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555

1111

A

B

C

A

B

C

G

H

I

J

K

L

M

N

2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A

3333

1

2

3

7

8

9

C

E

Y

I

O

W

Y

P

Code Snippet
---update eployee 2222 with employee 1111
-so
if i put the value 5
than ------------------ swap value between 2 employees
set empid1= 1111
set empid2=2222
value_swap=5
if value_swap=5
than
update fld5,fld55,fld555
with fld5,fld55,fld555
------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333
--------------------in field- fld5,fld55,fld555

=1

=2

=3

=4

=5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555

1111

A

B

C

7

8

9

G

H

I

J

K

W

Y

P

2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A

3333

1

2

3

A

B

C

C

E

Y

I

O

L

M

N

TNX FOR ALL THE HELP I GET IN THIS Forum

View 7 Replies View Related

Is There Any Easy Way To Evaluate Complex Date Logic In Expressions?

Dec 7, 2006

Hello all,

I am new to SSIS, so I am hoping there is an easier way to do this...

I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:

IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)

Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:

(DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2

Any help you all could give would be appreciated.

Thanks!

Josh

View 7 Replies View Related

Logic Needed

Jan 4, 2007

Hi pundits,

I need a logic for solving a realtime issue.

I have series 2^0,2^1,2^2,.......2^n ( i.e 1,2,4,8,16.....2^n)

I want to write a function(algorithm) which returns all the terms used(added) to form the number

e.g fun(7) :-1,2,4
fun(5) :-1,4
fun(257):-1,256
fun(6):-2,4

Ur help is highly appreciated.

Thanks in advance.

View 1 Replies View Related

Logic For A Blank Value Help Needed

Mar 28, 2007

here's my stored proc and i will tell you what I'm trying to do. I'm passing in a form value. It should handle anything. But it doesn't....
I have tried:
="",
='',
<>"",
<>'',
isNull,
IS NULL,
IS NOT NULL and now the isNumeric() function. Can anyone help me please?!?!

CREATE PROCEDURE dbo.spUpdateProductSpecialPostage

@ProductID as int,
@ScaleID as int

AS

DECLARE @ROWCOUNT as int
select @ROWCOUNT = count(*) from tPostageProduct where productid=@ProductID

if @ROWCOUNT > 0
if isNumeric(@ScaleID) <> 1
delete from tPostageProduct where productid=@ProductID
else
update tPostageProduct set scaleID = @scaleID where productid=@ProductID

else
if isNumeric(@ScaleID) = 1
INSERT INTO tPostageProduct (productId,scaleID) VALUES (@ProductID,@ScaleID)
GO

Cheers.

View 1 Replies View Related

Upsert Logic Needed

Dec 30, 2007

My organization is required to ETL millions of rows of medical insurance claims data per month into our data structures from many different external systems. The data structures vary tremendously between the different source systems.
We need to develop an efficient "upsert" algorithm: e.g. when a new row arrives from a particular source system determine:
a: does a row with an identical unique natural key (often a composite) already exist in our system?
b: if "a" above is true, is any non-key data in the newly arrived row different from the existing row with the same key?
c: depending on the results of test a and b above, perform either an insert or update.

So we need to develop an internal "upsert" logic solution. We also want to have a solution that will support a generalized solution as early as possible in the data processing.
Therefore, we are considering applying two identical MD5 hashing algorithms (probably C or C# extended stored procs) to each incoming source row (and a one time generation of the two hashes for all legacy loaded rows). One hash will be created from the unique natural key for each row, and one will be created from all the remaining columns in the row. Together these two MD5 hashes should allow us to perform comparisons on all newly arrived vs. previously loaded data, and reliably determine results of tests "a" and "b" above.

This approach also supports the requirement that the code that performs the upsert logic be generalized as early as possible for all different source systems, as the actual test logic will be identical for all source data and independant of the custom logic used to generate the MD5 hashes for each unique source structure.

Is there a better way? How do others approach this problem? What kind of performance impact can be expected from the xp calls?

Thanks!

View 9 Replies View Related

Advanced DateDiff Logic/Code Needed

Jan 5, 2007

I am trying to figure out how much coverage (supervision time) an employee has based on their managers schedule.

Example
Name: Employee, SundayStart: 9 AM, SundayEnd: 5 PM
Name: Manager, SundayStart: 8 AM, SundayEnd: 4 PM

############# 8 AM |Covered Time| 5 PM
Employee ##########|------------|-----
Manger ######------|------------|

Employee is covered for 6 hours on sunday.

Anyway to figure this out? This is what I have so far. Sorry, but im in access 03.

SELECT tblCAESchedule.Name, tblCAESchedule.Title, tblCAESchedule.SunStart, tblCAESchedule.SunEnd,
IIf(DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])>=1,DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend]),DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])+1440)/60 AS SunHours,
tblSupSchedule.Name, tblSupSchedule.SunStart, tblSupSchedule.SunEnd,
Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60) AS StartDif,
Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS EndDif,
Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60)+Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS ShiftDif
FROM tblCAESchedule INNER JOIN tblSupSchedule ON tblCAESchedule.Skill = tblSupSchedule.Skill
WHERE tblCAESchedule.Skill = "Sales"

View 4 Replies View Related

Help Needed With Complex Query

Nov 2, 2004

Hi,

I have a sql table, over 30 milion recs, with the following fields:
(id1 int, id2 int, itemsCollection varchar(100), myText TEXT)
I have also sql table, with the following fields: (item varchar(10), rate int)

I need to write a query that returns the following info: id1, id2, itemsCollection, item, rate, myText
The output need to be ordered as:
- Get id1 & id2 with the bigest rate
- output all the recs for the id1 & 2, ordered by rate (sub order)
eg
Main table:
id1 id2 itemsCollection myText
1 1 'a,b' 'count-11-a,B - max = 15 additional txt'
1 1 'a,b' 'count-11-a-B - max = 15'
1 1 '' 'count-11'
1 1 'a,c' 'count-11-a,C - max = 20'
2 8 'c,d' 'count-28-C-d - max = 20 additional txt'
2 8 'c,d' 'count-28-C-d - max = 20'
2 8 'd' 'count-28-D - max = 5'
3 2 'a,d' 'count-32-A-d - max = 10'
3 2 '' 'count-32'

Rates table:
item rate
a 10
b 15
c 20
d 5
'' 0

RequestedOutput:
itemsCollection item rate id1 id2 myText
a,c c 20 1 1 count-11-a,C - max = 20
a,b b 15 1 1 count-11-a,B - max = 15 additional txy
a,b b 15 1 1 count-11-a,B - max = 15
0 1 1 count-11
c,d c 20 2 8 count-28-C-d - max = 20 additional txt
c,d c 20 2 8 count-28-C-d - max = 20
d d 5 2 8 count-28-D - max = 5
a,d a 10 3 2 count-32-A-d - max = 10
0 3 2 count-32
Thanks!

View 3 Replies View Related

Complex Query Help Needed....

Jul 23, 2005

I have been working with SQL for a while...but I am stumped. I can notseem to get my arms around this query....can anyone help...Here it is:Table = 12 rows, 4 columns (id, name, amount, date)row1 = 771, "steve", $50.00, "01/01/2005"row2 = 772, "steve", $100.00, "01/11/2005"row3 = 773, "steve", $200.00, "01/11/2005"row4 = 774, "dave", $300.00, "01/01/2005"row5 = 775, "dave", $400.00, "01/12/2005"row6 = 776, "dave", $500.00, "01/12/2005"row7 = 777, "mike", $600.00, "01/01/2005"row8 = 778, "mike", $700.00, "01/13/2005"row9 = 789, "mike", $800.00, "01/13/2005"row10 = 790, "chuck", $900.00, "01/01/2005"row11 = 791, "chuck", $950.00, "01/14/2005"row12 = 792, "chuck", $975.00, "01/14/2005"I need a query that returns (1) ONE ROW PER NAME based on the MOSTRECENT DATE and returns the correct corresponding information. Thekeys to this question are the following:1. The query needs to return ONE ROW PER NAME2. I do not want to use a First() function (in MS Access)3. Even though (2) two DATE for each NAME are the same, i want thequery to return one record and whatever record it returns, i have to beable to have all the corresponding records (id, name, amount, anddate). I recorgnize that the DATE is ambiguous and that SQL may returnone or the other...but that is ok.4. The return set should include (4) four rowsAny help with this would be thoroughly appreciated...

View 1 Replies View Related

Complex Query / Advice Needed

Jul 20, 2005

Hello!I'm seeking advice on a rather complex type of query I need to buildin an Access ADP (SQL-Server 7). There are four tables:tblPeopleID(PK)PRENAME---------------1Thomas2Frank3ChristblInventoryClassesID(PK)INVENTORYCLASS----------------------1Car2PhonetblInventoryItemsID(PK)relInvClass(FK)ITEM-----------------------------------11Dodge Viper21Chrysler32Nokia42SamsungtblPeopleInventoryID(PK)relPeople(FK)relInvItem(FK)--------------------------------------112213321423534In this example the last table tells me thatThomas owns a Chrysler (class Car) and a Nokia (class Phone).Can someone tell me how to write a query or a stored procedure whichproduces a resultset like this:qryOwnersPeopleCarPhone-----------------------------ThomasChryslerNokiaFrankDodge ViperNokiaChris[NULL]SamsungThe main idea is that I need to be able to iterate such a collection.It is guranteed that one "People" only owns one or zero "Car" and oneor zero "Phone".I guess that it might be impossible to design a stored procedure withsuch a variable amount of columns (in this case, each item fromtblInventoryClasses would mean another column).Ary there any possibilities in accomplishing this without creatingtemporary tables?Any help would be really appreciated ;-)Greetings,Christoph Bisping

View 4 Replies View Related

Complex Grouping In View - Help Needed

Nov 13, 2006

Hello,

I am having difficulty to find the right SQL query to create a View as i illustrate below.

Senario:

Criteria Table

Idn

Key1

Key2

Key3

TagId

1

A

C

B

100

2

A

NULL

B

200

3

B

D

NULL

300

Data Table

DataId

Key1

1

A

2

B

SubData Table

SubDataId

DataId

Key2

Key3

1

1

C

B

2

1

Z

B

3

1

X

B

4

2

D

Z

And below is my expected View:

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.

I would really appreciate if anyone could help me to make if faster or point me where i did wrong.

Below is sample T-SQL that which i can illustrate my situation:

set nocount on
create table #Criteria
(
idn int
,Key1 char(1)
,Key2 char(1)
,Key3 char(1)
,TagId int
)

create table #Data
(
DataId int
,Key1 char(1)
)

create table #SubData
(
SubDataId int
,DataId int
,Key2 char(1)
,Key3 char(1)
)

insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', 'C', 'B', 100)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', NULL, 'B', 200)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'B', 'D', NULL, 300)

insert #Data(DataId, Key1)
values (1, 'A')
insert #Data(DataId, Key1)
values (2, 'B')

insert #SubData(SubDataId, DataId, Key2, Key3)
values (1, 1, 'C', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (2, 1, 'Z', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (3, 1, 'X', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (4, 2, 'D', 'Z')

select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId

/** here is the query logic i used in the view **/
select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

drop table #Criteria, #Data, #SubData

P/s: i urgently need your feedback on this one.

Thank You!!!

sibikos@hotmail.com

View 3 Replies View Related

Complex DB Search Forms (Store Proc Vs. Complex Where)

Nov 12, 2003

I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.

Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.

I build clauses like this (i.e., 4 fields shown):

SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )

My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.

View 7 Replies View Related

Need Some Logic Help

May 10, 2008

Can anyone see where my logic may have gone a stray?
A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150.
I want to then tell them they cannot do this.
@AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM tblPurchase INNER JOIN tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND

View 2 Replies View Related

Need Some Logic Help

Jan 6, 2004

Trying to bind some data to a datalist for a report.

User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.

If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.

I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.

Any thoughts on how I can do this?

View 2 Replies View Related

Help With Logic...

Apr 27, 2006

tbl_one hv 8mil rows, tbl_2 have 8k rows...

if
select count(*) from tbl_one
where sub_col1 = 2

return 3mil rows

and

select count(*) from tbl_2
where ad_col1 = '000009'

return 4k rows

HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')

return more than 12 billion rowss?? helpp..

View 7 Replies View Related

Help The Logic

May 3, 2006

I have to write trigger to relate two table.

If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.

How this can be done, do we need to point the common key fields in both table while inserting

View 8 Replies View Related

Is This Right Logic

Jun 12, 2007

What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.

This is what i came up with, will it evaluate the way i need it to?

case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV

Thank-you

View 4 Replies View Related

SQL Logic

Mar 13, 2008

I have a logic problem,

I am selecting from a table

Select * from TAB_A
where state not in (1,4)
and create_date < '2008-01-01'
and
(
(x != 2 and y != 1 and z != 4) or
(x != 6 and y != 3 and z != 1) or
(x != 8 and y != 0 and z != 9)
)

then for example i am getting results where x,y and z is equal to one
or more of above combinations ?

Now i vaguely remember that using or with a != messes up the logic ?
if so can i use an NOR ? does it exist ?

View 5 Replies View Related

Logic To Use

Mar 25, 2008

I am looking for the best approach to update a table's column based on the results of two other different tables. My tables structure is as follows.

Table 1 has columns A and B (tblemployee has location and employeenumber )
Table 2 has columns C and A (tblocation has locationID (identity) and location)
Table 3 has coumns C and B (tblcountry locationID (foreign key) and employeenumber.)

I want to update Table 3 (tblcountry) with the new locationID if and employee changes location or gets miscoded using an SSIS package.

Thanks in advance.

View 7 Replies View Related

Logic

Nov 14, 2007

I am having a little problem with my logic.
i have a table simplehoursassignment that has a field named br.
i need to get the value from simplehoursassigment & say if @mybit AND br > 0
set each day.

--CREATE PROCEDURE rpt_siteMealList(

-- (@cmb1 AS VARCHAR(100)) WITH ENCRYPTION

--)

--

--AS

DECLARE @dtm1 AS DATETIME

DECLARE @mybit AS INTEGER

SET @mybit=10

SELECT @mybit = CASE datepart(dw,@dtm1)

WHEN 1 THEN 1 -- 'Sunday'

WHEN 2 THEN 2 -- 'Monday'

WHEN 3 THEN 4 -- 'Tuesday'

WHEN 4 THEN 8 -- 'Wednesday'

WHEN 5 THEN 16 -- 'Thursday'

WHEN 6 THEN 32 -- 'Friday'

WHEN 7 THEN 64 -- 'Saturday'

END

SELECT br FROM simplehoursassignment

IF @mybit AND br > 0

BEGIN

CASE WHEN @mybit = 1 THEN 'Sunday'

WHEN @mybit = 2 THEN 'Monday'

WHEN @mybit = 3 THEN 'Tuesday'

WHEN @mybit = 4 THEN 'Wednesday'

WHEN @mybit = 5 THEN 'Thursday'

WHEN @mybit = 6 THEN 'Friday'

WHEN @mybit = 7 THEN 'Saturday'

END

END

View 3 Replies View Related

Logic In T-SQL

Apr 30, 2008

I have this query that is returning the same result twice and i cannot find why. I only have one record in Subquote.
Does anyone know what the problem is?

Code Snippet
SELECT s.*
FROM Quote q
INNER JOIN SubQuote s
ON q.id = s.quoteID
INNER JOIN TakeOffSheetItem t
ON t.quoteID_takeoffitem = q.id AND t.subQuoteID_takeoffitem = s.subquoteid
INNER JOIN PipeGroup p
ON p.quoteID_PipeGroup = q.id AND p.subQuoteID_PipeGroup = s.subQuoteID
WHERE (q.id = 1
AND q.deleted = 0
AND s.deleted = 0
AND t.deleted_takeoffitem = 0
AND p.deleted_PipeGroup = 0)
ORDER BY s.subquoteid

Thanks

K

View 4 Replies View Related

Database Logic

Jul 31, 2006

I am trying to figure out how to set up this database.
Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.So far I have the following, but not sure if this is the best way to set it up...TABLE Products:product_id (int) (1-many relationship to product_id in Table Product_Category)sku (int)descriptionpriceTABLE Categorycategory_id (int) (1-many relationship to category_id in Table Product_Category)nameTABLE SubCategorysubcategory_id (int) (1-many relationship to subcategory_id in Table Product_Category)category_id (int)nameTABLE Product_Categoryprodcat_id (int) product_id (int) (many-1 relationship to product_id in Table Products)category_id (int) (many-1 relationship to category_id in Table Category)subcategory_id (int) (many-1 relationship to subcategory_id in Table SubCategory)
Thanks,Mick

View 1 Replies View Related

Need The Logic To Get This Done Without Cursors

Mar 20, 2007

Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?

View 4 Replies View Related

SQL Logic Problem

May 21, 2007

I'm trying to figure out how to track what messages have been read by what users in a message board program we wrote. The users are identified by an ID number and each message has a unique number.
Let's say there are 10000 users and 200,000 messages. What would be the database setup/logic to track every user as to what messages they had read or not read? Maybe I'm missing something but it does not seem to be a trivial task.

View 2 Replies View Related

Db Logic - Can't Put It Back Together

Feb 27, 2006

Hi there!
Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.
Overview:
I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.
Layout for a magazine will look something like this:
Size              Trim          Type           Bleed
Full Page      280x440     270x430      290x450
Half Page     140x220     130x210       150x230
etc...
Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.
Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.

Break it down!
Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).
I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!

The Problem.
The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.
I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.

Any ideas on how i could manage this?

If you made it tis far through my question then thanks anyways! I hope you can help me out!

Ta

View 4 Replies View Related