Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-19
11
Medium Priority
?
306 Views
Last Modified: 2012-05-05
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...!
0
Comment
Question by:goltrek
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 1

Expert Comment

by:DaProwler
ID: 23000572
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
 
LVL 41

Expert Comment

by:Sharath
ID: 23000585
Do you have fixed number of columns or is it a dynamic number?
0
 
LVL 3

Expert Comment

by:Stanyslaw
ID: 23000588
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Expert Comment

by:Stanyslaw
ID: 23000591
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
 
LVL 3

Assisted Solution

by:Stanyslaw
Stanyslaw earned 1000 total points
ID: 23000632
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
 

Author Comment

by:goltrek
ID: 23000868
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
 

Author Comment

by:goltrek
ID: 23000875
Thanks Sharath_123,

Yes, the numbers of columns is fixed.

Regards.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 23003738
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 23004710
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
 
LVL 9

Expert Comment

by:Ernariash
ID: 23005166
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

805 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