Loops in SQL? 2

[jsplice]

I have basic SQL knowledge which doesnt allow me to do what I am about to ask, nor do I know if it's even possible. But here's my problem:

I have a database table that stores sales postings from each day. I need to be able to verify that no days are missing in the table (the software we use isn't perfect, and doesnt always post the sales). Is there some way I can go through the table, and end up having the query tell me which days were not found in the table? As of now, I am looking through these tables manually, and there's probabaly at least 100 of them...

 

[ClaireHCM]

1.Create a comparing table

create table time2(days datetime)

2.create the following stored procedure to insert complete list of dates

create proc proc1 as
declare @count int
set @count = 0
while @count <=(select datediff(d,min(days),max(days)) from time)
begin
insert into time2 select min(days)+@count from time
set @count = @count+1
end

--Note.The original table I named it as &quot;time&quot;,and the column you want to check named &quot;days&quot;

3.Execute this stored procedure

exec proc1

4.execute the following query to see what are the missing date

select * from time2 where days not in
(select time.days from time2
inner join time on time.days = time2.days)

 

[NoCoolHandle]

Assuming 1 record per day you could use a corelated sub query and an existance test..

If you have more that one record perday, you could use a distinct clause in the select and where clauses...

sample script should demonstrate the query technique...

HTH

Rob

create database junk
go
use junk
create table dates(postdate datetime)
insert into dates values(convert(varchar(10) , dateadd(dd,-1,getdate()),1))
insert into dates values(convert(varchar(10) , dateadd(dd,-2,getdate()),1))
insert into dates values(convert(varchar(10) , dateadd(dd,-4,getdate()),1))
insert into dates values(convert(varchar(10) , dateadd(dd,-5,getdate()),1))
insert into dates values(convert(varchar(10) , dateadd(dd,-6,getdate()),1))
insert into dates values(convert(varchar(10) , dateadd(dd,-7,getdate()),1))
insert into dates values(convert(varchar(10) , dateadd(dd,-9,getdate()),1))
go
select * from dates
go
select dateadd(dd,-1,postdate) from dates d1
where not exists (select * from dates d where dateadd(dd,1,d.postdate) = d1.postdate)

 

[tlbroadbent]

Try this.

--Set nocount on to eliminate Rows Affected messages
set nocount on

--Create a temporary table to hold all the days you want to verify
Create Table #days(MissingDate SmallDateTime)

--Declare a variable to control the While loop
Declare @cnt int
Set @cnt=90 --check the past 90 days

--The loop
While @cnt>0
Begin
--Insert a row for each day to be checked against.
--This sample uses getdate() - @cnt to calculate dates.
Insert #days Values(convert(int, getdate() - @cnt))

--Decrement the counter
Set @cnt=@cnt-1
End

--Select the dates with no match in the Sales table
Select d.MissingDate
From #days d
Left Join dbo.tblSales s
--Convert needed only if the column in the sales table is date/time.
On d.MissingDate=convert(int, s.SaleDateTime)
Where s.SaleDateTime Is Null
Order by 1

--Cleanup
Drop Table #days

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[r937]

all these different tables....

my suggestion? a generic integers table

the best part is, you can use it over and over, and not only for generating dates, but for other purposes

see the article Finding all the dates between two dates

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html

then use a left join with your data table


rudy


rudy

 

[tlbroadbent]

Execllent suggestion Rudy.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[NoCoolHandle]

Rudy,
doesn't your soultion need a new table?

Mine only showed a query that would solve the problem, but had a script to build a working demo on a valid datatime field. It would even be better if dates were in a plane char field.

No triggers, just a nice little query.....

And it works for any sequence where there is a missing entry.

 

[r937]

yes, my solution requires a table, but once you have the integers table, you'll find yourself using it for other things as well

and in addition, you won't be writing any more scripts, at least not for simple stuff like this...

 

[jsplice]

Thanks for all the help everyone. I've ran into another problem. What I forgot to mention is that there is also a location field. This field corresponds to the store number (we are a grocery chain). So I guess what I need to do now is generate the compare table, but for each date have the corresponding store number. Is that correct?

 

[r937]

that's a simple cross join between the generated dates and the store numbers, after which you left join to your data

if that's not clear, show your queries/tables

 

[JJB81]

I need to build a query that splits up records into three fields.
this is the field:
2:268:22:11,1 2:268:20:4,1 2:268:21:12,1 2:268:22:12,12 2:268:21:13,13

i need it to go into three seperate fields
SB1 SB2 SB3 based on the spaces between the origial field

is this possible? the data sets vary meaning it could also look like this:
E_SBLS
2:268:22:11,1 2:268:20:4,1 2:268:21:12,1 2:268:22:12,12 2:268:21:13,13 2:268:22:11,1 2:268:20:4,1 2:268:21:12,1 2:268:22:12,12 2:268:21:13,13 or just have one =2:268:22:11,1

 

[SQLBill]

JJB81,

Please start your own thread.

-SQLBill

 

[JJB81]

huh

 

[SQLBill]

JJB81,

You 'tagged' onto someone elses thread (jsplices). Please create your own thread/post.

-SQLBill

 

[jsplice]

r937: So are you or are you not saying that I need to create the missingdates table, having a list of the dates for each location? Here's how everything is set up to make it more clear.

The main table that stores the sale activity is called slshead. As of now I have a table I created called days which is a simple one column table that contains a list of dates for which I wish to check against slshead to find the missing dates. I need to find the missing dates for each store. As of now, the query I'm using only returns the dates for which there are no postings from any of the stores. So the fields of interest in the slshead table are:

location (store number)
postdate (date that the sales record was posted)

This is the query that I am using that is returning only the dates for which there are absolutely no sales postings:

select d.MissingDate
from days d
left join dbo.slshead s
on d.MissingDate=convert(int, s.postdate)
where s.postdate is null
order by 1

Perhaps another way would be to simply have the query accept as input which location it is I wish to check for missing dates. Would that be easier than doing it all at once? I stumped someone in my department on this one...

 

[SQLBill]

JJB81's query has moved to thread183-568580

-SQLBill

 

[r937]

jsplice, do you have an integers table? if not, can you build one? you'll only have to do it once, whereas with your dates table, you'll forever have to update it for the date range you want to report on

also, what sort of date ranges are we talking about? a week? a year? this will tell you how manuy integers to load

finally, do you have a location table? if not, can you create one (use SELECT DISTINCT location INTO... )

 

[jsplice]

No, I'm not using an integers table. I'm using a simple table that stores dates. This shouldn't be a problem. The query can simply recreate the table each time after this query is perfected because I'll only be checking back 100 days or so.

I don't have a location table either. I guess I'd have to rip the ones out of the slshead table like you said, but I'm not sure how to do that either...

 

[r937]

&quot;The query can simply recreate the table each time...&quot; -- okay, as you wish, but an integers table is done just once and you never need to worry about it again

as for your table of locations --

select distinct location
into locationtable
from slshead

once you have that, try the cross join --

select datestable.thedate
, locationtable.location
from datestable
, locationtable

notice there's no join condition

you can also write it like this --

select datestable.thedate
, locationtable.location
from datestable
cross
join locationtable

peruse that output and assure yourself it's producing all the dates for every location

now do the left join

select whatever,columns,you,want
from (
select datestable.thedate
, locationtable.location
from datestable
cross
join locationtable
) as X
left outer
join slshead
on X.thedate = slshead.thedate
and X.location = slshead.location

rudy