Generating Dates
-
I am working on a database in which there is a table SaleLine, -
SaleLine
SaleCode [FK Sale table]
PCode [Fk Product table]
Qnty
Price
And other tables, -
Sale
SaleCode [PK]
VendorCode [FK Vendor table]
SaleDate
Now entries exists in Sale and SaleLine only for the date on which Sale has been made. I have to make a report of following type (for any specific vendor):
Date|TotalValueofSale
Now this is easy but what needed is it must show dates on which sales has not been made with TotalValueofSale = 0.
Any suggestions how to do this?
Thanks
-
Create a table with one row for each date, and left join that to the result of the join of the other two tables.
-
I use individual queries to define each level, then:
Select * FROM
(Select query that returns the desired Master
, Date in your example) Master
LEFT JOIN (Select query that returns the level of child data
, TotalValueofSale in your example) Sales
ON Master.PK = Sales.FK
Use of PK = FK means data that can be used to join the queries, this may be a multi field Join, like an account number and a date.
You can also add additional Joins to restrict at differing levels, Show all Sales over 100, etc. Use of the INNER Join will restrict the Master, while LEFT lets Master do it's own thang.
Once this becomes static you can consider a view or conversion to table joins, which will be faster than the above.
Please excuse the use of * I am lazy.
Andy
-
I can understand this should be the approach, main problem is on generating dates.
-
I can understand this should be the approach, main problem is on generating dates.
-
Have a look at this article
http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp
N 56°04'39.16"
E 12°55'05.25" -
Do you have a range of dates ? I mean is the above requirement for a specific period of dates.
If you know your startDate and EndDate
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @CurrDate DATETIME
SET @CurrDate = @StartDate
WHILE @CurrDate < =@EndDate
BEGIN
SELECT .......
@CurrDate AS SaleDate
FROM
.....
WHERE
SaleDate = @CurrDate
GROUP BY .....
SET
@CurrDate = DATEADD(dd,1,@CurrDate )
END
IF you don't have the period, Get the min and max of SaleDate from your table and set them to your variables.
Thanks
Prasad Bhogadi
www.inforaise.com -
Micheal Valentine Jones has a faster way but this is an easy way...
Step 1. Create a permanent Tally table... (it has many, many uses)
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
Step 2. Then you can get dates pretty easily and anytime you want...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '20070101'
SET @EndDate = '20071231'
SELECT @StartDate + N - 1
FROM dbo.Tally
WHERE @StartDate + N - 1 <= @EndDate
No loops, no cursors, no RBAR
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No loops, no cursors, no RBAR 
You just love patting yourself on the back eh Jeff
Far away is close at hand in the images of elsewhere.
Anon. -
You may find the function on this link useful for generating a date table.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Function F_TABLE_DATE is a multistatement table-valued function that returns a table containing a variety of attributes, more than 60, of all dates from @FIRST_DATE through @LAST_DATE. You can store the results in a table, or just use it directly in an application.
--This code returns all dates for the current decade. select * from dbo.F_TABLE_DATE ( '20000101','20101231')
-
You just love patting yourself on the back eh Jeff
Heh... LOL... beats the hell out of patting myself on the cursor
DOH! You guys made me say "cursor" again 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Now, THAT's what I'm talkin' 'bout
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'm new today - both to SQL Server and this forum - and this thread has been really useful: thanks to all contributors.
However, my problem is that I've got multiple sales items being listed for each date which means I only get a single NULL record generated from the date table side of the join if no records in the other table have that date. I would like to generate null records for each sales item that has no entry for a particular date. Has anyone got a solution to that?
-
OK, thanks for the advice. Here goes. If I have the following:
Table: Values
Item SaleDate Value
Z 01-Oct-07 15
Z 04-Oct-07 7
and:
Table: Dates
Date
01-Oct-07
02-Oct-07
03-Oct-07
04-Oct-07
I can generate null rows for Z with the following SQL:
SELECT Values.Item, Dates.Date, Values.Value
FROM Dates LEFT OUTER JOIN Values ON Dates.Date = Values.SaleDate
However, if there are multiple items, eg:
Table: Values
Item SaleDate Value
X 01-Oct-07 10
Y 01-Oct-07 12
Z 01-Oct-07 15
X 02-Oct-07 8
Y 02-Oct-07 9
Y 04-Oct-07 11
Z 04-Oct-07 7
the same SQL gives me:
Item Date Value
X 01-Oct-07 10
Y 01-Oct-07 12
Z 01-Oct-07 15
X 02-Oct-07 8
Y 02-Oct-07 9
03-Oct-07 NULL
Y 04-Oct-07 11
Z 04-Oct-07 7
I know why it doesn't work but can anyone write the SQL that would give me nulls for all missing item records, i.e.:
Item Date Value
X 01-Oct-07 10
Y 01-Oct-07 12
Z 01-Oct-07 15
X 02-Oct-07 8
Y 02-Oct-07 9
Z 02-Oct-07 NULL
X 03-Oct-07 NULL
Y 03-Oct-07 NULL
Z 03-Oct-07 NULL
X 04-Oct-07 NULL
Y 04-Oct-07 11
Z 04-Oct-07 7
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply