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
ADVERTISEMENT
Mar 28, 2008
Table:GRoupAllocation
______________________
GroupId (Primarykey)
GroupName
Table:SystemAllocation
_______________________
SystemId(Primarykey)
SystemName
GroupId (foreignkey)
Table:DeviceAllocation
_______________________
DeviceId (Primarykey)
DeviceName
SystemId (foreignkey)
_______________________
Table:SensorAllocation
_______________________
SensorId (Primarykey)
SensorName
GroupId (Primarykey)
SystemId (foreignkey)
_______________________
Table:GRoupAllocation
______________________
GroupId GroupName
1 Group1
2 Group2
3 GRoup3
4 Group4
Table:SystemAllocation
___________________________
SystemId SystemName GroupId
1 system1 1
2 system2 2
3 system3 3
4 system4 2
5 system5 1
Table:DeviceAllocation
___________________________
DeviceId DeviceName SystemId
1 Device1 1
2 Device3 3
3 Device4 2
Table:SensorAllocation
____________________________________________
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
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jan 23, 2007
I have a report that I need to run on 2 different date ranges.
Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE
The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)
So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007
My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007
Any help would be greatly appriciated!
View 2 Replies
View Related
Apr 17, 2008
hi need help to solved date calculation for next month
i explain
i have table employee and the employee insert into table the holidays
the date start >>>> to date end
now i need to create a view only for next moth , in this view i need to see only the relative dates for the next month if the dates is not for the next month i don't need to see it
like this example 09/07/2008 > 09/08/2008 (is not for next month)
like this example 10/09/2008 > 12/09/2008 (is not for next month)
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
in this example i need to see only the relative dates for next month only in the view
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
tb_all_holiday before
id
fname
Start_Date
EndDate
val_holiday
111
aaaa
15/03/2008
10/05/2008
1
222
bbbb
02/05/2008
31/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
VIEW_all_holiday after -next month only
id
fname
Start_Date
EndDate
val_holiday
sum_day_next_month
111
aaaa
01/05/2008
10/05/2008
1
4
222
bbbb
02/05/2008
31/05/2008
3
29
333
cccc
01/05/2008
15/05/2008
4
4
333
cccc
01/05/2008
31/05/2008
1
30
444
dddd
01/05/2008
02/05/2008
1
1
444
dddd
09/05/2008
31/05/2008
1
22
all the time i need to see only the relative dates for the next month only
tnx
View 10 Replies
View Related
Aug 31, 2007
Good morning all,
I've got a little headscratcher for you involving date ranges.
We have a table for recording absences:
Absence(unique_identifier, parent_identifier, date_from, date_to ... )
And an employee table
Employees(unique_identifier, Surname, Firstname, birth_date ...)
Where the relationship between the two is:
Employees.unique_identifier = Absence.parent_identifier
The problem lies when wanting to know whether an employee was off within a specified date range.
Absence:
u_idp_iddate_fromdate_to
112007-02-012007-02-06
222007-01-292007-02-06
322007-03-252007-03-25
432007-06-062007-06-08
542007-02-052007-02-06
Given the above sample results, how can I identify which employees were off during the first week of February (2007-02-01 to 2007-02-07)?
Expected Results:
u_idp_iddate_fromdate_to
112007-02-012007-02-06
222007-01-292007-02-06
542007-02-052007-02-06
Any advice you can give to help me get the answer I need is much appreciated :)
View 8 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Sep 29, 2014
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
View 6 Replies
View Related
May 25, 2015
I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?
I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
View 1 Replies
View Related
Jan 20, 2004
I hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view?
Thanks
View 14 Replies
View Related
Mar 6, 2008
I have a table the looks like this :
IDVar1 Date1
-- --- -------
11018-Feb-02
11123-Dec-02
23027-Nov-02
35009-Oct-02
46025-Mar-02
46310-May-02
55230-May-02
66907-Feb-02
67426-Feb-02
68023-Sep-02
69009-Oct-02
712301-Feb-02
712910-Dec-02
85612-Jan-02
91004-Feb-02
91518-Feb-02
91930-Sep-02
92012-Oct-02
102103-Oct-02
102308-Oct-02
102430-Nov-02
I want to create a table like this :
IF the date (Date1) diffrence between any two dates for the same ID is less than 30 Days CNT1 = 1, ELSE CNT1 =0
IF the date (Date1) diffrence between any two dates for the same ID is less than 60 Days CNT2 = 1, ELSE CNT2 =0
IF the date (Date1) diffrence between any two dates for the same ID is less than 90 Days CNT3 = 1, ELSE CNT1 =0
Note : CNT1,CNT2 and CNT3 for ID =2,3,5 and 8 should be 0 because there are no multiple recors for the same ID
ID CNT1 CNT2 CNT3
-- ---- ---- ----
1
2
3
4
5
6
7
8
9
10
Any help will be welcomed.
View 6 Replies
View Related
Jan 8, 2008
Hi,
Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.
ID FromDate ToDate
01 06/09/2006 06/09/2006
02 06/09/2006 06/12/2006
03 06/09/2006 06/12/2006
04 06/09/2006 06/12/2006
05 06/09/2006 06/12/2006
06 06/09/2006 06/12/2006
07 06/10/2006 06/10/2006
08 06/12/2006 06/12/2006
09 08/30/2006 09/05/2006
10 08/30/2006 09/05/2006
11 08/30/2006 09/05/2006
12 09/13/2006 09/17/2006
13 09/13/2006 09/26/2006
14 09/13/2006 09/26/2006
15 10/05/2006 10/10/2006
16 10/05/2006 10/10/2006
17 10/10/2006 10/15/2006
18 10/11/2006 10/11/2006
19 10/16/2006 10/16/2006
20 10/20/2006 10/22/2006
21 10/23/2006 10/25/2006
From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue.
Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.
The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:
ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate
05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006
09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006
13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006
? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006
? 10/20/2006 10/25/2006 0
View 3 Replies
View Related
Mar 5, 2008
Hi,
I have a table with date ranges for activitys. I need to get the userid , firstdayidle, lastdayidle for when a user has been idle in the last 90 days.
it gets tricky since it also must include users that have no records in activity table or only one record so it has 2 idle periods, etc
I have a hard time wrapping my head around the logic, weither using a datetables or not, so any help would be appreaciated
--my first step is to get the activitys that enter the last 90 days
select * from Activity2
where (enddate > DateAdd(d, -90, getDate()) AND enddate < getDate()) OR (startdate > DateAdd(d, -90, getDate()) AND startdate < getDate())
CREATE TABLE [dbo].[InternalUser](
[userid] [int],-- IDENTITY(1,1) NOT NULL,
[fullname] [varchar](50)
}
insert into [InternalUser] values(1,'a')
insert into [InternalUser] values(2,'b')
insert into [InternalUser] values(3,'c')
insert into [InternalUser] values(4,'d');
insert into [InternalUser] values(5,'e');
insert into [InternalUser] values(6,'f');
CREATE TABLE [dbo].[Activity2](
[activityid] [int] NOT NULL, --IDENTITY(1,1)
[userid] [int] NOT NULL,
[startdate] [datetime] NULL,
[enddate] [datetime] NULL
)
insert into [Activity2] values(1,1,'2007-02-15 00:00:00.000','2008-03-15 00:00:00.000')
insert into [Activity2] values(2,2,'2007-01-01 00:00:00.000','2008-01-02 00:00:00.000')
insert into [Activity2] values(3,2,'2008-01-20 00:00:00.000','2008-04-10 00:00:00.000')
insert into [Activity2] values(4,3,'2008-02-20 00:00:00.000','2008-10-10 00:00:00.000')
insert into [Activity2] values(5,4,'2007-01-01 00:00:00.000','2008-01-16 00:00:00.000')
View 5 Replies
View Related
Aug 10, 2000
I have some tables in which we store integer values for the week and year(by using the datepart function on datetimes). My problem arises because I need to let users be able to query this table based on dates they enter. However, I am having no luck in being able to convert these integers back into valid dates. I would like users to be able to enter something like '6/1/2000' as a start date, and '7/1/2000' as an end date. Then the query would pull all weeks in between. No luck, and I'm thoroughly confused. Is this method of storing dates as ints just plain stupid? thanks.
jason
View 1 Replies
View Related
Feb 10, 2006
Hello
I have a Table
Code Name Gender DateJoined
Now when i Group by Name i get
Name Male Female
jasldk 1 2
But the problem i have is i need to filter the records based on Date joined, but i cant include the DateJoined in my Group by clause ( i am making the Groupby As View) and since my view dont contain the date i couldnt use that as view to use in my application.
Is there any other way i can achieve this?
Thanks in advance
'
With Regards
View 2 Replies
View Related
Dec 12, 2007
I have a date "4/25/2007"
but the requirement is to have data as "04/25/2007"
.if month is single digit it should have leading zeroes, same with date,
can some tell me how to convert this.
View 2 Replies
View Related
