Solved

VB SQL

Posted on 2015-01-28
8
164 Views
Last Modified: 2015-02-09
Hi Experts

Using VB.net and SQLServer2008

I have a function

SELECT    distinct Id, Start, Finish, Code
FROM         CodeFile
WHERE     ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or
((Start < '2014-04-01') and (finish > 2014-01-01')) or
((Start > '2014-01-01') and (finish < '2014-04-01')) or
((Start < '2014-04-01') and (finish is NULL))

This supplies

Id                    Start                        End                  Code
1                     2012-03-30             2014-01-28     C1
2                     2014-01-28             2014-02-10     C2
3                     2014-02-10             NULL               C1

I now need to create a function to have entries showing each day for each id
between the selected dates 2014-01-01 to 2014-04-01
e.g id - 1 has 27 entries between 2014-01-01 to 2014-01-28 so I need 27 entries
id                   Start                          End                 Code
1                     2014-01-01              2014-01-01   C1
 1                    2014-01-02              2014-01-02   C1 etc
0
Comment
Question by:Kevinfeening
8 Comments
 
LVL 14

Expert Comment

by:nishant joshi
ID: 40576740
Do you have  duplicate or multiple values in id column?

Please give more data rows to analyse data and it's requirement.

Thanks,
0
 

Author Comment

by:Kevinfeening
ID: 40576897
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40576968
Did you realize that you have a non useful criteria (2nd line of WHERE clause)?
SELECT    distinct Id, Start, Finish, Code
 FROM         CodeFile
 WHERE     ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or
 ((Start > '2014-01-01') and (finish < '2014-04-01')) or
 ((Start < '2014-04-01') and (finish is NULL))

Open in new window


Also, if I understood your question what you need is to run the same query but without the DISTINCT keyword:
SELECT    Start, Finish, Code
 FROM         CodeFile
 WHERE     ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or
 ((Start > '2014-01-01') and (finish < '2014-04-01')) or
 ((Start < '2014-04-01') and (finish is NULL))

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40577111
try this ....

not clear what you want for the last entry ... the actual finish date or 20140401  if finish is greater....
similarly what do you want when start is before 20140101?

;with cte as (
SELECT    distinct Id, Start, Finish, Code
FROM         CodeFile
WHERE     ((start <= 2014-01-01' ) and (Finish >= '2014-04-01')) or
((Start < '2014-04-01') and (finish > 2014-01-01')) or
((Start > '2014-01-01') and (finish < '2014-04-01')) or
((Start < '2014-04-01') and (finish is NULL))
)
,n as (select 0 as d union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9)
,n3 as (select (a.d*100)+(b.d*10)+c.d  as d from n as a,n as b,n as c)
Select id
      ,Dateadd(dd,case when start between '20140101' and '20140401'
                       then n3.d else 0 end,start) as start
      ,Dateadd(dd,case when start between '20140101' and '20140401'
                        and dateadd(dd,n3.d,start)<finish
                       then n3.d else 0 end,
                  case when start between '20140101' and '20140401'
                        and dateadd(dd,n3.d,start)<finish
                       then start else finish end)as finish
      ,code
 from cte cross join n3
 where n3.d between 0 and datediff(d,'20140101','20140401')
 order by id,start,finish 

Open in new window

0
 

Author Comment

by:Kevinfeening
ID: 40578361
Thanks

Id             Start                      End                   Code
1              2011-03-30           2014-01-28         4
1              2014-01-28           2014-02-10         5
1              2014-02-10           NULL                   4

I need the file to have from the first line   1              2011-03-30           2014-01-28         4
the dates between the selected start date 20140101 and selected end date 2014-04-01
e.g start = 2014-01-01 next record 2014-01-02 up to 2014-01-28
2nd line all the dates between 2014-01-29 and 2014-02-10 (as 2014-01-28 has already been created)
3rd line all the dates between 2014-02-11 and 2014-04-01

Hope that makes sence
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40578515
no its doesn't make sense to me at present , and seems very different to what you originally where showing...

for the 3 rows
Id             Start                      End                   Code
1              2011-03-30           2014-01-28         4
1              2014-01-28           2014-02-10         5
1              2014-02-10           NULL                   4


please show us the results you desire.... and have another attempt at specifying what you need....

it would assist if you could give us more background as to what the system is trying to provide...
0
 

Author Comment

by:Kevinfeening
ID: 40578550
Sorry from the original table using the Function at the top
I get

Id                    Start                        End                  Code
 1                     2012-03-30             2014-01-28     C1
 2                     2014-01-28             2014-02-10     C2
 3                     2014-02-10             NULL               C1
etc

This table has the start date for when a item with Code C1 has been issued to a employee then the end date when returned or NULL is still issued

I need the create between  2014-01-01 to 2014-04-01 a individual record for each date the  item was issued
so Id 1 code C1 was issued between 2012-03-30 till 2014-01-28 as the start date is outside 2014-01-01 the records I need
are 2014-01-01 to 2014-01-28 (28 records) as shown below
Id                    Start                        End                  Code
1                     2014-01-01             2014-01-01     C1
1                     2014-01-02             2014-01-02     C1
1                     2014-01-03             2014-01-03     C1
etc up to 2014-01-28

With Id 3 as the item has not been returned (End = NULL) I need each date between 2014-02-10 and 2014-04-01

Thanks for you help if this doesn't make sense I will have to try another approach
0
 

Author Closing Comment

by:Kevinfeening
ID: 40599972
Sorry It took so long I changes the last line to
where n3.d between 0 and datediff(d,start,finish)-1 and all worked
Thanks
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question