Solved

VB SQL

Posted on 2015-01-28
8
162 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 47

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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