Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB SQL

Posted on 2015-01-28
8
Medium Priority
?
172 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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