help with a query
-
unfortunatly I'm not allowed to make changes to the data but the issue system actually works pretty well because it is for magazine data which is all based off issue. thanks very much for your help that query put in exactly what I was trying to do.
have a great week
Chuck
-
Remi, I absolutely agree with you that this would be the right thing to do... and somehow I'm afraid that not only this one table requires normalization.
So, to make sure chuck is getting me right : My "solution" is not a real solution, but a workaround only. It is possible to do it that way, but it is advisable to avoid it, if you can. It seemed to me that you can't... that's why I mentioned this possibility.
-
If he can't change the data it's the only possible work around... Unless you want to go into creating a temp normalized table where you insert the data before each select... which would seem to be overkill in this case.
-
I looked around a bit more more and was able to find tables where instead of issue there is a delivery date. I tried to pull together what you gave me before into something that would work with these tables. I tried to pull both the sold amount and the sold units in at the same time with this query i was thinking i could use datepart in the case statement instead of between but I havent been able to get this to work. I also thought I could pull across the units information from a second table but I am unsure how this is going to work.
Here is what I have so far
SELECT Q.city_code, Q.outlet_code, SUM(Q.soldjan), SUM(Q.soldfeb), SUM(Q.soldmar), SUM(Q.soldapr), SUM(Q.soldmay),SUM(Q.unitjan), SUM(Q.unitfeb), SUM(Q.unitmar), SUM(Q.unitapr), SUM(Q.unitmay)
FROM
(select tblInvoiceMaster.city_code, outlet_code,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'jan 2004') THEN net_amt_due ELSE 0 END as soldjan,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'feb 2004') THEN net_amt_due ELSE 0 END as soldfeb,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'mar 2004') THEN net_amt_due ELSE 0 END as soldmar,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'apr 2004') THEN net_amt_due ELSE 0 END as soldapr,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'may 2004') THEN net_amt_due ELSE 0 END as soldmay,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'jan 2004') THEN SUM(tblInvoicedetail.delv_unit - tblInvoicedetail.rtn_unit) ELSE 0 END as unitjan,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'feb 2004') THEN SUM(tblInvoicedetail.delv_unit - tblInvoicedetail.rtn_unit) ELSE 0 END as unitfeb,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'mar 2004') THEN SUM(tblInvoicedetail.delv_unit - tblInvoicedetail.rtn_unit) ELSE 0 END as unitmar,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'apr 2004') THEN SUM(tblInvoicedetail.delv_unit - tblInvoicedetail.rtn_unit) ELSE 0 END as unitapr,
CASE WHEN tblInvoiceMaster.invoice_date = datepart(m, 'may 2004') THEN SUM(tblInvoicedetail.delv_unit - tblInvoicedetail.rtn_unit) ELSE 0 END as unitmay
from InvArchDB.dbo.tblInvoiceMaster
join InvArchDB.dbo.tblInvoicedetail
on InvArchDB.dbo.tblInvoiceMaster.invoice_num = InvArchDB.dbo.tblInvoiceDetail.invoice_num
group by tblInvoiceMaster.[city_code], [outlet_code]) AS Q
GROUP BY city_code, outlet_code
ORDER BY city_code, outlet_code
thanks for all your help
chuck
-
I'm gonna say it again :
you need to stop thinking horizontally and start thinking vertically :
Select sum(col1), sum(Col2), datename(m, datecol), ...
from Table T inner join (Select id1, id2, Col1, Col2, datecol) dtDates on T.id1 = dtDates.id1 and T.id2 = dtDates.id2
group by datename(m, datecol), ...
where Year_col = 2004 (make sure this where uses an index)
Let the programmers display the stuff in the app. You shouldn't try to do a pivot table if yo don't need to.
-
so do you think this is just to much to accomplish on the SQL end then? To preface this a bit more I'm not a programmer (yet anyway, I have taken a 2 day SQL course) and the database I am pulling this from isnt designed for what I'm trying to accomplish, but My goal is just to get the data to that final format where it will be easy to analyze from an excel perspective and do it so that when I have to update the analysis in a month it wont take 2 days. I apolagize for all the "hand holding" i need to get the answer I am looking for but I am just pretty new to SQL and i am trying to get the server to complete as much of the calculation as possible.
Im going to try it the way you suggest and I'll see if I can get the pivot table off that dataset
thanks
-
In this case this will correct your query and should give you what you need :
CASE WHEN datepart(m, tblInvoiceMaster.invoice_date) = 5 THEN net_amt_due ELSE 0 END as soldmay,
SUM(CASE WHEN datepart(m, tblInvoiceMaster.invoice_date) = 1 THEN tblInvoicedetail.delv_unit - tblInvoicedetail.rtn_unit ELSE 0 END) as unitjan
-
thanks again
-
HTH.
And here's post #1000 <<<<<<<<<<<<<<<<<<<
-
nicely done
-
can this be adjusted to call for just 2004?
thanks
-
Sorry didn't see this last question untill now...
just add
CASE WHEN datepart(m, tblInvoiceMaster.invoice_date) = 5 AND year_col = 2004 THEN...
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply