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

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

Derived Columns Expressions Evaluate Incorrectly

Oct 26, 2007

All,

I have seen it happen frequently that I type in a perfectly valid SSIS expression (this is easy for me since I am an old hand at C++/C/C#) in a row in a Derived Column transformation, and it turns red. Or sometimes, I will have an invalid expression that I correct, but it stays red. Finally I have also seen it happen that I make some change in the data flow pipeline and suddenly a Derived Column transform develops an error. I then go into the Derived Column transform and find that the expression has turned red. So, I literally have to go into the expression in these cases, and make a trivial change to them to get the red error to go away. Alternatively, I can cut the derived column expression text, and then paste it back in and it works (this is most telling.)

So, it seems to me the Derived Column is somehow holding onto some meta data about the Derived Column that is getting out of date (rather than re-evaluating the correctness of the Expression.) One thing I usually can do to repro this at times is to remove a column (that the Derived Column depends upon) from the pipeline and then re-add it. When I go into the Derived Column it will be red, and then like I said I have to tweak the expression to force SSIS to re-evaluate the expression.

Anyway, I'm curious if anyone else has seen this?

Thanks.

View 10 Replies View Related

QP Is Missing And It Is Required To Evaluate Default Expressions?

Sep 17, 2007

Hi everyone

I have a really odd problem that has manifested its self. I have a Windows Mobile 6 device that I debugging my application on, the application uses a local Sql Compact sdf file, if I just run the app, when it comes to executing some ado.net against the database I get the following error:-

QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)

If however I open the the database using query analyzer first on the device then everything is ok until I next soft reset the device

Has anyone else come across this?

Thanks in advance
Dan

View 8 Replies View Related

Variable Expressions In Derived Columns - Evaluate ColumnNames??

Jan 24, 2008

Trying to setup a derived column to use an expression stored in a package variable. But it seems that variables are always evaluated as text...I need it to evaluate as a Columname sometimes.

Example:
On an ETL of Products I want a new derived column that uses two other columns.

I can hardcode an expression of ProductID + " - " + ProductName and that results in dynamic output. But now I want to use that as an expression stored in a variable so I can change it when needed.

So I make that expression = @[User::Variable]
and stuff into @variable ( a string param) : ProductID + " - " + ProductName
My output is the literal "ProductID + " - " + ProductName", and not the actual ID's and Names

I've tried with/without brackets, quotes and braces but no change.

Any way I can get the pieces in that variable expression to evaluate as column names?

View 6 Replies View Related

Evaluate Logic Output As Resulting Field From Query

Nov 25, 2005

have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.

For a very general example: In the .NET code, the following variables are recognized:
sex="M"
Paid=4350.00
Outstanding=28000.50

One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..

EVAL EXPR
BOOL Sex='F' and Paid/Outstanding < 27.50
BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F'
INT Paid*52.33

In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?

If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....

View 3 Replies View Related

SQL CE Error QP Is Missing And It Is Required To Evaluate Default Expressions. Ensure That Sqlceqp30.dll Is In The Same Director

Oct 9, 2007

Hi,
I am getting an error when i am trying to insert some value to the sql ce database

"QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)"

Please help

Thanks
Nebu

View 1 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

Simplifying Queries With Complex Sub-expressions?

Dec 11, 2007

I'm dealing with queries that manages a lot of data consolidation and I'm having to repeat large blocks of code to get the desired result.
I'm wondering if that is any way to make it simple.

My queries looks like this:

Code BlockSELECT
(A + B) AS C,
(D + E) AS F,
(G + H) AS I,
(A + B) * (D + E) AS total1,
(D + E) * (G + H) AS total2,
(A + B) * (G + H) AS total3,
(A + B) * (D + E) * (G + H) AS total
FROM table

Can I somehow declare each part of the expression and make it something like this?

Code Block
SELECT
(A + B) AS C,
(D + E) AS F,
(G + H) AS I,
C * F AS total1,
F * I AS total2,
C * I AS total3,
C * F * I AS total
FROM table

Maybe it seems silly, but each sub-expression, on the real life, includes a lot of CASEs and aggragate functions and my codes are getting complex and confuse.

Since I have lots of code pieces that I repeat more than once, I would like to make it simpler.

tks for any help

View 5 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

Help Needed In This Complex Logic

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

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

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

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

{RESOLVED} Date Logic - Calculating A Date Range

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

Help On Complex Date Solution Date Remain To Next Month

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

Date Conversion And Expressions

Aug 31, 2006

I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2 FROM myTable ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7"). Thanks in advance to anyone who helps me.

View 4 Replies View Related

Date Literals In Expressions?

Sep 12, 2007

How do I specify a date literal in an expresison? It's not covered in Books Online. None of the following worked:

mydate == '1899-12-30'
mydate == "1899-12-30"
mydate == #1899-12-30#

This did work:

mydate == (DT_DATE) 0

but it's not self-explanatory and it would be utterly stupid if that's the only way to specify a date literal. Are we once again victims of the "rushed-out-the-door" syndrome?

View 10 Replies View Related

Date Range Logic

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

Reporting Services :: SSRS Date Expressions

Sep 21, 2015

SSRS expressions for the following queries.

Last year last Week (15/9/2014)
Last Week ( 14/9/2015)
Before Last Week (7/9/2015)

View 2 Replies View Related

Transact SQL :: GROUP Dynamically Generated Date And String Expressions

May 17, 2015

I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?

INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID)
SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID
WHERE ...
GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID

View 2 Replies View Related

T-SQL (SS2K8) :: Date Logic - How To Get Previous 6 Months Data

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

SQL Server 2008 :: Write Query For Date Logic?

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

Valid Expressions Are Constants, Constant Expressions, And (in Some Contexts) Variables. Column Names Are Not Permitted.

Dec 11, 2007

I want to have this query insert a bunch of XML but i get this error...

Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117

The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151

The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

What am i doing wrong...Can anyone help me out!! Thanks!!

p.s I know this query looks crazy...

Code Block

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON

-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1

--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0

INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0

INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked

-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0

-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1

go

View 1 Replies View Related

Complex Date Formatting

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

Complex Date Computations

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

Really Complex Date Calculation

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

Complex Date Range Gaps

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

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.