Solved

VB SQL

Posted on 2015-01-28
8
166 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 50

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Title # Comments Views Activity
SQL Server 2008 R2 service pack updates 5 61
Database Owner 3 41
SQL syntax question 6 69
T-SQL: Need a database plan to mine a pretty big Log table 4 47
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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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