Filter Expression

Hi,

I'm trying to create a query where the results are restricted by the date selected. When the user selects two different dates, the query works. However, if the user wanted to see receivals for only one day, the query returns nulls even though there are receivals for that day. Below is the code:

SELECT {{[Measures].[Tonnes]}} on 0,
NonEmptyCrossJoin({{Filter([Date].[Day].members, [Date].currentmember.name = "2006-10-16")}}, {{[Transaction].[Closing CarryOver].[IN].[Receival tonnes]}}, {{[Measures].[Tonnes]}}, 2) DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

The "2006-10-16" is the date selected by the user. Any ideas why the filter doesn't like just returned one date, would be extremely helpful! Thanks.

First, you should lose NonEmptyCrossJoin from the query, which is hurting here, and also may produce wrong results if you have more than one measure group.

The simplest query to do what you want is

SELECT {[Measures].[Tonnes]} on 0,
NON EMPTY {([Date].[Day].[2006-10-16], [Transaction].[Closing CarryOver].[IN].[Receival tonnes])} DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

Filter does not work on a group?

I must be missing something. I have a table with two groups. One groups on
the user who created the record the other on the date the record was created.
I sort on the username group by name and the createdate group with a
category type.
My dataset pulls information for the current year however in this table I
want to only display data for the last week. If I apply the filter to the
table I receive expected results however if I apply the filter to the group
nothing comes back.
For either group if I put "= #2/22/2005#" for the expression "<=" as the
operator and "= Fields!CreateDate" as the Value I return 0 records despite
having some. I know my syntax is correct becaus this works at the table
level.
Can anyone tell me what I am missing?And you have date values in there more recent than 2/22/2005?
2/22/2005 <= Fields!CreateDate
I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
>I must be missing something. I have a table with two groups. One groups
>on
> the user who created the record the other on the date the record was
> created.
> I sort on the username group by name and the createdate group with a
> category type.
> My dataset pulls information for the current year however in this table I
> want to only display data for the last week. If I apply the filter to the
> table I receive expected results however if I apply the filter to the
> group
> nothing comes back.
> For either group if I put "= #2/22/2005#" for the expression "<=" as the
> operator and "= Fields!CreateDate" as the Value I return 0 records despite
> having some. I know my syntax is correct becaus this works at the table
> level.
> Can anyone tell me what I am missing?|||Same result. I know there are values past 2/22/2005 because I see them
without the filter applied. The filter works at the table level just not the
group level. This is strange.
"Jeff A. Stucker" wrote:
> And you have date values in there more recent than 2/22/2005?
> 2/22/2005 <= Fields!CreateDate
> I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
> the value.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
> news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
> >I must be missing something. I have a table with two groups. One groups
> >on
> > the user who created the record the other on the date the record was
> > created.
> > I sort on the username group by name and the createdate group with a
> > category type.
> >
> > My dataset pulls information for the current year however in this table I
> > want to only display data for the last week. If I apply the filter to the
> > table I receive expected results however if I apply the filter to the
> > group
> > nothing comes back.
> >
> > For either group if I put "= #2/22/2005#" for the expression "<=" as the
> > operator and "= Fields!CreateDate" as the Value I return 0 records despite
> > having some. I know my syntax is correct becaus this works at the table
> > level.
> >
> > Can anyone tell me what I am missing?
>
>

Filter date for ToDay, some problem with time.

I all.

In a talbe I've a datatime field. for example it contain '16-4-2007 10:45'.

I like to write a SQL that return all record with the date field equals '16-4-2007' (it's not important the time). how to?

thank you.

Try this query to retrive data

select

*from clientswhere clientAddressbetween'2007-04-12 00:00:00.000'and'2007-04-12 23:59:59.999'

The alternative query is

select

*from clientswhere clientAddress>='2007-04-12'and clientAddress<'2007-04-13'

The time format is yyyy-MM-dd and time. Since you are saving time in DB the query should be framed as above

Hope this will help you

|||

try this syntax

print

convert(varchar(20),convert(datetime,'16-04-2006 16:45',105),105)

how it works: convert string to date time with correct format for date string and next convert date time back to string with format you need.

See help for CONVERT in T-SQL help for more format info.

filter data by row number

SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2

This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).

SELECT [Patient Identifier], Date, [Operator Index], Time

FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Filter (Where?) in an Expression

I have an expression in SSRS that I need some help with. I am trying to just
filter off of a date that is passed from another expression and return all
the Db results where the date is = to the Date in the other expression.
Basically something like this
=Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value = Parameters!FiscalYearMinus1.Value)
I know where isnt valid in an expression but this is the only way I know to
say it. So lets say the Date is 2006 it would give me the Transaction
amount for all of 2005.
ThanksHi,
have you tried to use the filter tab in the table/matrix properties ?
"rbyers01" wrote:
> I have an expression in SSRS that I need some help with. I am trying to just
> filter off of a date that is passed from another expression and return all
> the Db results where the date is = to the Date in the other expression.
> Basically something like this
> =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value => Parameters!FiscalYearMinus1.Value)
> I know where isnt valid in an expression but this is the only way I know to
> say it. So lets say the Date is 2006 it would give me the Transaction
> amount for all of 2005.
> Thanks|||Thanks for the reply
Ok I tried that but I got an error message about Data types not matching.
If I do the filter on the whole table will affect oter dates in the Table? i
just need the filter to run on one cell of the table.
"Cedric" wrote:
> Hi,
> have you tried to use the filter tab in the table/matrix properties ?
> "rbyers01" wrote:
> > I have an expression in SSRS that I need some help with. I am trying to just
> > filter off of a date that is passed from another expression and return all
> > the Db results where the date is = to the Date in the other expression.
> > Basically something like this
> >
> > =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value => > Parameters!FiscalYearMinus1.Value)
> >
> > I know where isnt valid in an expression but this is the only way I know to
> > say it. So lets say the Date is 2006 it would give me the Transaction
> > amount for all of 2005.
> >
> > Thanks|||Hi,
the filter will apply to all the data on the table. The only way I know is
to create another table with a unique cell containing your data filtered by
the parameter .
Cédric
"rbyers01" wrote:
> Thanks for the reply
> Ok I tried that but I got an error message about Data types not matching.
> If I do the filter on the whole table will affect oter dates in the Table? i
> just need the filter to run on one cell of the table.
> "Cedric" wrote:
> > Hi,
> >
> > have you tried to use the filter tab in the table/matrix properties ?
> >
> > "rbyers01" wrote:
> >
> > > I have an expression in SSRS that I need some help with. I am trying to just
> > > filter off of a date that is passed from another expression and return all
> > > the Db results where the date is = to the Date in the other expression.
> > > Basically something like this
> > >
> > > =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value => > > Parameters!FiscalYearMinus1.Value)
> > >
> > > I know where isnt valid in an expression but this is the only way I know to
> > > say it. So lets say the Date is 2006 it would give me the Transaction
> > > amount for all of 2005.
> > >
> > > Thanks|||Try something like this expression in the cell expression:
=Sum(IIF(Fields!FiscalYear.Value =Parameters!FiscalYear.Value.AddYears(-1),Fields!TransactionAmount.Value,0))
rbyers01 wrote:
> Thanks for the reply
> Ok I tried that but I got an error message about Data types not matching.
> If I do the filter on the whole table will affect oter dates in the Table? i
> just need the filter to run on one cell of the table.
> "Cedric" wrote:
>> Hi,
>> have you tried to use the filter tab in the table/matrix properties ?
>> "rbyers01" wrote:
>> I have an expression in SSRS that I need some help with. I am trying to just
>> filter off of a date that is passed from another expression and return all
>> the Db results where the date is = to the Date in the other expression.
>> Basically something like this
>> =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value =>> Parameters!FiscalYearMinus1.Value)
>> I know where isnt valid in an expression but this is the only way I know to
>> say it. So lets say the Date is 2006 it would give me the Transaction
>> amount for all of 2005.
>> Thanks

Filling out gaps in a date based select statement

Hi,
I have a query which simply returns a count of a particular field by w
number. My query as it works fine however for ws where there are no data
it obviously doesn't return a row. My problem is that is I wish for the
statement to return a zero for those ws with no data - I'm just not sure
how to do this.
I realise that I can achieve it by creating a table listing w numbers and
joining my query on that table. However I'd rather not create a table like
that. Is there another approach that will let me achieve the same thing?
Currently my query is:
SELECT datepart(wk, datein) AS Wno , COUNT(movementno) AS ContainersIn
STOCK stock
WHERE year(datein) = '2005'
GROUP BY datepart(wk, datein)
which returns:
W ContainersIn
29 3
31 1
34 5
35 4
36 8
37 6
38 3
39 1
40 12
Thanks,
Chris.See if this helps you
http://weblogs.sqlteam.com/jeffs/ar...09/12/7755.aspx
Madhivanan|||Why don't you want to create a table for this? I would recommend you
keep an auxiliary numbers table (a table integers from 0 to some
arbitrarily large number) for exactly such an application. A Calendar
table is also very useful to have around.
A SELECT statement can't create data out of nothing. Some other options
are: use a derived table in your query with a UNION of the numbers 1 -
52 ; create a view containing those numbers ; create a table-valued
function that returns a parameterized range of numbers ; extract the
numbers from system table(s) (not recommended). Creating the permanent
table would be my favourite - it's fast, easy, portable and much more
concise and transparent than any of the others.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128332658.694445.44410@.g47g2000cwa.googlegroups.com...
> Why don't you want to create a table for this? I would recommend you
> keep an auxiliary numbers table (a table integers from 0 to some
> arbitrarily large number) for exactly such an application. A Calendar
> table is also very useful to have around.
> A SELECT statement can't create data out of nothing. Some other options
> are: use a derived table in your query with a UNION of the numbers 1 -
> 52 ; create a view containing those numbers ; create a table-valued
> function that returns a parameterized range of numbers ; extract the
> numbers from system table(s) (not recommended). Creating the permanent
> table would be my favourite - it's fast, easy, portable and much more
> concise and transparent than any of the others.
> --
> David Portas
> SQL Server MVP
> --
>
Thank your for your help David - most appreciated. I guess I find the idea
of creating a new table "messy". No idea why - just something that has
always struck me as something to avoid. A habit I will have to get out of!
thanks again
Chris.

Filling in missing data for a continous range, e.g. x-axis ..

Hi,

I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.

The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.

I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?

Cheers
james
`I dont think there is such a inbuilt function available in sql server. But if you could post the ddl ,some sample data and the expected result, i m sure someone here would definately be able to help you.|||One way to do this is to enumerate the values that you need, something like:SELECT d
FROM (SELECT Convert(DATETIME, '2006-03-01') AS d
UNION SELECT '2006-03-02'
UNION SELECT '2006-03-03'
UNION SELECT '2006-03-04'
UNION SELECT '2006-03-05'
UNION SELECT '2006-03-06'
UNION SELECT '2006-03-07'
UNION SELECT '2006-03-08') AS z-PatP|||Or more generically, create a table of sequential numbers in your database starting with zero, from which you can dynamically create any sequence of dates. Like this:select XValues.XValue,
YourData.YValue
from --XValues
(select dateadd(day, SeqValue, '2006-01-01') as XValue
from SequentialNumbers) XValues
left outer join YourData on XValues.XValue = YourData.XValue|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott|||Most times that I have need to do something like this, I have generated a calendar master table with dates and perhaps some bit flags to indicate other generic data about that date (eg, weekends, public holidays, non-working day).

Then I can join (or outer join) my recordset to the calendar master table for reporting purposes.

Regards,

hmscott

Thank you all
I think blindman and htmscott have described pretty much the same method.
I will give these a try and see how it goes.

Cheers
James|||Thanks again guys, the method suggested by you guys works great.

Cheers
Jamessql

Filling in date records

Hi:
This should be pretty simple, but I'm struggling with it. I've got
a table with two columns, Site and dtm_Date. I start with one record
for site that represents the minimum date for that site, and I want to
fill up the table with rows for every date up to the day before the
query is run. So if I start with:
Site dtm_Date
SiteA 11/27/2005 12:00:00AM
SiteB 11/29/2005 12:00:00AM
and I run my SQL Code on Dec 3, 2005, I want to end up with a table
like:
Site dtm_Date
SiteA 11/27/2005 12:00:00AM
SiteA 11/28/2005 12:00:00AM
SiteA 11/29/2005 12:00:00AM
SiteA 11/30/2005 12:00:00AM
SiteA 12/1/2005 12:00:00AM
SiteA 12/2/2005 12:00:00AM
SiteB 11/29/2005 12:00:00AM
SiteB 11/30/2005 12:00:00AM
SiteB 12/1/2005 12:00:00AM
SiteB 12/2/2005 12:00:00AMThe easiest approach is to use a Calendar table. Search the archives of this
newsgroup & you'll find several methods to create one.
Once you have it, you come up with something along the lines of :
SELECT tbl.site, Calendar.dt
FROM Calendar
LEFT OUTER JOIN tbl
ON Calendar.dt BETWEEN tbl.dtm_Date AND CURRENT_TIMESTAMP ;
Anith|||If you plan to insert data into a table, you need to have a source
for the data. In this case, a convenient source is a calendar
table containing all possible SQL Server dates. You can create the
table this way:
create table AllDates (
d datetime primary key
)
go
declare @.mindate datetime set @.mindate = '19000101'
declare @.maxdate datetime set @.maxdate = '21001231'
insert into AllDates values (@.mindate)
while @.@.rowcount > 0 begin
insert into AllDates
select d + tabsize
from AllDates, (
select datediff(day,min(d),max(d))+1 as tabsize from AllDates
) M
where d <= @.maxdate - tabsize
end
go
Once that's done, you can
INSERT INTO yourTable
SELECT yourTable.Site, AllDates.d
FROM yourTable, AllDates
WHERE AllDates.d > yourTable.dtm_Date
AND AllDates.d <= getdate() - 1
[not tested]
This assumes there is exactly one row per Site in your table,
so it will fail if you run it twice, for example.
Steve Kass
Drew University
blairjee@.gmail.com wrote:

> Hi:
> This should be pretty simple, but I'm struggling with it. I've got
> a table with two columns, Site and dtm_Date. I start with one record
> for site that represents the minimum date for that site, and I want to
> fill up the table with rows for every date up to the day before the
> query is run. So if I start with:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> and I run my SQL Code on Dec 3, 2005, I want to end up with a table
> like:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteA 11/28/2005 12:00:00AM
> SiteA 11/29/2005 12:00:00AM
> SiteA 11/30/2005 12:00:00AM
> SiteA 12/1/2005 12:00:00AM
> SiteA 12/2/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> SiteB 11/30/2005 12:00:00AM
> SiteB 12/1/2005 12:00:00AM
> SiteB 12/2/2005 12:00:00AM
>|||I think the Calendar idea is probably a better idea, but you can accomplish
your goals with a cursor:
go
set nocount on
DECLARE @.markdate datetime ,@.site varchar(10)
DECLARE sites_cursor CURSOR
FOR
Select site from sitedate order by site desc
OPEN SITES_cursor
FETCH NEXT FROM SITES_cursor INTO @.site
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.markdate = (select min(dtm_date) from sitedate)
WHILE @.markdate < getdate()
BEGIN
Insert into sitedate values(@.site,@.markdate)
select @.markdate = dateadd(d,1,@.markdate)
END
FETCH NEXT FROM SITES_cursor into @.site
END
Close SITES_cursor
DEALLOCATE SITES_cursor
<blairjee@.gmail.com> wrote in message
news:1133804570.141750.248940@.g49g2000cwa.googlegroups.com...
> Hi:
> This should be pretty simple, but I'm struggling with it. I've got
> a table with two columns, Site and dtm_Date. I start with one record
> for site that represents the minimum date for that site, and I want to
> fill up the table with rows for every date up to the day before the
> query is run. So if I start with:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> and I run my SQL Code on Dec 3, 2005, I want to end up with a table
> like:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteA 11/28/2005 12:00:00AM
> SiteA 11/29/2005 12:00:00AM
> SiteA 11/30/2005 12:00:00AM
> SiteA 12/1/2005 12:00:00AM
> SiteA 12/2/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> SiteB 11/30/2005 12:00:00AM
> SiteB 12/1/2005 12:00:00AM
> SiteB 12/2/2005 12:00:00AM
>

Fill label of chart X axis

Hi!

I have a report that diplay the sales per month from 2 date (start date, end date) passed in parameters. For exemple if the dates are 2006-01-01 to 2006-12-31 i want to diplay the sales for all the month between 2006-01-01 and 2006-12-31. Right now my graph display only month where sales exist. What i mean is that for this range of date my chart display only March april and may month because there are no sales for the other month. My problem is that i would like to display all month in the label on the X axix even if the month have no sales. So for this exemple i would like to diplay all month from January to december but i would have column only for the march april and may month.
From what i understand i have to use the "category group" to generate the label of my X axis but i don't know how to generate all the month from 2 date. I have to do another report that display sales per days. So i would have to generate all the day between 2 dates. How can i do such a thing ?

Thanks and sorry about my bad English ^_^

The x-axis has two modes:
* category mode (this is what you seem to be using)
* scalar mode (if "numeric / timescale data" is checked on the x-axis tab)

In the scalar mode, if you use actual DateTime objects for the category grouping, the x-axis will be drawn as a real datetime axis. So if you have sales in January and in December, it will show all the months in between in the scalar mode.

-- Robert

Fill in missing date ranges

I have a table AssetValues that contains several rows of data for a
particular date. The table has missing dates and I need to populate these
missing dates one-time to create historical data and then daily to populate
missing data on an ongoing basis. The missing dates are not related to the
particular day of the w.
The logic for inserting new rows is as follows:
If there is a date without data, insert data using the previous date that
has data as long as the previous day is in the same month. So if I was
missing data for 20060430 and there was data for 20060429 then copy the data
from 20060429 changing the date to 20060430. If I was missing 20060430 and
20060429 then use the data from 20060428.
If I was missing 20060501 do not use the data from 20060430. The data needs
to be in the same month. In this case I would need to use the data from
20060502 if this was available or the next day in May when the data is
available. If the data is not available then take no action until it is.
I have a calendar table if needed.
Thanks to anyone who could help.
CREATE TABLE AssetValues
(
AssetValueDate datetime,
Category char(1),
Subcategory int
)
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 1 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','B', 2 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','C', 3 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 4 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 5 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','C', 6 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','A', 7 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 8 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 9 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','A', 10 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','B', 11 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 12 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 13 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','B', 14 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','C', 15 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 16 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 17 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','C', 18 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','A', 19 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 20 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 21 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','A', 22 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','B', 23 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 24 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 25 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','B', 26 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','C', 27 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 28 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 29 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','C', 30 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','A', 31 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 32 )
DROP TABLE AssetValuesTry,
create view v1
as
select a.dt
from dbo.calendar as a
where not exists (
select * from dbo.AssetValues as b
where b.AssetValueDate = a.dt
)
and a.dt between convert(char(6), (select min(AssetValueDate) from
dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
'01'))
go
select *
from v1
go
-- previous available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
v1.dt) = 0)
go
-- next available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
a.AssetValueDate) = 0)
go
AMB
"Terri" wrote:

> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues
>
>|||This duplicates all rows from the previous day. Is that what you want?
CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
DECLARE @.Date DATETIME
, @.MaxDate DATETIME
SELECT @.Date = MIN(AssetValueDate)
, @.MaxDate = MAX(AssetValueDate)
FROM AssetValues
WHILE @.Date <= @.MaxDate BEGIN
INSERT Dates
SELECT @.Date
SET @.Date = @.Date + 1
END
GO
SELECT DateValue
, AssetValueDate
, Category
, Subcategory
FROM AssetValues av
, Dates
WHERE YEAR(DateValue) = YEAR(AssetValueDate)
AND
MONTH(DateValue) = MONTH(AssetValueDate)
AND
EXISTS
(
SELECT *
FROM AssetValues av1
WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
HAVING COUNT(DISTINCT AssetValueDate) = 1
)
ORDER BY DateValue, AssetValueDate
--Alan
Terri wrote:
> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues|||Thanks that worked. The only thing I still need to change is that the script
populated all the days in May from May 2nd data which was the most recent
data I had. I think I can add a date filter that will prevent population of
future dates.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87565D10-F136-4D26-988B-5208CBEED90A@.microsoft.com...
> Try,
> create view v1
> as
> select a.dt
> from dbo.calendar as a
> where not exists (
> select * from dbo.AssetValues as b
> where b.AssetValueDate = a.dt
> )
> and a.dt between convert(char(6), (select min(AssetValueDate) from
> dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
> convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
> '01'))
> go
> select *
> from v1
> go
> -- previous available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
> v1.dt) = 0)
> go
> -- next available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
> a.AssetValueDate) = 0)
> go
>
> AMB
>|||No, but thanks. Sometimes I need to populate a date with data from a more
recent date when that data becomes available.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1146684129.666739.113960@.j73g2000cwa.googlegroups.com...
> This duplicates all rows from the previous day. Is that what you want?
> CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
> DECLARE @.Date DATETIME
> , @.MaxDate DATETIME
> SELECT @.Date = MIN(AssetValueDate)
> , @.MaxDate = MAX(AssetValueDate)
> FROM AssetValues
> WHILE @.Date <= @.MaxDate BEGIN
> INSERT Dates
> SELECT @.Date
> SET @.Date = @.Date + 1
> END
> GO
> SELECT DateValue
> , AssetValueDate
> , Category
> , Subcategory
> FROM AssetValues av
> , Dates
> WHERE YEAR(DateValue) = YEAR(AssetValueDate)
> AND
> MONTH(DateValue) = MONTH(AssetValueDate)
> AND
> EXISTS
> (
> SELECT *
> FROM AssetValues av1
> WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
> HAVING COUNT(DISTINCT AssetValueDate) = 1
> )
> ORDER BY DateValue, AssetValueDate
> --Alan
> Terri wrote:
these
populate
the
that
data
and
needs
>

Filemaker -> MS SQL

I am new to filemaker, and i was wondering if there is any solution
out there to export date from filemaker to SQL Server in an automated
process?Hi

I am not experience with filemaker so I can only be very general from the
SQL Server side, therefore I can say about pushing data, but if you wanted
to pull the data from SQL Server then you may want to see if you can create
a linked server or an ODBC connection. You may also want to see if you could
use XML as either a web service or a intermediate format.

John

"Jason Berg" <jason_berg@.sundance.org> wrote in message
news:a828f4a1.0311070857.a0c46a4@.posting.google.co m...
> I am new to filemaker, and i was wondering if there is any solution
> out there to export date from filemaker to SQL Server in an automated
> process?

File source error : so much rows

Hello,

I have a problem with my SSIS. I have a data flow with a file source in csv, but itself has 140 000 rows, so when I execute the date flow, I have a error who say that the data exceed the temp of I/O (sorry for the translate, but I have the message in french).
I test to pass the DefaultBufferMaxRow to 140000 but I have always the problem.

If we can help me, thank you.
You might try setting the BufferTempStoragePath property on the data flow to point to a drive with plenty of free space. If you search on the forum for that property, you'll find more information.|||Ok, I go to search it.

Thank you for your answer.

File source error : so much rows

Hello,

I have a problem with my SSIS. I have a data flow with a file source in csv, but itself has 140 000 rows, so when I execute the date flow, I have a error who say that the data exceed the temp of I/O (sorry for the translate, but I have the message in french).
I test to pass the DefaultBufferMaxRow to 140000 but I have always the problem.

If we can help me, thank you.
You might try setting the BufferTempStoragePath property on the data flow to point to a drive with plenty of free space. If you search on the forum for that property, you'll find more information.|||Ok, I go to search it.

Thank you for your answer.

File name as yesterday's date

I'm running a DTS daily that needs to create a file with a unique name, as
yesterday's date. (Ex: 20050223)
I figured out how to get the date part of it:
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
but I can't seem to get it to create a table with that unique name each day.
Any ideas?
CREATE TABLE 'Date'
Ed>> I'm running a DTS daily that needs to create a file [sic] with a
unique name, as yesterday's date. (Ex: 2005-02-23) <<
No. no. no.
1) You have just re-discovered the old IBM tape system management
convention, which used a "yyddd' numbering scheme on the tape labels.
It was one of the thing that blew up in the Y2K disaster. You are over
50 years behind current IT standard practices.
2) We do not alter a schema by adding new tables without a good
reason. Altering a schema on a daily basis is sure sign of a major
design flaw.
3) This design flaw is called attribute splitting. you have taken the
value of a temporal attribute (date of creation) and split it into
separate tables. Would you keep a personnel table in two parts, male
and female employees? Of course not. You need one table with a date
column in it.|||Create an os file or a table?
***** OS File *****
Working with files and the FileSystemObject
http://www.sqldts.com/default.aspx?292
***** TABLE *****
Use dynamic sql.
Example:
declare @.tn sysname
declare @.sql nvarchar(4000)
set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
execute sp_executesql @.sql
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Edo" wrote:

> I'm running a DTS daily that needs to create a file with a unique name, as
> yesterday's date. (Ex: 20050223)
> I figured out how to get the date part of it:
> CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112) = 20050222
> but I can't seem to get it to create a table with that unique name each da
y.
> Any ideas?
> CREATE TABLE 'Date'
> Ed|||Thanks for your help on that matter.
But how would this apply to inserting, especially when appling sums and
inner joins.
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
Select COLMAT_TRACK_EMPS.EMPLOYEE_NAME,
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'H' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'H',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'M' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'M',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'P' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'P',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'A' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'A',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'V' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'V',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'L' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'L',
SUM(CASE SUBSTRING(ITEM_MAST.CATEGORY, 4, 1) WHEN 'S' THEN
TRACK_DETAIL_CI.QTY ELSE 0 END) as 'S',
SUM (TRACK_DETAIL_CI.QTY) AS QTYSUM
FROM COLMAT_TRACK_EMPS INNER JOIN
TRACK_DETAIL_CI ON COLMAT_TRACK_EMPS.EMPLOYEE_NO =
TRACK_DETAIL_CI.EMPLOYEE_NO
INNER JOIN ITEM_MAST ON ITEM_MAST.EDP_NO = TRACK_DETAIL_CI.EDP_NO
WHERE TRACK_DETAIL_CI.TRANS_DATE =
CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
GROUP BY COLMAT_TRACK_EMPS.EMPLOYEE_NAME
INSERT INTO CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
SELECT 'TOTAL' AS EMPLOYEE_NAME, SUM (EDS_TRIAL.H), SUM (EDS_TRIAL.M), SUM
(EDS_TRIAL.P), SUM (EDS_TRIAL.A), SUM (EDS_TRIAL.V), SUM (EDS_TRIAL.L), SUM
(EDS_TRIAL.S), SUM (EDS_TRIAL.QTYSUM)
FROM CONVERT(char(32),DATEADD(DAY,-1,GETDATE()),112)
"Alejandro Mesa" wrote:
> Create an os file or a table?
> ***** OS File *****
> Working with files and the FileSystemObject
> http://www.sqldts.com/default.aspx?292
> ***** TABLE *****
> Use dynamic sql.
> Example:
> declare @.tn sysname
> declare @.sql nvarchar(4000)
> set @.tn = N'tbl_' + convert(nvarchar(8), dateadd(day, -1, getdate()), 112)
> set @.sql = N'create table ' + @.tn + '(colA int, colB datetime)'
> execute sp_executesql @.sql
> go
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
> "Edo" wrote:
>