|
5. They might in the future.
|
|
|
|
|
They're apples and oranges.
|
|
|
|
|
Exactly, just like the question and your answer.
|
|
|
|
|
this is my original query:
INVOICE NC_DOC NC_VALUE ND_DOC ND_VALUE RC_DOC RC_VALUE
40842 137 130130 140 60000 3 130130
40842 137 130130 141 85060 3 130130
40842 138 -130130 140 60000 3 130130
40842 138 -130130 141 85060 3 130130
40842 139 190130 140 60000 3 130130
BUT Now i need some query like this
doctype numberDoc doc_value
NC_DOC 137 130130
NC_DOC 138 -130130
NC_DOC 139 190130
ND_DOC 140 60000
ND_DOC 141 85060
RC_DOC 3 130130
|
|
|
|
|
It looks like you have simply added the doctype column!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
yes!! using this 3 columns nc_doc nd_doc and rc_doc , and then transpose as DocType...
any ideas!!!?
|
|
|
|
|
It looks more like unpivot to me.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
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>=[?]
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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 
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
Thanks God you didn't expect anyone here to remote connect to your computer and change the colors for you.
|
|
|
|
|
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.)
|
|
|
|
|
Do some reading around the UNION keyword.
Select Vendor_Name from Vendor
UNION
Select Labour_Name from Labour
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thnks for rply....but, It will show last two records from both two tables....
Vishal V Patil
(M. C. A.)
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
A simple goggle [^]search seems to indicate there are plenty of sources out there!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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?!
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
