Help to build a query to select data from the previous n days

Hi to all.

I'm new with SQL. I need to build a query that reports data from the previous two (2) or five (5) or n days.

I think is better explain the scenario with an example:

Database Name: db_rec_ss
View Name: is_vrec
Tables associated in views: is_reg, is_bill, is_items

The view (is_vrec) groups data in this format (simplified as I can):

id_item | desc_item    |  totalsell_ammount | date
01        | Blue book     |  21.00                     | 10/2/2008
02        | Red book      |  34.00                     | 10/2/2008
01        | Blue book     |  10.00                     | 10/3/2008
02        | Red book      |  32.00                     | 10/3/2008
01        | Blue book     |  25.00                     | 10/4/2008
02        | Red book      |  15.00                     | 10/4/2008
01        | Blue book     |  12.00                     | 10/5/2008
02        | Red book      |  10.00                     | 10/5/2008

Ok.. you got it..!

I need to build a SQL query that retrieves the data from the previous 2 days, in this format: (For the example today is 10/5/2008)

id_item | desc_item    |  10/4/2008    | 10/3/2008
01        | Blue book     |    25.00         |    10.00
02        | Red book      |    15.00         |    32.00                  

An important tip is that non-work days, doesn't generate data. I think this simplified the query, because I only need that the query retrieves the last n days. Doesn't important to calculate if is work or non-work day.

I don't want to import data on an Excel worksheet.

With the information supplied, can U help me?

Thanks a lot in advance for your help...!
goltrekAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaProwlerCommented:
If I understand you correctly, you are trying to select data from a view in a SQL database in which you want to get data that is only within the past few days. If so, you can do this with a select statement such as this:
SELECT * FROM [is_vrec] WHERE [date] BETWEEN DATEADD(d,-5,GETDATE()) AND GETDATE()

Open in new window

0
SharathData EngineerCommented:
Do you have fixed number of columns or is it a dynamic number?
0
StanyslawCommented:
Try something like this:

SELECT id_item, desc_item,
Sum( CASE WHEN date BETWEEEN GetDate()-1 AND GetDate() THEN totalsell_ammount ELSE 0 END ) as "date1",
Sum( CASE WHEN date BETWEEEN GetDate()-2 AND GetDate()-1 THEN totalsell_ammount ELSE 0 END ) as "date2"
WHERE date > GetDate()-2
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

StanyslawCommented:
FROM missed in prev. answer:
Try something like this:



SELECT id_item, desc_item,
Sum( CASE WHEN date BETWEEEN GetDate()-1 AND GetDate() THEN totalsell_ammount ELSE 0 END ) as "date1",
Sum( CASE WHEN date BETWEEEN GetDate()-2 AND GetDate()-1 THEN totalsell_ammount ELSE 0 END ) as "date2"
FROM is_vrec
WHERE date > GetDate()-2

Open in new window

0
StanyslawCommented:
Ops, DATEADD should be used instaed of date-1 and date-2

SELECT id_item, desc_item,
Sum( CASE WHEN date BETWEEEN BETWEEN DATEADD(d,-1,GetDate()) AND GetDate() THEN totalsell_ammount ELSE 0 END ) as "date1",
Sum( CASE WHEN date BETWEEEN DATEADD(d,-2,GetDate()) AND DATEADD(d,-1,GetDate()) THEN totalsell_ammount ELSE 0 END ) as "date2"
FROM is_vrec
WHERE date > DATEADD(d,-2,GetDate())

Open in new window

0
goltrekAuthor Commented:
Hi Stanyslaw, thank you very much.

Your last message was applied in my case:

Only two questions:
1.- I need to reflect the corresponding date for data in the query. What sentence must I add to the query to show the date for the day reported?

2.- What about adding three or more days previous? Must I add the following code to retrieve data for three days?


Sum(CASE WHEN hasta BETWEEN DATEADD(d,-3,GetDate()) AND DATEADD(d,-1,GetDate()) THEN total ELSE 0 END) as "date3"

Open in new window

0
goltrekAuthor Commented:
Thanks Sharath_123,

Yes, the numbers of columns is fixed.

Regards.
0
Mark WillsTopic AdvisorCommented:
Here you go, might be overkill, but completely flexible - just nominate the @date, and really could be a stored procedure with @date as a parameter - will dynamically generate all the columns from that date...

if doing a stored procedure, just uncomment the lines and remove the declare and set of @date as indicated... then all you do is exec sp_pivot_totalsell '01 Sep 2008'


--create procedure sp_pivot_totalsell(@date datetime)      -- remove comment to make stored procedure
--as                                                       -- remove comment to make stored procedure
declare @date datetime                                     -- comment out if stored procedure
set @date = '01 Sep 2008'                                  -- comment out if stored procedure
 
declare @sql varchar(max)
declare @hdrdate varchar(max)
declare @pvtdate varchar(max)
 
select @hdrdate = isnull(@hdrdate,'') + ',['+convert(varchar,date,106)+'] AS '''+convert(varchar,date,106)+''' ' from is_vrec where date >= @date group by date
print @hdrdate
select @pvtdate = isnull(@pvtdate,'') + ',['+convert(varchar,date,106)+']' from is_vrec where date >= @date group by date
print @pvtdate
 
set @sql = '
SELECT id_item, desc_item '+@hdrdate+'
FROM 
( SELECT id_item, desc_item, convert(datetime,convert(varchar,date,106)) as date, totalsell_amount from is_vrec) as p
PIVOT
( SUM (totalsell_amount) FOR date IN ( '+right(@pvtdate,len(@pvtdate)-1)+' ) ) AS pvt
ORDER BY id_item, desc_item'
exec (@sql)
 
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErnariashCommented:
Using dynamic query will solve your case...@N = 5 , 2 or any number...

DECLARE @N AS INT
DECLARE @STARTDATE AS datetime
DECLARE @DYNAMICCASE AS NVARCHAR(4000)
 
SET  @STARTDATE = Getdate() 
SET @DYNAMICCASE =  ' '
 
SET @N = 5
 
WHILE @N > 0
BEGIN
SET @DYNAMICCASE =    @DYNAMICCASE +', '
SET @DYNAMICCASE = @DYNAMICCASE+'SUM(CASE WHEN Convert(nvarchar(8),DATE,112) =''' + Convert(nvarchar(8),dateadd(day,-@N,@STARTDATE),112)+ ''' THEN totalsell_ammount ELSE 0 END) AS '''+Convert(nvarchar(8),dateadd(day,-@N,@STARTDATE),112) +''''
SET @N = @N-1
END
 
SET @DYNAMICCASE = N'select id_item, desc_item '+ @DYNAMICCASE + N'from  db_rec_ss.dbo.is_vrec group by id_item, desc_item'
 
execute sp_executesql @DYNAMICCASE, N'@N AS INT, @STARTDATE AS datetime', @N, @STARTDATE

Open in new window

0
ErnariashCommented:
Ok, in case you only need that the query retrieves the last n days with data from your table here is the dynamic query to do the job.
Please notice if you change my previous @DYNAMICCASE AS NVARCHAR(MAX) it will allow you to have a very big N number of days. Thanks...


DECLARE @N AS INT
DECLARE @STARTDATE AS datetime
DECLARE @DYNAMICCASE AS NVARCHAR(MAX)
SET  @STARTDATE = Getdate() 
SET @DYNAMICCASE =  ' '
 
SET @N = 20
DECLARE @is_vrec TABLE (
	ID INT IDENTITY(1,1) NOT NULL,
	DATE DATETIME
)
SET ROWCOUNT @N
INSERT INTO @is_vrec (DATE)
select DATE   from  is_vrec
where date < @STARTDATE
order by date desc
--in case @N is less than your ROWCOUNT
SELECT  @N = MAX(ID) FROM @is_vrec
 
WHILE @N > 0
BEGIN
 
SET @DYNAMICCASE =@DYNAMICCASE+ ', ' +  (SELECT 'SUM(CASE WHEN Convert(nvarchar(8),DATE,112) =''' + Convert(nvarchar(8),DATE,112)+ ''' THEN totalsell_ammount ELSE 0 END) AS '''+Convert(nvarchar(8),DATE,112) +''''
FROM @is_vrec
WHERE ID = @N)
 
SET @N = @N-1
END
 
Set @DYNAMICCASE = N'select id_item, desc_item '+ @DYNAMICCASE + N'from is_vrec group by id_item, desc_item'
eXecute sp_executesql @DYNAMICCASE, N'@N AS INT, @STARTDATE AS datetime', @N, @STARTDATE

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.