September 22, 2009 at 12:45 pm
Hello.
I am new to SQL and I need to create a chart in SSRS for that i need a help to write T-SQl script.
I have to populate the total number of customers who are in their business with years and months, with a condition of
Total customers for the past 6 years..
so the result must show, the total customers < year 2004 + the counts of year 2004 and month Jan till todate.
let say, if they have 5100 Total customers < 2004 .
The result set must shown for ,
2004 Jan would be
5100 + the period of jan 04 (let say 200) which is Total 5300
Then for 04 Feb
5300 + the period of Feb 04 (let say 300) which would be 5600.. up to current year and month..
I think it is something to do with running count..
Could you please help with this..
Thanks
Joe.
September 22, 2009 at 1:14 pm
do you have any table layouts, data that you could post?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 22, 2009 at 1:30 pm
To get the best help in response to your request for help, read and follow the instructions in the first article i have referenced in my signature block regarding asking for assistance.
I think you will be surprised at the responses you get if you do.
September 22, 2009 at 1:33 pm
My script
Select
YEAR(Eligibility.IntakeDate) as Years
, Month(Eligibility.IntakeDate)as Month
, Count(distinct Customer.Customer_ID) as TotalCustomers
FROM dbo.Customer
INNER JOIN dbo.Eligibility
ONCustomer.Customer_ID = Eligibility.Customer_ID
WHERE
(
(Eligibility.IntakeDate is NULL) OR YEAR(Eligibility.IntakeDate) > 2004
) AND
(
(Eligibility.FileClosedDate IS NULL) OR YEAR(Eligibility.FileClosedDate) > 2004
)
Group by
YEAR(Eligibility.IntakeDate), Month(Eligibility.IntakeDate)
ORDER BY YEAR(Eligibility.IntakeDate)
I want the result set to show, the total customers < year 2004 + starting with Jan of 04, like the below listed.
YearMonthCustomerTotal Customers
< 2004 customers total2500
20051232523
20052142537
20053242561
20054232584
20055272611
20056372648
20057222670
20058202690
20059182708
200510182726
200511172743
200512172760
20061262786
20062332819
20063312850
20064262876
20065272903
20066252928
20067162944
20068302974
20069222996
200610403036
200611383074
200612133087
20071273114
20072283142
20073213163
20074303193
20075303223
20076253248
20077293277
20078253302
20079473349
200710283377
200711143391
200712293420
20081293449
20082333482
20083413523
20084473570
20085373607
20086273634
20087353669
20088233692
20089383730
200810523782
200811303812
200812233835
20091283863
20092353898
20093453943
20094574000
20095764076
20096844160
20097564216
20098544270
September 22, 2009 at 1:37 pm
You can use a CTE to accomplish this, but I think you should read the link provided by Lynn first.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply