Database

Re: Do I need a pivot Query?

16-Jan-13 19:36

It looks like you have simply added the doctype column!

Never underestimate the power of human stupidity
RAH

GREG_DORIANcod17-Jan-13 1:59

GREG_DORIANcod

17-Jan-13 1:59

yes!! using this 3 columns nc_doc nd_doc and rc_doc , and then transpose as DocType...

any ideas!!!?

Re: Do I need a pivot Query?

Jörgen Andersson21-Jan-13 8:58

Jörgen Andersson

21-Jan-13 8:58

It looks more like unpivot to me.

People say nothing is impossible, but I do nothing every day.

Totals of each transaction type

Richard.Berry10015-Jan-13 21:38

Richard.Berry100

15-Jan-13 21:38

Hi
How can I get the total number of two types of transaction between two dates per user

Example data:

User TransactionType MovementDate
Avril RECP 2012/01/04
Pat RECP 2012/01/09
Pat SRET 2012/01/09

What I would like is the following:

User Recipts Returns
Avril 1 0
Pat 1 1

This is where I was heading, but as soon as I put in a where clause to limit the date range I get an Error " Incorrect column expression: 'SUM(CASE WHEN stkhistm.transaction_type = 'RECP Then 1 else 0 end)'

SELECT SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) , SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)  
FROM vektron.scheme.stkhstm stkhstm
WHERE stkhstm.movement_date>=[?]

Mycroft Holmes15-Jan-13 22:57

15-Jan-13 22:57

What database are you using, [?] as a parameter looks like Access, the code looks like TSQL and you are mixing the parameter types. A TSQL parameter would be @Date

Never underestimate the power of human stupidity
RAH

Richard.Berry10016-Jan-13 0:36

Richard.Berry100

16-Jan-13 0:36

Oops sorry - SQL Server 2008 R2 - MS Query - Excel

Seem to have got a liitle further:

This works:

SELECT user_id, SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) , 
SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)  
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id

Then I tried to add a restriction on the dates:

SELECT user_id, SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) * 
SUM(CASE WHEN stkhstm.movement_date > #01/01/2012# then 1 else 0 end) * sum(CASE WHEN stkhstm.movement_date < #01/01/2013# then 1 else 0 end), 
SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)  
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id

But get the same error as before

PIEBALDconsult16-Jan-13 3:42

PIEBALDconsult

16-Jan-13 3:42

As to the dates, you probably want to use BETWEEN -- I do hope you are storing dates as DATE or DATETIME, not strings.

If you execute your command in SSMS, what line does it tell you the error is on?

Customize color coding in SQL editor window

Richard.Berry10016-Jan-13 5:36

Richard.Berry100

16-Jan-13 5:36

Thanks PIEBALDconsult

The problem was amongst other things the dates !!

This works now:

SELECT user_id, 
SUM(CASE WHEN (stkhstm.transaction_type = 'RECP' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'})  then 1 else 0 end)  , 
SUM(CASE WHEN (stkhstm.transaction_type = 'SRET' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end)  
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id

'BETWEEN' would be a bit more elegant I guess Smile | :)

biop.codeproject15-Jan-13 20:04

biop.codeproject

15-Jan-13 20:04

I just wonder is there a visual guide in customizing the color coding.

Problem I face with is that I need to change the color for column name. I have tried changing many items, but with no luck.

modified 23-Jan-13 3:27am.

Re: Customize color coding in SQL editor window

Mycroft Holmes15-Jan-13 22:51

Re: Customize color coding in SQL editor window

15-Jan-13 22:51

You can change the colours in options => text editor. Make sure you save and backup the setting if you customise it, losing them is a bitch!

Never underestimate the power of human stupidity
RAH

Message Closed

16-Jan-13 19:26

biop.codeproject

16-Jan-13 19:26

Message Closed

Mycroft Holmes16-Jan-13 19:34

Re: Customize color coding in SQL editor window

16-Jan-13 19:34

Sure I read your question, I just was not interested in spoon feeding you like a baby. I did point out where to look for the change, now you need to do what the rest of us did and rat through the configurations and find out what you need change to get the effect you want!

Had there been a more relevant answer posted I would not have had to help at all, I gave you the pointer to where to find make the changes because there was no better help offered!

Never underestimate the power of human stupidity
RAH

Shameel22-Jan-13 4:41

Shameel

22-Jan-13 4:41

Thanks God you didn't expect anyone here to remote connect to your computer and change the colors for you.

query..

vishal.v.patil14-Jan-13 17:49

vishal.v.patil

14-Jan-13 17:49

i want the SQL query for getting single column from two different tables column....

i have two tables...vendor and labour, In that Vendor_Name and Labour_name columns..

i want all names in one single column...

Thnks....

Vishal V Patil
(M. C. A.)

Re: query..

Mycroft Holmes14-Jan-13 17:54

Building dictionary translation

14-Jan-13 17:54

Do some reading around the UNION keyword.

SQL

Select Vendor_Name from Vendor
UNION
Select Labour_Name from Labour

Never underestimate the power of human stupidity
RAH

Re: query..

vishal.v.patil14-Jan-13 18:21

vishal.v.patil

14-Jan-13 18:21

Thnks for rply....but, It will show last two records from both two tables....

Vishal V Patil
(M. C. A.)

Re: query..

Blue_Boy15-Jan-13 3:11

Blue_Boy

15-Jan-13 3:11

SQL

select * from vendor where id in (select top 2 id from vendor order by id desc)
union all
select * from labour where id in (select top 2 id from labour order by id desc)

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Y@ssco14-Jan-13 16:19

Y@ssco

14-Jan-13 16:19

Hi friends,
I have a requirements to build a data base with bilingual dictionary. Upto certin limit i found many threads on how to structure the DB table. But from where i can get the dictionary itself? I need The raw material (in excell or xml) to fill the DB. Any one can help with this?
Thanks.

Mycroft Holmes14-Jan-13 17:52

14-Jan-13 17:52

A simple goggle [^]search seems to indicate there are plenty of sources out there!

Never underestimate the power of human stupidity
RAH

Y@ssco14-Jan-13 18:41

Y@ssco

14-Jan-13 18:41

Thanks RAH for your reply, I guess i was not clear maybe in my previous question, I have few sources for the English dictionary, and other languages. But what I want to build is a kind of Offline Translator:

- Language 1 --> Stored in DB.
- Language 2 --> Stored in DB.
- Language 3 --> .....

And as am typing a word in my application it will search for the translation in the DB.

The challenge no is From where i can get the source of the Bilingual Dictionary for example (English - Spain) in xml or excell.

any advice?!

Mycroft Holmes14-Jan-13 18:52

14-Jan-13 18:52

Ah I see, not going to be able to help, the few times I have done multi lingual apps I have forced the client to supply the translations, publicly available info was inadequate to the requirements especially when dealing with a technical subject.

Never underestimate the power of human stupidity
RAH

Corporal Agarn15-Jan-13 1:51

Corporal Agarn

15-Jan-13 1:51

You may be able to purchase such a thing. Be careful of downloading information for copy-write laws.