|
Hi I have an Sql Table Like this
MatTable
type = 1 Is IN Qty ( Buy )
type = 0 Is Out Qty ( Sell )
mat qty type
-------------------------------------------------- ---------------------- -----
mat1 10 0
mat2 2 0
mat3 10 0
mat1 5 1
mat2 5 1
mat2 7 1
mat4 4 0
mat4 4 0
mat4 8 1
mat3 6 1
mat1 10 0
How I can Get a result Like
mat Current qty
-------------------------------------------------- ----------------------
mat1 15
mat2 -10
mat3 4
mat4 0
Thank you In Advance
I know nothing , I know nothing ...
|
|
|
|
|
Try:
"Select mat, sum(case type
when 0 then qty * -1
else qty) [Current qty] from MatTable
group by mat"
or if you don't like to use a case statement then -
select mat, sum(qty) from (
(Select mat, sum(qty * -1) qty from MatTable group by mat
where type = 0) t1
union
(Select mat, sum(qty) qty from MatTable group by mat
where type = 1) t2
)
group by mat
I didn't check either of these for syntax so let me know if you have problems running these...
|
|
|
|
|
Thank you so much ,
it;s worked like charming ,
However I added a little "END" word to the first statement ,
SELECT MAT, SUM(CASE TYPE WHEN 0 THEN Qty ELSE -Qty END) AS CurrentQty FROM MatTable GROUP BY Mat
thank you , for you code , it's so simple and brilliant 
I know nothing , I know nothing ...
|
|
|
|
|
Hi,
I have a bit of a problem with a Forms App using Access for the DB.
I have a Calendar Control that has date ranges bolded from records in a table in the DB.
I have the Calendar Control populated and now the user can select a date and find the event associated to the date. The date may be the "StartDate", "EndDate", or a date within the range of the "StartDate" & "EndDate" in a row.
What I need help with is wrighting the SQL to bring back the record ID where the selected date falls between the StartDate & End Date and return the Record ID value.
SELECT E_ID FROM Events WHERE "SelectedDate" is Between StartDate AND EndDate
OR something like that???
|
|
|
|
|
Try
SELECT E_ID FROM Events
WHERE (SelectedDate >= StartDate) AND (SelectedDate <= EndDate)
Make sure you replace all the date variables with their string representations for the SQL, of course.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Hi Walt,
Thanks for the sample code... It works great. But I have another issue that maybe you can help me with.
The Value I am passing is a string with the DateTimeValue in it. I have put single quotes around the value and it works when I run the SQL manually... but when I run it in the app it rejectes it because the "Time" is still attached.
My issue is how can I convert the string value to a Date ONLY value?
I have tried Convert.ToDateTime(Value)... but that still has a Time in it.
I tried DateTime.ParseExact(Value,... ,...) but I couldn't find a Date only filter.
I am using this in a Forms App in C# on an Access DB, I build my SQL in the App and pass it over.
Got any suggestions on how to convert the string value of "DateTime" to a "Date" only value?
Thanks
|
|
|
|
|
Have your tried DateTime.ToShortDateString?
A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Walt,
Thanks for the pointer the page dose have a lot of great formats etc on it. I have added it to my favorites.
I had to change the Single Quotes to # around each of the parameter dates and it worked great.
Thanks for all the help and pointers.
SquireDude
|
|
|
|
|
Walt Fair, Jr. wrote: DateTime.ToShortDateString
Not ideal, as it depends on regional settings (and could contain all kinds of nonsense), whereas the database content does not. An explicit format would be the preferred one here.
Here is an alternative summary[^].
|
|
|
|
|
True! Thanks for pointing that out, Luc.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
No problem.
|
|
|
|
|
Hi,
I need some feedback on some code generation software I'm in the process of re-developing for SQL Server (2005/2008).
Its called SSCodeGen (for 2005/2008) and the new version (8.3) is now up at www.RaisinTech.com/SSCodeGen.exe, It will
- Generate the 4 standard (A/I/U/D) stored procedures for a table (text/image field not currently handled)
- Generate the execute statements for a stored procedure (slightly more complete than that generated from SS Man Studio)
- Generate the sql for S/I/U/D statements for one or 2 tables on the database.
Obviously it is free, and it doesn't require installation, you can just download and run.
There is a version for SQL Server 7/2000 at http://users.quista.net/thealey/.
Any comments appreciated
BillyTheKidney
|
|
|
|
|
This is a good excercise and every developer should build such a tool, I am continually astonished to see some quite skilled dev hand coding crud procedures and methods.
Try extending it to include the c#/VB stubs to call the stored proc. Also think about parameterised queries instead of procs, I am currently changing mine to do this because sql compact does not support procs  .
Then go on to create the model class for the table. Take into account where you have a view of the same data and add the additional fields into the model class.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the input, appreciated.
The old SQL Server 7/2000 version generates the old VB/ASP code for calling a stored proc, though I haven't got round to changing it for the 2005/8 version as yet.
I would be interested to see your code generation software.
BillyTheKidney
|
|
|
|
|
i have to insert into two tables
1st table orders
orderno int primary key autoincrement,
username nvarchar(50) ,
shopname nvarchar(50),
email nvarchar(100)
2nd table is Orderdetails
serialno int primarykey autoincrement,
orderno int foreignkey,
categorynamee nvarchar(50),
itemkey nvarchar(50),
itemkeyname nvarchar(50),
currentqty decimal,
salesprice decimal,
quantity int,
total decimal
can you give example which helps me
|
|
|
|
|
I am assuming you are using SQL 2005 or better.
Firstly, you need to insert into the order table and get the order number by using scope_identity. Then use this to insert into the order details. The whole lot needs wrapping in a single transaction
(clue - the bold words should be used for searching)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I couldn't find any reference to if it's a free upgrade from 2008. I'm assuming not ... but then again I find it hard to believe so many people will spent tens of thousands for a new license for point release?
|
|
|
|
|
Free if you have Software Assurance, 7499$ per socket for the standard edition if you don't. It's not considered a service pack (like server 2008 R2 was not considered a service pack)
|
|
|
|
|
In MS SQL 2008 I'm often doing big SELECT * statements from a couple tables looking for data in various fields, and have to scroll sideways in the SSMS window.
What I'd like to do is for every field in whatever table I'm selecting, to pivot so its name goes into FieldName, and its value into FieldValue. Then I can use sorts and quickly find what I'm looking for.
I've seen some articles on PIVOTs but they are almost always hardcoded for a specific table, and use aggregates. I want something dynamic and simple.
ie: SELECT * FROM AnyTable
Column 1, Column 2, Column 3
Data 1, Data 2, Data 3
Into:
Column 1, Data 1
Column 2, Data 2
Column 3, Data 3
I'm guessing some stored procedure is necessary but I haven't been able to find any. Does anyone have something simple in the toolbox?
|
|
|
|
|
Ahh now I have to explain to my collegues why I am sitting here chuckling - quick, generic and pivot all in the same sentence.
There is nothing quick or generic about pivot tables. I have an article[^] that uses dynamic sql to avoid the hard coding but it is not quick and generic.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
for example two columns of table1 , have relation to the id column of table2
for example:
tbl_city: (id , title)
tbl_person: (id , name , CurrentCityID , LastCityID , ...)
now , I want to make two relations from tbl_person to tbl_city
CurrentCityID and LastCityID must relation to tbl_city
this query is wrong:
SELECT tbl_person.name , tbl_city.title , tbl_city.title
FROM tbl_person
JOIN tbl_city ON tbl_person.CurrentCityID = tbl_city.id
JOIN tbl_city ON tbl_person.LastCityID = tbl_city.id
can you help me ?
H.R
|
|
|
|
|
Yes, but you may want an AND instead.
|
|
|
|
|
Maybe you ar looking for aliases in sql queries?
I think this query is the right one for you:
SELECT
person.name as personName, currentCity.title as currentCityTitle, lastCity.title as lastCityTitle
FROM tbl_person as person
JOIN tbl_city as currentCityON person.CurrentCityID = currentCity.id
JOIN tbl_city as lastCity ON person.LastCityID = lastCity.id
Note the as keyword used in the querry.
I have no smart signature yet...
|
|
|
|
|
Stanciu Vlad wrote: Note the as keyword used in the querry.
I've always left the as out.
|
|
|
|
|
That is a common practice, but in the previous example I assumed the questioner has no or little knoledge about aliases, therefore I followed a more explicit approach.
I have no smart signature yet...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
