Solved

VB SQL

Posted on 2015-01-28
8
163 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 48

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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