Collation problem: pull

Showing posts with label pull. Show all posts

Thursday, March 22, 2012

combine values in view?

Is there an easy way to do this?
I need to pull sales values for each store and make one record per month.
This used to be easy, as I was provided only one sales record per month per
store, but now I've been told that SOME of the stores have a "secondary" ID
from which to pull sales numbers, and these numbers need to be added to the
number from their PRIMARY number before displaying them. I'm not sure how
to do this.
Here is some sample data (cols don't align well here - sorry):
STORES:
ID1 ID2 STORE
100 AAA A-STORE
200 B-STORE
300 CCC C-STORE
400 D-STORE
(note that B-Store and D-Store have only ONE ID, not a secondary ID)
SALES:
ID SALES MONTH YEAR
100 5.00 01 2004
AAA 5.00 01 2004
200 5.00 01 2004
300 5.00 01 2004
CCC 5.00 01 2004
400 5.00 01 2004
(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
records for each month & year so those fields need to be accounted for.)
I need a view that will show:
ID SALES MONTH YEAR
100 10.00 01 2004
200 5.00 01 2004
300 10.00 01 2004
400 5.00 01 2004
(note that IDs 100 and 300 show the combined sales of both their primary AND
secondary IDs)
Is this easily doable? If yes, how'!!
Thanks!
-RThere are some data integrity problems here, and in particular,
if the ID2 values are not unique, this is something of a mess, but
if the data is not messed up, something like this should work:
select ID, sum(SALES) as SALES, [MONTH], [YEAR]
from (
select ID, SALES, [MONTH], [YEAR]
from SALES
union all
select ST.ID1, SA.SALES, SA.[MONTH], SA.[YEAR]
from SALES AS SA join STORES AS ST
on SA.ID = ST.ID2
) S
group by ID, [MONTH], [YEAR]
If the data is messed up, this query could provide completely
wrong information. You would be better off keeping track
of the store IDs differently:
-- primary IDs only, with store attributes
CREATE TABLE STORES (
ID char(3) primary key,
StoreName varchar(30),
.. other attributes of a store
)
-- all IDs, primary and alternate, for stores, with
-- the primary storeID for each
CREATE TABLE STORE_IDS (
ID char(3) primary key,
storeID char(3) references STORES(ID)
-- put an index on storeID to support the FK
)
The foreign key on SALES would now link to this
second table instead of the first. The query would
be different, too - something like this:
select ST.ID, sum(SA.SALES) as SALES, SA.[MONTH], SA.[YEAR]
from STORES as ST
join STORE_IDS as I
on I.storeID = ST.ID
join SALES as SA
on SA.ID = I.ID
group by ST.ID, SA.[MONTH], SA.[YEAR]
Steve Kass
Drew University
r wrote:

>Is there an easy way to do this?
>I need to pull sales values for each store and make one record per month.
>This used to be easy, as I was provided only one sales record per month per
>store, but now I've been told that SOME of the stores have a "secondary" ID
>from which to pull sales numbers, and these numbers need to be added to the
>number from their PRIMARY number before displaying them. I'm not sure how
>to do this.
>Here is some sample data (cols don't align well here - sorry):
>STORES:
>ID1 ID2 STORE
>100 AAA A-STORE
>200 B-STORE
>300 CCC C-STORE
>400 D-STORE
>(note that B-Store and D-Store have only ONE ID, not a secondary ID)
>SALES:
>ID SALES MONTH YEAR
>100 5.00 01 2004
>AAA 5.00 01 2004
>200 5.00 01 2004
>300 5.00 01 2004
>CCC 5.00 01 2004
>400 5.00 01 2004
>(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
>and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
>records for each month & year so those fields need to be accounted for.)
>I need a view that will show:
>ID SALES MONTH YEAR
>100 10.00 01 2004
>200 5.00 01 2004
>300 10.00 01 2004
>400 5.00 01 2004
>(note that IDs 100 and 300 show the combined sales of both their primary AN
D
>secondary IDs)
>Is this easily doable? If yes, how'!!
>Thanks!
>-R
>
>
>sql sql

Tuesday, March 20, 2012

Combine rows data into a Column

I have two tables, which you can call master and details. I want to pull data
from master table and respective details from details table. Here are tables
and data information.
Master Table
MID Description
1 Person â' 1
2 Person -2
3 Person â' 3
Detail Table
DID MID Credit Card
1 1 Visa Card
2 1 Master Card
3 2 Visa Card
4 3 Visa Card
5 3 Master Card
and I want report something like this:
================================= Description Credit Card
================================= Person -1 Visa Card, Master Card
Person â' 2 Master Card
Person â' 3 Visa Card, Master Card
Now I am not sure this is possible in Reporting Services.You will have to write an SP which returns data in the format you want.
>--Original Message--
>I have two tables, which you can call master and details. I want to pull data >from master table and respective details from details table. Here are tables >and data information.
>Master Table
>MID Description
>1 Person =E2?" 1
>2 Person -2 >3 Person =E2?" 3
>Detail Table
>DID MID Credit Card
>1 1 Visa Card
>2 1 Master Card
>3 2 Visa Card
>4 3 Visa Card
>5 3 Master Card
>
>and I want report something like this:
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D
>Description Credit Card
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D
>Person -1 Visa Card, Master Card
>Person =E2?" 2 Master Card
>Person =E2?" 3 Visa Card, Master Card
>
>Now I am not sure this is possible in Reporting Services.
>.
>|||You'd have to join the two tables. Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1l4j.asp?frame=true
for examples.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sam B" <SamB@.discussions.microsoft.com> wrote in message
news:29AF9D45-CAFE-4749-9754-E5B6EA0DC660@.microsoft.com...
> Thanks for your quick response.
> Now I am not clear how would I do that, can you please provide me some
hints?
> "anonymous@.discussions.microsoft.com" wrote:
> > You will have to write an SP which returns data in the
> > format you want.
> >
> > >--Original Message--
> > >I have two tables, which you can call master and details.
> > I want to pull data
> > >from master table and respective details from details
> > table. Here are tables
> > >and data information.
> > >
> > >Master Table
> > >
> > >MID Description
> > >1 Person Ã¢?" 1
> > >2 Person -2
> > >3 Person Ã¢?" 3
> > >
> > >Detail Table
> > >
> > >DID MID Credit Card
> > >1 1 Visa Card
> > >2 1 Master Card
> > >3 2 Visa Card
> > >4 3 Visa Card
> > >5 3 Master Card
> > >
> > >
> > >and I want report something like this:
> > >
> > >=================================> > >Description Credit Card
> > >=================================> > >Person -1 Visa Card, Master Card
> > >Person Ã¢?" 2 Master Card
> > >Person Ã¢?" 3 Visa Card, Master Card
> > >
> > >
> > >Now I am not sure this is possible in Reporting Services.
> > >
> > >.
> > >
> >

Monday, March 19, 2012

Combine Detail rows in one column

Combine 2 datasources in one report?

I have loaded up the newest latests, greatest Visual Studio, SQL reporting etc. I have a need to pull in data from multiple sources. Currently I have DTS packages I have to run to dump information into 1 data source to report on. Has anybody figured out a way to have multipled data sources available when in the query builder?

I am scratching my head on this one.

Your report can have 2 datasets, each with a different datasource, but you cannot combine them into a single dataset within the report.

If you need to combine the data from 2 datasources into a single dataset, you'll need to handle this in your query/stored proc via a linked server or something similar.|||Thanks Andy for your quick response. It interesting that one can pull this off in MS Access with a simple ODBC but I cant in SQL reporting. I am not sure what you mean by linked server in this case.|||Linked Servers:
http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Distributed Query:
http://msdn2.microsoft.com/en-us/library/aa172738(sql.80).aspx

Sunday, February 12, 2012

Collecting Active Directory dates

Hi,

I've just started using ADSI to pull in info from our Active Directory
tree into SQL Server 2000. I've made the link ok, and can pull in most
fields (cn whenCreated etc) fine. However, there are some date fields
(the one I'm interested in is pwdLastSet) that are represented as a
long numeric string, which throws up an error when SQL tries to pull it
in. Is there an easy way to parse these fields into a standard
datetime field, or if not how do I force SQL to pull the numeric field
in, and convert it later?

TIA,
Ross"Ross Luker" <ross_luker@.hotmail.com> wrote in message
news:1104940631.745926.294600@.c13g2000cwb.googlegr oups.com...
> Hi,
> I've just started using ADSI to pull in info from our Active Directory
> tree into SQL Server 2000. I've made the link ok, and can pull in most
> fields (cn whenCreated etc) fine. However, there are some date fields
> (the one I'm interested in is pwdLastSet) that are represented as a
> long numeric string, which throws up an error when SQL tries to pull it
> in. Is there an easy way to parse these fields into a standard
> datetime field, or if not how do I force SQL to pull the numeric field
> in, and convert it later?
> TIA,
> Ross

It would probably be a good idea to give an example of one of the numeric
strings, and the date it represents - personally, I'm not at all familiar
with ADSI, although others here may be. I'm also not sure how you're pulling
the data - if you're using DTS, you could use a custom ActiveX
transformation, if the existing date transformation won't handle it; if
you're using a linked server, then a UDF might be one solution.

Simon|||Hi Simon,

If I look at AD data using the windows LDIFDE tool, there are some
fields such as the one below, which are retrieved ok:
whenChanged: 20041202105508.0Z - MSSQL formats this fine to 02/12/2004,
10:55

However, most of the date/time fields are in the format:
pwdLastSet: 127463655814071600
which I'm guessing is a counter in (maybe) seconds from some date, but
I can't find any info to prove this! Trying to pull this field in (I'm
using a linked server) results in an error "Could not convert the data
value due to reasons other than sign mismatch or overflow". As I said,
if I knew more about what the data in the fields are, I might be able
to work on transforming it!

Ross|||"Ross Luker" <ross_luker@.hotmail.com> wrote in message
news:1104943553.231483.173640@.c13g2000cwb.googlegr oups.com...
> Hi Simon,
> If I look at AD data using the windows LDIFDE tool, there are some
> fields such as the one below, which are retrieved ok:
> whenChanged: 20041202105508.0Z - MSSQL formats this fine to 02/12/2004,
> 10:55
> However, most of the date/time fields are in the format:
> pwdLastSet: 127463655814071600
> which I'm guessing is a counter in (maybe) seconds from some date, but
> I can't find any info to prove this! Trying to pull this field in (I'm
> using a linked server) results in an error "Could not convert the data
> value due to reasons other than sign mismatch or overflow". As I said,
> if I knew more about what the data in the fields are, I might be able
> to work on transforming it!
> Ross

It looks unlikely to be seconds since an epoch, since the number above would
be more than 4 billion years (I think - very quick calculation). You should
probably follow up on the ADSI side - in an AD newsgroup, perhaps - to find
out what the number represents.

Until you find out more details, you could use ISDATE() to put in a null (or
something else) for your import - it's not always reliable, but in this case
it should be OK:

select cast(case when isdate(pwdLastSet) = 0 then null else pwdLastSet end
as datetime) as pwdLastSet
from ADSI..LinkedTable

Simon